Comparing tables

[YoungWolf]

Hey guys,
I'm new here and trying to get to know access a little better.

Heres the situation:
I have a daycare center and i have an access control system that allows parents of the children to access the center using key cards. what i want to do is link it using access so i can see a report of whos attending and whos not.

Table1 :
Child Directory
(fields)
User ID(autonumber) - PK
Last Name
First Name
address
Phone
Monday (yes/no)
tuesday (yes/no)
wednesday (yes/no)
thurs (yes/no)
friday (yes/no)

the monday to friday is if the child is scheduled to be attending or not

Table 2:
Card Numbers
(fields)
User Id (number) - PK .. relationship with the user ID of the child directory table
Card #1
Card #2
Card #3
(some families have more than one card)



Table 3:
Log File (link from the dbf made by the access control system)
(fields)
Card #
last name
first name
Date of entry
Hour of entry
Minute of Entry

what i want to do is link it so that automatically, if the child's card number is entered in the Log file on that date, it checks if their supposed to be attending, and puts a check mark in a new "monthly attendance" table for that date and child. Is this possible or am i in way over my head?

I appreciate the time.

Thanks,
John

[weekend00]

First:you'd better redesign your table2 card numbers to:
Table 2:
Card Numbers
(fields)
User Id (number) - FK .. relationship with the user ID of the child directory table
Card_no

one record for each card. multi rows for multi cards regardless they are for same user ID or not.

second: you child directory is base on Monday to Friday. if you want a monthly attendance, you need the schedule for a every day of whole month, because maybe this friday is required to attend and next friday not, e.g if next friday is holiday.

then you can link 3 tables together to get you monthly attendance report.

[SteveF]

I suggest you rework your Card table -- for example, what happens if you need to issue a fourth card? Your current structure does not allow this.

This would work better:
Table 2:
Card Numbers
(fields)
Card ID - PK
User Id (number) - Foreign Key .. relationship with the user ID of the child directory table

If a family has more than one card, they'll have more than one row in the table.

Next, a question: how do you want to handle a situation where a card is used on a day the child is NOT scheduled to attend?

More later...
Steve

[jzwp11]

Since a child has many prescribed attendance days that is a one-to-many relationship which should be captured in a related table

tblUserDays
-pkUserDayID primary key, autonumber
-fkUserID foreign key to your table1
-fkAttendanceDayID foreign key to tblAttendanceDays


tblAttendanceDays (holds the days of the week)
-pkAttendanceDayID primary key, autonumber
-longAttendanceDay a long number field to hold the day # i.e. Sunday=1
-txtAttendanceDayName (a field to hold Sunday, Monday...)

Having the day # is necessary for some date-related functions in Access which you will probably need to use in order to translate the date in the log file table and then match to the the days the person is scheduled to attend.

Per weekend00's comments, you may need a table to hold dates for holidays, so that you can account for them.

[SteveF]

Follow-up to previous message:

If I were designing this, I would approach it differently:

You already know when the child actually attends: that’s in your Log file. You don’t need a new, redundant table to track that. What you need to be able to do is compare that with when they’re supposed to attend, and generate Reports (not tables) that show attendance performance.

So, to that end, you need to translate that weekly schedule of weekdays into dates. Put the dates into a Schedule table – probably doesn’t need much more than the ChildID and the scheduled date – and do this on a monthly basis. The actual translation and adding of new dates to the Schedule table might need to be done with some VBA code behind a command button on a form – I’d have to think about that for awhile to see how best to do it.

Once the Schedule table is available, comparing it to the Log file and providing data for your reports is “easily” done with some queries that link the two tables together in various ways.

[YoungWolf]

wow thanks for all the advice guys.
to answer SteveF's question : i would like them to be listed as not attending that day

I've realised that i'm definetly not experienced enough to handle a project like this so I'm willing to hire someone whos willing and feels they can do this project quickly and efficiently. I'm new here and I dont know any of you but i'm sure theres a trustworthy guy out there willing to do some freelance work. My budget isnt huge but if theres anyone on these forums with an offer we can definetly work something out!!

thanks.

[YoungWolf]

anyone interested?

[SteveF]

Interested, yes. Available, no -- unfortunately.

Steve