Crosstab Query Date Range

[falcondeer]

Hi

Could anybody help me figure out why my attached crosstab query asks for date range twice.

Thanks

 

[Minty]

I think it's possibly because the underlying query is asking for the same parameters?

But I can't see enough of that to tell.

 

[Gasman]

You can make the criteria tempvars and set those?

 

[falcondeer]

You can make the criteria tempvars and set those?

Thanks Gasman

Would you please tell me how to do that, I am done with my project and this is the only thing that I need to make the user able to specify a time period instead of pulling all data since DB created.

I tried this but not working:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
TRANSFORM Count(qryPrescribedWA.P_MRN) AS CountOfP_MRN
SELECT qryPrescribedWA.PR_Items, Count(qryPrescribedWA.P_MRN) AS [Total Of P_MRN]
FROM qryPrescribedWA
WHERE (((qryPrescribedWA.PR_Date) Between [TempVars]![Start Date] And [TempVars]![End Date]))
GROUP BY qryPrescribedWA.PR_Items
PIVOT qryPrescribedWA.PR_Division;

 

[Gasman]

Well you need to define the parameters as the same?

You can use the query without the parameters statement? If you delete that statement in the SQL window and have set the tempvars, it should work.

 

[Minty]

@Gasman - you have to declare the parameters in a cross tab query, it won't work otherwise.

 

[Gasman]

@Gasman - you have to declare the parameters in a cross tab query, it won't work otherwise.

Oh really?, sorry, obviously did not know that, but they can be TempVars surely?

 

[Minty]

Yes, I don't see why not.

 

[Gasman]

I was thinking along these lines?

PARAMETERS [TempVars]![Start] DateTime, [TempVars]![End] DateTime;
TRANSFORM Sum(TestTransactions.ID) AS SumOfID
SELECT TestTransactions.Description, TestTransactions.Category, TestTransactions.TransactionDate, Sum(TestTransactions.ID) AS [Total Of ID]
FROM TestTransactions
WHERE (((TestTransactions.TransactionDate) Between [tempvars]![start] And [tempvars]![end]))
GROUP BY TestTransactions.Description, TestTransactions.Category, TestTransactions.TransactionDate
PIVOT Format([TransactionDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

 

[falcondeer]

Yes, I don't see why not.

Thanks for the response but still don't know how ! I am not that expert
is it like in the attachment

 

[arnelgp]

Could anybody help me figure out why my attached crosstab query asks for date range twice

It is Normal to ask Twice because you have 2 criteria, the Start Date and the End Date.

 

[Mike Krailo]

Entering a date RANGE twice is not normal.

 

[arnelgp]

unless this query has same criteria also:

qryPrescribedWA.P_MRN

 

[Gasman]

Thanks for the response but still don't know how ! I am not that expert
is it like in the attachment

Yes

 

[Gasman]

@Gasman - you have to declare the parameters in a cross tab query, it won't work otherwise.

@Minty
I do not want to appear contrary here, but I had to check for myself, as though my memory was bad, I *thought* I had done it before, only because I rarely used the Parameters option? Then again I had not created that many crosstab queries.

I just removed the Parameters line form that crosstab query I posted and it still worked? So what have I missed?

TRANSFORM Sum(TestTransactions.Amount) AS SumOfAmount
SELECT TestTransactions.Category, Sum(TestTransactions.Amount) AS [Total Of Amount]
FROM TestTransactions
WHERE (((TestTransactions.TransactionDate) Between [tempvars]![start] And [tempvars]![end]))
GROUP BY TestTransactions.Category
PIVOT Format([TransactionDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

 

[Minty]

There is definitely a case when a crosstab won't work without parameters being declared.

After some research - You have got around the requirement by specifying the Column headings.
If you remove those you will get the problem.

See that Allen Browne bloke again for the low-down http://allenbrowne.com/ser-67.html

 

[Gasman]

Ahh, OK, I generally would use the wizard at least to create the initial query, and I saw both parameters and column headings mentioned in that link, so would likely always have column headings.
Thank you.