Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Cross Tab query
- Thread starter NHogan
- Start date
I, too, once had problems writing parameterized crosstab queries. I have it down pat, now, but follow this link to see what it took to figure it out:
You can read the entire post and you should be able to figure it out. If not, follow this example. First, you need to specify your parameters in the SQL statement, as such (the following would be for a xtab query on a table with two columns -- the first with Fruit types and the second with quantities of each type of fruit). The following will allow you to select the type of fruit for which you want the total quantity.
PARAMETERS [Select Fruit (Apples or Oranges or Bananas)] Text ( 255 );
TRANSFORM Sum(MyTable.Quantity) AS SumOfQuantity
SELECT MyTable.Fruit, MyTable.Quantity, Sum(MyTable.Quantity) AS [Sum Of Quantity]
FROM Mytable
WHERE InStr([Select Fruit (Apples or Oranges or Bananas)],[Fruit])= Your_Value
PIVOT MyTable.Field2 In ("Apples";"Oranges";"Bananas"
;
If you want to be able to use multiple values (separated by commas) in the parameter dialog prompt, or if you want to use a wildcard to select all records, you will need to modify your WHERE InStr statement, like so:
PARAMETERS [Select Fruit (Apples or Oranges or Bananas)] Text ( 255 );
TRANSFORM Sum(MyTable.Quantity) AS SumOfQuantity
SELECT MyTable.Fruit, MyTable.Quantity, Sum(MyTable.Quantity) AS [Sum Of Quantity]
FROM Mytable
WHERE InStr([Select Fruit (Apples or Oranges or Bananas)],[Fruit])>0 OR InStr([Select Fruit (Apples or Oranges or Bananas)] IS NULL
PIVOT MyTable.Field2 In ("Apples";"Oranges";"Bananas"
The code above will allow you to specify multiple parameter values (separated by commas). It will also allow you to enter a null value to return all records.
Do not forget to force the column headings, or else you won't be able to use the query results to build reports.
I hope this helps.
Captain_D
You can read the entire post and you should be able to figure it out. If not, follow this example. First, you need to specify your parameters in the SQL statement, as such (the following would be for a xtab query on a table with two columns -- the first with Fruit types and the second with quantities of each type of fruit). The following will allow you to select the type of fruit for which you want the total quantity.
PARAMETERS [Select Fruit (Apples or Oranges or Bananas)] Text ( 255 );
TRANSFORM Sum(MyTable.Quantity) AS SumOfQuantity
SELECT MyTable.Fruit, MyTable.Quantity, Sum(MyTable.Quantity) AS [Sum Of Quantity]
FROM Mytable
WHERE InStr([Select Fruit (Apples or Oranges or Bananas)],[Fruit])= Your_Value
PIVOT MyTable.Field2 In ("Apples";"Oranges";"Bananas"
;If you want to be able to use multiple values (separated by commas) in the parameter dialog prompt, or if you want to use a wildcard to select all records, you will need to modify your WHERE InStr statement, like so:
PARAMETERS [Select Fruit (Apples or Oranges or Bananas)] Text ( 255 );
TRANSFORM Sum(MyTable.Quantity) AS SumOfQuantity
SELECT MyTable.Fruit, MyTable.Quantity, Sum(MyTable.Quantity) AS [Sum Of Quantity]
FROM Mytable
WHERE InStr([Select Fruit (Apples or Oranges or Bananas)],[Fruit])>0 OR InStr([Select Fruit (Apples or Oranges or Bananas)] IS NULL
PIVOT MyTable.Field2 In ("Apples";"Oranges";"Bananas"
;The code above will allow you to specify multiple parameter values (separated by commas). It will also allow you to enter a null value to return all records.
Do not forget to force the column headings, or else you won't be able to use the query results to build reports.
I hope this helps.
Captain_D
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question