Cross-Tab Query

L

Lisa

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];
 
J

John Spencer

First thing, with a crosstab query you MUST declare your parameters and if
any
other queries are used in the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

If you enter directly into the SQL view, you would need to end up with
something like:

Parameters [Forms]![frmReportCard]![Month] text(255),
[Forms]![frmReportCard]![Month2] text(255),
[Forms]![frmReportCard]![Month3] text(255),
[Forms]![frmReportCard]![Month4] text(255),
[Forms]![frmReportCard]![Month5] text(255),
[Forms]![frmReportCard]![Month6] text(255);
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

WHERE (((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]))

GROUP BY tblStores.StoreName
, Format$([tblPeriods].[FirstDayOfPeriod],'mmmm yyyy'),
Year([tblPeriods].[FirstDayOfPeriod])*12+DatePart('m',[tblPeriods].[FirstDayOfPeriod])-1


By the way, I would moved the Having clause into a WHERE clause for
increased efficiency.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Lisa said:
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];
Click to expand...
 
D

Duane Hookom

I would try do this with a single totals query:
SELECT tblStores.StoreName,
SUM(Abs(Format([FirstDayOfPeriod],'mmmm yyyy'))
=[Forms]![frmReportCard]![Month]) * SalesSales) As Mth1,
SUM(Abs(Format([FirstDayOfPeriod],'mmmm yyyy'))
=[Forms]![frmReportCard]![Month2]) * SalesSales) As Mth2,
SUM(Abs(Format([FirstDayOfPeriod],'mmmm yyyy'))
=[Forms]![frmReportCard]![Month3]) * SalesSales) As Mth3,
SUM(Abs(Format([FirstDayOfPeriod],'mmmm yyyy'))
=[Forms]![frmReportCard]![Month4]) * SalesSales) As Mth4,
SUM(Abs(Format([FirstDayOfPeriod],'mmmm yyyy'))
=[Forms]![frmReportCard]![Month5]) * SalesSales) As Mth5,
SUM(Abs(Format([FirstDayOfPeriod],'mmmm yyyy'))
=[Forms]![frmReportCard]![Month6]) * SalesSales) As Mth6
FROM tblStores INNER JOIN
(tblPeriods INNER JOIN tblSales ON tblPeriods.PeriodNo =
tblSales.SalesPeriodNo)
ON tblStores.StoreNo = tblSales.SalesStoreNo
GROUP BY tblStores.StoreName;
--
Duane Hookom
Microsoft Access MVP


Lisa said:
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];
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

REP: Changing Text to Number 1
How to display zero value in blank fields of Cross Tab query 0
Crosstab : PIVOT ... IN (...) problem ! 6
Format column headings in Cross-Tab 1
Access 2007 - Need YTD AND Monthly Info From Query 0
Consecutive months 2
Cross tab query solution 4
Combining 3 queries 2

Top