Query Problem

[Guest]

I have a table with 10 fields; four fields are for photographers assigned to
a particular event. There maybe up to four photographers at each event. Now I
find I need a report by dates and each photographer. A report with start date
and end date is no problem, but how to sort by one particular photographer
and report by dates is. Does anyone know how this can be done?

Thanks

 

[Ken Snell \(MVP\)]

May I strongly recommend that you redesign your table structure first. You
should not have four fields for four photographers in the table. You should
have a separate table (a junction table) that lets you assign photographers
to an event.

tblPhotographers
PhotogID
PhotogName
(etc.)

tblEvents
EventID
EventName
EventDate
(etc.)

tblEventPhotog
EventID
PhotogID

Then you can run a query using the tblEventPhotog table and get your desired
results / report.

If you stay with your current setup, not only will your complexities just
increase as you continue to want more info from the database, but you'll
have to write a complex UNION query to get the results you want at this
time.