Crosstab headings/parameters (1 Viewer)

Hi all,

I am working on making a crosstab query that uses another query as its record source. The purpose of this crosstab query is to take 5 days worth of production data and display it as a week summary. My rows are the different product families that we have, and the column headings are supposed to be the dates. I'm desigining the query to always display 5 days (Mon - Fri). There is one parameter that is taken from a form (startDate - which is executed on the source query).

This crosstab worked perfectly the first time I ran it and displayed the five days worth of data. However, when I tried testing it on another week I get a "The Microsoft Database does not recognize " as a valid field or expression". I'm guessing that after I ran it the first time, the 5 dates become locked in as the column headers, and now I can't run any other dates. Would anyone happen to have any suggestions for me? The SQL for both queries is below:

Source query:
PARAMETERS [Forms]![Home]![Quality].[Form]![summaryDate] DateTime;
SELECT standards.prodFamily, standards.JOP, prodData.goalRate, prodData.prodDate, prodData.uplh
FROM standards INNER JOIN prodData ON standards.prodFamily = prodData.prodFamily
GROUP BY standards.prodFamily, standards.JOP, prodData.goalRate, prodData.prodDate, prodData.uplh
HAVING (((prodData.prodDate) Between [Forms]![Home]![Quality].[Form]![summaryDate] And ([Forms]![Home]![Quality].[Form]![summaryDate]+5)));

Crosstab Query:
TRANSFORM Sum(productionDataPull.uplh) AS SumOfuplh
SELECT productionDataPull.prodFamily, productionDataPull.JOP, productionDataPull.goalRate
FROM productionDataPull
GROUP BY productionDataPull.prodFamily, productionDataPull.JOP, productionDataPull.goalRate
PIVOT Format([prodDate],"Short Date");

I also attached a screenshot of what I want it to look like (This screenshot is taken from the one date that works on the crosstab query)

"The Microsoft Database does not recognize " as a valid field or expression"

Click to expand...

Most likely the Source Query is returning a record with a null value for prodDate. Run the source query and sort it ascending on prodDate and you should find the culprit.

Additionally, the HAVING criteria in the Source query should be in a WHERE clause, not HAVING. HAVING is criteria on aggregate data (e.g. COUNT(), SUM(), MAX() etc.)

Tried running the source query, and there were no null values that were returned. I also tried changing the Having statement to Where and got an error message that it was invalid.

Jmuraski said:

Tried running the source query, and there were no null values that were returned. I also tried changing the Having statement to Where and got an error message that it was invalid.

Click to expand...

Hi. A crosstab query should only use whatever data is available unless you specify the column headings, which I don't see you did from the SQL statement you posted. To help you figure this out, you might consider posting a sample copy of your db.

Hello,

I have posted the development version of my DB.

Jmuraski said:

Hello,

I have posted the development version of my DB.

Click to expand...

Hi. Thanks for posting a copy of your db. Can you also give us a step-by-step instruction on how to duplicate your problem?

I sure can:

1) On the Manufacturing Tab go to the Weekly Summary box
2) In the single Date combobox select a Monday. There is available test data for the weeks of 11/11/2019, 11/18/2019 and 11/25/2019.
3) Press the button below that combobox.

If I enter a date of 11/18/2019, it works. Entering dates of either 11/11 or 11/25 produces the error

Jmuraski said:

I sure can:

1) On the Manufacturing Tab go to the Weekly Summary box
2) In the single Date combobox select a Monday. There is available test data for the weeks of 11/11/2019, 11/18/2019 and 11/25/2019.
3) Press the button below that combobox.

If I enter a date of 11/18/2019, it works. Entering dates of either 11/11 or 11/25 produces the error

Click to expand...

Hi. Thanks. I see what you mean now. Yes, the crosstab query doesn't have any problems recalculating which columns it uses, but a report can't do that on its own. When you designed your report, you bound its textboxes based on data from 11/18. When the crosstab query uses data from 11/11, then the report cannot know that the data has changed. What you will need to do is tell the report, via code, which data to use. Take a look at this demo to see how that could be done using VBA. Hope that helps...

Thanks for that link! I took a look through it and it mostly makes sense.

But I have another question about this. Is there another more effective way, without code, to display a report like that? What I want to display in a report is a summary of production data for every one of my product families, and if there is no production (uplh) value for that day, to display a null. At the end, I want the report to display 5 days, with the cells highlighted red for below goal, green for above goal and yellow for no value/null (with conditional formatting). A crosstab query seemed the most effective way to do this which is why I went that route. But I'm wondering if there is another way?

I ask because I am the only person in my company with any programming experience, and they will not hire any kind of programmer or DB admin. So, if I'm not here and something goes wrong, the whole DB goes kaput.

Jmuraski said:

Thanks for that link! I took a look through it and it mostly makes sense.

But I have another question about this. Is there another more effective way, without code, to display a report like that? What I want to display in a report is a summary of production data for every one of my product families, and if there is no production (uplh) value for that day, to display a null. At the end, I want the report to display 5 days, with the cells highlighted red for below goal, green for above goal and yellow for no value/null (with conditional formatting). A crosstab query seemed the most effective way to do this which is why I went that route. But I'm wondering if there is another way?

I ask because I am the only person in my company with any programming experience, and they will not hire any kind of programmer or DB admin. So, if I'm not here and something goes wrong, the whole DB goes kaput.

Click to expand...

Hi. A crosstab query is dynamic, but a report is not. So, one other alternative for you to share/display the data is to export the crosstab query into Excel rather than use a report, which is static.


By static and dynamic, I am talking about how the crosstab query will determine the necessary columns at runtime; whereas, a report has to know these columns before it can run.

I tried your database and am sorry to say that all 3 Mondays gave the same error for me. Possibly as I have UK date format?

Anyway its easy enough to get a crosstab report with dynamic headings. I have various examples both here and on my website. For example, see this example database Extended File Properties which includes code to do this

You can also use conditional formatting on dynamic crosstab reports. For example see two of the fairly low resolution screenshots for one of my commercial apps http://www.mendipdatasystems.co.uk/school-data-analyser/4584605482

Hope that's enough to help you move forward