SQL Query Question (Access)

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?

.

Relevant Pages

  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: How do the records retrieve from data base when using sql statements?
    ... file size changes significantly the query optimizer may decide to get the ... unless you have select/omit criteria in a logical that you want SQL to ... And I'd recommend using the SQL against the physical file, ... on the physical file only, ensures retrieving rows according ...
    (comp.sys.ibm.as400.misc)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- table structure?
    ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... I have one report complete, ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)