Creating one record from many with pseudo crosstab

[GBall]

I saw a question similar to this a little while ago, but do you think I can find it ?
What I have is as follows

Product Month Sales
aaa 01 123
bbb 01 222
ccc 01 333
aaa 02 312
bbb 02 444
aaa 03 555

What I need is
product month1 month2 month3
aaa 123 312 555
bbb 222 444
ccc 333

Now I'm sure that this question was answered, I just can't find it

 

[tlbroadbent]

You need to create a cross-tab query. When you create a new query you should be able to choose the Cross-tab query wizard. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[GBall]

Unfortunately, I don't think that the crosstab wizard is capable of providing that solution, but I'm sure that one of the clever sql people on this forum supplied quite a nifty solution.

 

[tlbroadbent]

Here is some "clever" SQL code that returns the result you want. It was created by that most clever of SQL people, Ms. "Crosstab Query Wizard" with a little help from not so clever me.

TRANSFORM Sum(ProdSales.Sales) AS SalesTot
SELECT ProdSales.Product
FROM ProdSales
GROUP BY ProdSales.Product
PIVOT "Month" & Format([Month]);
Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[GBall]

Terry,
with such wit you must be a brit !
But you have highlighted a mistake on my part.
What I really want (what I really really want ...geddit?)
is

product month1 sales1 month2 sales2 month3 sales3
aaa 01 123 02 312 03 555
bbb 01 222 02 444
ccc 03 333

 

[tlbroadbent]

Actually, I'm an American with deep British roots.

Here is a query based on similar queries that I've created in SQL Server's T-SQL language. I don't know if it is exactly what you want but hopefully it will give you an idea to get you started.

SELECT
ProdSales.Product,
Max(IIf([month]=1,"01","&quot) AS Month1,
Sum(IIf([Month]=1,[Sales],0)) AS Sales1,
Max(IIf([month]=2,"02","&quot) AS Month2,
Sum(IIf([Month]=2,[Sales],0)) AS Sales2,
Max(IIf([month]=3,"03","&quot) AS Month3,
Sum(IIf([Month]=3,[Sales],0)) AS Sales3,
Max(IIf([month]=4,"04","&quot) AS Month4,
Sum(IIf([Month]=4,[Sales],0)) AS Sales4,
Max(IIf([month]=5,"05","&quot) AS Month5,
Sum(IIf([Month]=5,[Sales],0)) AS Sales5,
Max(IIf([month]=6,"06","&quot) AS Month6,
Sum(IIf([Month]=6,[Sales],0)) AS Sales6
FROM ProdSales
GROUP BY ProdSales.Product; Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[GBall]

thanks Terry, but my data is more dynamic than the example I gave (trying to be too simplistic) and I can't use the sort of hardcoding that you have used.
I've tried to aim in the direction of your example, which looks close, but can't see my way forward. Maybe there isn't a way forward in SQL. I know I can do it easily using VBA, but that's not what the user wants.
FYI the actual data is

product unit_type units
1234 8888 100
1234 1414 011
3131 1111 020
3131 4355 240
3131 6111 900
3131 6222 750

and so we should get

product fund1 units1 fund2 unit2 fund3 units3 etc.
1234 8888 100 1414 011
3131 1111 020 4355 240 6111 900

thanks for your help

 

[MichaelRed]

Create a column in your table for "Identification". Create two crosstab queries. Join them in a Select query on the identification. Arrange the columns in the select query (form/report) to suit.

Since Terry has generously supplied A crosstab query, the rest is left for the student.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[GBall]

Still can't see it.
I'm obviously too anal for crosstabs.
Disappearing in an orifice not a million miles away (well, pub actually !).

Thanks anyway guys.