New to Access 2003 - Matching Text

[DanV]

Forgive my potential redundancy in asking this question, but being new (just
started today) to Access I couldn't quite follow some of the responses in
other threads that might be answers.

I have two tables with identical field data types. Basically, each is a list
of product numbers with year over year data for each product. Most but not
all of the product numbers match between the two tables. I want to compare
the data between the years for each matching product number.

How do I have Access match the text on the product number field between the
two tables so that I can compare the remaining data?

 

[Jeff Boyce]

Dan

If you have multiple tables "with identical field data types", you probably
have a spreadsheet, not a relational database. If each table holds a year's
data, you most certainly are trying to force Access to "eat" 'sheet data.
This approach, while possible, will make both you and Access work a lot
harder than you need to.

If you have [Products] and you have [ProductsAvailableForAGivenYear], you
could use these two tables to find products available one year and not
another. No need to compare between "year" tables.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[DanV]

If you have [Products] and you have [ProductsAvailableForAGivenYear], you
could use these two tables to find products available one year and not
another. No need to compare between "year" tables.

Can you explain how?

 

[Jeff Boyce]

Dan

Here's an example:

tblAvailableProduct
AvailableProductID
ProductID (foreign key from tblProduct)
YearAvailable

1 99 2006
2 99 2007
3 99 2008
4 17 2006
5 17 2008

If you create a query that looks for all products in 2008, you'll see
productID 17 & 99. If you create a second query that looks for all products
in 2007, you'll only see productID 99. If you then create a third query
that looks for product(IDs) in the first query that are not in the second,
you'll see productID 17 ... the one that didn't happen in 2007.

Regards

Jeff Boyce
Microsoft Office/Access MVP

If you have [Products] and you have [ProductsAvailableForAGivenYear], you
could use these two tables to find products available one year and not
another. No need to compare between "year" tables.

Can you explain how?