SQL Query Question (Access)

[Sparky Arbuckle]

I am trying to do a SQL query that involves numerous tables and am
running into a problem.

First I am executing a query that returns the most recent model that is
in a particular location:

Location - NewestModel

A1 - A
A1 - B
A2 - A
A3 - A
A3 - B
A3 - C
A4 - A
A5 - A
A5 - B
A5 - C
A5 - D
A5 - E

_______________________
A1 - C
A2 - A
A3 - C


I am able to get those results with no problem using the following SQL
statement:


SELECT tblProduct.Location, tblProduct.ProductType,
tblProduct.ProductNumber, Max(tblProduct.NewestModel) As
MaxOfNewestModel FROM tblProduct WHERE tblProduct.Location = "A1" AND
(tblProduct.ProductType = "CD") GROUP BY tblProduct.Location,
tblProduct.ProductType, tblProduct.ProductNumber;

How would I go about hooking up another table to this and instead of
retrieving the max for NewestModel, have all the revisions to the new
model listed? I'll explain.


What if I wanted to bring in another table (tblRevision) and achieve a
result that is:


A1 - C - C1
C2
C3
A2 - A - A1
A3 - C - C1
C2
C3
C4

I have tried to simply add tblRevision.RevisionNumber to the SQL from
above but instead of the MAX NewestModel it is retrieving all of them.
I'm thinking that I need to save the first query in Access and then
include it in a new query.

Am I missing something? Any suggestions?

 

[W.G. Ryan MVP]

If you do an inner join on tblProduct and tblRevision (I'm guessing on
ProductNumber), then you'll get each product and all of the corresponding
revisions that match it. Is that what you're looking for?