Tables & Relationships

[brucey54]

Hi Folks,
My rota table is not linking correctly to my Client table; I have tried a lot of different variations but I always seem to end up with the same problem else where!!
Basically I’m trying to create 3 tables i.e.
Client Details
Carer details
Rota details
Then from this I was trying to create a form that would allow the user to enter carer name, Client name and then input the date/times of the rota!
Probably a lot easy if I attached my database, any help would be great.
View attachment Care.zip

 

[plog]

I'm looking at just your relationships and see a few things:

1. Why is CarerIDFK in TblClientDetails?

2. Why is RotaIDFK in TblClientDetails?


3. Is ComboClientName in TblClientDetails the full name of the client? If so you shouldn't have this.

4. You shouldn't store age since you have their DOB, instead you calculated Age when you need it.

5. Is Total Time in TblRota just the difference between Start Time and Finish Time? If so, this should be calculated and not stored in a table.

6. TblRota probably shouldn't have a field for every day of the week. Most likely this should be one Date field.

 

[brucey54]

Hi Plog, thank you for your response; I have updated the database with your suggestions.

Please could you help me with the following?

Why is it when I click on the TblCarerdetails and try to open a record with the plus sign I am present with the following error message

“Enter Parameter value CarerID”

The frmCarerRota seems to be working ok, the relationships seem to be ok and they make sense to me i.e.

Carers can have many rotas i.e. a different rota each week

Clients can have different carers each week so

Any help or feedback would be much appreciated

Tom

Happy New Year

 

[plog]

I don't know. I usually use forms for data entry not tables. I would post this as a new question, or hopefully someone else will come along.

 

[RainLover]

I tried to help but your attachment is in 2007 or 2010.

Us older people can't afford the latest of everything so most of us still run 2003.

If you can convert back to 2003 then I could have a look.

Sorry about that.

 

[brucey54]

Converted to 2003, thanks much appreciated.

Tom

 

[RainLover]

Tom

Your design appears to be correct, except do not use Date try RotaDate instead as Date is a Reserved word. Also do not use spaces in field names.

You are not having a problem with your Form as you said so use that. Users should never have access to the tables as they can do too much damage. So forget about that problem.

Other than that things look good. What else do you need help with.

 

[brucey54]

I was hoping to understand why the tables are not linking correctly, if I click on the TblCarer plus sign.

I should see the Rota details and a plus sign. If click on the plus sign, I should see the clients details.

Therefore I am assuming that my relationships are not correct, I think I need to add an association table, but where and what fields do I need to add/create.

Tom

 

[RainLover]

I have no Idea. Have never used this and can't see why you would.

But that does not answer your question. Sorry.

In one case I get it looking for a key that does not exist. Strange.