Speed up Query (1 Viewer)

[Huey462]

I have a query that I think may be too much for access to handle (unlikely) or is poorly designed and causing Access to work too hard to find the information (most likely).

The 2 tables involved with this query are tblAlphaList and tblSchedule

What I want the query to do is do some date math and see if there are any appointments that fall on that day. (all this data is in the tblSchedule) and then link them with an individual by their SSN.

The way it is set up now is there are 31 daily queries (qrySchedule01-31) to see if any appointments are “due” that day and one massive query that does the linking.

Appt1: [qryScheduleDay01]![Apptcomment]
through
Appt31: [qryScheduleDay31]![Apptcomment]

When this is run for the 7 and 14 day reports, the data is displayed (albeit somewhat slowly) but when I added more data to take it out to 31 days Access locks up. Is there a better/faster way to perform these calculations?

I suspect using the query wizard allows a novice like myself to make something that works for small projects, but with the 19,000+ calculations (625+ people with 31 dates each) it creates a digital choke point. I had thought about changing the queries to an append/update to a temporary table and have the report pull the data from that, but I want to get the guidance/opinion of someone above the “mucking around and hope it works” proficiency.

==== Database Information ====

tblAlphaList (contains personal information DOB, Time at current position etc)
Field: SSN
Type: Text (11 digit field size i.e 123-45-6789)

tblSchedule
Field: SSN
Type: Text (11 digit field size i.e 123-45-6789)
Field: Scheduled
Type: Date/Time (start date of the appointment/event)
Field: StopDt
Type: Date/Time (start date of the appointment/event)
Field: ApptComment
Type: Text (contains text regarding appointment i.e. 0900 Medical)

On the main form, the user picks a date (stored as “Dstamp” in the “tblMainInformation”) to start the query from, each of the daily queries use the following to find out what (if any) appointments are “due” that day. The farther out they go from the example below a +# is tacked on to the end.

Scheduled:
DLookUp("DStamp","tblMainInformation","[ID] = 1")
[blank line]
<=DLookUp("DStamp","tblMainInformation","[ID] = 1")

StopDt:
[blank line]
DLookUp("DStamp","tblMainInformation","[ID] = 1")
>DLookUp("DStamp","tblMainInformation","[ID] = 1")-1

Each of these 31 queries are then pulled in/linked into a massive query that links tblAlphaList to each of the daily queries by SSN.
Appt1: [qryScheduleDay01]![ApptComment]
through
Appt31: [qryScheduleDay31]![ApptComment]

These are all displayed similar to the table below (1 Aug would be the date picked by the user as the start date)

1 Aug 2 Aug 3 Aug 4 Aug 5 Aug 6 Aug 7 Aug
Doe, John Q. 0600 CATM
Norris, Chuck CTO

Once I get the query working I’m going to work on formatting this to have it print to 1 page wide by however many long, similar to how excel will.

I was referred to this site from a post I made at Mr. Excel.
<link removed because I don't yet have 10 posts>

 

[jdraw]

Can you tell us in 4- lines WHAT you are trying to do in plain English-- no jargon, no table, no query -- just plain English?

 

[Huey462]

The database keeps all of the information for personnel in the shops, manning rosters and what not. The training monitors requested that I build a calendar type function to allow them to print out 7, 14 and 31 day forecasts so people can see what is coming up (appointments, events and so on) as well as plan for vacation time.
The 7 and 14 day calendars work, but they take a few minutes to open. The 31 day calendar locks up the database. I left it running for an hour while I ran errands and it made no progress at all.

 

[jdraw]

Can you show us your table and relationships (jpg of your relationships window)?
You'll have to zip it because of your post count.

 

[Huey462]

I didn't think to capture this on the screenshot, but, the Daily Queries to Alpha List is linked by SSN in tblAlphaList and tblSchedSSN in each of the qryScheduleDay##.

Option 2 is enabled. All records from tblAlphaList and only the matching in qryScheduleDay## are displayed.

 

[Huey462]

I tried to go with a different approach that seemed to be working better.

I have the 2 tables combined with a one to many type relationship
Each of the 31 days runs a variation of the following formula
Appt01: IIf([Scheduled]<=ELookUp("DStamp","tblMainInformation","[ID] = 1") And [StopDt]>=ELookUp("DStamp","tblMainInformation","[ID] = 1")=-1,[ApptComment],"")

This runs and the table/datasheet is displayed in well under a second. The new problem is that if an individual has more than one appointment during the timeframe, they will have more than one record (right term?) on the datasheet.

It would seem to be a fairly easy procedure to have Access combine all of the records with the same specified field value (i.e. SSN)…I just don’t know how to do it.

…………………….Appt 1…………….Appt2
John, Doe…….Dental 0900
John, Doe……………………………..Oil Change 0730

to

…………………….Appt 1…………….Appt2
John, Doe…….Dental 0900……Oil Change 0730