Auto Value and primary Key Question

English
#1

Hello all,

I have a table(2) where I would like to use an integer as a primary key and use the same number more than once since I have multiple entries on table 2 linking back to another table (1) where the same matching key # resides to ID the relationship.

Since the primary key function doesn’t allow use of the same number more than once I sought to get around this by having 2 integer columns and joining them as a dual primary key. I wanted to make the column linking back to the related table a non auto value and make the second column an integer auto value which would also give me a way to count my entries and manage the order they show up on in the table.

What I find is that once I try to set a primary key the integer auto value will not work on a second integer field…throws an error when I attempt a save.

Is there a way around this? I have multiple entries in my second table that link back to the first table…and the only way I know to do this is make sure the related integer for each entry is the same on both tables…I’m stumped.

Any thoughts would be greatly appreciated…

SC

#2

I’m intrigued but do not understand why both integer columns needed to be part of the primary key. Often complexity of the nature you describe is addressed by having a FORM and a SUBFORM linked on the values (which streamlines linking them up) and a FOREIGN KEY relationship which enforces the relationship and cascades changes.

#3

Doug…Actually I think it was purely out of my own ignorance of how a database works. I was trying to keep my tables in order so if I went into them to look for something everything would be in an order… I’m starting to get the impression that I should not be worried about that and to just use a querry to fetch the data…

Sorry for confusing everyone with my questions…but I’m new to this and it is a bit like drinking from a firehose…misunderstanding of the basics will do that haha

#4

He, can you post an example of your file ?

#5

I’ll give it a shot.

This is a minimal test .obd file only.

The table “CD-Collection” contains a CD Name.

The table “Songs” is supposed contain songs from the CD. It has the Primary Key set to the “Song ID” column. It does not have an auto value assigned to either column.

Table “Songs-Copy” has the Primary Key set to the “Song ID” column and has an auto value set for the integer in that column.

Works if you make “Song ID” (which is also the Primary Key) an auto value. But if you try to switch the 2 and make the “Table ID” an auto value….you get an error.

If you have multiple integer columns and 1 of them is the Primary Key then it would appear that Base only allows an auto value in that primary key column and if you try an auto value in the second integer column…errors out.

Hope this explains what I am seeing…it is probably either my lack of understanding or my fat fingers that are causing the problem…ha…

I would greatly appreciate anyone who could help me understand.

Best regards…SC

TestBase1.odb

#6

He, You can make like in my example attached. The Query1 shows the result.
http://dropcanvas.com/#W5108834V33nqW

#7

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.