Recently asked on Yahoo! Answers:

SQL / Access query?

I have a 1-table client database which, as well as all the usual name / address info, includes client ‘visit’ fields. Each client may have between 1 and 5 visits per week (Mon to Fri) and each visit can be at a different time, duration and frequency (weekly, fortnightly or monthly) e.g.

Cust XVisit 1 Day1 Hrs1 Time1 Freq1
Visit 2Day2 Hrs2 Time2 Freq2
Visit 3Day3Hrs3 Time3 Freq3etc…

Cust YVisit 1 Day1 Hrs1 Time1 Freq1
Visit 2Day2 Hrs2 Time2 Freq2
Visit 3Day3Hrs3 Time3 Freq3etc…

I want to generate a report that shows monthly visits - but not details of weekly or fortnightly calls the same customer may have. E.g. If Cust X has 1 weekly visit on a Mon, a fortnightly visit every other week on a Wed - and a visit on the last Fri of each month, I only want to see details of the Fri/monthly visit.

Hope this makes sense!!
Any assistance very much appreciated

My initial answer to the question prompted a reply from the questioner, who noted my suggested SQL query returned all visits for the client, and who also asked about relational databases and expressed some confusion.

I’ll explain why the questioner’s current design is bad, how to make the proper relationships and demonstrate, via a downloadable copy of the improved Access database, how to best generate queries such as this user wants.

Read the rest of this entry »