Comparing Content of Tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good afternoon, and I hope someone here can help me, because I've been
pulling my hair out!

Basically, I need to compare the contents of two tables (or the results of
two queries) and display items that are in Table 1 but NOT in Table 2.

We have a database of orders & products, and having extracted the raw data
into access I have managed to create the simple queries needed to display
"Products ordered in 2003" and "Products ordered in 2004" and "Products
ordered in both 2003 & 2004". The problem I've had is a) that none of these
queries will show me a simple list of products ordered without duplication
(ie every order for a specific product will result in the product appearing
in the query result that many times) and b) I can't for the life of me fathom
how to create a query that would show me products ONLY ordered in 2004,
rather all the ones that were orderd in 2004, of that makes sense.

The tables list Product number, order number, and order date. How do I go
about removing the duplicate entries, and in displaying items that were
ordered in 2004 and NOT in 2003?

Thanks in advance,

Matt Volatile
 
Let's say that you have Query1 listing all orders in 2004 and Query2 listing
all orders in 2003. Each has the same columns; productId, orderNumber and
orderDate. The SQL to list all products in Query1 but not in Query2 will be
SELECT DISTINCT productId FROM Query1 WHERE productId NOT IN (SELECT
DISTINCT productId FROM Query2)

Hope This Helps
Gerald StanleyMCSD
 
Excellent! Thank you very much!

Regards

Matt

Gerald Stanley said:
Let's say that you have Query1 listing all orders in 2004 and Query2 listing
all orders in 2003. Each has the same columns; productId, orderNumber and
orderDate. The SQL to list all products in Query1 but not in Query2 will be
SELECT DISTINCT productId FROM Query1 WHERE productId NOT IN (SELECT
DISTINCT productId FROM Query2)

Hope This Helps
Gerald StanleyMCSD
Click to expand...
 
Hi Gerald,

Sorry to ask a supplementary query, but once this DISTINCT query has been
processed, I'd like also to display, per distinct product, the lowest price /
product paid over the entire period, and the total quantity ordered.

I'm concerned because when I did a simple query joining the partno field on
the DISTINCT query with the part no field in my original table and then
opting to display the query.partno and table.price field, there were more
Rows than in the DISTINCT query by itself, so I've obviously made a mistake
somewhere.

I presume I need to employ the "MIN" function combined with DISTINCT - will
this work in the way I need it to, ie give me the minimum price per distinct
product? What's the syntax?

Also, can all of these three actins be run as a single query or do I need to
build each separately?

Aplogies for all the questions but I'm a complete beginner!

Thanks,

Matt Lodder
 
Back
Top