count dates in multiple columns

[Guest]

My table looks similar to this:

ID Name Appt1 Appt2 Appt3
1 Paul Smith 04-sep-06 31-oct-06 02-dec-06
2 Jim Taylor 09-sep-06 31-oct-06 02-dec-06
3 Mike Jones 04-sep-06 31-oct-06 02-nov-06

My intent is to build a report where appointment dates are sorted
chronologically with the total number of appointments on that day summed to
the right. For example:

04-sep-06 2
09-sep-06 1
31-oct-06 3
02-nov-06 1
03-dec-06 2

Any help would be appreciated. Thanks for your time!

Ortley

 

[Jeff Boyce]

Your design, using "repeating fields" for dates, is ... a spreadsheet! Are
you prepared to add yet additional columns for Appt4, Appt5, ... Apptn? And
modify all your forms, queries, reports, macros and code that references the
table?!

Take a look at the topic of normalization -- Access is a relational
database, and you won't get the best use of Access' features and functions
if you feed it 'sheet data.

Here's an alternate table structure:

tblAppointment
AppointmentID
PersonID (not person name)
ApptDate

Notice that you don't need to number these ... you can sort by ApptDate for
each PersonID.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

Two stages
1. Create a UNION Query to get the data in a proper way to count them

SELECT TableName.Appt1
FROM TableName
UNION ALL
SELECT TableName.Appt2
FROM TableName
UNION ALL
SELECT TableName.Appt3
FROM TableName

2. Create a group by query based on the Union query to count the dates

SELECT QueryName.Appt1, Count(QueryName.Appt1) AS CountAppt
FROM QueryName
GROUP BY QueryName.Appt1

*** Base the report on the second query *****

 

[Guest]

Ofer,

THANK YOU SOOOOOOOOOOOOOO MUCH!!! You are amazing! I sincerely appreciate
all of your support. I solved my dilemma and learned more about Access
thanks to you!

Ortley