How to best shape the data structure of Monthly Sales Budget (1 Viewer)

[hamidreza.sajjadi]

hello.
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 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.
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:

and here is the schema of my current form to gather sales data from users in a form named "frm_BudgetgDataEntry"

Thank you in advence

 

[arnelgp]

try you New table first, then adjust later.
to fill that table with data from your existing table, you will be needing a Union Query that will
be the source of your Insert Query.

 

[Minty]

You will almost certainly want to add a MonthYear or Period field to allow for different years, unless you are going to overwrite the data each time?

 

[Pat Hartman]

Normalize the data. Then use crosstabs to pivot it for reporting.
Here is a sample of what I call a bound denormalized form. It is based on queries that are joined to gether. The form, even though it looks like a crosstab, is updateable because it is based on queries of the normalized table that are updateable.

Bound Denormalized Form

I've left the old versions here but ALWAYS go to the last post to get the current version. This database contains two examples (using the same technique) of how to take data that is properly normalized and display it so that it looks like a spreadsheet (rows displayed as columns) but is...

www.access-programmers.co.uk

 

[Steve R.]

Who in your organization will be using the data?
If you haven't already, you have to ask the intended user what data they would want to enter/see.
After determining what the users would want, as @Pat Hartman wrote, you would then need to normalize the data.
The questions above are rhetorical, I'm not expecting a response.

 

[hamidreza.sajjadi]

You will almost certainly want to add a MonthYear or Period field to allow for different years, unless you are going to overwrite the data each time?

yes. but you know? there is big problem here and it is that users are not supposed to enter year and month for each sales prediction!. for example if they are predicting sale budgets for 12 months, they are supposed only to minds entering the sales value, not months names and at the form there should be just 12 text boxes for sales value

 

[hamidreza.sajjadi]

would you please explain in more detail or refer me to some articles about it?
i can't use a normalized table as a row or control source for my form. it is matter of making simplified data entry form for users.
@Pat Hartman

 

[plog]

it is matter of making simplified data entry form for users.

Usually the data is the most important part to people working with a database. I mean, what's the point of having amazing, art-like forms if the data just gets thrown into a hole from which it can't be retrieved?

Here's what your workflow should be--> Tables then Reports and the Queries needed to feed them data and then finally, forms. You need to be sure that your tables capture everything you need and that you can get the data out as you need before you start working on how to get the data into them.

Forms don't dictate tables and fields. The data dictates the tables and fields. Normalize your data.

 

[Minty]

yes. but you know? there is big problem here and it is that users are not supposed to enter year and month for each sales prediction!. for example if they are predicting sale budgets for 12 months, they are supposed only to minds entering the sales value, not months names and at the form there should be just 12 text boxes for sales value

I didn't say the user need to enter it.
You are confusing required data and end user interaction. You default the mm/yy to the next year or this year depending on when you are doing this, make sure the user see's it but can't edit it, if you need to.

 

[Pat Hartman]

I guess you didn't look at the sample database I posted. The data is properly normalized but the data entry form shows 12 months at a time. There is only a few lines of code to support this along with 13 queries. One for each month and one to assemble the 12 months into one query.