Hello, I'm creating(attempting to create) an access db to track members that receive referrals for jobs. I can't nail down the table relationships. I have my Employee info table with all the member info. The way it goes is this: Members Sign every Monday(they write down their Ticket Number and completion date of their last job, if it's the first time they are signing then they receive an Index number. This is how I plan to sort them on the list. They must sign on subsequent Mondays in order to maintain their place on the list. So the first Monday a member signs he/she receives an Index number and receives a status of U. Now suppose he signs a couple of more times. When he/she reaches the top of the list the next job referral goes to them. The Index number they recieve will static so that as I remove the Index numbers above them they will eventually rise to the top. My main problem is that I am not starting with giving them a referral and a start date and I can't create a jobs table to relate the data. I also need to track their status and be able to change it at any time, the Sign In Dates, the referral dates with contractor ID and their Start and Completion dates to determine number of days working and number of days on the list. My current design is this:
tblsignIn:
SignInID-PK autonumber
SignInDate
Status
IndexNumber
TicketNumber-describes the member as each member's
TicketNum is unique. It is the PK for EmpInfo table
tblreferral:
tblReferral-PK autonumber
startDate
compDate
ContrID
SignInID-FK
The problem is how do I relate the Sign In Dates with the referral date. Every record in tblSign gets a new PK so I'm only writing one PK to tblReferral.
Another issue I ran into was deleting a wrong data input, e.g. , my end user places the wrong member on the list or writes a referral for the wrong member. I need to be able to delete the entry and return the member to their previous status.
Any suggestions on a better way to design this
Thanks, Jim
tblsignIn:
SignInID-PK autonumber
SignInDate
Status
IndexNumber
TicketNumber-describes the member as each member's
TicketNum is unique. It is the PK for EmpInfo table
tblreferral:
tblReferral-PK autonumber
startDate
compDate
ContrID
SignInID-FK
The problem is how do I relate the Sign In Dates with the referral date. Every record in tblSign gets a new PK so I'm only writing one PK to tblReferral.
Another issue I ran into was deleting a wrong data input, e.g. , my end user places the wrong member on the list or writes a referral for the wrong member. I need to be able to delete the entry and return the member to their previous status.
Any suggestions on a better way to design this
Thanks, Jim
