Access query for last date entered

[David Ryan]

I have a data base where i need to find the last date entered for each record.
there are 3 tables.
the first table has fields for name, Surname
the second table has fields for Details
the third table has the field for dates

i need to pull the surname from the first table, details from the second
table and the last date for each record from the third table

eg details may have orientation attended, dates has multiple records per
person but i need the last time they attended.

hope this makes sense

 

[Allen Browne]

Can we assume that your first table has a primary key (e.g. an AutoNumber
named PersonID), and that your 3rd table has a PersonID number field that
relates back to this one?

1. Create a query using your 3rd table.

2. In query design, depress the Total button on the toolbar.
Access adds a Total row to the query design grid.

3. Drag the PersonID field into the grid.
Accept Group By in the Total row under this field.

4. Drag the date field into the grid.
In the Total row under this field, choose Max.

5. Save the query.

You can now create another query with your other 2 tables and the query you
just saved as input 'tables.'

Note that the results will not be editable (due to the grouping.)

 

[David Ryan]

Hi Allen that worked a treat thanks. However I also need to know how to get
those that have not attended training ie the date field is blank.

I tried "IIf(IsNull([date]),"00/00/0000",[date])" in a query that desplays
the null values as "00/00/0000" but am unsure how to get it to work in your
solution. Which by the way was very simple.

Hope you can help, again thanks

 

[Allen Browne]

Use an outer join to get all records from one of the main tables.

In query design, double click the line joining the main table to the
junction table (your 3rd one.) Access pops up a dialog offering 3 options.
Choose the one that says:
All Records from table xxx, and any matches from yyy.
You will see an arrow going away from the main table.
Repeat for the other table. Again the arrow should point away from the main
table.

If outer joins are new, this might help:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

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

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

Hi Allen that worked a treat thanks. However I also need to know how to
get
those that have not attended training ie the date field is blank.

I tried "IIf(IsNull([date]),"00/00/0000",[date])" in a query that desplays
the null values as "00/00/0000" but am unsure how to get it to work in
your
solution. Which by the way was very simple.

Hope you can help, again thanks

Can we assume that your first table has a primary key (e.g. an AutoNumber
named PersonID), and that your 3rd table has a PersonID number field that
relates back to this one?

1. Create a query using your 3rd table.

2. In query design, depress the Total button on the toolbar.
Access adds a Total row to the query design grid.

3. Drag the PersonID field into the grid.
Accept Group By in the Total row under this field.

4. Drag the date field into the grid.
In the Total row under this field, choose Max.

5. Save the query.

You can now create another query with your other 2 tables and the query
you
just saved as input 'tables.'

Note that the results will not be editable (due to the grouping.)

 

[David Ryan]

Hi Allen

So simple thanks so much.

Use an outer join to get all records from one of the main tables.

In query design, double click the line joining the main table to the
junction table (your 3rd one.) Access pops up a dialog offering 3 options.
Choose the one that says:
All Records from table xxx, and any matches from yyy.
You will see an arrow going away from the main table.
Repeat for the other table. Again the arrow should point away from the main
table.

If outer joins are new, this might help:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

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

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

Hi Allen that worked a treat thanks. However I also need to know how to
get
those that have not attended training ie the date field is blank.

I tried "IIf(IsNull([date]),"00/00/0000",[date])" in a query that desplays
the null values as "00/00/0000" but am unsure how to get it to work in
your
solution. Which by the way was very simple.

Hope you can help, again thanks

Can we assume that your first table has a primary key (e.g. an AutoNumber
named PersonID), and that your 3rd table has a PersonID number field that
relates back to this one?

1. Create a query using your 3rd table.

2. In query design, depress the Total button on the toolbar.
Access adds a Total row to the query design grid.

3. Drag the PersonID field into the grid.
Accept Group By in the Total row under this field.

4. Drag the date field into the grid.
In the Total row under this field, choose Max.

5. Save the query.

You can now create another query with your other 2 tables and the query
you
just saved as input 'tables.'

Note that the results will not be editable (due to the grouping.)

I have a data base where i need to find the last date entered for each
record.
there are 3 tables.
the first table has fields for name, Surname
the second table has fields for Details
the third table has the field for dates

i need to pull the surname from the first table, details from the
second
table and the last date for each record from the third table

eg details may have orientation attended, dates has multiple records
per
person but i need the last time they attended.