Relationships

B

burg

Hello,

If I have one quote that relates to many different designs as the designs
can change, how can I then search by quote and only report on the desgn that
was created last? Basically I want it to ignore all of the other designs that
relate to the quote and only use the newest one.

Would I have to insert a date created into the design table when the design
is created and search by max date or something?

Thanks
 
M

[MVP] S.Clark

This is the Microsoft Access query newsgroup. I can not tell if you are
discussing an Access Database, or some other software. Please elaborate.
 
J

John Spencer (MVP)

Basically, the answer is yes.

You have to include something in the record that tells you when the record was
created. It can be a datetime or it can be an artificially created number that
is one up for each designs within each quote.
 
B

burg

Hi John,

What criteria would I have to put in the query design. Is it max?
Or is there another way of doing it?
This is in Access XP y the way.
Thanks
 
J

John Spencer (MVP)

Hard to say since you didn't give us any field names or table names.

Basic technique using two queries.
First query is a Totals query where you get the MAXIMUM date for each design and
then use that in a join query.

Something like:

SELECT Design.QuoteID, Max(Design.ActionDate) as LastDate
FROM Design
GROUP BY Design.QuoteID

Save that as QryLastDesign

Now, use the above query as one of the "tables" in your query.

SELECT *
FROM Quotes as Q INNER JOIN QryLastDesign as D
 
Top