handling a multi-field primary key 1

[Gerilee]

Hi all,

I'm hoping someone out there can help as I have been reading all morning so now I need some advice. I have a table where I have defined the primary key as a combination of 2 fields in one table -- the year and a container number(contno). The table holds 4 years of data. A container number can only be used once in a year. So, the year and the contno can be dupes separately but must be unique together. I use a form to add new records and update the table and both of these fields are on the form. As of now, I am allowing these 2 fields to be changed. It seems to work as I am getting Access's error message when the two fields together are not unique. Is allowing the user to change these fields's acceptable? Or is there a better way to do this? I've read a lot about the option of the AutoNumber but that seems to have it's own problems. Thanks for any help!

 

[Golom]

Sounds like your two field primary key is enforcing the business rule exactly as you want it to. If you go to an autonumber as the primary key then every record will be unique and you'll have to write code to do what the DBMS is doing for you now.

You can trap the Access error message and present them with something more user-friendly than "Duplicate primary key ..." that you're getting now.

As to allowing the user's to change these fields ...
That's a business decision. Generally, if you have some automated way to supply those values then use it but, if you don't then user input is the way to go. You can ease the burden by giving them a way to select the year and then populate a combo box (for example) with all the container numbers that are still legal (i.e. not in the table) for that year.

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[Gerilee]

Hi Golom,

Thanks so much for the sanity check! I will definitely give them a more user friendly message. I am intrigued by your suggestion about giving them all of the available container numbers for that year. Could you expand on that a little further? Thanks much.

 

[Golom]

Yes ... but only a little because I don't know what domain you are using (and that's technical talk for "what constitutes a legal container number".)

Let's imagine a situation where the user types or selects a year number from some control on a form (a text box or combo box for example) Then in the change event for that control (or the click event for a combo box) you will do something like (WARNING: Pseudo code ahead)

[COLOR=green]' Assuming that rs is a recordset that is being used as the combo box's recordsource AND we're using DAO[/color]
Set rs = CurrentDB.OpenRecordset ( _
    "Select C.ContainerID " & _
    "From ValidContainers C LEFT JOIN YearContainerTable Y " & _
    "     ON C.ContainerID = Y.ContainerID " & _
    "WHERE Y.YearNumber = " & ComboYear.Text & " AND " & _
    "  AND Y.ContainerID IS NULL "

In words ...

Assuming that we have a table "ValidContainers" that contains all valid container numbers, pick up all the containerID's that are not being used in the current year.




[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[Gerilee]

Hi Golom,

Thanks so much for your help. Your answers were very helpful and easy to understand!

 

[Pampers]

Hi Gerilee,
I see that you work with containers. Are those containers that go on ships, like 40', 20', reefers and so on??

Pampers
There is only one way to change a diaper - fast