Access Query with Paramaters

Question

I have a series of Access queries where I have identified data parameters. Because there are multiple queries and a chart, the process requests the information multiple times. Is there a better process method that would reduce the duplication of information entry?

The first query "qryWOData_Crosstab" is a Select Query:

SELECT Month([MCMP date]) AS Months, [Operational_Data_data (1)].[Order Number], [Operational_Data_data (1)].[On Time], [Operational_Data_data (1)].[Main Work Center]
FROM [Operational_Data_data (1)]
GROUP BY Month([MCMP date]), [Operational_Data_data (1)].[Order Number], [Operational_Data_data (1)].[On Time], [Operational_Data_data (1)].[Main Work Center];

The second query qryCrosstab_SubTotal is a Crosstab Query:

PARAMETERS [Enter main Work Center:] Text ( 255 );
TRANSFORM (Nz(Count([qryWOData_Crosstab].[Order Number]),0)) AS [CountOfOrder Number]
SELECT qryWOData_Crosstab.Months, Count(qryWOData_Crosstab.[Order Number]) AS [Total Of Order Number]
FROM qryWOData_Crosstab
WHERE (((qryWOData_Crosstab.[Main Work Center]) Like "" & [Enter Main Work Center:] & ""))
GROUP BY qryWOData_Crosstab.Months, qryWOData_Crosstab.[Main Work Center]
PIVOT qryWOData_Crosstab.[On Time];

The third Query qryCrossTab_SubTotal 2 is a Select Query:

SELECT DISTINCTROW qryCrossTab_SubTotal.Months, Sum(qryCrossTab_SubTotal.[MCMP or Priority Code Missing]) AS [Sum Total MCMP or Priority Code Missing], Sum([MCMP or Priority Code Missing])/Sum([Total Of Order Number]) AS [MCMP or No Priority %], Sum(qryCrossTab_SubTotal.[On Time]) AS [Sum Total On Time], Sum([On Time])/Sum([Total Of Order Number]) AS [On Time %], Sum(qryCrossTab_SubTotal.[Not On Time]) AS [Sum Total Not On Time], Sum([Not On Time])/Sum([Total Of Order Number]) AS [Not On Time %], Sum(qryCrossTab_SubTotal.[Total Of Order Number]) AS [Sum Total Order Number], 0.73 AS [Goal %]
FROM qryCrossTab_SubTotal
GROUP BY qryCrossTab_SubTotal.Months;

Answer 1

Hello @Ken Sheridan

Thanks for the response. I have added your suggestion from above to the first query...

PARAMETERS [Forms]![frmDClassification]![Classification] Text ( 255 );
TRANSFORM (Nz(Count([qryWOData1].[Order Number]),0)) AS [CountOfOrder Number]
SELECT qryWOData1.MonthYr, Count(qryWOData1.[Order Number]) AS [Total Of Order Number]
FROM qryWOData1
WHERE (((qryWOData1.[Main Work Center])=[Forms]![frmDClassification]![Classification]))
GROUP BY qryWOData1.MonthYr, qryWOData1.[Main Work Center], qryWOData1.MCMPYear, qryWOData1.MCMPMonth
PIVOT qryWOData1.[On Time];

At that point I'm confused about how to handle for the end result. Do I need to then combine MCMPMonth and MCMPYear and group on that result and Order by MCMPMonth? I do want my data to appear as...

Jul2021
Aug2021
Sep2021
Oct2021
Nov2021
Dec2021
Jan2022
etc.

Answer 2

You just need to add MCPYear, MCPMonth at the start of the crosstab query's GROUP BY clause.

Answer 3

Hello @ken

Again, thanks for all your assistance. It is very much appreciated. I am still having issues with the repeating request for entering parameter values. I have created the dialogue form which calls OpenReport based on the 3rd Query. I still receive the following multiple times. I also receive these when I attempt to make changes or updates to the report, etc.

Query1
SELECT tblTableauWOList.[Order Number], tblTableauWOList.[On Time], tblTableauWOList.[Main Work Center], tblTableauWOList.[MCMP Date], Year([MCMP Date]) AS MCMPYear, Month([MCMP Date]) AS MCMPMonth
FROM tblTableauWOList
WHERE (((tblTableauWOList.[MCMP Date])>=#7/1/2021#))
ORDER BY tblTableauWOList.[MCMP Date];

Query 2
PARAMETERS [Forms]![frmDClassification]![Classification] Text ( 255 );
TRANSFORM (Nz(Count([qryWOData1].[Order Number]),0)) AS [CountOfOrder Number]
SELECT qryWOData1.[Main Work Center], qryWOData1.MCMPYear, qryWOData1.MCMPMonth, Count(qryWOData1.[Order Number]) AS [Total Of Order Number]
FROM qryWOData1
GROUP BY qryWOData1.[Main Work Center], qryWOData1.MCMPYear, qryWOData1.MCMPMonth
ORDER BY qryWOData1.MCMPYear, qryWOData1.MCMPMonth
PIVOT qryWOData1.[On Time];

Query 3
SELECT DISTINCTROW qryWOData2.MCMPYear, qryWOData2.MCMPMonth, MonthName([MCMPMonth],True) AS MonthLbl, Sum(qryWOData2.[MCMP or Priority Code Missing]) AS [Sum Total MCMP or Priority Code Missing], Sum([MCMP or Priority Code Missing])/Sum([Total Of Order Number]) AS [MCMP or No Priority %], Sum(qryWOData2.[On Time]) AS [Sum Total On Time], Sum([On Time])/Sum([Total Of Order Number]) AS [On Time %], Sum(qryWOData2.[Not On Time]) AS [Sum Total Not On Time], Sum([Not On Time])/Sum([Total Of Order Number]) AS [Not On Time %], Sum(qryWOData2.[Total Of Order Number]) AS [Sum Total Order Number], 0.73 AS [Goal %], qryWOData2.[Main Work Center]
FROM qryWOData2
GROUP BY qryWOData2.MCMPYear, qryWOData2.MCMPMonth, MonthName([MCMPMonth],True), qryWOData2.[Main Work Center]
HAVING (((qryWOData2.[Main Work Center])=[Forms]![frmDClassification]![Classification]));

Answer 4

That suggests that the form is either not open in form view, is a subform (subforms are not members of the Forms collection) or the name of the form and/or control is misspelt.