473,320 Members | 2,054 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

auto number by category?

Can anyone tell me if there is a way to auto number by category?

I have 2 tables, ExpenseCategories and Vendors.

Each there are many vendors to each expense category, but each category
has a specific set of numbers for each vendor listed in it. i.e.
Expence Catigory is Office Supplies... all vendors for that catigory
are listed between 22401 and 22499.

Is there a way to have it auto number so Office supplies would start at
22400 and say Office Repair would start and 22500?

Jan 24 '06 #1
10 1812
On 24 Jan 2006 12:13:07 -0800, "Marina" <ma*********@earthlink.net>
wrote:

No. Too bad you don't lurk here more often, or you would have noticed
this is a FAQ. Check out this article:
http://www.mvps.org/access/general/gen0025.htm

To do what you want, change the field to a regular long integer, and
roll your own numbering scheme. One approach I like is to have a table
with:
CategoryName, IDToBeUsedNext
OfficeSupplies, 22401
OfficeRepair, 22501
etc.
Then write a function:
GetNextID(byval strCategoryName as string) as Long
that would read the next ID from this table, increment the value by 1,
and return the read value.

-Tom.
Can anyone tell me if there is a way to auto number by category?

I have 2 tables, ExpenseCategories and Vendors.

Each there are many vendors to each expense category, but each category
has a specific set of numbers for each vendor listed in it. i.e.
Expence Catigory is Office Supplies... all vendors for that catigory
are listed between 22401 and 22499.

Is there a way to have it auto number so Office supplies would start at
22400 and say Office Repair would start and 22500?


Jan 25 '06 #2

"Marina" <ma*********@earthlink.net> wrote
Can anyone tell me if there is a way to
auto number by category?
Autonumber is only intended to be unique, for internal use in the
application, such as joins in Queries. For any purpose where the value of
the number is meaningful, you should not use an Autonumber.

There are, or have been, some approaches to starting an Autonumber at a
particular value, but whether they work tends to depend on the version of
Access involved -- and, because that implies that the value is meaningful,
Autonumbers should not have been used.

I can't imagine trying to manipulate AutoNumbers to somehow synchronize in
two tables. At any point, you may find a gap in the series, and that may be
as little as a single number or may be many, many numbers.

You can write (or hire someone to write for you) VBA code that will follow
most any numbering scheme you want. On the other hand, you may have some
other design problems that you should have reviewed before you put fingers
to keys writing code.

Larry Linson
Microsoft Access MVP

I have 2 tables, ExpenseCategories and Vendors.

Each there are many vendors to each expense category, but each category
has a specific set of numbers for each vendor listed in it. i.e.
Expence Catigory is Office Supplies... all vendors for that catigory
are listed between 22401 and 22499.

Is there a way to have it auto number so Office supplies would start at
22400 and say Office Repair would start and 22500?

Jan 25 '06 #3
Marina wrote:
Each there are many vendors to each expense category, but each category
has a specific set of numbers for each vendor listed in it. i.e.
Expence Catigory is Office Supplies... all vendors for that catigory
are listed between 22401 and 22499.


See the other replies on use of autonumner. My comment is that the
above is a stupid way to assign vendor numbers or identification. Do
you really think you're going to have just 99 possible vendors? Perhaps
if your application was just for a town and suppliers only located on
the south pole... 8)

Your design also seems stupid in that it seems that if a vendor supplies
you with two or more types of items from different expense categories
then the vendor must be listed two or more times.

It makes far, far more sense to not get wrapped around the axle over
what specific numbers mean. Systems that do this are usually very poor
or are being dictated to by dinosaurs who insist an identifier "mean
something". A far better approach would be to have some sort of
sequence for a vendor number that could be number only or even
constructed from alphanumerics. But what you really need is a third
look up table to allow you to relate a single vendor (listed ONCE) to
multiple categories.

Hope this helps. Of course, I may have got things wrong, but from the
little you've described, this is a disasterous approach. You may need
to rethink your whole operation and how you are relating it to data
entities.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto
Jan 25 '06 #4
Hi Tim
You're totally pissed off too, eh?

Jan 25 '06 #5
Lyle Fairfield wrote:
Hi Tim
You're totally pissed off too, eh?


By the fact that our province still sent Liberals to parliament, yes.
After those assholes tried so hard to screw us twice out of the ATlantic
Accord, we fully deserve every negative sobriquet ever attributed to our
lack of intelligence... 8)

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto
Jan 25 '06 #6
I'm stuck with the old numbering system dictated by "the BOSS" who, and
you can guess, won't hear of any other way. Of course this is the guy
who went out and bought a used P 166 thinking he could use it with
QuickBooks Pro 2006.

Just hoping to make my life just a little bit easier as far as this db
is concerned

Jan 25 '06 #7
Now we know the problem. It is not auto-number; it is the boss. It
seems that he or she requires numbers generated according to some
archaic rule. (He/She is probably a great fan of DAO, too?)

