Flipping Tables from Rows to Columns

[DaleMatt]

I have a recurring problem. Table A has these fields in each record:
Account - Month/Year - Edition - Amount

For any account there are records for each month + year, and for every
account + month + year there are records for several different "editions".

My problem is how to transform this data into something like the following:

Account - Amount for Edition A - Amount for Edition B - Amount for Edition C

where
-the records in each Amount column can be for different Month/Year ranges
(i.e., Amt-EditionA might be for 2008, Amt-Edition B might be for 3rd Qtr
2007, etc.).
-only the Editions I'm interested in show up (i.e., I don't want a column
for all editions, just for specific editions)
-the Amount-Edition columns show up in a specific order

Crosstab queries are only of limited help here; I'm looking for a solution
that doesn't include them. Currently I use several nested queries to perform
this task but am looking for more streamlined or effiicient solutions.

One final thing: performance is the biggest issue; queries that work in
minutes rather than tens of seconds are going to be a problem
(unfortunately!). There are approximately 250,000 records in the table with a
maximum of 75,000 relevant to any one query. The records are stored on a
server and are accessed by only a handful of people.

Any advice or suggestions would be GREATLY appreciated, and thanks in
advance for your time.

 

[Jeff Boyce]

Consider looking into a crosstab query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KARL DEWEY]

Try this --
SELECT [Table A].Account, Sum(IIf([Edition]="A",[Amount],0)) AS [Edition A],
Sum(IIf([Edition]="B",[Amount],0)) AS [Edition B],
Sum(IIf([Edition]="C",[Amount],0)) AS [Edition C]
FROM [Table A]
GROUP BY [Table A].Account;