Question - Cross Tab Pivot

[101287]

I'm trying to use a technique to give the Heading of a crosstab to be "A", "B", "C" etc. in order to generate a Report in access. However, when I use the query below I'm getting data mismatch on criteria. Suggestions to correct the query.

TRANSFORM Count(qryTop5Input.AuditID) AS CountOfAuditID1
SELECT qryTop5Input.AuditID
FROM qryTop5Input
GROUP BY qryTop5Input.AuditID
PIVOT qryTop5Input.Date In ("A", "B", "C", "D", "E", "F");

Thank you for collaboration.

 

[dhookom]

If [Date] is a date type field, how could it contain the values "A", "B",...?

What are you attempting to do. You might want to check out the Reports Forum FAQ for a solution of monthly crosstab report. You should be able to convert this to day intervals.

Duane
Hook'D on Access
MS Access MVP

 

[101287]

dHookom - Thank you for your replied. I'm trying to do a cross tab report using your sample cross tab example that you have in your website. What I'm trying to do is generate dynamically a crosstab report. This report will be executted everyday and the dates will be change. Was thinking about using the similar approach that you use but not able to figure out how to do it how to do you "sync up" the reports.

Desired outcome - Build crosstab report with the capability of dynamically setting up the columns.

Hopefully that help.

Luis

 

[dhookom]

Again, it looks like your column headings will be dates. I suggest you design your report around a specific number of dates. Then use the FAQ that I suggested faq703-5466. You will need to make minor adjustments for days rather than months.

Duane
Hook'D on Access
MS Access MVP

 

[101287]

Don't have enough knowledge to follow your instructions. I set up the crosstab query for the form. However, don't know how to set up generic columns and headings because the crosstab query will be execute everyday and the data will change. Therefore, need to set up like a variable report. So that when I execute the query and generate the crosstab the appropriate column heading, column controls etc. are populated correctly.

Do you have any suggestions on how to proceed forward? Now that it's possible but don't have the technical knowledge to execute it. Instructions and/or articles on how to proceed forward will be appreciated.

Luis

 

[dhookom]

Can you design your report around a specific number of consecutive dates? Do you have a form with a text box control for the ending date? If "no" to either of these questions, you are making your report/query much more difficult.

Duane
Hook'D on Access
MS Access MVP

 

[101287]

You are correct, I'm making the report "much more difficult". I'm going to study the sample report that you published a while back regarding "crosstab" database and reports. Hopefully, I will be able to use that as an example. Thank you again.

Luis

 

[101287]

Hook'D - Follow the same approach that you use in your sample database. That is a menu to enter begining date and ending date. Using the approach it works!