UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Selecting Latest Record From Junction Table, Access 2016    
 
   
projecttoday
post Sep 19 2019, 08:00 AM
Post#21


UtterAccess VIP
Posts: 11,069
Joined: 10-February 04
From: South Charleston, WV


You create the temp table in the report.

--------------------
Robert Crouser
Go to the top of the page
 
BruceM
post Sep 19 2019, 08:33 AM
Post#22


UtterAccess VIP
Posts: 7,971
Joined: 24-May 10
From: Downeast Maine


This situation has nothing to do with a report. It is a combo box row source, nothing else.
Go to the top of the page
 
nuclear_nick
post Sep 19 2019, 08:39 AM
Post#23



Posts: 1,789
Joined: 5-February 06
From: Ohio, USA


Because of the large tables, I do think filling a combo box with lots of values is... troublesome.

It's that double edged sword... make it easier on you, it's tougher on the users. Make it easier on the users, it's tougher for you.

Perhaps you can 'break contain' and have a table with the combo box information in it, that's updated (via some sort of trigger, or using VBA) whenever someone adds a new certification date. Another PITA, but your combo box wouldn't suffer speed issues.

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
projecttoday
post Sep 19 2019, 08:54 AM
Post#24


UtterAccess VIP
Posts: 11,069
Joined: 10-February 04
From: South Charleston, WV


Same difference.

If you cannot get the performance you want by tweaking the queries then upon opening the form fill a temporary table broken down into steps and use that as your combo box record source. If that doesn't work then you have to go to a table that gets prepped daily, hourly, or whatever that's ready when the form is opened.

Another possibility is a custom pop up.

Did you say 60,000? That's not so big.

--------------------
Robert Crouser
Go to the top of the page
 
BruceM
post Sep 20 2019, 02:52 PM
Post#25


UtterAccess VIP
Posts: 7,971
Joined: 24-May 10
From: Downeast Maine


I used a make table query based on the query that combines three tables, and saved the result as a local table. After indexing the new table I did the TOP 1 subquery on it, and got a result after a minute or so.

Then it occurred to me I may have been going about this all wrong, and I tried a grouping query. The two queries gave me slightly different results, so I'll have to sort that out. As I mentioned in a later posting, I neglected to include the customer when first describing the problem, and I may have included the customer incorrectly in the reworked grouping query, but my brain is tired, and I won't look at it any more until next week. But I think the grouping query is going to get the job done. I will post back once I have ironed out the wrinkles.

Thanks to both of you for your help and suggestions.
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    19th October 2019 - 05:21 AM