1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Unique ID numbers related to Categories in Access 2010

Discussion in 'Business Applications' started by scstreit, Jan 2, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. scstreit

    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
     
  2. OBP

    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?
     
  3. scstreit

    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?
     
  4. OBP

    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.
     
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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1083412

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice