Queries

[ccordner]

Good Morning,



I am a bit new to Access and have two questions really.

1) I have two tables, one containing staff and one containing addresses. Each address has a date they moved there. Is it possible to build a query field which picks the most recent address for each person?

2) I have a table showing the amount of overtime worked by each person and their manager. If I want to count the records for each manager, what's the best way? Do I do it as a separate query?

Thanks
Chris

[June7]

1. Making some guesses about your data structure to suggest following.
Do a query that returns the Max date for each staff in the address table.
Do a query that joins the Max query to the Addresses table. Have the MaxOfDate value from the first query be the criteria for the date field in Addresses table.

2. This would be an aggregate query with the Count function grouping on the manager ID.

[ccordner]

Kind of got this working, but I seem to have a bit of a problem:

Maybe I've got it set up wrong, but it only gives addresses that are valid from the most recent date, instead of the most recent address for each staff member.

Having had a query to find out the most recent date using Max([Date]), I then put a criterion in another query that says "Date of Address=Max([Date]). But if the most recent address was put in on, say, 22nd August, it only returns employees that had their address put in on that date.

Any ideas?

Chris

[June7]

Show the SQL statements for analysis.

The second query would not use the Max function as criteria, it would use the name of the Max field from the first query. The second query must JOIN the first query and the addresses table.

Is Date the name of the date field in the addresses table? Date is a reserved word and should not use reserved words as names. Better would be DateMove.

[ccordner]

Sorry, I'm not very good at explaining these things. Hope the following makes sense. It's highly likely that it's my misapplication of good advice!

I have one table, tblStaff. For arguments sake, there are three fields, ID, DOB, FirstName and Surname.

In the second table, tblAddresses, there are other fields, ID, HouseName,...,DateOfMove.

What I want to do is display the all the names from tblStaff, with their most recent address from tblAddresses. Obviously all the dates in 'DateOfMove' will be different.

What I did was set up a query, qryLatestAddressDate with just one column, which was MoveDate: Max([tblAddresses]![DateOfMove]).

I then set up another query, qryCurrentAddress, with fields from tblStaff and tblAddresses, adding a criterion under DateOfMove to say it was equal to [qryLatestAddressDate]![MoveDate].

I know this is me not doing it right, but I'm not really very familiar with this kind of thing i'm afraid.

Thanks
Chris

[June7]

The first query requires two columns: staffID and Max(DateOfMove) As MaxDate with GROUP BY on the staffID.

Second query must join the first query to the Addresses table on the common staffID field. Including the Staff table so that the staff names can be retrieved is appropriate. The criteria under the DateOfMove column for this query would be the calculated field MaxDate.

[ccordner]

Perfect! Thanks!