Issue pulling information from 2 tables

[hilltop804]

Hello everyone!

So, I have 2 tables with the same structure (one for 2013, one for 2012). Each contains 4 columns (County, Market, UPC, and Sales). What I'm trying to do is make a query that will condense these 2 tables into 1 list containing columns for County, Market, UPC, Current[sales], and Past Year[Sales]. I've tried several setups, but it keeps doubling or tripling duplicate products (since the same UPC can be sold in the same market and county multiple times.)

I can provide screenshots, but I cannot attach the database because it contains confidential business information.

I feel like I'm just missing something and that this should be a pretty easy fix, but I'm just kinda stuck. Any help anyone can provide would be greatly appreciated...

(Attached are screenshots of how I thought the query would work, and an excel spreadsheet example showing a particular item's incorrect totals. The query should output the same overall sales for any particular item as the table, and you can see the that the query total is much higher.)

Thanks,
Brad

 

[plog]

You shouldn't store data in table nor field names. You are essentially doing that by having different tables whose only difference is their name . This makes your issue a table issue not a query issue. All of your data should be in the same table.

Here's what you should do:

1. Make a copy of Sales (this is just to protect it from any errors you might make in future steps).

2. In design view of Sales add a numeric field named 'SalesYear' which will help differentiate your different years of sales.

3. Create an UPDATE query to make all the values of Sales.SalesYear equal 2013.

4. Create an APPEND data to move data from PYSales to Sales, adding an additional field to make the SalesYear values 2012 (SalesYear: 2012)

After running your APPEND query all the data should be in Sales along with a SalesYear to help you differentiate the two. That is the proper way to store your data.

 

[plog]

I just read what your endgame was--a query with all fields and current and past years sales fields. You still need to do what I said in my prior post, once you do, this is what your query should look like:

SELECT County, Market, UPC, Sum(Iif(SalesYear=2013, [Sales], 0)) As CurrentSales, Sum(Iif(SalesYear=2012, [Sales], 0)) As PastYearSales
FROM Sales
GROUP BY County, Market, UPC;

 

[hilltop804]

How will the append query look any different from the current setup, though? Won't it look and behave the same, but instead of displaying the information, it will change it in the table?

Edit: I see what you're saying. Instead of basically vlookuping the past year sales info, I create and separate row for it and just combine the two tables together, and having a query with group boy's will eliminate redundant information?

 

[plog]

I don't exactly understand what you mean by redundant data. If you get your data into the table structure I outlined, then the query I posted will get you the data like you initially requested.

 

[Geotch]

Don't you need a union query? It joins two tables together. Fairly easy to do. Can throw an example together if google doesn't explain it clear enough.

 

[plog]

A union query is a coat of paint on a car with body damage. He needs to fix the real issue and not work around it. Otherwise every year more and more rust will build up which he will have to figure out a way to work around.

Properly structuring the data is the correct solution.

 

[hilltop804]

Your idea of combining the tables worked great. Thanks for the help!