Selecting the first from multiple records combining two tables

  • Thread starter Thread starter Jelle
  • Start date Start date
J

Jelle

LS,

I have to tables, say A and B. For all records in A I would like to find a
corresponding record in B. Unfortunately I can not do this using a unique
key, so from all records in B that match I would like to pick the first.

Can anyone help me with the logic to follow?

Thx, Jelle
 
Make a first query that does find the multiple matches.


Next, make another query based on the previous one to find the 'earliest'
record, like finding, as example, 'who is the last one to have borrow each
book? ' Take a look at http://www.mvps.org/access/queries/qry0020.htm


Sure, if you don't have any 'date_time' stamp field, you can use a TOTAL
query: GROUP BY on each relevant field making a group, use LAST on each
other fields which is 'not' relevant for making a group.



Hoping it may help,
Vanderghast, Access MVP
 
It sounds like you need a 1 to 1 relationship. Use an autonumber field to
join the tables would probably be easiest. If you need help doing that post
back.
 
Michel,

I guess I did as you said, I first created a query joining the two tables
based on the mutual field and with a constraint on one of the fields, being a
datetimestamp.
This way I selected multiple items for each entry in A, but the date was
always smaller the the constraint date.

Using the outcome of this query, I used the max to find the single record
being most close to the constraint date, being the required one.

Thx, Jelle
 
Back
Top