Creating a Unique Primary Key based on two other fields which are auto populated

[bharathsadanand]

HI All,

I have 2 tables Orders and OrderDetails.
The Order ID is the primary key in Orders and is a foreign key in OrderDetails.
OrderDetailID is the primay key in the OrderDetails table.

Now I have an Orders form with an OrderDetailsSubform
What I want to do is create a sequential record number for all order details starting from 1, append it to the Order ID and save it to OrderDetailID.

It should look something like this
1.1
2.1
3.1 ... for 3 records with Order ID as 1.

For Order ID as 2

1.2
2.2
3.2 etc.

any help will be appreciated.

 

[jdraw]

You may be confusing some concepts.
Primary keys are for the use of the database management system. They are not for human consumption. That is why dbms have things like Autonumbers, sequences etc.

I recommend you use autonumbers as your PKs.

If you want a field to represent an OrderNumber, or OrderDetailNumber, then create same for human use (if you really, really need it). You will have to populate these numbers. And you will have to maintain these numbers.

NewOrderNumber = Max(OrderNumber) +1 type of thing.

see this http://www.utteraccess.com/wiki/index.php/Autonumbers

Good luck with your project.

 

[Isskint]

Use the DCount() function to return the number of records with Order ID = X.
EG
UniqueID = Ltrim(Str(Dcount("OrderID","TABLENAME","OrderID=" & [OrderID]))) & "." & Ltrim(Str(OrderID)) ' if the OrderID will not have been created then you will need to +1 the DCount()

if you wanted it as a numerical value simply use the VAL() function on the formaula result.

Although i am a little confused about where you want to store this as it appears the field is auto populated in which case it can not be changed

 

[bharathsadanand]

Getting close....

So what I Did was pasted the following in my Line No field.

=LTrim(Str(DCount("OrderID","[Order Details]","OrderID=" & [OrderID]))) & "." & LTrim(Str([OrderID]))

It is picking up the max entry as per order number i.e.

for Order no 1 - which has 2 records.

It is filling up 2.1, 2.1 whereas I want it to fill in 1.1, 2.1 in the two records...

 

[bharathsadanand]

I think Im confusing myself... I already have an autonumber PK in that table... which I can use to reference each ID..... guess I dont need more than that.....

will figure out... and shout out if I need help.

Thanks
B

 

[jdraw]

Why do you want the numbering set up as you have described?

 

[bharathsadanand]

Ok so what I thought was like this...

Each Order has a number of Order Items... Like Order 1 may have Line items 1-50....

So I wanted to create the OrderDetails Primary Key in a way that shows all the line items sequentially associated with the particular order....

So if I had order 1 with 5 line items,
The primary Key in OrderDetails should read as
1.1
1.2
1.3
1.4
1.5
The next Order Number will be 2 with say 3 line items
The Primary Key in OrderDetails should read as
1.2
2.2
3.3

That way I am not only creating a unique Primary key but am also creating a field that will show me immediately how many rows I have per Order.

Dont know if my thinking is wrong....

 

[bharathsadanand]

Oops.. Sorry .. am a few down... it should read

1.1
2.1
3.1
4.1
5.1 .... etc... got the numbers mixed up.

For the second Order Id it should read
1.2
2.2
3.2
4.2
5.2.... etc...

Sorry about that my bad ... just saw it...

 

[jdraw]

But my basic question is why? I think you are making something too complex for no reason.

OrderDetails by Order can be obtained via

Select count(*) from OrderDetails where OrderId = [enter the order id here]

 

[Pat Hartman]

@Isskint - it is far safer to use DMax() to get the highest sequence number. Using DCount() fails if records are deleted since it will cause you to generate duplicate sequence numbers.

@bharathsadanand - you already have a pk that is unique. What is the purpose of this generated sequence number? Are you prepared to handle deletions? Is there some particular order that needs to be maintained?
For most purposes, you simply don't need anything. If you have orders with large numbers of items and you want to make it easy for the customer to refer to a particular item, then generate a sequence number using DMax() as has already been suggested. You don't need the compound number at all.

 

[Isskint]

@Isskint - it is far safer to use DMax() to get the highest sequence number. Using DCount() fails if records are deleted since it will cause you to generate duplicate sequence numbers.

Good point Pat. I did not suggest Dmax() as there is always the possibility of non consecutive autogenerated numbers. I made an assumption that there would not be any deletions (dangerous perhaps).