Tracking/sequence numbers

[Kyle Friesen via AccessMonster.com]

Mike,
I have databse that creates a "tracking number" based on the selections made on the form via concatenating. At the end of the tracking number, I need a two digit (01-99) sequence number by product group for each customer. Hpw do I do this without creating a table for each customer and each product group with autonumbers. For example based on the entries in this sample, the desired result would be:

Customer Business Unit Tracking
Dealer Group Binders 01
Dealer Group Office Labels 01
Forms & Supply Binders 01
Dealer Group Binders 02

It is Dealer Group's second Binder entry so 02.

Please Help!

Thanks!

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

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Why do you need a tracking number per Customer/BusinessUnit? If you
want to count how many times a Customer [something] in/with a Business
Unit you could just use the SQL COUNT(*) function. E.g.:

SELECT Customer, BusinessUnit, Count(*) As Tracking
FROM table_name
GROUP BY Customer, BusinessUnit

It would return a result set like this:

Customer Business Unit Tracking
================================================== ======
Dealer Group Binders 2
Dealer Group Office Labels 1
Forms & Supply Binders 1

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQfXcvIechKqOuFEgEQKthQCfYwqYPokUGMyUUevlpuhrrx nMjT4AoJJ7
+RNJ0kdS9Xmb7xkjNfHAS4YX
=N3w7
-----END PGP SIGNATURE-----
Kyle Friesen via AccessMonster.com wrote:

Mike,
I have databse that creates a "tracking number" based on the selections made on the form via concatenating. At the end of the tracking number, I need a two digit (01-99) sequence number by product group for each customer. Hpw do I do this without creating a table for each customer and each product group with autonumbers. For example based on the entries in this sample, the desired result would be:

Customer Business Unit Tracking
Dealer Group Binders 01
Dealer Group Office Labels 01
Forms & Supply Binders 01
Dealer Group Binders 02

It is Dealer Group's second Binder entry so 02.

[Ed Robichaud]

If you search this group on "autonumber" you'll find a lot of advice.

Autonumber is a field type that is really a long integer, with some hidden
code
to increment the value for every added record. I do NOT recommend
any of these pseudoID forms that use combinations of initials/date/other,
as they ALL will eventually fail and most are vulnerable to user input
mistakes.
Trying to "imbed" data within an ID is almost always a bad idea.
Most good data designs have a primary key for each table,
generally an autonumber field. Most applications do not display that ID, but
use it internally for relational integrity.

That said, one of the more common solutions for what you ask, is to
concatenate
2 or more fields in the format that you require.

On your forms/reports
that need the pseudoID displayed, create an unbound textbox and set its
control source to something like:
=InStr([Dealr Group Binder],1)&InStr([Dealer Group Office Labels],1)
....etc.
-Ed

"Kyle Friesen via AccessMonster.com" <fo***@AccessMonster.com> wrote in
message news:9f******************************@AccessMonste r.com...

Mike,
I have databse that creates a "tracking number" based on the selections
made on the form via concatenating. At the end of the tracking number, I
need a two digit (01-99) sequence number by product group for each
customer. Hpw do I do this without creating a table for each customer and
each product group with autonumbers. For example based on the entries in
this sample, the desired result would be:

Customer Business Unit Tracking
Dealer Group Binders 01
Dealer Group Office Labels 01
Forms & Supply Binders 01
Dealer Group Binders 02

It is Dealer Group's second Binder entry so 02.

Please Help!

Thanks!

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

[Bob Quintal]

"Kyle Friesen via AccessMonster.com" <fo***@AccessMonster.com>
wrote in
news:9f******************************@AccessMonste r.com:

Mike,
I have databse that creates a "tracking number" based on the
selections made on the form via concatenating. At the end of
the tracking number, I need a two digit (01-99) sequence
number by product group for each customer. Hpw do I do this
without creating a table for each customer and each product
group with autonumbers. For example based on the entries in
this sample, the desired result would be:

Customer Business Unit Tracking
Dealer Group Binders 01
Dealer Group Office Labels 01
Forms & Supply Binders 01
Dealer Group Binders 02

It is Dealer Group's second Binder entry so 02.

Please Help!

Thanks!

You need to do a dlookup() that returns the maximum value already
in the table that meets the conditions you set, in this case
customer and business unit, then add 1.

Your problem is that if you concatenate it to the remainder of
the tracking number, you have to jump through hoops to extract
it.

The tracking number doesn't need to be stored if your table
contains the customer and Business unit data, so just store the
tracking as an integer in your table.

Therefore the default value for a tracking number should be

nz(
Dmax("tracking","tablename",
"Customer = """ & me.txtcustomer &
""" AND [Business Unit] = """ & me.txtbusinessUnit & """"
,0)+1

put that all on one line, change tablename to the real name of
your table, me.txtcustomer to the name of the control on your
form that shows the customer name, and me.txtBU to the one that
shows business unit.

You then concatenate in the form, report or query used to see the
data.
--
Bob Quintal

PA is y I've altered my email address.