should have put this question in here


http://groups.google.com/group/micro...0dc1b88dd60c4c

aljohnssngp (AT) yahoo (DOT) com.sg wrote:
"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.

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 (AT) yahoo (DOT) com.sg wrote:
So I guess you need a crosstab for any and all group id's that occur in
the data. This is hard to do in SQL. In general, you need to know each
distinct value so you can make a column for each one. There is no way
to query for a variable number of columns. SQL does not know how to
produce additional columns for you.

Regards,
Bill K.

"Bill Karwin" <bill (AT) karwin (DOT) com> wrote

For a more complete treatment the OP can look in "Oracle: The Complete
Reference" for a section entitled "Turning a table on its side". What its
really discussing is crosstabulation.

Thank you for the valuable replies
I dont have Oracle complete reference,so no information on this is
avilable.

i think the change in table field structure will make it easy for me.

but it will contain duplicate values.but qurey will be easy


David Cressey wrote:

Thank you for the valuable replies
I dont have Oracle complete reference,so no information on this is
avilable.

so change in table field structure will make it easy for me.

but it will contain duplicate values.but qurey will be easy


David Cressey wrote:

is it good practice to create temporary tables for querying only?


aljohnssngp (AT) yahoo (DOT) com.sg wrote:

aljohnssngp (AT) yahoo (DOT) com.sg wrote:
Sure, sometimes it is the most straightforward solution. In some cases
using a temp table can offer better performance than trying to do the
same work in a very complex query. It depends on each individual case.

Regards,
Bill K.

Pivot table is not avilble in MSsql,

i may use Mysql or MSsql or Oracle or MS ACCESS itself, so there must
be some

methods to achive this common to all.

As Bill karwin point out i should go for temporary tables.

any help in this area ?


Bill Karwin wrote: