A large problem

[dan54]

Here is my problem:

i have three tables, each containing stock for a music shop (one containing the stock of records, one containing the stock of CDs, and one containing the stock of paper items). I also have a 'Orders' table. When a new order comes in, i enter it into the Orders table using a form (a record for each item that the customer has ordered).

I have created three queries, each one searches for the ordered items in each of the three stock tables.

To display the information in one report, i have created two sub reports within the main report (as you cannot have three record sources in one report). The record source for the main report is the query that finds orders from the Records Table, the record source for subreport 1 is the query that finds orders from the CD Table, and the record source for subreport 2 is the query that finds orders from the Paper Items table.

Within these three queries there is a 'Price' field that shows the price of the item that the customer has requested. There is one of these fields in each sub report, and one in the main report (showing the prices of the records, CDs and paper items requested). What i want to do is add the prices of all the records, CDs and Paper items the queries find (and consequently show in the report and sub reports) together, to calculate a grand total of all the items requested at the bottom of the report.

Any help will be greatly appreciated

cheers

Dan

 

[Travis]

My first Gut Reaction is to ask this question:

Why do you have a different table for Records/CD's/Paper Items?

They are all products.

 

[dan54]

because they have different fields

can you give me any help??

 

[Rich]

You certainly can make a report from three tables by using a union query, although I suspect it's not practical for you so: Sum the relevant fields in each subreport footer, on the main report just put a text box with the data set to =[subreport1]![ControlName]+[subreport2]![ControlName] etc.

 

[dan54]

Cheers for the help

 

[chrismcbride]

I feel bound to point out the Travis is correct. They all are products and, as such, should be stored in the same table. You should adapt your table structure to hold all the products. Once this is done you will find that all other tasks relating to this DB will be simplified.
Chris

 

[dan54]

Yes, but each type of item (the records, CDs, and paper items) all have fields that are not relevant to the other items (for example, the field Record Code will not be relevant to the paper items).

 

[chrismcbride]

Would not each product have a code? Is it not just semantics to make a distinction between RecordCode and Papercode? If this is all true, could you not say the same about most of the other fields that seem different?

I think that you could keep all the data you really needed about all three of these type of products in one table if you looked critically at the properties of each that you want to store.

Chris

 

[Pat Hartman]

Try to visualize the variety of products sold by Sears. They are ALL stored in the same product table. Change your table structure and simplify your life.

[This message has been edited by PatHartman (edited 02-22-2002).]