Automatically Create a Concatenated Primary Key

[usfregale]

In the interest of full disclosure, I actually studied Access in college. I also got a C and it was my least favorite class during my entire college experience. I earned my C and knew as much about Access the day I finished the class as I did the day I started. Perhaps less. I did learn a great deal about database theory, which I have found actually makes Access harder for me to use ten years later.

Anyway. We have created a database in access. Presently the primary key used in most of the tables is a 'record number' which is concatenated by combining a 'store number' and a 'batch number'. The user actually enters all three data points (store number, batch number, and store number-batch number) manually into an input form.

The 'batch number' and 'store number' are logical datapoints for the user (actually they aren't logical at all, but I used a drop down for store number with limits options, and made 'batch number' auto populate the next number in sequence), but adding them together to form a unique 'record-number' throws them for a loop.

How can I have access combine the two datapoints themselves to create the needed key?

I have attempted to do this by designating a composite primary key, but when I attempt to join the two tables I am clueless as to how I would go about joining them in SQL. How does one define a relationship based on multiple keys I only know how to do so based on a single key...

Thank you in advance for your help.

Richard

 

[Golom]

Let's see if I understand this.

You have multiple tables, each with a composite primary key composed of StoreNumber, BatchNumber.

StoreNumber is assigned from a set of allowed values and BatchNumber is an autonumber field.

I assume that you have somehow resolved the issue that autonumber fields are not under your control and attempting to match autonumber fields in different tables is chancy at best.

You now want to match records from two of those tables based on the primary key.

If that's more or less correct, your SQL will, in part, look like

FROM TblA INNER JOIN TblB
     ON     TblA.StoreNumber = TblB.StoreNumber
        AND TblA.BatchNumber = TblB.BatchNumber

Keep in mind that SQL (syntatically at least) knows nothing about Keys or Indexes. You need to talk about fields and the fact that they are indexed is useful to the SQL parser when processing the statement but does not appear in the specification of what you want to do.

 

[BNPMike]

Richard

You shouldn't really be building database applications if you don't know absolute basics like this.

You need to buy and read some books or follow a different career if you can't be bothered.

 

[BlueHorizon]

Can you create a query that creates a new field by concatenating the two fields, then build a form based on the query?

Best,
Blue Horizon

 

[boblarson]

Blue Horizon, yes you can create a new field in a QUERY by concatenating two fields. It would be something like this:
(let's say I want a lastname, firstname from the Employees table in the Northwind database):

SELECT [Last Name] & ", " & [First Name] As EmpName 
FROM Employees
ORDER BY [Last Name], [First Name]

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com