Calculated primary keys and datatypes

[minez50]

I have a table with primary key, application ID ( this is autonumber type).
In a new table i want the primary key, copy number, to take a value that consists of the application ID and add an autonumber to it.

e.g
Their are two copies of Application X which has an application ID of 19.

so the first copy will have a copy number of 19+1= 19 1
and the second copy will have a copy number of 19+2 = 19 2

is this possible and if so how or are there any other options?

 

[Ron_dK]

Why make a second table. You could make a query based on table one, which would be something like this :

SELECT Table1.primary, Table1.[application-id], [primary] & ' ' & [application-id] AS copynumber
FROM Table1;
Here the primary is a number field and the application-id is the autonumber field.
See attached.

 

[Pat Hartman]

The copy number should be a separate field. You can generate its sequence number by using the DMax() function. Do a search here and you'll find lots of posts on generating your own sequence numbers.

 

[minez50]

I am still not quite sure how to do this:

table Application:

Application ID TotalCopies

121......................................3
122......................................2

Now what i wish to do is create a new table, COPIES, which will have a primary key Copy Number.
This primary key is based on the Application ID and TotalCopies info in the Application table, so:

CopyNumber
121 1
121 2
121 3
122 1
122 2

so basically it is looking up the applicationID and looking at the totalcopies and then creating entries according to this. Its similar to a cartesian equation. I also need a relationship to be maintained so that if i create a form where i select say application from a combobox updating the application ID (via dlookup), another combobox will allow me to choose the from the various copies of that application.

e.g,

I select Adobe Acrobat (ID 122) from a combobox. This updates the Application ID textbox to 122. Since there are 2 copies of of Adobe Acrobat,
from the copyNo combo box, i should be able to select from 122 1 and 122 2.

Other considerations include:

-if i design a form to delete a copy of an application, just say copy number 121 2, then the total copies of the application in application table should go to 2 since only copies 121 1 and 121 3 remain

How do i implement this all, is dmax() the answer here?

 

[Pat Hartman]

The primary key of the copies table should be an autonumber. You would create a two field unique index for the ApplicationID and CopyNumber.

To create a multi-field index, open the indexes dialog (lightning bolt tool).
Go to the first empty line.
Name the index.
Choose the first column.
Specify that the index will be unique and ascending.
Go to the next line.
Leave the name field empty and choose the second colum from the combo.

Access supports up to 10 columns for indexes and primary keys.

To make a multi-field primary key, you can use the same technique as I showed you for indexes or you can use the quicker methof of using cntl-click to select multiple fields and then pressing the key button on the toolbar.

 

[minez50]

Please help Pat Hartman

Thanks Pat,

Im a very basic user and am still abit baffled about how to set the indexes for the multi-filed primary key and how this would enable me to achieve my outcome. Please would you be able to provide a more detailed explanation of the process.

Much Appreciated!!

 

[Pat Hartman]

I thought my directions were pretty specific. Which step did you have trouble with?