how can i design this query??

[hotmalepiyush]

i am really new to access

i have built a table which appears like this:

SCRIP DATE QTY
A 6-jun 100
B 6-jun 50
. .. ..
Z 6-jun 200
A 5-jun 140
B 5-jun 150
. .. ..
Z 5-jun 200


there are many more fields...

i need a query which returns teh following:

SCRIP 6-jun 5-jun
A 100 140
B 50 150
. . .
Z 200 200

also the no. and names of scrips may vary for each date..

plz help.

 

[MStef]

Use a CROSTAB QUERY for this.

 

[hotmalepiyush]

crosstab allows me to select only one field.. i need to show multiple fields

 

[MStef]

Look at "DemoCrostabScripA2000.mdb" (attachment).
Run "Query1Crostab", I think it is what you need.

 

[hotmalepiyush]

i m using an older version of MS Access so cannot open ur file plz send me the sql.. and design details... i guess i will be able to understand

 

[MStef]

OK, Look at word attachment (zip)

 

[hotmalepiyush]

crosstab works only when i have 3 fields..
lemme re-state the problem

i have a table which contains 5 feilds: scrip, date, qty, deliverable qty, deliverable qty% there is no primary key defined

each day i recieve values for qty, deliverable qty, deliverable qty% for some 250 scrips(varies rom day to day).
say i have data for june 5th,june 6th and so on...

i need a query which shows scrip in the first row then qty for 5th jun,deliverable qty for 5th jun,deliverable qty% for 5th jun,then qty for 6th jun,deliverable qty for 6th jun,deliverable qty% for 6th jun,

and so on..

please suggest a way

thanx

piyush

 

[khawar]

Check out Main query in the attached sample
Does this fulfill your requirements


Regards

Khawar

 

[hotmalepiyush]

Check out Main query in the attached sample
Does this fulfill your requirements


Regards

Khawar

dude i am using an older version of access.. so could not see what u have sent... plz help

 

[khawar]

Uploaded again in 97 format



Khawar

 

[hotmalepiyush]

thank you khawar.. i just have basic knowledge of SQL it would be great if you can explain me this part of ur query,,

[DateS] & "-Qty"

 

[khawar]

it is to get the string combined with date

Sdate is the date in the table and by writting the above statement i get the date and
"-qty" combined so it looks like

06-jun-Qty ,07-Jun-Qty and so on