Primary Keys Gone (2 Viewers)

[Alexander Willey]

Dear Forum

I am running an Access database that works like a timesheet system. It is split in to a front end and back end and the front end is used by about 15 users who each have a copy of the front end on their PC. The back end sits on the server. One of the users had an issue with their data entry form and after trying to compact and repair the back end, the issue appeared to be that one of the back end tables had lost its compound primary keys and had allowed duplication of an autonumber. I deleted the duplicate records, re-set the primary keys and joins and all appears OK, for now.

I would be grateful for any suggestions on how to prevent this issue occurring again.

Thank you in advance.

 

[plog]

... one of the back end tables had lost its compound primary keys and had allowed duplication of an autonumber.

That doesn't make a lot of sense. A table with an autonumber primary key, shouldn't have a compound primary key. Just so we are on the same page:

Primary key - deisgnated field(s) in a table whose value makes a row unique within that table.

Compound primary key - This occurs when multiple fields in a table are used as its primary key.

Autonumber primary key - a field in a table generated by the database whos value will always be unqiue to that row.

So, using an autonumber and another field as the primary key is redundant--the autonumber itself is good enough.

Could you post a screenshot of your table in design view?

 

[Alexander Willey]

Thanks
I have attached screen grabs of part of the database
The field where two advisors seem to create duplicates of the auto number was TimeLogID
It was when I tried to compact and repair that I got an error message, this prompted me to look at the design and that is when I saw that the relationships and keys had dropped out
I then looked at the TimeLog field saw 2 duplicate records. Record 905 and 906 were present, twice
I deleted them and reset the keys and relationships and it seems OK for now

Thanks
Alex

 

[plog]

Yeah, as I explained there's no point in making a compound primary key if one of those fields is an autonumber, because that field itself ensures uniqueness. So TimeLog only needs TimeLogID as its primary key.

Relationshipwise, I think your big issue is that Week shouldn't contain AdvisorID--it shouldn't be connected to the Advisor table. Instead it should only be related to TimeLog.

Additionally, it seems TimeLog has too much data in it. First of all it has ProjectNo, ProjectName and ProjectId--if you have a Project table (which having a ProjectID field implies), you don't need anything but the ProjectID field in TimeLog. With that value you can connect to your Projects table and get all the other project data.

Second, Country, ClientAgreedEndDate seem out of place in a table used for logging time. Those things seem like they should be stored elsewhere--perhaps the Project Table or Client table.

Third, how many clients can a project have? IF its only 1, then you don't need Client (or any of their data) in TimeLog. You simply connect to it through your relationships.

 

[Alexander Willey]

Thank you

My (limited) understanding is that if I want to relate a repeating field, then I need to use a one to many compound key. Am I wrong in that understanding?

I set the Advisor, Week, TimeLog in that cascaded related fashion to help accommodate the 'form within a form within a form', again, just based on my limited knowledge of how to create that arrangement. Users are used to seeing a page per week etc. What would you suggest as a more sensible way of linking?

The users need to see the project info on display whilst they enter and adjust their hours. I had included fields that capture the data as a means of displaying it, since I was worried that if I used a query that looked up the project info, they could see the info but not be able to add (edit) hours in to the record source. What strategy would you suggest to continuously display the project info (that is stored elsewhere) and to be able to add/edit hours against it.

Many thanks for your help

 

[plog]

Forms are the last things to worry about when designing a database. Tables, then Reports and the queries they are based on, and finally Forms.

What would you suggest as a more sensible way of linking?

My initial post detailed that as well as a few other changes you should make and some questions you need to answer.

 

[Alexander Willey]

...I neglected to answer...a project will only have one client

thank you for your help

best regards