Primary key based on field's content

[aian]

Hello to all

I have a large Excel file from where I will be importing data to a table.
This file has two main categories, COM and RES.
Entries can either belong to a COM or to a RES, never both.
I am looking for a primary key generated by Access 2010, which will consist of the COM or RES prefix, along with a simple sequential number. The prefix would be decided by what the Field "CATEGORY" would be for each entry.

For example, the primary key ID field would be something like:
COM-01
COM-02
RES-01
COM-03
RES-04
RES-05
RES-06
COM-04
etc...

I don't really mind if the numbers for each COM or RES are sequential or not, but it would be could to have it that way. What I really need is that the primary key begins with COM or RES, depending on what each entry's category is.

One approach would be to create two separate tables, one for the RES category and one for the COM.

The other is to include both categories under the same table.

What are your views on this? Is it at all possible?

Thank you in advance.

 

[The_Doc_Man]

In general, the problem is easily soluble in any of several ways, but the description you give MAYBE is easiest done in one table using a compound primary key that is NOT based on the Access autonumber concept.

Instead, you have two fields in the compound PK - one text (call it CG), one numeric (call it CGX), and you dump either COM or GEN in the text field. The other one, you use the DMax function. Then you have a variable such as sCG in your code to show the next CG value you would use for this field. Perhaps something like

NxtVal = 1 + DMax( "[CGX]", "tablename", "[CG]='" & sCG & "'" )

Then you would store sCG into [CG] and NxtVal into [CGX], then store whatever else is involved in the record. (This would be true for forms or recordsets, either way that you choose to store them.)

Note that this method doesn't guarantee consecutive numbering over time if it is possible to delete earlier records. Deletion would create gaps.

In this layout, you have one table, a field that says COM or GEN, a field that gives a number, and whatever else is in the table. Then, if you needed to split this in two, you can write queries that include " ... WHERE [CG]='GEN' ..." or 'COM' as needed.

Particularly if the two tables would have similar structures if you used one table for COM and one for GEN, keeping them together unifies (and simplifies) the schema, reducing the number of things you have to do in duplicate (which you would have to do if you had two tables).

 

[aian]

@The_Doc_Man, thank you very much for the super-fast reply.

I got the idea, I'll come back to this thread if I have any issues.
Just one question: following this technique, should I also add a primary key as well?
Or would the compound suffice?

Thanks again.

 

[aian]

Also, I'd like to add that after the import of the Excel file's contents, there won't be any additional data entered. What enters during the import will be final.