Funky Query

Hi,

I need to write a query or sub-query, but only use a parameter once.
Take a look at this:


A Z198051 27-AUG-07 27-AUG-07
B Z199111 31-AUG-07 31-AUG-07
C Z215180 17-OCT-07 17-OCT-07
D Z27867 21-FEB-06 22-FEB-06
E Z239557 28-DEC-07 28-DEC-07
F Z230612 01-DEC-07 03-DEC-07
G Z108701 22-NOV-06 23-NOV-06
H Z171480 04-JUN-07 04-JUN-07
I Z143538 02-MAR-07 02-MAR-07
J Z107108 16-NOV-06 17-NOV-06
K Z114553 18-DEC-06 19-DEC-06
L Z168095 24-MAY-07 24-MAY-07
M Z82875 22-AUG-06 23-AUG-06
N Z71657 14-JUL-06 14-JUL-06
O Z55598 19-MAY-06 19-MAY-06


Basically the code (1st column) will be passed in, and I want to
retrieve the ID (second column) for that code with the MAX date (3rd
column).


Problem being that the way the engine for this application is
written,
you can only pass 1 parameter to it. So, I can only pass the code
once, which means I can only use it in the query once......

I try things like this, but no luck:

SELECT id
FROM table1
WHERE date1 = (SELECT MAX(date1)
FROM table1
WHERE code = 'E' AND id LIKE 'Z%');


Any ideas? I've been playing with sub-selects and MAX(date), but it
seems that I still need to put CODE in there twice......


Thanks a bunch.

.

Relevant Pages

  • Re: How do I show all in a count query?
    ... You should be able to just change Table1 ... an easier way to do this would be to create the sub-query and save ... Now, create a new query, add the Users table, and qryTasks to the query grid. ...
    (microsoft.public.access.queries)
  • Re: Displaying row no/Record Counting
    ... EnteredOn Date/Time when the record was added. ... You create a query that contains Table1. ... On each row of your query, you need to count the number of records in Table1 ... In> order to generate an alphabetical listing of these people,> I have to analyze the report in Excel and sort the> spreadsheet based on the client's name. ...
    (microsoft.public.access.queries)
  • Re: How to add to a new field based on information in existing field?
    ... a query to join the two tables. ... For example, Table1 includes fields TestID ... Table2 includes fields Description and DescriptionFr. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Non-updateable query issue
    ... You have an unnormalized table (of course, as it came from a flat spreadsheet instead of a relational source), and is it has 100 columns. ... joined to the master, showing all the records, the query is not updateable. ... > Say Fred deletes record 79 from Table1, and then Betty changes the> phone ... > Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.queries)
  • Re: Non-updateable query issue
    ... Microsoft Access MVP ... When putting together that query, ... "Duane Hookom" wrote: ... Say Fred deletes record 79 from Table1, ...
    (microsoft.public.access.queries)