can this be made in a single query??

[murali]

I have a table called customer where the master details like name,address etc... my second table contains the details of the downloads he made from my website... for each download I capture the document's shortname like dwn1,dwn2,dwn3 etc in a field called download_details...

Now I need to prepare a report where I need to track down the no of downloads for each user on a particular day. I join the two tables by matching the emai id and taking the record.

my req should be like this
Date name email addre state country dwn1 dwn2 dwn3....
20/04/ Test t@t.c tst t t 2 0 3

the corresponding details in dwn_details will be like
20/04 t@t.c dwn1,dwn3
20/04 t@t.c dwn1,dwn3
20/04 t@t.c dwn3

First I join the tables by checking for the data for the dates entered by the user. and then within the recordset I query for the download table matching the email and date and by applying single level control break I achieve the above result..

is there any other better alternative??
hope I have explained well

[bastien]

select dl.date, u.users, u.email, u.address, u.state...,
dl.details
from download dl inner join users on u.email = dl.email
where dl.dateField = '$my_input_date' [other conditions]

gotta say I am not keen on the structure of your table for th details. i would have a layout similar to this:

dateField
userID
download

It makes for more versatile queries and is more flexible.

hth

[murali]

bastien

Thanks for your reply.. If we go by your suggestion it also means that the table size will increase.. Especially even when there are only 100 downloads a day, it means we are losing space.. if the download options increase, so will the table space....

[bastien]

only slightly. And an even better structure would be to user the user_id (like a autonumber value) and the download id value...the idividual records are then very small.

Is space that much of an issue to you?