Combined Report (1 Viewer)

[D B Lawson]

I have two tables which store products sold. One table holds info on products sold to customers and the other holds info on products sold at functions. It is the same data in both, products, product codes but the sales are linked to either an orders table or and functions table respecitvely. I need a report that shows the total amount of sales regardless of where or how it was sold. I have individual reports but can I combine the information in both tables to create one report?

 

[Atomic Shrimp]

It might be a little late in your project development to be recommending this, but I would keep the whole lot in one table, with an extra field to identify whether each record was 'Orders' or 'functions' - then you can either apply a criteria to that field in a query based on the table to get data relating to just 'orders' or 'functions', or not apply criteria to get the whole lot combined.

HTH

Mike

 

[KevinM]

Good advice Mike.

D B

I would consider this and it may not be too much extra work. A simple Append query from one tables to the other should do the trick.

hth

 

[Atomic Shrimp]

Alternatively, you could use a union query to combine the two tables, but I would strongly recommend the single table approach, for example, if a new type of sales situation arises (let's call it 'Promotions'):

In the multiple table model, you have to add a table to store the promotion sales, you have to redevelop many of your forms, queries and reports to take into account the new table.

In the single table model, you just start identifying the promotional sales records in the table as 'promotions' instead of 'functions' or 'orders' and everything else should still work, if you had any reports grouped by type, they would now simply come out with a new section and so on.

HTH

Mike

 

[D B Lawson]

Thank you both, you are right, of course. The function bit is a recent development and I've not thought it through properly. I'll amend my tables.

Thanks again.

Dawn