Prev: Query Speed
Next: 2 quesions about query
From: AccessKay on 23 Mar 2010 10:02
Good Morning,

I read the threads about creating parameters in crosstab queries and this
helped me to get it to work but the parameter box pops up when I try to save
the query. Though my changes are saved, I don't think this is normal? It
happens in all views. Can anyone tell me what might be wrong? Here is my
SQL:

PARAMETERS [Enter Month and Year] DateTime;
TRANSFORM Sum(Trans_Mstr_ODC.ODC_Cost) AS SumOfODC_Cost
SELECT Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category,
Trans_Mstr_ODC.Product, Sum(Trans_Mstr_ODC.ODC_Cost) AS [Total Of ODC_Cost]
FROM Trans_Mstr_ODC
WHERE (((Trans_Mstr_ODC.TransDate)=[Enter Month and Year]))
GROUP BY Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category,
Trans_Mstr_ODC.Product
PIVOT Trans_Mstr_ODC.ODC_Cost_Category;

TIA,
Kay

From: Jerry Whittle on 23 Mar 2010 10:36
Crosstabs can be a little buggy with parameters. Your best bet is to create a
normal select query that includes the declared parameter and any other
criteria to gather up the records and fields that you want to see. Make sure
that this query works then save it with a name. Next create a crosstab query
using the first query as its record source.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"AccessKay" wrote:

> Good Morning,
>
> I read the threads about creating parameters in crosstab queries and this
> helped me to get it to work but the parameter box pops up when I try to save
> the query. Though my changes are saved, I don't think this is normal? It
> happens in all views. Can anyone tell me what might be wrong? Here is my
> SQL:
>
> PARAMETERS [Enter Month and Year] DateTime;
> TRANSFORM Sum(Trans_Mstr_ODC.ODC_Cost) AS SumOfODC_Cost
> SELECT Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category,
> Trans_Mstr_ODC.Product, Sum(Trans_Mstr_ODC.ODC_Cost) AS [Total Of ODC_Cost]
> FROM Trans_Mstr_ODC
> WHERE (((Trans_Mstr_ODC.TransDate)=[Enter Month and Year]))
> GROUP BY Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category,
> Trans_Mstr_ODC.Product
> PIVOT Trans_Mstr_ODC.ODC_Cost_Category;
>
> TIA,
> Kay
>
From: AccessKay on 23 Mar 2010 11:16
Thank you. I will do this. I've been trying to not use so many layers of
queries but I guess I'll have to accept the crosstab's buggy nature.

Kay

"Jerry Whittle" wrote:

> Crosstabs can be a little buggy with parameters. Your best bet is to create a
> normal select query that includes the declared parameter and any other
> criteria to gather up the records and fields that you want to see. Make sure
> that this query works then save it with a name. Next create a crosstab query
> using the first query as its record source.
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
> "AccessKay" wrote:
>
> > Good Morning,
> >
> > I read the threads about creating parameters in crosstab queries and this
> > helped me to get it to work but the parameter box pops up when I try to save
> > the query. Though my changes are saved, I don't think this is normal? It
> > happens in all views. Can anyone tell me what might be wrong? Here is my
> > SQL:
> >
> > PARAMETERS [Enter Month and Year] DateTime;
> > TRANSFORM Sum(Trans_Mstr_ODC.ODC_Cost) AS SumOfODC_Cost
> > SELECT Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category,
> > Trans_Mstr_ODC.Product, Sum(Trans_Mstr_ODC.ODC_Cost) AS [Total Of ODC_Cost]
> > FROM Trans_Mstr_ODC
> > WHERE (((Trans_Mstr_ODC.TransDate)=[Enter Month and Year]))
> > GROUP BY Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category,
> > Trans_Mstr_ODC.Product
> > PIVOT Trans_Mstr_ODC.ODC_Cost_Category;
> >
> > TIA,
> > Kay
> >
From: John Spencer on 23 Mar 2010 12:02
That is not the standard behavior. Simply saving the query should not display
the parameter prompt.

What version of Access are you using?

I would try copying the SQL text into a new blank query and see if you get the
same behavior. If not, then you have something in your malfunctioning query
that is causing the problem. The simplest cure would be to delete the
offending query and rename the new query with the name of the old query.

BACK UP YOUR DATABASE before doing this. Just in case.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

AccessKay wrote:
> Good Morning,
>
> I read the threads about creating parameters in crosstab queries and this
> helped me to get it to work but the parameter box pops up when I try to save
> the query. Though my changes are saved, I don't think this is normal? It
> happens in all views. Can anyone tell me what might be wrong? Here is my
> SQL:
>
> PARAMETERS [Enter Month and Year] DateTime;
> TRANSFORM Sum(Trans_Mstr_ODC.ODC_Cost) AS SumOfODC_Cost
> SELECT Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category,
> Trans_Mstr_ODC.Product, Sum(Trans_Mstr_ODC.ODC_Cost) AS [Total Of ODC_Cost]
> FROM Trans_Mstr_ODC
> WHERE (((Trans_Mstr_ODC.TransDate)=[Enter Month and Year]))
> GROUP BY Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category,
> Trans_Mstr_ODC.Product
> PIVOT Trans_Mstr_ODC.ODC_Cost_Category;
>
> TIA,
> Kay
>
From: AccessKay on 23 Mar 2010 14:57
I did what you said about copying the SQL into a fresh query and this worked.
Strange…but my problem is solved (for now). BTW, I'm using Access 2007.

Thank you,
Kay


"John Spencer" wrote:

> That is not the standard behavior. Simply saving the query should not display
> the parameter prompt.
>
> What version of Access are you using?
>
> I would try copying the SQL text into a new blank query and see if you get the
> same behavior. If not, then you have something in your malfunctioning query
> that is causing the problem. The simplest cure would be to delete the
> offending query and rename the new query with the name of the old query.
>
> BACK UP YOUR DATABASE before doing this. Just in case.
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
>
> AccessKay wrote:
> > Good Morning,
> >
> > I read the threads about creating parameters in crosstab queries and this
> > helped me to get it to work but the parameter box pops up when I try to save
> > the query. Though my changes are saved, I don't think this is normal? It
> > happens in all views. Can anyone tell me what might be wrong? Here is my
> > SQL:
> >
> > PARAMETERS [Enter Month and Year] DateTime;
> > TRANSFORM Sum(Trans_Mstr_ODC.ODC_Cost) AS SumOfODC_Cost
> > SELECT Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category,
> > Trans_Mstr_ODC.Product, Sum(Trans_Mstr_ODC.ODC_Cost) AS [Total Of ODC_Cost]
> > FROM Trans_Mstr_ODC
> > WHERE (((Trans_Mstr_ODC.TransDate)=[Enter Month and Year]))
> > GROUP BY Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category,
> > Trans_Mstr_ODC.Product
> > PIVOT Trans_Mstr_ODC.ODC_Cost_Category;
> >
> > TIA,
> > Kay
> >
> .
>
 | 
Pages: 1
Prev: Query Speed
Next: 2 quesions about query