Primary Key

[Robert]

I have a table with 2 primary keys (this cannot be changed) one key is a date
and the other is a 6 digit number. What I need is to be able to show the
unique value access has created for referencing purposes. Again I want to
see what access has created as my primary key so I can search for records
based on this new key.
Thank you
robb

 

[Douglas J. Steele]

It's not possible to have two primary keys, so I assume you mean that your
primary key is a combination of two fields.

Where does the 6 digit number come from? It cannot be an Autonumber field,
since you cannot control the size of the field.

Access doesn't create primary keys: you do!

I think some more details are required...

 

[Public]

It is perfectly reasonable to use autonumbers as your primary keys and to
define unique indexes to enforce business rules. Your business rule is that
the combination of the date and the 6-digit number be unique. That's a
perfect rule for the database engine to enforce. You would use the
autonumbers in all your joins but never display them.

 

[Robert]

Let me clarify, yes I have 2 fields that create a primary key (I am not using
an autonumbers field). The two fields that create the primary key are
Today’s date
and job number (job # can repeat every two months)
I know this is unorthodox but we import a lot of outside data we have no
control over. In order to reconcile an external closeout statement we must
create a primary key utilizing two fields. What I’m hoping to figure out is
if access can show (create) me a field with the combination of these two
fields as reference (example Job # + Date= 123xyz) it's showing the 123xyz
that I’m interested in.

 

[Daniel]

You won't want to 'create' a new field - 'show' is the right idea.
Make a query which displays that field, and then perform whatever
table operations you want on the query.

The SQL for the query should be something like this:

SELECT Equipment.*, Equipment.[EQUIP_NO] & Equipment.[MOD_DATE] AS PK
FROM [tableName] AS Equipment;

Save this query as something like qryEquipment, where Equipment is
your table name. That will make it easy for you to include it in any
subsequent joins, etc...

HTH,
Daniel