Max of Date - complex query

[Julia Boswell]

I've had a read through several postings and although there are lots of
discussions on this topic, I can't find a resolution to my particular
problem, so would really appreciate some help.

I've got 2 tables:

SerialNo
DespatchHistory
DespatchLocation

SerialNo has [SerialNoID] and [Status] as its fields
DespatchHistory has [DespatchID], [SerialNo], [DateDespatched],[LocID]
[DespatchRef] as its fields
DespatchLocation has [LocID], [LocName] and [LocType] as its fields

There's a one to many relationship between the serial number and the
despatch history and a one to many relationship between despatch location
and despatch history.

I want a query that displays the last despatch history record for a serial
number with all of the fields in the despatch history table.

If I use max of DateDespatched it will only work if I don't use any of the
other fields in the despatch table. As soon as I add any other fields I get
multiple records. I've tried using a base query to determine which is the
max date, but that still doesn't work because ultimately I still need the
fields from the despatch history and as soon as I add them in, wherever I
add them, the query fails.

Any ideas anyone?

Thanks in advance

Julia

 

[Allen Browne]

This article describes 4 approaches:
http://www.mvps.org/access/queries/qry0020.htm

The subquery would be best. It can return the ID value. You can then use
that query as the source for another.

 

[Julia Boswell]

That seems to have done the trick. Thanks very much. I'd missed out the
linking of the date fields from the base query to the main query, hence the
reason it didn't work.

Thanks!

This article describes 4 approaches:
http://www.mvps.org/access/queries/qry0020.htm

The subquery would be best. It can return the ID value. You can then use
that query as the source for another.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I've had a read through several postings and although there are lots of
discussions on this topic, I can't find a resolution to my particular
problem, so would really appreciate some help.

I've got 2 tables:

SerialNo
DespatchHistory
DespatchLocation

SerialNo has [SerialNoID] and [Status] as its fields
DespatchHistory has [DespatchID], [SerialNo], [DateDespatched],[LocID]
[DespatchRef] as its fields
DespatchLocation has [LocID], [LocName] and [LocType] as its fields

There's a one to many relationship between the serial number and the
despatch history and a one to many relationship between despatch location
and despatch history.

I want a query that displays the last despatch history record for a serial
number with all of the fields in the despatch history table.

If I use max of DateDespatched it will only work if I don't use any of the
other fields in the despatch table. As soon as I add any other fields I get
multiple records. I've tried using a base query to determine which is the
max date, but that still doesn't work because ultimately I still need the
fields from the despatch history and as soon as I add them in, wherever I
add them, the query fails.

Any ideas anyone?

Thanks in advance

Julia