ONE MORE TRY....

[Scott Baird]

Thank you. Sorry for the delay, I hope this message, as old as it is,
still gets reviewed. I'm in the USN, and quite busy with a lot of
different things:

TABLE A
AUTONUMBER
VALVE NAME (i.e., 1-BB-2A)
VALVE TYPE (i.e., Globe, Butterfly, etc.)
VALVE SYSTEM (i.e, Potable water, Flushing water, fuel, etc.)
VALVE PSI (i.e., 700, 1500, 5000, etc.)

TABLE B
AUTONUMBER (USED TO ESTABLISH RELATIONSHIP)
VALVE WORK DATE (Date work completed)
VALVE WORK COMPLETED (i.e., repacked, hydro'd, refurbished)
VALVE WORK HYDRO SAT/UNSAT (i.e. Sat, Unsat)
VALVE WORK PSI TESTED (i.e., 750, 1600, 5250)

A valve usually get worked on about once a quarter, maybe more/less
depending on type valve, so after a while, there is a lot of records
in table B that relate to each valve in table A. The query/report I'm
after is one that will allow me to show all the data from table A with
ONLY the latest "valve work date" (and associated data) from table B
for each valve.

Right now, when I try to use DLAST, what I get is a report that lists
all of the data from table A for each valve. That's fine, but the
system is also putting the latest date in table B as the work date for
each valve. For instance:

- Valve 1 was worked on 1 Jan 2004, 1 Feb 2004, and 1 Mar 2004
- Valve 2 was worked on 1 Jan 2004 only

I need a report that shows valve 1 data/history from 1 Mar only, and
valve 2 data/history from 1 Jan. What it is doing is showing 1 Mar
2004 for every valve, regardless of when each individual valve was
actually worked. Report is a datasheet, listing 20-30 valves on each
page. I know there is probably a better way to explain this, but…

[Dave M]

You need a summation query based on TableB that groups by valve name and
selects the maximum of work date; presumably ValveName is a foreign key in
TableB? The example below shows the latest invoice date for each client,
which is a similar issue:

SELECT Invoices.Client, Max(Invoices.[Invoice Date]) AS [MaxOfInvoice Date]
FROM Invoices
GROUP BY Invoices.Client;

If you then use this query (maybe called qryValveLastService, or something
like that) and use it in a query that also uses TableB, you can join the two
on the ValveName and ServiceDate fields (i.e. ValveWorkDate joined to
MaxOfValveWorkDate) you will select only the service records for the most
recent dates.
"Scott Baird" <sc*********@japan.com> wrote in message
news:c6**************************@posting.google.c om...

Thank you. Sorry for the delay, I hope this message, as old as it is,
still gets reviewed. I'm in the USN, and quite busy with a lot of
different things:

TABLE A
AUTONUMBER
VALVE NAME (i.e., 1-BB-2A)
VALVE TYPE (i.e., Globe, Butterfly, etc.)
VALVE SYSTEM (i.e, Potable water, Flushing water, fuel, etc.)
VALVE PSI (i.e., 700, 1500, 5000, etc.)

TABLE B
AUTONUMBER (USED TO ESTABLISH RELATIONSHIP)
VALVE WORK DATE (Date work completed)
VALVE WORK COMPLETED (i.e., repacked, hydro'd, refurbished)
VALVE WORK HYDRO SAT/UNSAT (i.e. Sat, Unsat)
VALVE WORK PSI TESTED (i.e., 750, 1600, 5250)

A valve usually get worked on about once a quarter, maybe more/less
depending on type valve, so after a while, there is a lot of records
in table B that relate to each valve in table A. The query/report I'm
after is one that will allow me to show all the data from table A with
ONLY the latest "valve work date" (and associated data) from table B
for each valve.

Right now, when I try to use DLAST, what I get is a report that lists
all of the data from table A for each valve. That's fine, but the
system is also putting the latest date in table B as the work date for
each valve. For instance:

- Valve 1 was worked on 1 Jan 2004, 1 Feb 2004, and 1 Mar 2004
- Valve 2 was worked on 1 Jan 2004 only

I need a report that shows valve 1 data/history from 1 Mar only, and
valve 2 data/history from 1 Jan. What it is doing is showing 1 Mar
2004 for every valve, regardless of when each individual valve was
actually worked. Report is a datasheet, listing 20-30 valves on each
page. I know there is probably a better way to explain this, but.

[mar10a]

Will this work – create a query off Table B that is a group by query ,
with the fields AutoNumber and ValveWorkDate -

Autonumber should be set to GROUP BY, and ValveWorkDate to MAX – which
will give the latest work date.

Then create a second query and pull in Table A along with the query
created above. Link the Autonumbers together and you should have a
single record for each Value along with the Maximum work date -

sc*********@japan.com (Scott Baird) wrote in message news:<c6**************************@posting.google. com>...

Thank you. Sorry for the delay, I hope this message, as old as it is,
still gets reviewed. I'm in the USN, and quite busy with a lot of
different things:

TABLE A
AUTONUMBER
VALVE NAME (i.e., 1-BB-2A)
VALVE TYPE (i.e., Globe, Butterfly, etc.)
VALVE SYSTEM (i.e, Potable water, Flushing water, fuel, etc.)
VALVE PSI (i.e., 700, 1500, 5000, etc.)

TABLE B
AUTONUMBER (USED TO ESTABLISH RELATIONSHIP)
VALVE WORK DATE (Date work completed)
VALVE WORK COMPLETED (i.e., repacked, hydro'd, refurbished)
VALVE WORK HYDRO SAT/UNSAT (i.e. Sat, Unsat)
VALVE WORK PSI TESTED (i.e., 750, 1600, 5250)

A valve usually get worked on about once a quarter, maybe more/less
depending on type valve, so after a while, there is a lot of records
in table B that relate to each valve in table A. The query/report I'm
after is one that will allow me to show all the data from table A with
ONLY the latest "valve work date" (and associated data) from table B
for each valve.

Right now, when I try to use DLAST, what I get is a report that lists
all of the data from table A for each valve. That's fine, but the
system is also putting the latest date in table B as the work date for
each valve. For instance:

- Valve 1 was worked on 1 Jan 2004, 1 Feb 2004, and 1 Mar 2004
- Valve 2 was worked on 1 Jan 2004 only

I need a report that shows valve 1 data/history from 1 Mar only, and
valve 2 data/history from 1 Jan. What it is doing is showing 1 Mar
2004 for every valve, regardless of when each individual valve was
actually worked. Report is a datasheet, listing 20-30 valves on each
page. I know there is probably a better way to explain this, but?