Better Query Advice?

[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).</SPAN>

The 2 tables involved with this query are tblAlphaList and tblSchedule</SPAN>

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.</SPAN>

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.</SPAN>

Appt1: [qryScheduleDay01]![Apptcomment]</SPAN>
through</SPAN>
Appt31: [qryScheduleDay31]![Apptcomment]</SPAN>

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?
</SPAN></SPAN>

 

[adam087]

I've assumed your tables look something like.
tblAlphaList - [alpSsn (Autonumber, PK)] | [alpDesc (text, description)
tblSchedule - [schIndex (Autonumber, PK)] | [schDate (Date)] | [schSsn (SSN from Alpha List, FK)]

Then you could use a query something like...

SELECT Day([schDate]) AS DayOfMonth, schDate, alpDesc
FROM tblSchedule INNER JOIN tblAlphaList ON tblSchedule.schSsn = tblAlphaList.alpSsn
WHERE DayOfMonth=29 Or DayOfMonth=30;

Not sure if that helps.

/AJ

 

[SydneyGeek]

Just wondering about the table structures.
What are the fields in the 2 tables, and what is the desired result? (ie, what are the output fields in the query and how do you want them grouped)?
Also, will this be used for something else or is the desired output a printed report?

Denis

 

[Huey462]

Here is the information for the tables that</SPAN>

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

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

On the main form, the user picks a date 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.</SPAN>

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

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

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.</SPAN>
Appt1: [qryScheduleDay01]![ApptComment]</SPAN>
through</SPAN>
Appt31: [qryScheduleDay31]![ApptComment]</SPAN>

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

	1 Aug</SPAN>	2 Aug</SPAN>	3 Aug</SPAN>	4 Aug</SPAN>	5 Aug</SPAN>	6 Aug</SPAN>
Doe, John Q.</SPAN>				0600 CATM</SPAN>
Norris, Chuck</SPAN>					CTO</SPAN>

<TBODY>
</TBODY>

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.</SPAN>

 

[Huey462]

Would a sql string work better? I don't understand the difference between the two.

 

[GinaWhipp]

Huey462,

I received an eMail referencing this thread, did you send it?

 

[Huey462]

Yes Ma'am, I had hoped that you might have a website/resource/something that I could use to troubleshoot the issue. Fixing the issue would be good, but knowing the hows and whys it works the way it does would be preferred.</SPAN>

 

[GinaWhipp]

Hmm, well, no offense to this site which is centered mostly around Excel, this site...

Access World Forums

Is more centered on Access (also, my favorite). I beleive this might serve you better.

 

[Huey462]

I will give it a try. Thank you.

 

[SydneyGeek]

DLOOKUP is a very slow way to retrieve information.
A join is almost always faster.
I would consider creating one query that covers the whole month (without DLOOKUP), then pivoting that to get your output. To get the text in the VALUE area, use MAX as the summary calculation.

Denis