Query to return only the latest records record from linked table

[marcusblackcat]

This has probably been posted but I have been looking for it confused for quite some time. I'm quite new to access - have written a few queries but am looking for a solution which doesn't seem to fit anything I have found as yet.

I have a database with linked tables with 1 to many relationships
I have a table called tACTIONS and a table called tUPDATES. Each action can have multiple updates
I am trying to pull a query together which will only return the latest report. Unfortunately I am not able to post a copy of the database as it has sensitive information in. I can, however, pop in some screen shots:

Query design:

The highlighted field has a formula is of:

RecNo: IIf(DCount("aid","tUPDATES","aid=" & [tACTIONS]![AID])<=1,1,IIf([UID]=DMax("UID","tUPDATES","AID=" & [tactions]![AID]),1,0))

The Actions to Updates join as follows:

And the output (truncated as there are over 100 records):

The issue I have is that, when I add the RecNo field with it's formula, the query takes over a minute to run. (Split database with the back end on a network)
Without that field it is less than 3 seconds.

What I need, is a fast way to be able to run the query and only return the record where the recno = 1. But without having to use the recno field. I understand I can set the criteria of the RecNo field to 1 which will work - but, as mentioned, this is a very slow process (particularly working with our our network and VPN setup)

Effectively, I need all of the actions records whether they have updates or not. But I only need the latest update (so the max of UID which is an autonumber) and not return the other records.

If I have written the post incorrectly, please let mw know how I should have done it as I haven't posted many on here

 

[Micron]

Both of those IIF expressions are being evaluated for each record. I'm surprised it takes only a minute, so expect it to get worse as the table record number grows, especially when running this through a VPN. All I can suggest is that you break this into 2 or more queries and use queries instead of tables, the idea being to filter out unneeded records. That, or try something totally different, such as a Totals query and let that count your values.

 

[marcusblackcat]

Both of those IIF expressions are being evaluated for each record. I'm surprised it takes only a minute, so expect it to get worse as the table record number grows, especially when running this through a VPN. All I can suggest is that you break this into 2 or more queries and use queries instead of tables, the idea being to filter out unneeded records. That, or try something totally different, such as a Totals query and let that count your values.

Thanks for the response. As I say still new to this Access thing so not really sure what or how I could do it. Using the totals would be easy if I didn't need to also bring in all records where the updates field was blank! I'll have a play and see what I can do regarding running multiple queries. I tried this using a dlookup or a dmax in the criteria but that completely killed the database. Was well in excess of 10 minutes when I force closed Access!! Sadly I come from a very much logical side of Excel so I'm probably trying to put into Access what I would in Excel!

Thanks again

 

[Micron]

Using DLookups in a query is considered bad form by many/most db developers. It would not surprise me if your underlying issue was table design as many Excel afficionados apply spreadsheet thinking to db table design, which is about the worst thing you can do. Your pics are very fuzzy so I can't make out the field names but it looks like you're ok with that. Impossible to be sure. If you're allowed to post db's here (I don't know, have never tried) and need to do so, there is a way to randomize data so that it can't be read -
Randomizing Data for Posted db's

 

[marcusblackcat]

Using DLookups in a query is considered bad form by many/most db developers. It would not surprise me if your underlying issue was table design as many Excel afficionados apply spreadsheet thinking to db table design, which is about the worst thing you can do. Your pics are very fuzzy so I can't make out the field names but it looks like you're ok with that. Impossible to be sure. If you're allowed to post db's here (I don't know, have never tried) and need to do so, there is a way to randomize data so that it can't be read -
Randomizing Data for Posted db's

Good to know. Thanks for the info. As I say I'm pretty new to this and, as you've said, I'm an Excel bod learning databases! Field names are all no spaces with captions. How I was taught to do it by my boss.

What I have discovered is that creating the 2 queries instead of one works. The first query filters the data according to date selectors and a certain committee being selected, this brings back a much smaller recordset. I can then add the iif statements into a second query which brings back only those records. Runs in less than 5 seconds now. Seemingly there will never be more than 10-12 records according to them.

There has to be a better way than I am doing it, but this is working for now. Said I would work on it in the background to speed it up for them so will keep trying things to get it working. Thanks for your help

 

[Micron]

NP. 5 secs might be the best you'll get under the circumstances.