Query to return only the latest records record from linked table

marcusblackcat

New Member
Joined
Mar 11, 2015
Messages
18
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:
DB1.png

The highlighted field has a formula is of:
Excel Formula:
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:
DB2 Join.png


And the output (truncated as there are over 100 records):
DB3 Output.png

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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,426
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 11, 2015
Messages
18
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

Well-known Member
Joined
Jun 3, 2015
Messages
2,426
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 11, 2015
Messages
18
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

Well-known Member
Joined
Jun 3, 2015
Messages
2,426
Office Version
  1. 365
Platform
  1. Windows
NP. 5 secs might be the best you'll get under the circumstances.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,855
Messages
5,766,781
Members
425,378
Latest member
kapoor2892

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top