Max of Date - complex query

  • Thread starter Thread starter Julia Boswell
  • Start date Start date
J

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
 
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!
Allen Browne said:
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.
Julia Boswell said:
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
Click to expand...
Click to expand...
 
Back
Top