Transform Query

[Guest]

Hi

Query below executes properly, creating a separate column for each drug name, but creates an additiona
block of rows (Stores) for each drug. How can I get just one row for each store, with an entry for each dru
on the same line

Thanks
Mik

Transform Sum(Data.`# of scripts`) AS sumofscript
SELECT DISTINCT Data.`Drug Name`, Panels.Stor
FROM `Cycle 8 Data`.Data Data, `Cycle 8 Data`.Panels Panel
WHERE Panels.Store = Data.`ABS #`
GROUP BY Data.`Drug Name`, Panels.Stor
PIVOT Data.`Drug Name

Sample - Data
STORE DRUG SALE
161 P 15MG
161 R 15MG
161 T 15MG
161 P 30 MG
161 R 30MG

Desired - Outpu

Store P15 R15 T15 P 30 R3
161 5 7 5 9 6

 

[John Viescas]

You put Drug Name in there one more time than necessary:

Transform Sum(Data.`# of scripts`) AS sumofscripts
SELECT DISTINCT Panels.Store
FROM `Cycle 8 Data`.Data Data, `Cycle 8 Data`.Panels Panels
WHERE Panels.Store = Data.`ABS #`
GROUP BY Panels.Store
PIVOT Data.`Drug Name`


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Hi,

Query below executes properly, creating a separate column for each drug

name, but creates an additional

block of rows (Stores) for each drug. How can I get just one row for each

store, with an entry for each drug

on the same line ?

Thanks,
Mike

Transform Sum(Data.`# of scripts`) AS sumofscripts
SELECT DISTINCT Data.`Drug Name`, Panels.Store
FROM `Cycle 8 Data`.Data Data, `Cycle 8 Data`.Panels Panels
WHERE Panels.Store = Data.`ABS #`
GROUP BY Data.`Drug Name`, Panels.Store
PIVOT Data.`Drug Name`

Sample - Data
STORE DRUG SALES
161 P 15MG 5
161 R 15MG 7
161 T 15MG 5
161 P 30 MG 9
161 R 30MG 6

Desired - Output

Store P15 R15 T15 P 30 R30
161 5 7 5

9 6