Unique ID numbers related to Categories in Access 2010

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

scstreit

Thread Starter
Joined
Jan 2, 2013
Messages
2
Hi!

I am working on an Access (2010) Database for our School Auction. I have set up a table with a list of Categories for the donations (Dining, Kids Activities, Sports etc). I then set up an Auction Donations table to enter all of the information related to each donation and included a Category field that pulls from the Category table. In years past each Category had its own series of 100 numbers (i.e. Dining 100, Kids Activities 200, Sports 300, etc).

My Questions:

Is there a way to set up the ID# field to look at the category and then give a unique ID# starting with 100, 200, 300 depending on the category?

Do I set this up in the Category Table or the Auction Donation Table?

If this is possible, will it continue to add sequential numbers based on categories as I continue adding items?

If this isn't possible, do I need to set up separate tables for each category and then start each one as 100, 200, 300 etc?

I'm new to Access so any help would be appreciated.
Thanks!
Stephanie
 

OBP

Joined
Mar 8, 2005
Messages
19,895
scstreit, welcome to the Forum.
I have a couple of question back to you?
Why bother?
Why not just let Access create unique keyID numbers and use those, as they should be transparent to the the users anyway?
 

scstreit

Thread Starter
Joined
Jan 2, 2013
Messages
2
Thank you for responding! When we print our auction program the numbers will be listed next to each item (each category of items will start a new page in the report) We have multiples of the same items and once the silent auction is closed we will match up the item number with the winning bidder.

As far as using what Access is assigning to the item as I enter it...I have been entering items as I receive them so right now Dining may have a 1, 10 and 12 while kids may have a 2, 4 and 7. I'd like for them to be sequential in each category.

Would it be easier to make a table for each category? Or is there a way to keep every category in one table but have unique ID #'s based on the category?
 

OBP

Joined
Mar 8, 2005
Messages
19,895
It is possible to use unique values for each Category in the one table, it takes some VBA to do so.
Basically you create a Query with the categories and their ID numbers and use Totals, Group by Category and "Max" to get the last ID value for each category.
You then have VBA to use the query to increment the last value for the required Category.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top