Querying dates from multiple tables

[Saudi Mike]

I am trying to compare dates between multiple tables and retrieve the latest date. Basically:

1 - I have a master table that records work requests given to the group.

2 - I have 9 different tables, one for each team in the group. Each team gets handed different parts of one task and reports when they completed the task under a field on their respective tables called "1stIssuedDate". All tasks have the same ID that relates the tasks.


Now I am trying to derive a query that will compare the various "1stIssuedDate"s and determine the latest date. I think this should be straight forward, but I've not figured it out.

I thought I could write an IIF statement that would compare the first team to the second, third, etc, and if it does not have the latest date it would go to the next IIF statement that would compare the second to the third, forth, ect, but this gets to be a rather long winded multi-nested IIF statement.

In general, how would I go about doing this in an efficient manner?

thanks!!

 

[MarkK]

First thing is all teams should be in the same table. If there are different team names or numbers or something, add a field called TeamName, or TeamNumber. Then comparing teams, or doing summaries on teams is easy, because they are all in the same place.

 

[Saudi Mike]

We tried that at one time. The problem we ran into was we had occurences of two people trying to edit the same record at the same time and getting error messages. So, we thought it would work better to have separate tables. That way, Team 1 is only editing Table 1, Team 2 is editing Table 2, etc. While you could still have two same-team members trying to edit one record, it has not been a problem. Hence, the multiple tables.

I tried creating a query that would just examine the dates, but I think it had issues, because not all tasks to to all teams, which means some teams will have a Null value for the date. Maybe it's the Null values throwing off the comparison?