Go Back  DVD Talk Forum > General Discussions > Tech Talk
Reload this Page >

Access Experts: Need your help

Tech Talk Discuss PC Hardware, Software, Internet and Other Technology

Access Experts: Need your help

Old 04-04-08, 09:46 PM
  #1  
DVD Talk Legend
Thread Starter
 
Join Date: Jul 1999
Location: The L.A.
Posts: 18,122
Likes: 0
Received 1 Like on 1 Post
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.
Old 04-04-08, 10:47 PM
  #2  
X
Administrator
 
X's Avatar
 
Join Date: Oct 1987
Location: AA-
Posts: 10,948
Received 48 Likes on 39 Posts
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.
Old 04-05-08, 09:57 AM
  #3  
DVD Talk Legend
Thread Starter
 
Join Date: Jul 1999
Location: The L.A.
Posts: 18,122
Likes: 0
Received 1 Like on 1 Post
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

Last edited by DodgingCars; 04-05-08 at 10:11 AM.
Old 04-05-08, 10:09 PM
  #4  
X
Administrator
 
X's Avatar
 
Join Date: Oct 1987
Location: AA-
Posts: 10,948
Received 48 Likes on 39 Posts
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.
Old 04-06-08, 10:51 AM
  #5  
DVD Talk Legend
Thread Starter
 
Join Date: Jul 1999
Location: The L.A.
Posts: 18,122
Likes: 0
Received 1 Like on 1 Post
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
Old 04-06-08, 06:29 PM
  #6  
DVD Talk Legend
Thread Starter
 
Join Date: Jul 1999
Location: The L.A.
Posts: 18,122
Likes: 0
Received 1 Like on 1 Post
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.

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Thread Tools
Search this Thread

Archive - Advertising - Cookie Policy - Privacy Statement - Terms of Service - Do Not Sell My Personal Information -

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