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>
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>
Last edited: