Do you absolutely need to have a field in each table with autonumbering especially if it the PK?

If I dont what are the consequences?

Is there a way to make it so that AutoNumbering uses a formula that you came up with?

IE. I want to use a letter an ID, month year, and a sequence (quote #1, #2, #3 etc)

I just need help identifying a good google search string because i am not sure how to word what I am trying to do.

Typically your PK field is used to index entries, so that you can reference them in other tables. Using autonumber to ensure that it is always a unique number is the best way of doing this.

In you case, it looks like you want an additional record ID, that may sometimes be absent. In this case, I would think your best bet would be to have an Autonumber PK field, and then a secondary field that would contain you ID - you could set this one to required/not required and unique/not unique.

We would really need to understand why you need this ID field and how you intend to use it to provide a better answer.

Hope that helps,

Mike

Mike
EchoVue.com

So if I understand this correctly, the PK should be Autonumbered because it it Indexes my entries by it.

I am trying to create a unique ID for the purposes of Quoting. There can only be one quote ID entered (making it unique?).

IF I have the "Indexed" Property set to "Yes (No Duplicates) and the "Required" Property set to "Yes" shouldnt that work also?

For proper RDBMS design, you want the PK field to be an autonumber. But in reality, if this is just a relatively simple application, I wouldn't worry about things like that - When you get up around 20 or 30 tables with hundreds of thousands of records, then stuff like this really makes a difference to performance.

Your approach sounds like it will work.

Mike
EchoVue.com

Well I want to do this the right way. If I should insert a QuoteID (Autonumber) and my QuoteNumber and have them both as a PK's I will do it. My concern is that everytime I have more than one PK i get the "Indeterminant" relationship.

Or

Should I only be making the QuoteID the PK and the QuoteNumber just be "Indexed" as "Yes (No Duplicates)"

Wow I am glad I did that I found another problem. Thanks!

hmmm something interesting has happened when I added the Autonumber to every table.

[u]Vendor Table</u>
MFGID-PK
Company name
Etc

[u]Parts Table</u>
PartsID-PK
MFGID-FK
etc

When I enterd the parts in I did so through the subtable in the [u]Vendor</u> Table.

Now when I go to Parts it is showing the Autonumber associated with the [u]MFG</u> Table rather than populating the Company Name.

I looked at the properties and they seem fine.

Is there something I am Missing?