two primary keys, both with duplicates OK 2

[ragsisme]

I am non-programmer by trade developing a database program to enter data into predefined tables that must maintain a certain structure according to the federal agency that wants the data. I called the agency and they had provided the tables as a shell and stated that these tables they provided must be used due to certain instructions they embedded within their structure. Unfortunately and typically, the person I discussed these problems with was a mainframer and unable to talk "Access". I was almost complete but ran into a pretty big snag.

It appears that one of the fields that I need to use to connect to subsequent tables to establish referential integrity will have to have duplicate entries. Is there a way to establish 2 primary keys, both allowing duplicates but together serve as a "primary key" for subsequent relationship connections??? Whenever I use the control button to highlight the two fields and make them both primary keys, it will not allow me to change the indexes to "duplicates OK". I am using Access 97.

thanks to anyone that can help me have a Merry Christmas without this one hanging over my head.

rags

 

[RoyVidar]

Hi!

If you alredy have records in the table, ensure that the fields in question do not have any violations of the integrity rules this will enforce.

* First deselect any primary key fields and set the indexes of the two fields to no
* To completely remove any existing indexes, click the index button (to the right of the primary key button) and delete all indexes
* Then select both fields (press the CTRL button and click the grey boxes)
* Click the primary key button on the toolbar

This will create a combined primary key consisting of to fields (= one primary key;-))

You should now also be able to set the indexes of the two fields to Yes - dupes OK.

This will create a primary key where the combination of the two fields need to be unique, so that the following values would be OK:

[tt]1 1
1 2
1 3
2 1
2 2
2 3[/tt]

But the following, would be a violation, and not allowed after entering the above values.

[tt]1 1[/tt]

HTH Roy-Vidar

 

[ragsisme]

Roy-Vidar

I deleted all data from the table. Turned all primary keys off and set the indexes to no. Deleted all indexes. Then added the two back as you stated. I get an "Invalid Index Definition" error message when I try to save it. Can you think of why this may be happening? I also deleted all relationships to this table. There is a form that is connected to this table. Would that make a difference?

thanks

rags

 

[RoyVidar]

Hi!

Yesterday I only tested in access 2000 and xp. Today I also fired up my access 97. If I've understood your post correctly, I was allowed the same operation in all versions.

The form should not matter, as long as it isn't opened (neither hidden nor visible).

Off the top of my head;

* only the individual fields in the PK can have the Yes - dupes OK settings, not the whole PK, if that's what you might be trying, the whole PK (both fields combined) index would be unique (PK means a unique index - here as I've understood the meaning from your first post - both allowing duplicates but together serve as a "primary key")
* saving the table between each step, perhaps (this would also indicate where/when the problem arises)
* use the Tools | Database Utilities Repair/Compact (in case the deleted thingies aren't completely deleted (as I read it, what you're doing is exactly the same as I do from time to time, and not being allowed to do it, might indicate some kind of corruption)
* since all data in the table alredy is deleted (or backed up) perhaps create a new one (or copy the old one)

Else, I'm afraid I'm as stumped as you. One might perhaps hope other members who have resolved same type of challenge might pop in to the thread an provide other suggestions.

- should there be any more problems, creating a new databsae and importing all objects, could also be an option to try.

Roy-Vidar

 

[ragsisme]

Thanks Roy-Vidar

I have run the repair utility a couple of times and I think the last option you mentioned (importing to a new database) will be my next step. Thanks for your help on this matter and have a happy holiday season.

rags

 

[RoyVidar]

Same to you, and good luck!

Perhaps, if I've misunderstood the datastructure you wish to achieve, you could post samples of values you wan't to enter in those fields (especially how you want the uniqueness to be) if the steps above don't solve the issue.

Roy-Vidar

 

[ragsisme]

OK .. Here's what I want:

Table A

Field A = Facility ID # (dupes OK)
Field B = Emissions Unit # (duplicates OK)

Table B

Facility ID # (dupes OK)
Emissions ID # (dupes OK)

In Table B - do not want any one Facility ID# to have a duplicate Emissions ID#

The problem I run into is that Access 97 is not allowing me to save a table that has a primary key field that is indexed with "duplicates OK"

I am stumped ...

thanks for your persistence in offering help

rags

 

[ragsisme]

I think the problem lies with me wanting to use one of the new keyed fields (indexed with dupes OK) in a relationship forcing referential integrity. What I was hoping was that there was a way to use the two newly keyed fields together as a unique "two field primary key" to force referential integrity to the next table.

Anyway, I think I have caused enough trouble for today .

Just want to say thanks again

rags

 

[MichaelRed]

per RoyVidar's origninal instruction, the compound index (PK) cannot have dupllicates. IF ypu also index the elements of hte PK (compound index) SEPERATLY, you can set them individually to permit duplicates. Indexing the elements of the PK (compound index) is not required from your original post. It (indexing the elemens of the PK individually) MAY be detrimental to the overall performance of your app.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[ragsisme]

thanks Michael

What I have found out is even more baffling. I installed Access 97 on my home computer and brought in the raw shell (tables with no relationships, data, or primary keys set). There are 7 tables that I use. I pulled up the one that I was trying to set up the two column key and IT WORKED just as both you and RoyVidar described! Here's the piece of work ... I tried to do it on another table (still haven't entered data or set relationships) and I got the "index invalid" message when I tried to save it.

OK, then I decided to delete that .mdb file and recopy the same shell that I had before into the directory where I am working. Now I am unable to do the same thing that I did above to the same table (the one that worked before). It is as if the program itself only let me do it one time and that's all I get. Sounds like there is some corrupted file in the program somewhere ... doesn't it??? Anyone ever run into this before out there?? Any ideas which file drives that particular feature?

I will look in the program files and see which files may have been changed as of this date. Maybe that will give me a clue.

Just thought I would give you an update since the information you gave me is obviously correct (it worked the first time) and it is consistent with the literature.

rags

 

[ragsisme]

Resolved

A long process to a simple answer. Setting up a multiple field PK worked if you use the PK button on the menu bar. It didn't work using the rt. click on the box beside the field in the design view of the table. Although the PK symbols showed up with the latter method as it is supposed to, it wouldn't save it. It saved it fine when using the menu bar PK button. Don't know if this is just a bug in our program or if it is one that others have run into but I am glad it is over.

Hope this helps someone else.

rags

 

[gjd6640]

This thread did help someone else. I'm having the exact same problem and when I use the Primary Key icon from the toolbar instead of right-clicking everything starts working. Thanks!

 

[ragsisme]

Glad to know that gjd! Good luck to you.

rags