query for managing data

[rohini]

hi..
can you please tell me how to write a query for the following.

this is the input file :

Zone	Code	Sold By	Dealer/Workshop/TASC	Option
E	11	dealer	abc (dealer)	2+2
E	11	workshop	abc(workshop)	2+1
S	13	dealer	wer (dealer)	2+1
S	12	dealer	tyu(dealer)	2+2
S	13	dealer	wer (dealer)	2+2
E	11	workshop	abc(workshop)	2+2
E	11	dealer	abc( dealer)	2+2
N	34	dealer	jkl ( dealer)	2+2
S	12	workshop	tyu(workshop)	3+1
S	13	dealer	wer (dealer)	2+2
W	45	workshop	hjk( dealer)	2+2

the required output file is as follows:

Zone	Code	Dealer/Workshop/TASC	dealer	workshop
E	11	abc	2	2
S	13	wer	3	0
S	12	tyu	1	1
N	34	jkl	1	0
W	45	hjk	1	0

i would like to run a query to get the table 'output' from the table 'input'

Thanks for your help in advance!

[recyan]

Just check out if below gives some guidelines :

Query1

SELECT 
    myTable.Zone, 
    myTable.Code, 
    myTable.[Dealer/Workshop/TASC], 
    myTable.[Sold By], 
    Count(myTable.Option) AS CountOfOption
FROM 
    myTable
GROUP BY 
    myTable.Zone, 
    myTable.Code, 
    myTable.[Dealer/Workshop/TASC], 
    myTable.[Sold By];

The Final Query

TRANSFORM Nz(Max([CountOfOption]), 0) AS TheNoOff
SELECT 
    Query1.Zone, Query1.Code, Query1.[Dealer/Workshop/TASC]
FROM 
    Query1
GROUP BY 
    Query1.Zone, Query1.Code, Query1.[Dealer/Workshop/TASC]
PIVOT 
    Query1.[Sold By];

Thanks

[rohini]

hey..thanks but i dint get the 'pivot' part in the final query as in how do you actually implement it?

[rohini]

as in the query i create is in design mode...how do i make a query in vba mode

[recyan]

Alternatively. check

TRANSFORM Nz(Count(myTable.Option),0) AS CountOfOption
SELECT myTable.Zone, myTable.Code, myTable.[Dealer/Workshop/TASC]
FROM myTable
GROUP BY myTable.Zone, myTable.Code, myTable.[Dealer/Workshop/TASC]
PIVOT myTable.[Sold By];

Not in to VBA syntax.
Let us wait for someone to come along.

Thanks

[rohini]

umm...no i meant where do i put the code that you have given?

[recyan]

Open a new query in Query Builder Design View.
Open it in SQL View & copy paste the code after replacing it with appropriate Table name & Field names.
Run it.
If it runs OK, open in Design mode & see the structure & then test it thoroughly with dummy data.
If it does not run OK, troubleshoot.

Thanks

[rohini]

hey! thanks a lot..i think the first code itself works !

however now i am having another problem...i would like the output to be saved in a table and i cant use this query as a make table query...

[recyan]

A small query,
Why do you want to save the output in a table ?

Thanks

[rohini]

Cause i will be further using this data for another query

[recyan]

If that is the reason, then there is no need to save the results to a table.
You can use a query as source for another query.

I don't know, if you tried my second code of a single query. Does it work ? If it works without any problem, better to use it, as less complicated.

Thanks

[rohini]

the second code went into some problems, probably just syntax...but then i thought if the first one works and i am new to access, better use it