how to gather categorized monthly sales in ms access form and retrive them as new record per month-product-category

Question

I have designed a sales budgeting system using "MS Access" in which users can assign sales Value in addition of sales price for different months along with some other functionality.

Here is the schema of the main table (tbl_SalesMonthing) over which numerous forms store data. As-Is structure

As it is obvious, The current cross-tabular structure of the "tbl_SalesMonthing" table poses many challenges. Its complexity impacts query performance and makes forms difficult to maintain. For example, joining multiple tables to retrieve monthly data is very time-consuming and requires to make table Unpivoted by using UNION, and besides,form logic becomes convoluted.

Thank you in advence

Therefore, I'm seeking alternative table structures to enhance efficiency and simplify database maintenance. I'm particularly interested in exploring a design that utilizes VBA and textboxes to capture monthly data, potentially eliminating the need for a cross-tabular structure. here is the conceptual model of Table: Conceptual Structure and here is the schema of my current form to gather sales data from users in a form named "frm_BudgetgDataEntry" Data Entry Form

Answer 1

I think the design of your "conceptual structure" (with 1 field 'MonthName' instead of 12 monthvalue fields) is the correct one to use for the storage of your data.

The image of your input form is no very clear (in Arabic?). In general, users will like to do their input in crosstab format, but then you should organise the form to save the crosstabbed input values correctly to the non-crosstabbed storage table.

This will involve some VBA code to generate the necessary SQL instructions. You could base the input form on an auxiliary table with the current design, and then use a series of append queries to add the data from the aux table to the "base" table.

E.g. to add january values from auxTable to baseTable:

INSERT INTO baseTable(ProductCode, PricingType, Destination, MonthName, Value) SELECT ProductCode, PricingType, Destination, 1, [1] FROM auxTable

(or create similar query using the append query design grid).