Problems Joining Tables

[sys_daw]

I have two tables, one that contains sales data for the current year and the other contains sales data for the previous year. The data in both tables relate to one customer. What I want to do is show the total values for both the current year and previous year on the same row.

In the attached db the query qryFinal shows the expected format of the output but the total value for the previous year is incorrect. The problem is due to the way that the tables are joined but I am unable to amend them to produce the correct data.

 

[Scooterbug]

Use a union query:

SELECT [Cust_alpha], [Product_Line],[Cust_type], [Analysis_Code], [Total_value],"LastYear" as [FromYear]
FROM lastyear
Union select
[Cust_alpha], [Product_Line], [Cust_type], [analysis_Code], [Total_value],"ThisYear" as [FromYear]
from currentyear

I added the FromYear so if you need to tell what year, you can.

Also, you might want to revisit your table design. When you have repeating fields (m1_Units, m2_units, etc) that shows that the table is not normalized. What you should have is two tables with a one to many relationship. You should have one table that stores the order or whatever it is you want and then a separate table to list each product and their price. Something like this:

tblOrders
OrderID (Primary Key)
CustomerID (Foreign Key)
OrderDate

tblOrderDetails
OrderDetailID (Primary Key)
OrderID (Foreign Key)
ProductID (Foreign Key)
Quantity
Price

Getting a query of total items ordered by customer with the above table setup is a piece of cake then.

 

[sys_daw]

Thanks for the advice ScooterBug and the union query however the problem is still there.

The result needs to be that each customer (only Oscar in this case) needs to appear on one row therefore showing a sum of M1_Units, M2_Units etc and the sum of the total current year but without showing the product line, customer type or analysis code. The total for the LastYear must be the total for that customer irrespective of whether a particular product sold last year has also been sold this year. However, if the user wants to filter by product_line the total for last year must be the total bought by that customer and product_line. Hope this makes sense.

So the output using the test data should be that for Oscar the total current year is 4776128, the total last year is 5282255 on one line. If the user wanted to filter by product line 01 the result would be 4220334 for the current year and 4111758 for last year along with the _Units totals.

Thanks.

 

[namliam]

This is more of a design issue than a query one, why seperate the years in seperate tables??

Add the data into one table and all your troubles go away...

 

[sys_daw]

This is more of a design issue than a query one, why seperate the years in seperate tables??

Add the data into one table and all your troubles go away...

If a customer buys 2 products this year and bought 15 last year how would I handle the M1_Units etc for those 13 products that have not been purchased this year if the data were all in one table?

 

[namliam]

M1_Units, I guess that is Month 1? you speak of products... what is this?? You dont have this distinction in your second table (last year)

Normaly some table with sequential field names like M1, M2... M12 point to a design issue.
Instead of one record with 12 fields you should have 12 records with a field M1... M12 and 1 value... This is the case in 99+% of all cases.

 

[stopher]

If you follow the advise given by Namliana and Scooterbug about table design then you can easily provide the right output structure using a cross-tab query which will not suffer from the problems you are currently facing.

Chris

 

[sys_daw]

If you follow the advise given by Namliana and Scooterbug about table design then you can easily provide the right output structure using a cross-tab query which will not suffer from the problems you are currently facing.

Chris

Ok, thanks to you all for your help, I will revisit the design and see if the cross-tab query works.