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:
The highlighted field has a formula is of:
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
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:
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:
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
