A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Need to have two autonumber fields in one table



 
 
Thread Tools Display Modes
  #1  
Old November 3rd, 2009, 03:05 PM posted to microsoft.public.access.tablesdbdesign
plisvb via AccessMonster.com
external usenet poster
 
Posts: 13
Default Need to have two autonumber fields in one table

Hello,

I've created an inventory dbase for a grocery warehouse and initially I
created one table for different types of invoices:

1) Incoming
2) Outgoing
3) Shrinkage

My accountant has informed me that each invoice of each type must be
sequential so incoming are INC1,INC2,INC3 and outgoing are O1, O2, O3 instead
of the numbers being mixed.

If I would've know this at the beginning I would've separated the tables, one
for incoming, one for outgoing etc...

Is there a way to add another autonumber field and have it only increment
depending on the type of invoice it is?

I've tried adding creating another table just to hold the Incoming number
ID's and linking it to my main table but when I add it to the form that I'm
using, all the fields go blank.

Thanks

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200911/1

  #2  
Old November 3rd, 2009, 03:57 PM posted to microsoft.public.access.tablesdbdesign
Klatuu[_2_]
external usenet poster
 
Posts: 53
Default Need to have two autonumber fields in one table

You should not be using Autonumber fields for any meaningful data. They
should only be used as surrogate primary keys and to link Parent/Child
tables.

And invoices are invoices. All invoices should be in the same table.
Ignore what your accountant is telling you. Most accountants live in a
spreadsheet world. Access is not a spreadsheet program.

Part of the problem with them would drive you accountant round the bend.
You cannot depend on them to be sequential. There will be gaps. That is
because once a new record is created, the number is assigned. If you don't
actually update the table with the record, the number assigned is discarded.

Your invoice table should have a field identifying the type of invoice it
is. How you assign invoice number to each type depends on your business
rules. If you want a sequential number for each type of invoice, you can
use the DMax function to find the highest number available for the invoice
type and add 1 to it. Here is a very basic example that should give you an
idea of how it works. I use the After Update event of the text box (or
combo, doesn't matter) where you enter the invoice type:

Private Sub txtInvType_AfterUpate()

If Me.NewRecord Then
Me.txtInvNumber = Nz(DMax("[InvNumber]", "tblInvoice", "[InvType] =
""" & Me.txtInvType & """"), 0) + 1
End If

End Sub

"plisvb via AccessMonster.com" u55912@uwe wrote in message
news:9e908e6ca0b6f@uwe...
Hello,

I've created an inventory dbase for a grocery warehouse and initially I
created one table for different types of invoices:

1) Incoming
2) Outgoing
3) Shrinkage

My accountant has informed me that each invoice of each type must be
sequential so incoming are INC1,INC2,INC3 and outgoing are O1, O2, O3
instead
of the numbers being mixed.

If I would've know this at the beginning I would've separated the tables,
one
for incoming, one for outgoing etc...

Is there a way to add another autonumber field and have it only increment
depending on the type of invoice it is?

I've tried adding creating another table just to hold the Incoming number
ID's and linking it to my main table but when I add it to the form that
I'm
using, all the fields go blank.

Thanks

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200911/1



  #3  
Old November 3rd, 2009, 05:52 PM posted to microsoft.public.access.tablesdbdesign
plisvb via AccessMonster.com
external usenet poster
 
Posts: 13
Default Need to have two autonumber fields in one table

Thanks Klatuu,

This is definitely a help for me. I'll see if I can implement your code.

Klatuu wrote:
You should not be using Autonumber fields for any meaningful data. They
should only be used as surrogate primary keys and to link Parent/Child
tables.

And invoices are invoices. All invoices should be in the same table.
Ignore what your accountant is telling you. Most accountants live in a
spreadsheet world. Access is not a spreadsheet program.

Part of the problem with them would drive you accountant round the bend.
You cannot depend on them to be sequential. There will be gaps. That is
because once a new record is created, the number is assigned. If you don't
actually update the table with the record, the number assigned is discarded.

Your invoice table should have a field identifying the type of invoice it
is. How you assign invoice number to each type depends on your business
rules. If you want a sequential number for each type of invoice, you can
use the DMax function to find the highest number available for the invoice
type and add 1 to it. Here is a very basic example that should give you an
idea of how it works. I use the After Update event of the text box (or
combo, doesn't matter) where you enter the invoice type:

Private Sub txtInvType_AfterUpate()

If Me.NewRecord Then
Me.txtInvNumber = Nz(DMax("[InvNumber]", "tblInvoice", "[InvType] =
""" & Me.txtInvType & """"), 0) + 1
End If

End Sub

Hello,

[quoted text clipped - 23 lines]

Thanks


--
Message posted via http://www.accessmonster.com

  #4  
Old November 5th, 2009, 08:58 PM posted to microsoft.public.access.tablesdbdesign
plisvb via AccessMonster.com
external usenet poster
 
Posts: 13
Default Need to have two autonumber fields in one table

Hi Klatuu,

I'm trying to use this code (which is yours more or less) but I keep on
getting a runtime error:

Private Sub Transaction_Type_AfterUpdate()
If Me.NewRecord Then
Â*Me.inv = Nz(DMax("[OrderID]", "Orders", "[Transaction Type] = 2"), 0) +
1
End If
End Sub

inv = field where I want the calculation to occur
OrderID = Autonumber field for the orders(invoices) table
Transaction Type = 1 - Incoming 2 - Outgoing 3 - Shrinkage I'm trying to do
all the outgoing orders hence the #2.

Not sure about the syntax, new to VB

Thanks

Klatuu wrote:
You should not be using Autonumber fields for any meaningful data. They
should only be used as surrogate primary keys and to link Parent/Child
tables.

And invoices are invoices. All invoices should be in the same table.
Ignore what your accountant is telling you. Most accountants live in a
spreadsheet world. Access is not a spreadsheet program.

Part of the problem with them would drive you accountant round the bend.
You cannot depend on them to be sequential. There will be gaps. That is
because once a new record is created, the number is assigned. If you don't
actually update the table with the record, the number assigned is discarded.

Your invoice table should have a field identifying the type of invoice it
is. How you assign invoice number to each type depends on your business
rules. If you want a sequential number for each type of invoice, you can
use the DMax function to find the highest number available for the invoice
type and add 1 to it. Here is a very basic example that should give you an
idea of how it works. I use the After Update event of the text box (or
combo, doesn't matter) where you enter the invoice type:

Private Sub txtInvType_AfterUpate()

If Me.NewRecord Then
Me.txtInvNumber = Nz(DMax("[InvNumber]", "tblInvoice", "[InvType] =
""" & Me.txtInvType & """"), 0) + 1
End If

End Sub

Hello,

[quoted text clipped - 23 lines]

Thanks


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200911/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:16 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.