Query latest date

M

Mack Neff

I have a multi-table database the maintains repair records. There are
multiple records in TBLService for each record in TBLEquip.

I want to query these two tables to pull out only the most recent service
record (date) for each piece of equipment.

I've tried MAX, but can't get it to work, perhaps my syntax is wrong -
 
J

Jeff Boyce

Mack

Since we're not there, and can't see your tables or the fields, you'll
probably need to post a copy of the syntax you DID use, to help folks here
get an idea what might not be working.

And by "can't get it to work" do you mean nothing happens, or errors happen
(if so, what do they say), or you get an answer but its wrong, or ...?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
M

Mack Neff

Query has includes 2 tables: TblSN and TblService.
Four fields: SN (TblSN); Service Complete, TechID, Comments (all from
TblService).
Service Complete is the date field.

All four fields are selected to "Show"
Criteria under Service Complete, I tried "Max" and I tried "Max[Service
Complete]"

Either results in error message: "Data type mismatch in criteria expression"
 
J

Jeff Boyce

If you are using 4 fields "all from tblServer", why do you have two tables
in the query?

If you are trying to find the maximum date of something, take a look at the
Totals query (see Access HELP).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Mack Neff said:
Query has includes 2 tables: TblSN and TblService.
Four fields: SN (TblSN); Service Complete, TechID, Comments (all from
TblService).
Service Complete is the date field.

All four fields are selected to "Show"
Criteria under Service Complete, I tried "Max" and I tried "Max[Service
Complete]"

Either results in error message: "Data type mismatch in criteria
expression"


Jeff Boyce said:
Mack

Since we're not there, and can't see your tables or the fields, you'll
probably need to post a copy of the syntax you DID use, to help folks
here get an idea what might not be working.

And by "can't get it to work" do you mean nothing happens, or errors
happen (if so, what do they say), or you get an answer but its wrong, or
...?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
J

John W. Vinson

All four fields are selected to "Show"
Criteria under Service Complete, I tried "Max" and I tried "Max[Service
Complete]"

Use a criterion on [Service Complete] like:

=(SELECT Max([Service Complete]) FROM tblService AS X WHERE X.SN=
TblService.SN)

assuming that SN is the link to the main table.

John W. Vinson [MVP]
 
G

Guest

4 fields, 2 tables
TblSN
TblService
Please reread the post below:

Jeff Boyce said:
If you are using 4 fields "all from tblServer", why do you have two tables
in the query?

If you are trying to find the maximum date of something, take a look at the
Totals query (see Access HELP).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Mack Neff said:
Query has includes 2 tables: TblSN and TblService.
Four fields: SN (TblSN); Service Complete, TechID, Comments (all from
TblService).
Service Complete is the date field.

All four fields are selected to "Show"
Criteria under Service Complete, I tried "Max" and I tried "Max[Service
Complete]"

Either results in error message: "Data type mismatch in criteria
expression"


Jeff Boyce said:
Mack

Since we're not there, and can't see your tables or the fields, you'll
probably need to post a copy of the syntax you DID use, to help folks
here get an idea what might not be working.

And by "can't get it to work" do you mean nothing happens, or errors
happen (if so, what do they say), or you get an answer but its wrong, or
...?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a multi-table database the maintains repair records. There are
multiple records in TBLService for each record in TBLEquip.

I want to query these two tables to pull out only the most recent
service record (date) for each piece of equipment.

I've tried MAX, but can't get it to work, perhaps my syntax is wrong -
 
G

Guest

Thanks.
That works.
Mack

John W. Vinson said:
All four fields are selected to "Show"
Criteria under Service Complete, I tried "Max" and I tried "Max[Service
Complete]"

Use a criterion on [Service Complete] like:

=(SELECT Max([Service Complete]) FROM tblService AS X WHERE X.SN=
TblService.SN)

assuming that SN is the link to the main table.

John W. Vinson [MVP]
 
J

Jeff Boyce

Thanks, I missed that the first one came from tblSN. My question still
stands ... on what field are you joining the two tables?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Mack Neff said:
4 fields, 2 tables
TblSN
TblService
Please reread the post below:

Jeff Boyce said:
If you are using 4 fields "all from tblServer", why do you have two
tables
in the query?

If you are trying to find the maximum date of something, take a look at
the
Totals query (see Access HELP).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Mack Neff said:
Query has includes 2 tables: TblSN and TblService.
Four fields: SN (TblSN); Service Complete, TechID, Comments (all from
TblService).
Service Complete is the date field.

All four fields are selected to "Show"
Criteria under Service Complete, I tried "Max" and I tried "Max[Service
Complete]"

Either results in error message: "Data type mismatch in criteria
expression"


Mack

Since we're not there, and can't see your tables or the fields, you'll
probably need to post a copy of the syntax you DID use, to help folks
here get an idea what might not be working.

And by "can't get it to work" do you mean nothing happens, or errors
happen (if so, what do they say), or you get an answer but its wrong,
or
...?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a multi-table database the maintains repair records. There are
multiple records in TBLService for each record in TBLEquip.

I want to query these two tables to pull out only the most recent
service record (date) for each piece of equipment.

I've tried MAX, but can't get it to work, perhaps my syntax is
wrong -
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top