Re: make sql query without crosstab

working on Groups query

i tested IIF in access it is working good

there is a little problem i did not mention it before.

the groups may be unlimiting numbers

Groups ID 1,2,3,4,5..... and same in catagory

i thank you very much for your reply Bill Karwin .


Bill Karwin wrote:
aljohnssngp@xxxxxxxxxxxx wrote:
should have put this question in here

http://groups.google.com/group/microsoft.public.vb.database/browse_thread/thread/8b0dc1b88dd60c4c

"Nwind" as far as I can tell is not a separate product, it is a sample
database for Microsoft Access. I am not practiced with MS Access, but
here's how I'd do this in MySQL:

By Groups:

SELECT i.Area, SUM(IF(g.group = 'Cupboard', i.qty, 0)) AS Total_Cupboard,
SUM( IF(g.group = 'Chair', i.qty, 0) ) AS Total_Chair,
SUM( IF(g.group = 'Bed', i.qty, 0) ) AS Total_Bed
FROM invoice AS i INNER JOIN group AS g ON i.category = g.id
GROUP BY i.area;

By Categories:

SELECT i.Area, SUM(IF(c.category = 'Acat', i.qty, 0)) AS Total_Acat,
SUM( IF(c.category = 'Bcat', i.qty, 0) ) AS Total_Bcat,
SUM( IF(c.category = 'Ccat', i.qty, 0) ) AS Total_Ccat
FROM invoice AS i INNER JOIN category AS c ON i.category = c.id
GROUP BY i.area;

The only nonstandard SQL above is the use of the "IF" function. I don't
know if Access supports an "IF" function as I am using it above. I
found a reference to a function "IIF" but it appears to be a client-side
function, so I'm not sure it'll work in this situation.

Refer to your MS Access documentation for details.

Regards,
Bill K.

.