Selecting last date from multiple rows (1 Viewer)

[ianacole]

I apologize up front for the length of this post, I need the clarity in question formulation for my own sake

I'm having some trouble, and having searched through the listing have not found a complete answer to my problem. The environment is as such:

We are responsible for testing Out Of Band functionality and testing Dial Backup functionality for about 40 routers. The test dates for each type of test and each router can be different. For example:

RTRName RouterName1
OOBTestDate 8/28/03
DBUTestDate 8/19/03

RTR Name RouterName2
OOBTestDate 8/23/03
DBUTestDate 8/21/03

I am trying to design, based on a query, a report that displays last test date for each router of each test and status. I have 3 tables capturing the necessary information (I have attached an image of my relationships). They are:

RouterDetailTBL w/ Fields
ID (AutoNumber)
Other Lookup and text fields identifying the routers

DBUTestResultsTBL w/ Fields
DBUTestDate
DBUTester
DBUStatus
DBUNotes
DBURouterID -- Child (linked) to RouterDetailTBL.ID

OOBTestResultsTBL w/ Fields
OOBTestDate
OOBTester
OOBStatus
OOBNotes
OOBRouterID -- Child (linked) to RouterDetailTBL.ID

My report needs to list each router with the last test date and other information for each type of test.

So, can this be done with a query or a series of queries? Would it be better to have a fourth table for consolidated information and then base the report on this new table? Independently, I can get the two test tables (based on queries) to list the most recent test date for each ID, but how do I relate them back to the RouterDetailTBL to complete the report?

Thanks for any suggestions.

Ian

 

[Jon K]

I have attached an example. It contains five queries:

DBUTwo gets the DBU last dates from query DBUOne.
OOBTwo gets the OOB last dates from query OOBOne.
qryForReport combines table RouterDetailTBL with the queries DBUTwo and OOBTwo.

You can open the DB and run the query qryForReport.

Note. In qryForReport, I used Left Joins to retrieve all the routers from table RouterDetailTBL.

Hope it helps.

(To open the DB in Access 2000 or 2002, just choose Convert and save as a new name when the DB is opened for the first time.)

 

[crook]

Worth Mentioning...

You should only be using two tables, not three. It is because you are using 3 tables that JonK's answer to you is necessary. I.e., writing 5 queries. If you only had two tables, you would only need 1 query.

Suggested change:
Your one table idenfying routers is fine. Leave it as-is.

Your two tables, each tracking a different type of inspection, should be combined. They appear to have identical fields in them, so all you need to do is add a [type] field to one of them. You'll enter OOB or DBU as the type of inspection.

How to do this:
*before you begin, I would recommend renaming all of your table fields by removing the OOB or DBU from in front of the field name. For example, rename OOBTestDate to just TestDate, and so on.
1. Add a [type] field to your DBU table. Then do a CTRL+H (search/replace), find Null, replace with "DBU".
2. Create a new query in design view. Bring the OOB table into it, and show all fields except for the primary key.
3. Add an additional field called whatever (Expr1 works fine) - for example, enter: Expr1: "OOB".
4. Change the query type to "Append", and select your DBU table to append to. Map all of the source fields from OOB to the appropriate destination fields in DBU (some may be done automatically for you). Make sure you map Expr1 to the [type] field in the DBU table.
5. Run the query. It will ask you to confirm that you want to append x number of rows to the DBU table.
6. Now all of the the data from both tables in combined into the DBU table (which you should rename at this point). Each record is identified as either an OOB record, or a DBU record in the type field.

Now write a new query to display the last date for each inspection (DBU & OOB together).

Do this by bringing (only) the RtrName & Type fields into a new query. Set the query properties to return unique VALUES (as opposed to unique records). Then add a field as follows: LastDate: Last([TestDate]).

That should do it. Two tables, one query. Should be easier to manage than 3 tables and 5 queries. Hope this helps.

 

[Jon K]

>
.... If you only had two tables, you would only need 1 query.
<

If you use 1 query, you are bound to use a subquery. Running a subquery will take time if the table is fairly large or the machine is slow.

 

[crook]

No sub-query required for the option I outlined.

Also, the 1 query I outlined only handles the data once. The 5 queries you outlined, while they certainly will work, must make 3 passes through the data, so take 3 times as long.

 

[Jon K]

"One Query Only"
Since you use Last([TestDate]), I presume your query is a Totals query that:
SELECT DISTINCT RouterDetailTBL.RouterName, DBU_OOB_Combined.Type, Last(DBU_OOB_Combined.TestDate) AS LastOfTestDate
FROM ............
GROUP BY RouterDetailTBL.RouterName, DBU_OOB_Combined.Type

But ianacole wrote:

My report needs to list each router with the last test
date and other information for each type of test.

How would you add the other information to the query if it is a Totals query?


