date query / multiple fields

[Guest]

i have one table with lastname, first name, (10) date fields with a service
and price field connected to each date.

i'm trying to create a weekly sales report so i need a query that will look
at each date field in the table and return the results for the user specified
starting and ending dates and hopefully if possible eliminate the fields that
have no data.

so if possible show the results as follows:

ie: smith, anna service01 $10.00
jones, george service 03 $25.00

ending weekly total $35.00

can anyone help

 

[Tom Ellison]

Dear Martin:

The fundamental difficulty here is that you have repeated the date and price
columns 10 times. A properly constructed database would have only one date
and price value per row.

You can temporarily overcome this by creating the appearance that your data
were stored properly. This can be done by a normalizing UNION query:

SELECT LastName, FirstName, "01" as Service, Date01, Price01
FROM YourTable
WHERE Date01 IS NOT NULL
UNION ALL
SELECT LastName, FirstName, "02" as Service, Date02, Price02
FROM YourTable
WHERE Date02 IS NOT NULL

repeat this till you have all 10 date/price combinations. You must correct
the column and table names I have assigned, as I don't know exactly how you
have done this.

You can then easily query the above to show only those rows belonging to the
user defined period.

A grand total can also be produced using a separate query, or as a total on
a report.

Tom Ellison