Database Structure in relation to Calculated Fields

[Dorothy]

I'm working on my first Access databse, have taken classes, but am not grasping the general structure needed for calculations and reports in my project.
I have tables and relationships set up and confirmed correct. Basically, I have a customer table, a main order table and item table joined with an order detail table.
I have to calculate the total for each order(Sum of Quantity*Price), calculate a service fee, tax and grand total.
I wrote a query that calculates the order total, and based a form on this.
Then I wrote a query based on that query that groups by order number and calculates the service fee and tax from the order total.
Then... I wrote a query based on that query that calculates the grand total (Order total + Service fee + tax)

Is there a simpler way to do this?

I need to display these calculated fields on my form, and on 6 different reports (by date, by week, by customer, by customer type,etc.)



It doesn't make sense to re-create the calculations on each individual form and report, since it's the same data.

I am looking for a big picture answer on the correct way to set up the queries, forms and reports in a simple database. Any insights would be appreciated.

[June7]

Reports can show order detail and do summary calculations by using report Grouping & Sorting with calcs functionality.

Might be able to build one report and open it with the particular filter criteria desired. If one report won't serve, build one with all the calcs done then copy/paste the others and set criteria specific for the desired output.

[Dorothy]

Thank you. This gets me headed in the right direction.

[June7]

I hope so. It isn't the calculations that cause the complication, it is the different groupings you want the data arranged in that complicates and might necessitate multiple reports. The reports could use the same table or query as data source. The reports would do the summations based on the groupings designed in the reports.

[Dorothy]

I am currently doing all this in Excel with various pivot tables to extract the data, so I know how the information needs to be grouped for the reports. One issue I'm still unsure of is the multiple calculations, since queries and reoprts can not perform a calculation on a calculated field. I need a total$ (sum of items$) for each order, then need to calculate the tax, service and sum of order total$ + tax + service. Do I need to use the series of queries listed above in order to get this data on my reports? An invoice where several dollar amounts are added together to get a grand total seems pretty basic. Searching the web, I have not found any instructions on how to do this.
For example, one of my Excel pivot reports has rows for date, Billed or Cash, and columns for order $, Service, Tax Grand total with the columns totals at the bottom. (I run this for a one week period). It seems that I would need about 6 cascading queries to do this - or am I missing something?

[June7]

Think I would have to work directly with data to figure out. I seldom have to do summary calcs in my database. I do have some elementary 'accounting' type functionality in db that is a financial summary and several queries are required. Was built years ago, running fine without any attention from me and users get the reports. Probably the only facet of the db that hasn't needed adjustments over the years.

Yes, might involve a series of dependent queries to achieve the final output.

[Dorothy]

I'm still trying to figure out how to structure this. I have a query (based on a query, based on a query) so that I have fields for the sum of Item $, sum of Service $, Sum of Tax$ and on the final query the sum of Item +Service + Tax. (Grand total). I have included fields from several tables that will be needed in my reports (Name, Billing, Division, Order #, etc.).
All of my reports will have these $ fields.
I have 4 reports that need run time parameters of FY, Period and Week.
1.Group by date
2 Group by date, then billing type (A and B), sub totals by date, GT for billing types for week.
3 Filter to show only billing type A, detail list of orders
4. Filter to show only billing type B, summary of totals by division.

I have 2 reports that need run time parameter of a single date, one more report that will be run by month (start date, end date)
1. Total by billing type, grand total
2. Detail list of all orders
Monthy - filter by employee

Do the run time parameters need to be on the query? If that's the case is it best to make two copies of my final query and add a different run time parameter to each?

[June7]

The parameters can be in the query or can be passed by code when the report is opened. In both cases I recommend the criteria be input on form and parameters refer to form controls for input. I never use query parameter input popup prompts.

DoCmd.OpenReport "reportname", , , "datefield=#" & Me.tbxDate & "#"

Filtering for FY, Period, Week will be more complicated. Need a field in query that has appropriate identifier for each record (FY, week number/year, quarter number/year, etc) that criteria can be applied to.

[Dorothy]

I have been reading about using form controls to open reports, and know how to add a button to open a report. I'm sorry but I don't know where I would enter the code you list, or understand the =#" & Me.tbxDate & "#"

On the properties sheet, I can get to On Click ... to add a Where Condition. Is this where the code gets entered? Say I wanted to run the report for one date, and want records where [Billing]="Personal". What would the code be?

Thank you for your patience.

[June7]

My suggestion is code to go in a VBA procedure. Review http://office.microsoft.com/en-us/ac...010341717.aspx