I suggest that it will save you much heart-ache (and head-ache) in the
future if you use autonumbers as internal record identifiers and devise
some other strategy to satisfy the whims of your boss, that is to print
or show him/her some meaningless number that meets his/her
requirements.

I would steal an image of Dilget's boss
http://www.dilbert.com/comics/dilbert/the_characters/
and use it as a button image which you boss could use to see his
numbers.

Jan 25 '06 #8
Dilget = the way someone who was in the process of writing Property Get
code when he responds here spells Dilbert.

Jan 25 '06 #9
"Marina" <ma*********@earthlink.net> wrote in
news:11*********************@z14g2000cwz.googlegro ups.com:
I'm stuck with the old numbering system dictated by "the BOSS"
who, and you can guess, won't hear of any other way. Of course
this is the guy who went out and bought a used P 166 thinking he
could use it with QuickBooks Pro 2006.

Just hoping to make my life just a little bit easier as far as
this db is concerned


One option is to use real Autonumbers with no meaning at all behind
the scenes for your actual PK values, then maintain a
"user-friendly" numbering system that is exposed to users that does
*not* function as the PK, just as a lookup. I have a client who was
supporting a legacy numbering system that could easily have been
abandoned when I took over their app, but instead I just left it
alone and converted to hidden Autonumbers behind the scenes.

The complicated part of it is writing the code to select the next
number, which is not quite as simple as it seems, particularly if
numbers have internal structure, such as 00-000-0 or some such,
where each group of numbers refers to a some particular group.
Obviously, storing that in a single field is denormalized, but it's
a pain to actually normalize something like this into 3 columns,
though it would be the technically correct way to accomplish it. The
problem is that it creates issues with user interaction, since users
don't care that it's stored in 3 fields not one.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jan 25 '06 #10
David W. Fenton wrote:
"Marina" <ma*********@earthlink.net> wrote in
news:11*********************@z14g2000cwz.googlegro ups.com:

I'm stuck with the old numbering system dictated by "the BOSS"
who, and you can guess, won't hear of any other way. Of course
this is the guy who went out and bought a used P 166 thinking he
could use it with QuickBooks Pro 2006.

Just hoping to make my life just a little bit easier as far as
this db is concerned


I'm responding to David's response because I can't see the original post
he quotes above...

As I suspected, this is something imposed by a dinosaur. In my opinion,
it's still a developer's duty to point out obvious flaws such as the one
I mention about the total lack of any potential for any growth in a
system. If someone insists on crossing the street with his eyes closed,
it would be approrpiate to remind him that such is dangerous.

If you've done that, then you've done your duty as a developer and the
best thing you can do is do the app in the method suggested by David or
Lyle with this atrocity of assigning vendor numbers as an afterthought
just so it's there.

I'm personally in the midst of an organization that is falling apart
because the boss is too dim to take any advice from his managerial and
supervisory staff and bases everything on what he thinks himself to be
an expert on computerized maintenance management systems yet he can just
barely operate his own email. It's a sad thing to see an organization
that was once recognized with an award of excellence from a peer review
of a professional organization, but it's amazing what effects poor
leadership have and the new brass always act before they think. It
sounds as if you doubtless have a similar atmosphere.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto
Jan 25 '06 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: L'Angel Admirable | last post by:
Hi, all, I have this table in which each row is associated with a category number. The category number is NOT unique, so basically more than one row in the table can have the same category...
11
by: Mr. Smith | last post by:
Hello all, My code can successfully open, write to, format and save several worksheets in a workbook then save it by a given name, close and quit excel. My problem is that if I try and do it...
6
by: Sebastien | last post by:
Hi, I am building a products database, linking sales and production. Each part has a unique sales Stock Code and Production Number. The sales stock code is a combination of letters and numbers...
7
by: Barkster | last post by:
I'm trying to list my categories and how many items are in each category. I'm not much on sub queries. How can I combine these two items. I'd like to display the category and the corresponding...
3
by: S.Dickson | last post by:
I had an access database that i use as an ordering system. I have a form for entering customer details. When i add a new customer on the form the customer number is an auto number that appears when...
6
by: luanhoxung | last post by:
hi all ! I have a text field with contruction: 0001-COM. I need to it automatically increase 0002-COM when add new record and ..... Any help with appreciated. Luan
2
by: OM | last post by:
I have two tables, one with categories in it, and only 3 records - Wages, Salary, Contract. I have a second table that records hours and numbers of employees in each category, per month. How do...
1
by: gauravtechie | last post by:
Hi all, I have a javascript that is reloading itself on every menu change, Now the problem is that the values of the product doesn`t reset itself on catalogue change, the code is <html>...
8
by: pld60 | last post by:
Making progress with my data base but have run into a snag. Working with an unbound form that place data into more than one table using a "save" button with this code. Private Sub...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.