CrossTab Query Report

[Klo]

I have a report based on a crosstab query. The report displays fine until the data from the crosstab query changes. The query places the column headings as dates and when the dates change the report gives a error saying that it dosen't recongize 2/6/06 ( or whatever it happens to be)as a valid field name. I can create a new report manually but need it to be automatic. Is there a way to set column headings as a variable or maybe insert the entire crosstab results into the detail section as an object?
Thanks for your help.

 

[randysmid]

Hi,
Have you considered re-running the query each time the report is run? This can be done in code, such as this sample code:

Dim strRptName as String
strRptName = "YourCrossTabReport"
DoCmd.OpenQuery "qryYourCrossTabQuery"
DoCmd.OpenReport strRptName, acViewPreview

HTH, Randy

 

[BoxHead]

I had a similar problem yesterday with a form for scheduling. My Crosstab query returned a selected week's worth of appointments in their respective timeslots, but when I moved to the next week, the field names returned by the query were different than my textbox controlsource names.

I ended up creating a query in VBA based on the crosstab and using that as my form's recordsource. Every time the crosstab query runs, the code deletes the existing query and recreates it with common field names.

Details:
1.) My Crosstab query is named qryCWA
2.) My Select query is called qryApptWeekThis is my Form's recordsource
3.) Decided on desired field names for qryApptWeek.
There are 3 rooms in the dental office I'm doing this for so there are three fields for Monday, Tuesday, etc. So my field names are Mon1, Mon2, Mon3, Tue1, etc.
4.) Open the qryCWA as a recordset to grab the current field names.
5.) Assign the appropriate field names to string values for building the SQL for qryApptWeek.
6.) Delete the existing qryApptWeek.
7.) Create QueryDef to create the new qryApptWeek.
8.) Requery the Form

