How to have 2 AutoNumber Fields in One Table

[wazniw]

Dear Friends,

I am having a table for invoicing and vouchers, and I need to have two fields that will have auto incremental numbers. Can you please help me understand how I can have two fields in the same table with autonumbers and how can I get one of them to start from a different value.

Eg. Record 1 - [inv No] 1 [Vno] 0005
Record 2 - [Inv No] 2 [Vno] 0006

Would appreciate your help to resolve this.

Thank you in advance.

 

[rzw0wr]

Try DCount("[YourNumberField]","YourTable") + 1

Dale

 

[Galaxiom]

Try DCount("[YourNumberField]","YourTable") + 1

That expression would fail by repeating previously used numbers if there were any gaps in the number sequence.

It is more reliable to use:

DMax("[YourNumberField]","YourTable") + 1

 

[wazniw]

I have attached by table and form.
I need to have an autonumber for receipt when the entry is an income and similary an autonumber for the voucher when the entry is an expense.

 

[Pat Hartman]

There is no logic to having two autoincrementing numbers in the same record (and that is why relational databases don't allow it). Either make the voucher number by concatenating something with the invoice number or generate voucher numbers in a separate table.

 

[jdraw]

Further to comments by others, I recommend you create a model of WHAT you are trying to do before getting too deeply involved with HOW to do it in Access.
Here is a link to some data models that may help organize your "business facts".
http://www.databaseanswers.org/data_models/index.htm

This one may be more specific:
http://www.databaseanswers.org/data_models/customers_and_invoices/index.htm

If you are having difficulty with database design generally, then this tutorial should be helpful to you.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip
Good luck with your project.

 

[DemonDNF]

I understand relational and hierarchical databases just fine. But there are situations when you might need more than 1 autonumber field in a record.

I use autonumber as the primary key for an Item table, as I should.

But I also need a separate autonumber to generate a barcode number in the Item table. I don't want to use the primary key as barcode number because I cannot guarantee that value will remain unchanged after I have printed labels.

I might have to manipulate the database later and the primary keys might change, deleted records, etc.

But yes, I do agree that the OP should use individual tables for Invoices and Vouchers.

Robert

 

[DemonDNF]

Thanks for the generous offer, but I figured a better way from another thread here.

(darn, can't post link, I don't have 10 posts yet)

It suggests using NOW() in a DATE field. I feel dumb. I always timestamped records when I was still programming (over 10 years ago). I was looking for a way that would work even in Database View, not only in Reports or Queries.

20140131-164507 makes for a nice barcode format too, not too long in Code39. My labels are only 2 inches wide.

(Ok, enough thread hijacking on my part)

I'll likely be back in a new thread with some real "smart" questions myself.

Robert

 

[Galaxiom]

Incrementing can also be done by storing the next number in a table.

To allocate a number, a recordset is opened with an exclusive lock. The number is read and incremented then the lock released. The front end has a loop to keep trying a few times to obtain a recordset in case it encounters a lock placed by another user.

It is very reliable and allows the next number to be adjusted.

 

[jdraw]

What exactly is the purpose of the Now() in your barcode? Seems Galaxiom's approach would give you an incrementing number (Not autonumber), and the number would serve as an item identifier.

 

[nanscombe]

Using the date in a barcode, off the top of my head, could be useful if you do a regular task like an asset inventory / inspection.

You would know, simply by looking at it, when the asset was last seen / visited or what possibly which batch of items it belonged to.

You could set the date to a finite time in the future, or a specific date, which could act as visual trigger to plan the next inspection or even as an expiry date for personal safety equipment.

 

[ButtonMoon]

Wazniw,

I have to agree with Robert that it is a common enough requirement to need to assign more than one kind of identifier to things in a database table. Nothing illogical in that and it certainly is not the case that relational databases don't allow it.

If your DBMS supports it then consider using a sequence or similar engine-level key generation features. Alternative methods of doing it in the database usually require inserts to be serialized (e.g. because you have to take an exclusive lock the value you are using to count with), placing a significant limitation on scalability.

If you are compelled to do it with an autonumber column try inserting first to another table (a "sequence" table retained only for that purpose), retrieve the value and then insert to your target table.

 

[nanscombe]

Dear Friends,

I am having a table for invoicing and vouchers, and I need to have two fields that will have auto incremental numbers. Can you please help me understand how I can have two fields in the same table with autonumbers and how can I get one of them to start from a different value.

Eg. Record 1 - [inv No] 1 [Vno] 0005
Record 2 - [Inv No] 2 [Vno] 0006

Would appreciate your help to resolve this.

Thank you in advance.

You could try this ...

Private Sub Form_BeforeUpdate()
  If Len(Me.[inv No] & vbNullString) = 0 Then
  ' Default to 0 if no records found
    Me.[inv No] = nz(DMax("inv No","yourTable"), 0) + 1
  Endif

  If Len(Me.[Vno] & vbNullString) = 0 Then
  ' Default to 4 if no records found
    Me.[Vno] = nz(DMax("Vno","yourTable"), 4) + 1
  Endif

End Sub

Whoops, I just spotted the date of the OP. Poster doesn't seem to have been back since.

 

[nanscombe]

It was just an example of a situation where a date could be useful on the label in reply to ...

What exactly is the purpose of the Now() in your barcode? Seems Galaxiom's approach would give you an incrementing number (Not autonumber), and the number would serve as an item identifier.

It really depends on your business requirements. Every School district in California has a district designation a three letter prefix.

As I've said elsewhere I took the three letter prefix idea a step further and used 3 digits as a building prefix for generating record IDs, within specific ranges, in my databases.

I could then take data from 30 odd buildings and consolidate them without any clashes.

 

[DemonDNF]

What exactly is the purpose of the Now() in your barcode? Seems Galaxiom's approach would give you an incrementing number (Not autonumber), and the number would serve as an item identifier.

I am making a personal DB of electronics components and needed barcoded labels for hundreds of drawers so I can keep the quantities updated.

MobiScan barcode app on my cell phone easily reads the barcode and copies the number to the clipboard. I ordered a mini-USB bluetooth dongle on eBay for $5. Now I need to "figure" a BASIC app to get the number from the clipboard to Access 2002 on the laptop via bluetooth (haven't selected which BASIC yet - no idea how to interface to Access 2002 via BT yet).

I didn't really care what was on the barcode, as long as it is unique and will not change. The Date and Time suit me fine. There's no way I can enter 2 entries under 1 second.

I thought it was the easiest and cleanest way for me to do this; Default Value: NOW(). It doesn't require reading all the records in the database nor updating a record in a separate database.

EDIT: And I can use it directly from the Database View. I'll do Forms much later once I've finalized DB structure.

Robert