Crosstab query crashing/ Too complex? (1 Viewer)

[OBBurton]

Hi,

I have a crosstab query that is crashing and I wonder if it is too complex. It uses another query which gets a value from a textbox on a form.

The code that calls the query is:
DoCmd.OpenForm "OpenTabsFrm"
DoCmd.OpenQuery "qryWeeklyBedtimeHumalog", acViewNormal
Me.Visible = False

The qryWeeklyBedtimeHumalog query is:
TRANSFORM Avg(qryDailyVitalsDetail.Bedtime) AS AvgOfBedtime
SELECT qryDailyVitalsDetail.Date
FROM qryDailyVitalsDetail
WHERE (((qryDailyVitalsDetail.Date)>=Forms!TabsFrm!txtStartDateR And (qryDailyVitalsDetail.Date)<=Forms!TabsFrm!txtStartDateR+7) And ((qryDailyVitalsDetail.TimeOfDayChoices)="BED"))
GROUP BY qryDailyVitalsDetail.Date
PIVOT qryDailyVitalsDetail.TimeOfDayChoices;

The qryDailyVitalsDetail query is:
SELECT qrySelectedDatesVitals.ID, TimeOfDayLookupTbl_1.TimeOfDayChoices, qrySelectedDatesVitals.Date, qrySelectedDatesVitals.Time, qrySelectedDatesVitals.BloodSugar, [_BUMealtimeHumalogQry].Meals, BedtimeHumalogQry.Bedtime, qrySelectedDatesVitals.MED, qrySelectedDatesVitals.Systolic, qrySelectedDatesVitals.Diastolic, qrySelectedDatesVitals.BPM, qrySelectedDatesVitals.BM, qrySelectedDatesVitals.Exercise
FROM ((qrySelectedDatesVitals INNER JOIN TimeOfDayLookupTbl AS TimeOfDayLookupTbl_1 ON qrySelectedDatesVitals.TODID = TimeOfDayLookupTbl_1.ID) LEFT JOIN _BUMealtimeHumalogQry ON qrySelectedDatesVitals.ID = [_BUMealtimeHumalogQry].MTHID) LEFT JOIN BedtimeHumalogQry ON qrySelectedDatesVitals.ID = BedtimeHumalogQry.BTHID
ORDER BY qrySelectedDatesVitals.ID;

The qrySelectedDatesVitals query is:
SELECT VitalsDataTbl.ID, VitalsDataTbl.TODID, VitalsDataTbl.Date, VitalsDataTbl.Time, VitalsDataTbl.BloodSugar, VitalsDataTbl.Humalog, VitalsDataTbl.MED, VitalsDataTbl.Systolic, VitalsDataTbl.Diastolic, VitalsDataTbl.BPM, VitalsDataTbl.BM, VitalsDataTbl.Exercise
FROM VitalsDataTbl
WHERE (((VitalsDataTbl.Date)>=[Forms]![TabsFrm]![txtStartDate] And (VitalsDataTbl.Date)<=[Forms]![TabsFrm]![txtEndDate]));

I tracked down the problem to an error: "The Microsoft Access database engine does not recognize '[Forms]![TabsFrm]![txtStartDate]' as a valid field name or expression." I have other queries that use the two bottom queries without crashing but they are not crosstabs. Is that significant?

I have run into queries that were too complex for access, but I always got a message telling me that was the case. I thought that breaking the queries into several smaller queries overcame that problem, but I am having my doubts right now. If anyone can enlighten me, I would greatly appreciate it. :banghead:

 

[JHB]

I which query does the error occur, (you can run them one by one, starting with "qrySelectedDatesVitals")?
Edit:
I just read you use the other 2 queries without problem.
Can't you put in the below criteria in the first query, (only for info, Date is a reserved word in MS-Access and shouldn't be used as field name)?

WHERE VitalsDataTbl.Date>=Forms!TabsFrm!txtStartDateR And VitalsDataTbl.Date<=Forms!TabsFrm!txtStartDateR+7

 

[Geotch]

I use Mike Alexander's trick for most of my crosstab needs now. It helps with issues like you are having. One thing that he leaves out is to do an "all other" just in case you miss something.

Here's his video.
http://www.datapigtechnologies.com/flashfiles/crosstab.html

 

[OBBurton]

Thanks for responding, Geotch!
I really appreciate it! I learned a lot from this video. I'll probably never use the crosstab query wizard again!

 

[OBBurton]

Thanks, JHB for responding!
I tried that, but still got the error. I solved the problem, thanks to an excellent video by Mike Alexander, suggested by Geotch. I'll be unlikely to do another crosstab query with the wizard again! Best wishes!

 

[vbaInet]

Issues with crosstab queries are caused by improper use or not knowing the full potential of crosstab queries. Crosstab queries are very useful and they can be dynamically built in code.

You experienced the problem because you didn't create Parameters. You must create parameters and define their data types within the query.

 

[vbaInet]

I should also add the following about the video:

1. the statement made by Mike Alexander in his video about not being able to use parameters in crosstabs is incorrect.
2. the way he creates a crosstab query makes it a static query. Crosstabs are dynamic
3. you should not create a Make Table query from calculated data unless it's absolutely necessary.