D
Donna Brooks
Can you use a crosstab query in a report?
Donna Brooks
Donna Brooks
D
Duane Hookom
Try the sample downloads from
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.
G
Guest
Duane or others-
I tried this and it WAS helpful. I have the following questions:
I need to do a crosstab query where the results will be used in a report in which I must display the data for an entire week, Sunday through Saturday. I need to show the data for each day even if that data is all zeros. The question I have is when the query calculates the data it only shows the days for which there is data. How do I create a report in which the data goes to the right columns (Sunday through Saturday for the selected week in a dialog box calendar control, yielding a start and end date for the week) and still have zeros displayed in the days in which there is no data?
Any help would be greatly appreciated.
Vincent DeLuca
(e-mail address removed)
I tried this and it WAS helpful. I have the following questions:
I need to do a crosstab query where the results will be used in a report in which I must display the data for an entire week, Sunday through Saturday. I need to show the data for each day even if that data is all zeros. The question I have is when the query calculates the data it only shows the days for which there is data. How do I create a report in which the data goes to the right columns (Sunday through Saturday for the selected week in a dialog box calendar control, yielding a start and end date for the week) and still have zeros displayed in the days in which there is no data?
Any help would be greatly appreciated.
Vincent DeLuca
(e-mail address removed)
D
Duane Hookom
Show us your SQL.
--
Duane Hookom
MS Access MVP
Saturday. I need to show the data for each day even if that data is all
zeros. The question I have is when the query calculates the data it only
shows the days for which there is data. How do I create a report in which
the data goes to the right columns (Sunday through Saturday for the selected
week in a dialog box calendar control, yielding a start and end date for the
week) and still have zeros displayed in the days in which there is no data?
--
Duane Hookom
MS Access MVP
in which I must display the data for an entire week, Sunday throughVincent DeLuca said:
Saturday. I need to show the data for each day even if that data is all
zeros. The question I have is when the query calculates the data it only
shows the days for which there is data. How do I create a report in which
the data goes to the right columns (Sunday through Saturday for the selected
week in a dialog box calendar control, yielding a start and end date for the
week) and still have zeros displayed in the days in which there is no data?
G
Guest
This project is being done in Access using the crosstab query wizard and the Visual Design tools.
D
Duane Hookom
Can you view the SQL View and then copy and paste the SQL statement in a
reply?
reply?
G
Guest
Here is the SQL-- sorry, new to this part of it.
PARAMETERS StartingDate DateTime, EndingDate DateTime;
TRANSFORM "~" & Sum([Pulls]) & "~" & Sum([Errors]) & "~" & Format(Avg([AccuracyPercent]),"0.00%") AS CheckPullsErrorsPercent
SELECT [Workers Query (All)].LastFirstMiddle, [WorkerPullsforCustomer Query].WorkerID
FROM [Workers Query (All)] INNER JOIN (DayOfWeekList INNER JOIN [WorkerPullsforCustomer Query] ON DayOfWeekList.DayNumber = [WorkerPullsforCustomer Query].Day) ON [Workers Query (All)].WorkerID = [WorkerPullsforCustomer Query].WorkerID
WHERE ((([WorkerPullsforCustomer Query].Date)>=[StartingDate] And ([WorkerPullsforCustomer Query].Date)<=[EndingDate]))
GROUP BY [Workers Query (All)].LastFirstMiddle, [WorkerPullsforCustomer Query].WorkerID
PIVOT DayOfWeekList.DayOfWeek;
PARAMETERS StartingDate DateTime, EndingDate DateTime;
TRANSFORM "~" & Sum([Pulls]) & "~" & Sum([Errors]) & "~" & Format(Avg([AccuracyPercent]),"0.00%") AS CheckPullsErrorsPercent
SELECT [Workers Query (All)].LastFirstMiddle, [WorkerPullsforCustomer Query].WorkerID
FROM [Workers Query (All)] INNER JOIN (DayOfWeekList INNER JOIN [WorkerPullsforCustomer Query] ON DayOfWeekList.DayNumber = [WorkerPullsforCustomer Query].Day) ON [Workers Query (All)].WorkerID = [WorkerPullsforCustomer Query].WorkerID
WHERE ((([WorkerPullsforCustomer Query].Date)>=[StartingDate] And ([WorkerPullsforCustomer Query].Date)<=[EndingDate]))
GROUP BY [Workers Query (All)].LastFirstMiddle, [WorkerPullsforCustomer Query].WorkerID
PIVOT DayOfWeekList.DayOfWeek;
G
Guest
Sorry, new to this part of it--
PARAMETERS StartingDate DateTime, EndingDate DateTime;
TRANSFORM "~" & Sum([Pulls]) & "~" & Sum([Errors]) & "~" & Format(Avg([AccuracyPercent]),"0.00%") AS CheckPullsErrorsPercent
SELECT [Workers Query (All)].LastFirstMiddle, [WorkerPullsforCustomer Query].WorkerID
FROM [Workers Query (All)] INNER JOIN (DayOfWeekList INNER JOIN [WorkerPullsforCustomer Query] ON DayOfWeekList.DayNumber = [WorkerPullsforCustomer Query].Day) ON [Workers Query (All)].WorkerID = [WorkerPullsforCustomer Query].WorkerID
WHERE ((([WorkerPullsforCustomer Query].Date)>=[StartingDate] And ([WorkerPullsforCustomer Query].Date)<=[EndingDate]))
GROUP BY [Workers Query (All)].LastFirstMiddle, [WorkerPullsforCustomer Query].WorkerID
PIVOT DayOfWeekList.DayOfWeek;
PARAMETERS StartingDate DateTime, EndingDate DateTime;
TRANSFORM "~" & Sum([Pulls]) & "~" & Sum([Errors]) & "~" & Format(Avg([AccuracyPercent]),"0.00%") AS CheckPullsErrorsPercent
SELECT [Workers Query (All)].LastFirstMiddle, [WorkerPullsforCustomer Query].WorkerID
FROM [Workers Query (All)] INNER JOIN (DayOfWeekList INNER JOIN [WorkerPullsforCustomer Query] ON DayOfWeekList.DayNumber = [WorkerPullsforCustomer Query].Day) ON [Workers Query (All)].WorkerID = [WorkerPullsforCustomer Query].WorkerID
WHERE ((([WorkerPullsforCustomer Query].Date)>=[StartingDate] And ([WorkerPullsforCustomer Query].Date)<=[EndingDate]))
GROUP BY [Workers Query (All)].LastFirstMiddle, [WorkerPullsforCustomer Query].WorkerID
PIVOT DayOfWeekList.DayOfWeek;
G
Guest
Duane and others-
I have successfully utilitized the techniques demonstrated in the example link above to create a report with days of the week headings and three subheadings, one for pulls, one for errors, and one for accuracy percent. I have modified the GetPart formula in the detail section fields and the totals section fields that calls the function to do a data separation from tilde to tilde to pull the data based upon the day of the week. The calendar control formula I have created uses a StartDate and and EndDate field to specify the week period in use with a button that automatically generates the week dates to the start and end date of the week in which a date is selected. The calendar control has a preview button that calls up the Crosstab query to view on screen. I have a days of week table that maps to the dayofweek number generated by the WEEKDAY function to show the text of the day of the week in the Crosstab Columns. The one issue with this is that the days are sorted alphabetically and not in day of week order. I do not know if this matters when mapping to a report, that the data is in the correct order already--will the report draw from the appropriate columns or will it look in column order only?
The next issue and the most important one: Using the technique illustrated for tilde separation and data combinations in the crosstab report, I have created the data necessary for the report. The problem I have yet to solve is pulling the data from the crosstab query into the report. I have tried using a DoCmd.OpenReport command from the calendar dialog print button with the Crosstab Query name as a parameter. I also have the Crosstab query listed in the properties of the report.
Can you help with these two issues? Thank you.
Vince DeLuca
(e-mail address removed)
I have successfully utilitized the techniques demonstrated in the example link above to create a report with days of the week headings and three subheadings, one for pulls, one for errors, and one for accuracy percent. I have modified the GetPart formula in the detail section fields and the totals section fields that calls the function to do a data separation from tilde to tilde to pull the data based upon the day of the week. The calendar control formula I have created uses a StartDate and and EndDate field to specify the week period in use with a button that automatically generates the week dates to the start and end date of the week in which a date is selected. The calendar control has a preview button that calls up the Crosstab query to view on screen. I have a days of week table that maps to the dayofweek number generated by the WEEKDAY function to show the text of the day of the week in the Crosstab Columns. The one issue with this is that the days are sorted alphabetically and not in day of week order. I do not know if this matters when mapping to a report, that the data is in the correct order already--will the report draw from the appropriate columns or will it look in column order only?
The next issue and the most important one: Using the technique illustrated for tilde separation and data combinations in the crosstab report, I have created the data necessary for the report. The problem I have yet to solve is pulling the data from the crosstab query into the report. I have tried using a DoCmd.OpenReport command from the calendar dialog print button with the Crosstab Query name as a parameter. I also have the Crosstab query listed in the properties of the report.
Can you help with these two issues? Thank you.
Vince DeLuca
(e-mail address removed)
G
Guest
When I insert this Crosstab query result as the record source in a report, I am getting a message The Microsoft Jet Query Engine does not recognize
SELECT WPCQXTPEP.LastFirstMiddle, WPCQXTPEP.WorkerID, WPCQXTPEP.[1Sunday], WPCQXTPEP.[2Monday], WPCQXTPEP.[5Thursday] FROM WPCQXTPEP;
as a valid field name or expression.
The result works fine if I run it directly.
I am using a copy of the crosstab report named WPCQXTPEP. I have changed the field names in the weekdaylist table to 1Sunday, 2Monday, etc so they will sort correctly. Not elegant, just brute force. Get it to work, then fix the pretty.
any tips? Thanks.
SELECT WPCQXTPEP.LastFirstMiddle, WPCQXTPEP.WorkerID, WPCQXTPEP.[1Sunday], WPCQXTPEP.[2Monday], WPCQXTPEP.[5Thursday] FROM WPCQXTPEP;
as a valid field name or expression.
The result works fine if I run it directly.
I am using a copy of the crosstab report named WPCQXTPEP. I have changed the field names in the weekdaylist table to 1Sunday, 2Monday, etc so they will sort correctly. Not elegant, just brute force. Get it to work, then fix the pretty.
any tips? Thanks.
G
Guest
When you are executing a form based upon a query, do the parameters define the dataset before the form receives the data? What are the effects of parameters upon the form?
G
Guest
Duane and others-
I have successfully pulled the data from the crosstab report into the form for only the days that have data. When I try to reference a day column without data, the message that the Microsoft Jet Database engine does not recognize (the tablename) as a valid field name or expression.
This may be the last question
Vince
I have successfully pulled the data from the crosstab report into the form for only the days that have data. When I try to reference a day column without data, the message that the Microsoft Jet Database engine does not recognize (the tablename) as a valid field name or expression.
This may be the last question
Vince
G
Guest
Duane and others:
I am having some success! The form is working to the screen, but only when I only include references to the column names (weekdays) that have data in them (generate output from the crosstab query report). If I include references to the days that came up empty, the report generates the following message: "Microsoft Jet Database Engine does not recognize (the table name) as a valid field or expression." This may be one of the last details to finish this project
Thanks
Vince DeLuca
I am having some success! The form is working to the screen, but only when I only include references to the column names (weekdays) that have data in them (generate output from the crosstab query report). If I include references to the days that came up empty, the report generates the following message: "Microsoft Jet Database Engine does not recognize (the table name) as a valid field or expression." This may be one of the last details to finish this project
Thanks
Vince DeLuca
G
Guest
How can I generate a crosstab report to include a column for all days from the start date to the ending date even if there is no data in that day period?
This may answer the previous question.
Thanks.
Vince
This may answer the previous question.
Thanks.
Vince
D
Duane Hookom
I create crosstabs with relative date columns. Rather than repeat the entire
process, search google groups on my name and
relative dates Day1
--
Duane Hookom
MS Access MVP
period?
process, search google groups on my name and
relative dates Day1
--
Duane Hookom
MS Access MVP
the start date to the ending date even if there is no data in that dayVincent DeLuca said:
period?
G
Guest
Duane-
I need to have the headings be Sunday through Saturday fixed. The week will vary depending upon the user selection of week in the calendar tool. When they select the date with a button, it will insert the StartingDate and the EndingDate of the entire week in two text boxes. I need to pass these values to the crosstab report to select the subset of data that will be in the report. How can I do this using the relative data column concept? Would simply using "w" in the dataadd function be my solution with addition for days out from Sunday?
Thanks.
Vince
In http://dbforums.com/t562860.html I see that
I need to have the headings be Sunday through Saturday fixed. The week will vary depending upon the user selection of week in the calendar tool. When they select the date with a button, it will insert the StartingDate and the EndingDate of the entire week in two text boxes. I need to pass these values to the crosstab report to select the subset of data that will be in the report. How can I do this using the relative data column concept? Would simply using "w" in the dataadd function be my solution with addition for days out from Sunday?
Thanks.
Vince
In http://dbforums.com/t562860.html I see that
G
Guest
Duane-
I need to have a fixed week calendar type output with days from Sunday through Saturday with the crosstab data (one row per employee) summarizing the data for the entire week. I need to show zeros for columns with no data. I have success with the method described in the link you gave at the earliest message in this thread getting the data to print only when there is data. Any references to columns (days of the week) where there is no data results in an error. Is there a way to let the crosstab report give zeros for columns with no data?
Your relative column concept: would this give me zeros in columns where there is no data, or would it simply omit the column from the report? I believe that is the approach displayed in the crosstab query result set itself. Is there a way to force a crosstab query and report to show columns with zeros where there is no data for a given date range in order to fill out a weekly calendar?
Thanks for your patience with me on this.
Vincent DeLuca
I need to have a fixed week calendar type output with days from Sunday through Saturday with the crosstab data (one row per employee) summarizing the data for the entire week. I need to show zeros for columns with no data. I have success with the method described in the link you gave at the earliest message in this thread getting the data to print only when there is data. Any references to columns (days of the week) where there is no data results in an error. Is there a way to let the crosstab report give zeros for columns with no data?
Your relative column concept: would this give me zeros in columns where there is no data, or would it simply omit the column from the report? I believe that is the approach displayed in the crosstab query result set itself. Is there a way to force a crosstab query and report to show columns with zeros where there is no data for a given date range in order to fill out a weekly calendar?
Thanks for your patience with me on this.
Vincent DeLuca
G
Guest
Duane-
I need to have a fixed week calendar type output with days from Sunday through Saturday with the crosstab data (one row per employee) summarizing the data for the entire week. I need to show zeros for columns with no data. I have success with the method described in the link you gave at the earliest message in this thread getting the data to print only when there is data. Any references to columns (days of the week) where there is no data results in an error. Is there a way to let the crosstab report give zeros for columns with no data?
Your relative column concept: would this give me zeros in columns where there is no data, or would it simply omit the column from the report? I believe that is the approach displayed in the crosstab query result set itself. Is there a way to force a crosstab query and report to show columns with zeros where there is no data for a given date range in order to fill out a weekly calendar?
Thanks for your patience with me on this.
Vincent DeLuca
I need to have a fixed week calendar type output with days from Sunday through Saturday with the crosstab data (one row per employee) summarizing the data for the entire week. I need to show zeros for columns with no data. I have success with the method described in the link you gave at the earliest message in this thread getting the data to print only when there is data. Any references to columns (days of the week) where there is no data results in an error. Is there a way to let the crosstab report give zeros for columns with no data?
Your relative column concept: would this give me zeros in columns where there is no data, or would it simply omit the column from the report? I believe that is the approach displayed in the crosstab query result set itself. Is there a way to force a crosstab query and report to show columns with zeros where there is no data for a given date range in order to fill out a weekly calendar?
Thanks for your patience with me on this.
Vincent DeLuca
D
Duane Hookom
If you found my previous postings, they have hard-coded weekdays in the
Column Headings property.
BTW: did you look at the calendar reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4
--
Duane Hookom
MS Access MVP
the data for the entire week. I need to show zeros for columns with no
data. I have success with the method described in the link you gave at the
earliest message in this thread getting the data to print only when there is
data. Any references to columns (days of the week) where there is no data
results in an error. Is there a way to let the crosstab report give zeros
for columns with no data?
believe that is the approach displayed in the crosstab query result set
itself. Is there a way to force a crosstab query and report to show columns
with zeros where there is no data for a given date range in order to fill
out a weekly calendar?
Column Headings property.
BTW: did you look at the calendar reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4
--
Duane Hookom
MS Access MVP
through Saturday with the crosstab data (one row per employee) summarizingVincent DeLuca said:
the data for the entire week. I need to show zeros for columns with no
data. I have success with the method described in the link you gave at the
earliest message in this thread getting the data to print only when there is
data. Any references to columns (days of the week) where there is no data
results in an error. Is there a way to let the crosstab report give zeros
for columns with no data?
there is no data, or would it simply omit the column from the report? I
believe that is the approach displayed in the crosstab query result set
itself. Is there a way to force a crosstab query and report to show columns
with zeros where there is no data for a given date range in order to fill
out a weekly calendar?
G
Guest
Duane-
Which earlier posts were you referring to? I did not see in this thread what you referred to...
Thanks
Vince
Which earlier posts were you referring to? I did not see in this thread what you referred to...
Thanks
Vince