DVD Talk Forum

DVD Talk Forum (https://forum.dvdtalk.com/)
-   Tech Talk (https://forum.dvdtalk.com/tech-talk-10/)
-   -   Access Experts: Need your help (https://forum.dvdtalk.com/tech-talk/528875-access-experts-need-your-help.html)

DodgingCars 04-04-08 10:46 PM

Access Experts: Need your help
 
How hard would it be to compare the data in 2 tables and get a variance report from Access? I'll give an example of what I want:

Table1 contains UPC, Qty, Price, Extended Price
Table2 contains UPC, Qty, Price, Extended Price

I want to get a variance on Qty, Price, and Ext Price when the UPC in Table1 matches the UPC in Table2.

Ideally, I'd like to take it even further and get the variances on the aggregation of those UPCs, so it'd really be the variances on:

The sum of qty, the Avg of Price, and the Sum of Extended Price.

This seems pretty simple to do, but I'm no expert in Access -- so I was hoping someone here could steer my in the right direction.

X 04-04-08 11:47 PM

You would just do a join on the two tables by UPC and then do two calculated fields of the difference between the two price fields for the other two columns of output.

Start with that and then work up to doing total type queries that would aggregate the differences. It's pretty easy.

DodgingCars 04-05-08 10:57 AM

Thanks.
I think I figured it out -- including the Group Bys and Sums. Now I have another questions.

Is there a way I can see BOTH the additional UPCs from Table1 and the additional UPCs from Table2? When I was created the Join, I saw that you could specific "All of Table1, and only those records of Table2 where the joins are equal" and vice versa. But how would I see both?

Would I have to create 2 queries?

I want my final result to be:

UPC | QTY1 | QTY2 | QTY Difference
--------------------------------------------
123 | 5 | 4 | 1
456 | 4 | 4 | 0
777 | 9 | 2 | 7
102 | 3 | _ | 3
543 | _ | 1 | 1

where 102 is in table1 but not table2 and 542 is in table2 but not table 1

X 04-05-08 11:09 PM

I don't know how you could easily see the results of both tables where the UPCs don't exist in either of them.

You can join two tables and ask for results where the join of the second table results in a null value. And you can do it the other way around. So I suppose you could just join the results of those two queries and get the overall sum of records where one table didn't match the other.

DodgingCars 04-06-08 11:51 AM

Yeah.. I'll keep working on it. Right now I have 3 queries.

Table 1 Matches
Unmatched Table1 UPCs
Unmatched Table2 UPCs

I'm going to see about how I can go about consolidating these into 1 report/query result

DodgingCars 04-06-08 07:29 PM

I figured it out. I created a 4th Query that combined all 3 other queries into one using UNION statement. I was then able to get everything pretty much the way I wanted in 1 variance query.

Thanks so much for helping me get started.


All times are GMT -5. The time now is 11:45 AM.


Copyright 2021 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.