G
Guest
I have a cross tab query that I want to only pull info forma parameter on a
form.
On the form ir will ask you for 6 months and I want the cross tab query to
only pull those 6 months, however they will change all the time so I want to
use parameters
I am able to get the parameters to work on the first query but they do not
carry over onto the cross -tab
Can anyone help please?
Here is my original query
SELECT DISTINCTROW tblStores.StoreName,
Format$([tblPeriods].[FirstDayOfPeriod],'mmmm yyyy') AS [FirstDayOfPeriod By
Month], Sum(tblSales.SalesSales) AS [Sum Of SalesSales]
FROM tblStores INNER JOIN (tblPeriods INNER JOIN tblSales ON
tblPeriods.PeriodNo = tblSales.SalesPeriodNo) ON tblStores.StoreNo =
tblSales.SalesStoreNo
GROUP BY tblStores.StoreName, Format$([tblPeriods].[FirstDayOfPeriod],'mmmm
yyyy'),
Year([tblPeriods].[FirstDayOfPeriod])*12+DatePart('m',[tblPeriods].[FirstDayOfPeriod])-1
HAVING (((Format$([tblPeriods].[FirstDayOfPeriod],'mmmm
yyyy'))=[Forms]![frmReportCard]![Month] Or
(Format$([tblPeriods].[FirstDayOfPeriod],'mmmm
yyyy'))=[Forms]![frmReportCard]![Month2] Or
(Format$([tblPeriods].[FirstDayOfPeriod],'mmmm
yyyy'))=[Forms]![frmReportCard]![Month 3] Or
(Format$([tblPeriods].[FirstDayOfPeriod],'mmmm
yyyy'))=[Forms]![frmReportCard]![Month4] Or
(Format$([tblPeriods].[FirstDayOfPeriod],'mmmm
yyyy'))=[Forms]![frmReportCard]![Month5] Or
(Format$([tblPeriods].[FirstDayOfPeriod],'mmmm
yyyy'))=[Forms]![frmReportCard]![Month6]));
And here is the cross tab
TRANSFORM Sum(qryReportCard1.[Sum Of SalesSales]) AS [SumOfSum Of SalesSales]
SELECT qryReportCard1.StoreName, Sum(qryReportCard1.[Sum Of SalesSales]) AS
[Total Of Sum Of SalesSales]
FROM qryReportCard1
GROUP BY qryReportCard1.StoreName
ORDER BY qryReportCard1.[FirstDayOfPeriod By Month]
PIVOT qryReportCard1.[FirstDayOfPeriod By Month];
form.
On the form ir will ask you for 6 months and I want the cross tab query to
only pull those 6 months, however they will change all the time so I want to
use parameters
I am able to get the parameters to work on the first query but they do not
carry over onto the cross -tab
Can anyone help please?
Here is my original query
SELECT DISTINCTROW tblStores.StoreName,
Format$([tblPeriods].[FirstDayOfPeriod],'mmmm yyyy') AS [FirstDayOfPeriod By
Month], Sum(tblSales.SalesSales) AS [Sum Of SalesSales]
FROM tblStores INNER JOIN (tblPeriods INNER JOIN tblSales ON
tblPeriods.PeriodNo = tblSales.SalesPeriodNo) ON tblStores.StoreNo =
tblSales.SalesStoreNo
GROUP BY tblStores.StoreName, Format$([tblPeriods].[FirstDayOfPeriod],'mmmm
yyyy'),
Year([tblPeriods].[FirstDayOfPeriod])*12+DatePart('m',[tblPeriods].[FirstDayOfPeriod])-1
HAVING (((Format$([tblPeriods].[FirstDayOfPeriod],'mmmm
yyyy'))=[Forms]![frmReportCard]![Month] Or
(Format$([tblPeriods].[FirstDayOfPeriod],'mmmm
yyyy'))=[Forms]![frmReportCard]![Month2] Or
(Format$([tblPeriods].[FirstDayOfPeriod],'mmmm
yyyy'))=[Forms]![frmReportCard]![Month 3] Or
(Format$([tblPeriods].[FirstDayOfPeriod],'mmmm
yyyy'))=[Forms]![frmReportCard]![Month4] Or
(Format$([tblPeriods].[FirstDayOfPeriod],'mmmm
yyyy'))=[Forms]![frmReportCard]![Month5] Or
(Format$([tblPeriods].[FirstDayOfPeriod],'mmmm
yyyy'))=[Forms]![frmReportCard]![Month6]));
And here is the cross tab
TRANSFORM Sum(qryReportCard1.[Sum Of SalesSales]) AS [SumOfSum Of SalesSales]
SELECT qryReportCard1.StoreName, Sum(qryReportCard1.[Sum Of SalesSales]) AS
[Total Of Sum Of SalesSales]
FROM qryReportCard1
GROUP BY qryReportCard1.StoreName
ORDER BY qryReportCard1.[FirstDayOfPeriod By Month]
PIVOT qryReportCard1.[FirstDayOfPeriod By Month];