DoCmd.DeleteObject acQuery, "qryCWA"
Set dd = CurrentDb
Set qryCurWeekAppts = dd.CreateQueryDef("qryCWA", mySql1)
[tt]         [COLOR=green]'''The above runs the Crosstab Query[/color][/tt]
Set rr = dd.OpenRecordset("qryCWA")[COLOR=green] '(4.)[/color]
Mon1v = " qryCWA.[" & rr.Fields(1).Name & "] AS Mon1," [COLOR=green] '(5.)[/color]
Mon2v = " qryCWA.[" & rr.Fields(2).Name & "] AS Mon2,"
Mon3v = " qryCWA.[" & rr.Fields(3).Name & "] AS Mon3,"
Tue1v = " qryCWA.[" & rr.Fields(4).Name & "] AS Tue1,"
Tue2v = " qryCWA.[" & rr.Fields(5).Name & "] AS Tue2,"
Tue3v = " qryCWA.[" & rr.Fields(6).Name & "] AS Tue3,"
Wed1v = " qryCWA.[" & rr.Fields(7).Name & "] AS Wed1,"
Wed2v = " qryCWA.[" & rr.Fields(8).Name & "] AS Wed2,"
Wed3v = " qryCWA.[" & rr.Fields(9).Name & "] AS Wed3,"
Thu1v = " qryCWA.[" & rr.Fields(10).Name & "] AS Thu1,"
Thu2v = " qryCWA.[" & rr.Fields(11).Name & "] AS Thu2,"
Thu3v = " qryCWA.[" & rr.Fields(12).Name & "] AS Thu3,"
Fri1v = " qryCWA.[" & rr.Fields(13).Name & "] AS Fri1,"
Fri2v = " qryCWA.[" & rr.Fields(14).Name & "] AS Fri2,"
Fri3v = " qryCWA.[" & rr.Fields(15).Name & "] AS Fri3,"
Sat1v = " qryCWA.[" & rr.Fields(16).Name & "] AS Sat1,"
Sat2v = " qryCWA.[" & rr.Fields(17).Name & "] AS Sat2,"
Sat3v = " qryCWA.[" & rr.Fields(18).Name & "] AS Sat3"

mySql2 = "SELECT tblTimeSlots.tSlot,  IIf(Mid(tblTimeSlots.tslot,InStr(tblTimeSlots.tslot," & """" & ":" & """" & ")+1,2)=" & """" & "00" & """" & ",Format(tblTimeSlots.tslot," & """" & "h:nn ampm" & """" & "),Space(InStr(tblTimeSlots.tslot," & """" & ":" & """" & ")) & Mid(Format(tblTimeSlots.tslot," & """" & "Short Time" & """" & "),3) & " & """" & "    " & """" & ") AS lblTslot, " _
& Mon1v & Mon2v & Mon3v & Tue1v & Tue2v & Tue3v & Wed1v & Wed2v & Wed3v & Thu1v & Thu2v & Thu3v & Fri1v & Fri2v & Fri3v & Sat1v & Sat2v & Sat3v & _
" FROM tblTimeSlots LEFT JOIN qryCWA ON tblTimeSlots.tSlot = qryCWA.BookedSlot;"

DoCmd.DeleteObject acQuery, "qryApptWeek" [COLOR=green] '(6.)[/color]
Set dd = CurrentDb
Set qryCurWeekAppts = dd.CreateQueryDef("qryApptWeek", mySql2) [COLOR=green] '(7.)[/color]
Me.Requery [COLOR=green] '(8.)[/color]

I didn't include the complete code because it's not going to relate to project. Just understand that
[tt]Mon1v = " qryCWA.[" & rr.Fields(1).Name & "] AS Mon1[/tt]
translates to query SQL
[tt] ...qryCWA.[05/29/06] AS Mon1...[/tt]
so that no matter what date is the field name in the crosstab query, it will be called Mon1 in my select query and as the controlsource for my text box.


HTH

John


Life is short.
Build something.

 

[dhookom]

You shouldn't have to use any code. Look at faq703-5466 from this forum. It groups by Month headings but should easily convert to any date interval.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Klo]

Thanks guys. I hope to get a change to work on it today or tomorrow. I'll let you know how it goes (or dosen't).
Thanks again.

 

[Klo]

Ok ... I'm confused on a much higher level now John. Here's what I have so far that works:

mySQL1 = "TRANSFORM First(Query7.Results) AS FirstOfResults "
mySQL1 = mySQL1 & "SELECT Query7.Test_full_name "
mySQL1 = mySQL1 & "FROM Query7 "
mySQL1 = mySQL1 & "GROUP BY Query7.Test_full_name "
mySQL1 = mySQL1 & "PIVOT Format([Date],'Short Date');"
Set dd = CurrentDb
Set qryCurResults = dd.CreateQueryDef("qryCresults", mySQL1)
Set rr = dd.OpenRecordset("qryCresults")
TestDate1v = " qryCresults.[" & rr.Fields(1).Name & "] AS TestDate1,"
TestDate2v = " qryCresults.[" & rr.Fields(2).Name & "] AS TestDate2,"
TestDate3v = " qryCresults.[" & rr.Fields(3).Name & "] AS TestDate3,"
TestDate4v = " qryCresults.[" & rr.Fields(4).Name & "] AS TestDate4,"
TestDate5v = " qryCresults.[" & rr.Fields(5).Name & "] AS TestDate5,"

This creates the crosstab query and set TestDate1 (etc) to the proper dates. I got lost on query 2. The crosstab query has all the data I need. What is the purpose of query 2?

Duane, I looked at the info you suggested but i don't have a set interval of time. The test can be from any date.

Cheers, Mike

 

[dhookom]

I'm not sure what you mean when you state "but i don't have a set interval of time" since your interval is one day per column.

Almost any solution you will create will be relatively easy if you predetermine the number of dates to return. If you want to make your solution much more difficult and involve a lot of code, you can allow users to select any number of dates and expect the interval between date columns to be something other than a single day.

If you can live with a set number of dates and want to view every date between the start and end date then the solution I suggested is easier since it involves no code and runs very efficiently.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Klo]

Hi Duane,
The dates I need are the last 5 test dates, so there is no set interval. The dates could be seperated by days or months and the person running the report has no way of knowing what the dates are. If it were easy I could figure it out myself and not have to pick the brains of the experts (I get the easy stuff).

 

[dhookom]

Can you use a multi-column (5 columns) subreport of the test information? Don't allow the subreport to grow beyond the last 5 records.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Klo]

I don't know. Sometimes there are fewer than 5 test dates. Also to be honest, I don't know how to set up a multi column report (yet).

 

[dhookom]

Multi-column reports are fairly simple. File->Page Setup->Columns. If there are fewer than 5 test dates it won't make any difference.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[BoxHead]

Klo,

I think if you can get Duane's solution to work you'll be better off.

FYI, the second query is a Select query that basically makes a copy of the CrossTab query with field names (from your code) [tt]TestDate1, TestDate2,...[/tt]

The Control Source for the report textboxes could be set as [tt]TestDate1, TestDate2,...[/tt] and no matter what the dates returned by the crosstab may be, the Control Source is consistent.

It was my understanding that your report had control sources like [05/29/06] that would fail when the crosstab was rerun and produced fields with different names.




Life is short.
Build something.

 

[Klo]

Hi John,
You may be right in that Duane's solution might be easier if I can get it to work but I started down this path (your code) and I really want to understand it. I'm never going to learn this stuff if I quit when it gets tough. I've already learned a great deal in the little I've acomplished. I agree with the "Life is short, build something" idea. I've built cars, airplanes (not models), a motorhome and my house and the only way I know to solve a problem is head on. So bloodied head and all, on I go.
By the way, you understand the problem exactly.
Cheers, Mike