Multiple days for client

I am making an access 2007 database, to track clients, one of the things I need to enter is the days of the week that they come to our center.  I wanted to know the best way to go about this, what I was considering is a field with yes no for each day but this seems inefficient .
smurfer1969Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
In the table, put an integer field
On the forms put an Option Group
Checkboxes in the Option Group
"Sunday" value 1
"Monday" Value 2
...
"Saturday" Value 7

Or a combobox if that your druthers.
Store it as a number.
There's VBA functions to turn the numbers back to text when you need it.
Rey Obrero (Capricorn1)Commented:
just use a Date/Time field to enter the dates to track the clients visits.

you can get name of the dates  using a query with

select client, VisitDate, format([VisitDate], "dddd")
from tableName
smurfer1969Author Commented:
The problem I am running into is they come multiple days, say tues thurs fri, or mon wed, etc and I need to produce some reports the day before in anticipation of arrival
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jeffrey CoachmanMIS LiasonCommented:
they come multiple days, say tues thurs fri, or mon wed, etc and I need to produce some reports the day before in anticipation of arrival
?
How can you generate a report of the day they come in, ...before they ever come in?
...am I missing something...

Can you post an example of your table and the exact graphical results you are expecting?
PatHartmanCommented:
Once you have more than one of something, you have many.  That means a separate table.  If you take what appears to be the easy way out suggested by the other experts, you will find that your queries, reporting, and analysis functions become more difficult, not to mention the fact that you will find yourself writing code and creating union queries that you otherwise wouldn't need.  It is always better to start with a properly normalized schema.  But before we can suggest an alternative we need to know a little more.  Are you creating a schedule or are you logging visits as they occur?
smurfer1969Author Commented:
Let me be a little more descriptive, it is an adult day care center,  Client are set up with days which they come in, can be any combination of days tue thrus, third and fourth Wednesday etc.  This changes on a regular basis, but is static by the week in advance.  So is not necessarily scheduling system.  I need to be able to print a list the night before which shows expected clients for which there are procedures which I have already attached to their id number.  I do in the long run want to develop a "check in" system, and getting the days of the week in is a key first step.  What I thought of is a separate table with a column for days of week with yes no, and attach client id to line with choices.  Is there a better way?
Nick67Commented:
What I thought of is a separate table with a column for days of week with yes no, and attach client id to line with choices.  Is there a better way?

I'd probably do a table called 'ExpectedVisits'
It'd have a primary key of ExpectedVisitID as an autonumber
It'd have a datetime field for VisitDate and a foreign key for ClientID.
One record for each visit.

You could then query for between two dates and get a listing of clients and expected visits.
PatHartmanCommented:
You need a table with one row for each week day the client is expected.  All you need is the day of the week and possibly whether it is a full day or half day and maybe a flag about lunch to help you plan meals.  Some people have special requirements or requests.

Then you can run a query once a week, probably on Friday to generate next week's expected schedule.  You would be able to add/change delete these records.  And finally, at the end of the day, you would print out all the scheduled people for the following day.  If a client's schedule needed to change permanently, you would update their weekday schedule so that the next time you ran the generate, it would generate the changed schedule.
Rey Obrero (Capricorn1)Commented:
just like what I said on my first post.
PatHartmanCommented:
Not exactly since you didn't specify that it would be a separate table that would contain a row for each day.  And you didn't separate the concept of the schedule from the actual.
IrogSintaCommented:
Seems to me you need to set a Repeating Event, same thing you find in a Calendar program such as Outlook our Google Calendar.  Something like this:
repeating eventThere are probably a few ways to set up a table for this kind of event.  The way that comes to mind would be one with the fields:

CustomerID - foreign key used to link to your other table
DaysOfWeek - text data type that stores selected days (e.g. "Tu,Th,Sa")
WeekInterval - number data type (i.e. 1, 2 or 3)
WeekOfMonth - text data type that stores selected weeks in the month (e.g. "2nd, 4th")
StartDate - date data type
EndDate - date data type

Ron

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.