Comparing / Subtracting Tables

[quigongrim]

I'm new to this site and access so please forgive me (and correct me) if I don't use the right terminology.



I have am currently using my employer's Access Database to try and run some sales queries to make my job easier.

I need to create a query or tool to find any records for products that are new to the database (within the last 90 days), and how much they have sold / or not sold during that 90 day period.

I've managed to create a query that shows me all items that have sold more than 0 units but I can't find any records that have not sold, as there is no sales record for me to search in my query.

Therefore I think that I have to do the following (but don't actually know how to do it in Access):
- Find all new products made in the last 90 days (NewProds)
- Find all NewProds that have sold in the last 90 days (Sellers)
- Remove all Sellers from NewProds to show me all items that haven't sold (NonSellers)

Can anyone help?

[jzwp11]

In order to help you, we will need to know the structure of the tables with which you are working and the relationships between those tables. You can provide the table structure in long hand like this:

tblCustomers
-pkCustomerID primary key, autonumber
-txtCustomerName text field

tblProducts
-pkProductID primary key, autonumber
-txtProdNumber

...or you can post a copy of your database (with any sensitive data removed).

Just from your description it sounds like you will need a couple of queries and in one of the queries you will need to join via a left join, more on that once we have more detail on your structure.

[quigongrim]

Thanks for the response, I think I've sussed it. I made one query to show me all new products. One query to show me which of these have sold. And then I found the solution. A third new query:

- In the Database Applications window click on Queries
- Click New
- Select "Find Unmatched Query Wizard" and followed the instructions

Might be a bit long-winded but it worked!

[jzwp11]

Glad you got it worked out!

[weekend00]

Please mark it as solved

[oldman]

The simple solution is to link the two tables you have for products and sales data. Use whatever the unique identifier is for each table that uniquely identifies each product as the link. By using a left join, one with the arrowhead on the right hand table, from the table that lists all products to the table that lists all sales, you will get a listing of all products and the sales from those that have sales. You can restrict the query to the last 90 days by using the DATE() function in either the creation date of the product or the sales date to set a criteria that says you only want values of "> DATE()-90".
This assumes you will use the asterisk as an output so all fields from both tables will be available for further use.