Sortable form with join or dlookup

[Claire]

I'm working on creating an updateable form for a table that is sorted by a
field not in that table (many to one relationship). All the queries I've
tried have too many levels or unions so that the field I need to change will
not allow entry. My next thought was to use DLookup to pull in the
information, which was successful, except that I can not sort by that field.
After hitting the wall a few too many times on a Friday, I was hoping someone
may have another idea.

tblScheduledTests
CS
DateScheduled

qryInspectionCallSlips (union of imported table and manually entered table)
CS
SiteNo

tblSites
SiteNo
SiteName

I need to update the scheduled date in tblScheduledTests, with the CS sorted
by SiteName (This is how the information will be passed over for data entry).


The only other solution I can think of de-nomalizes the data- when appending
the CSs to tblScheduledTests (which does need to happen)I'd also append a
SiteName field. This would work, but if I ever need to sort the form in a
different way I would be stuck.

Thanks for any suggestions!
~Claire

 

[Allen Browne]

You could put the DLookup() expression into a query, which gives you the
SiteName as a field of the query. That way you can sort by SiteName. If
there are thousands of records, that's going to run like treacle. This
ELookup() will be a bit faster, but performance will still be poor:
http://allenbrowne.com/ser-42.html

I'n not clear if tblScheduleTests has a primary key, but presumably
qryInspectionCallSlips has no primary key and is not updatable. Therefore
any query that uses it as a source table will be uneditable.