Advice - Looking to track monthly revenue by account

[vad77]

Hope everyone is doing well. I am looking for a little design help with my accounts database. My current db main table is called tbl_accounts and part of that table is ytd revenue, ytd margin and the margin percentage is calculated in a query. I have fields built up to 2018, not a good design I know. That is why I am looking to move my revenue numbers into their own table and set up a relationship to the main accounts table via siteID. What I show currently on the accounts form is the current fiscal year totals which I will continue to show. Since I receive the revenue on a monthly basis that is how I would like to input that data and then the totals will automaticlly update.
The trouble I am having is which is the best way to design my revenue table. My first idea was to create a table with each month being a field; cyID, siteID, CalYr, Rev_Oct through Rev_Sept, (Our fiscal year starts Oct 1 and ends Sept 30), Mar_Oct - Mar_Sept.
My second idea was to create a table as follows; cyID, siteID, CalYr, Qtr, Month, Rev, Mar.


Another idea I had was to create a table fore revenue and a table for margins and linking them together and with the main accounts.
Revenuetbl: revID, siteID, Qtr, Month, Rev
MarginTbl: marID, revID, siteID, Qtr, Month, Margin
The results I am trying to acheive is to show YTD numbers for each account on my accounts form and also if requested to show monthly numbers. I also plan to create some graphs and charts with these numbers.
I think this should be simple but just a little stumped on design.

Thanks in advance for your responses

Vito

[June7]

Should not save ytd calculated data into table. Calculate when needed.

Any of those approaches could be made to work.

The first allows for a nice report output but will make ytd summary calcs harder. Graphing could be more difficult as well.

The second approach is sufficiently normalized structure and will be easiest to generate ytd calculated data. Use CROSSTAB query to produce output like the first approach.

I think the last approach is breaking up the structure too much.

[vad77]

Thanks June7, currently the only calculation I have is the margin percentage [margin$]/[revenue$] and that is calulated in the query then I show it on the form. With this new table there are a few calculations I need to show. Quarterly total /account, YTD total per account and then the total for all accounts. You stated to use a CROSSTAB query in your response I have never created a CROSSTAB query. Once I build my table and relationships I will attempt to build a CROSSTAB query. I'll search the forum for CROSSTAB query first before I post any questions if I have trouble.

[June7]

Access Help can point to some references about building queries. There is a wizard for CROSSTAB.

CROSSTAB is a powerful query tool but can be difficult to build a report based on CROSSTAB because of the dynamic nature of the fields generated, especially for date/time. Review http://allenbrowne.com/ser-67.html

[vad77]

Understood, thanks again June7.

Vito