"The 5 queries you outlined .... must make 3 passes through the data"
I don't see the logic here. There are three separate tables, not just one. So how come 3 passes through the data?

 

[crook]

I don't have much more time for this right now.

I understand the frustration with the logic - sorry. Here's a quick explaination - I won't be responding to this thread after this.

You have 2 DBU queries - each one makes a single pass through the DBU table - once for the lastdate, and once to combine those results with the 'other' data. You then have 2 OOB queries that do the same thing to the OOB table. Finally, you use a 5th query to combine the results. So in sum, you've passed through the DBU table twice, the OOB table twice, and the query combinding the results passes through one each of the OOB & DBU queries. That's equivalent to 3 passes through the entire data set.

- by the way, this is not a bad technique, and I didn't mean to suggest otherwise. But if the fellow who you were responding to didn't figure it out on his own, then I thought the two-table / one-query approach would be easier for him. At a minimum, it's poor form to have the DBU and OOB data in separate tables, because they are identical in structure. The only reason to have seperate tables would be if there was so much data that a single table couldn't handle it all. I don't remember off-hand how many records an Access table is limited to - 67,000 or something like that - I could be confusing that number with something else though.

As for totals queries, you should look into the definitions of the first() and last() functions for more detailed information. But in short, they are very similar to the min() and max() functions which would be used in a totals query, only they do not try to total the enitre record like min() and max() would. So the query will not treat records with unique 'other' information as unique records. It only looks at whatever you used the last() function on for the unique data.

I think I did make a mistake, however, as follows:
I said use last([testdate]), but that is wrong. Should be last([testdate]&[routerID]).

Hope this makes sense - try it out - see if you can make it work - or see if you can fail it - either way will be a learning experience!

By the way, I don't know if there's a difference between 97 and 2002 re: the first() and last() functions. I know a lot of stuff changed between those two versions. The information I'm providing is based on Access 2002.

Good luck!

 

[Jon K]

Last() is not Max()

When you used the Last function to try to retrieve the "last date", obviously you confused the Last function with the aggregate Max function, no matter whether you were using Access 97, 2000 or 2002.


I think you should follow your own advice:

As for totals queries, you should look into the definitions of the first() and last() functions for more detailed information.

and spend some time on how these functions and the Max function work in a Totals query.


To help you a little, I have taken the following remarks from the Access help file:-

The First and Last functions are analogous to the MoveFirst and MoveLast methods of a DAO Recordset object. They simply return the value of a specified field in the first or last record, respectively, of the result set returned by a query. Because records are usually returned in no particular order (unless the query includes an ORDER BY clause), the records returned by these functions will be arbitrary.

When you eventually realize you have to use Max instead of Last to return the "last date", you will notice that you cannot use the Last function for the "other information" as Max(TestDate) and Last(otherInformation) may not be in the same record. That was why I said in my previous post that "If you use 1 query, you are bound to use a subquery."

 

[EMP]

crook

I do not agree with you about the "number of passes".

I think, when multiple queries are run, the query results of the inner queries just reside in memory so that when they are subsequently joined to another query or table, there is no need to make "another pass" on the data in the original table.

To improve performance, i often use inner queries to reduce the number of records that need to be joined with large tables. I think the number of queries used is not an issue. What is important is how you can use them effectively.

Regards

EMP

 

[ianacole]

Wow!! Thanks for all the suggestions! I created two separate tables thinking that a separation of data was required because of the way that data is being entered (see attachement). But, now that I am thinking along the lines of one table, I think I can see how to design the forms and reports. The end result (which drives all my requirements) has to reflect the most recent test date for each type of test and the status of each (I have attached an image of the report also).

In regards to writing queries, I have been able, for the independent tables, to return the last date. However, once I try to combine the results of the query, or to join them with the third table to pull the rest of the router details, I end up with only the rows that have the most recent date. To clarify, I have 100 rows with different dates, combined. With what I have done so far (which is the reason for my post) the queries return maybe 10 rows of 08/28/03.

Once I have the base, all-inclusive report complete, I will then be able to filter reports for region, OOB or DBU, etc.

Thanks in advance for the further help from everyone.

Ian

 

[ianacole]

...and the report...

 

[Brianwarnock]

Jon K

It some times pays just to browse the forum. I had a similar problem to ian and tackled it by the method you suggested, with one difference, I used first and last on the dates. It worked simply because of coincidence as I have now discovered by further testing. I will switch to min and max and I send you a heartfelt thanks.

Luckily the system isn't live yet


Thanks again

Brian

 

[ianacole]

Kudos go to Jon K!!! Thanks!!!! I finally had some free time to review the queries you sent over, and create them in my DB...and it worked like a charm!!!! Once again this forum saves my posterior!!

Thanks all who posted their suggestions.

Ian