Results 1 to 6 of 6
  1. #1
    pwmichaelsr is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    2

    Verify state field

    I have a DB with a table that contains fields for asset #, division and state. Each division works in up to 3 states. I am looking for a way to verify that the state field is valid for the division. For example, Div 21 works in NY, NJ and PA. Asset 10000 is assigned to Div 21 and I want to make sure the state field is either NY, NJ or PA. Any ideas?



    Thanks for the help.

  2. #2
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Welcome to the forum.

    Since your divisions are associated with particular states, you should define those in your table structure. Can two divisions be in the same state? The following table structure assumes that they can:

    tblStates
    -pkStateID primary key, autonumber
    -StateAbbr
    -StateName

    tblDivisions
    -pkDivisionID primary key, autonumber
    -txtDivision


    tblDivisionStates
    -pkDivisionStatesID primary key, autonumber
    -fkDivisionID foreign key to tblDivisions
    -fkStateID foreign key to tblStates

    In your form, you would use a combo box for the division and then a related combo box for the state. In other words, the first combo box is used to filter the states of the second combo. This concept is called cascading combo boxes.

  3. #3
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    well one needs to first clarify by what you mean by 'verify'. Autocomplete? offer the user just the correct 3 states?..or audit lots of existing records?

  4. #4
    pwmichaelsr is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    2
    Audit existing records. There are over 7,000 records to check.

  5. #5
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Setting up the tables as I indicated and then creating applicable queries can assist in your audit by identifying those records that have incompatible states for the divisions.

  6. #6
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    it should be quite straight forward. make a query on your table using query design; begin with entering criteria for 1 division i.e. "DIV 21" (or whatever works) - so that the query results in all the records for that division.... then while looking at the query results - just highlight the column that contains the state - and do an A-Z sort (this icon is in the tool bar)....you will then quickly be able to visually see any records that don't sort in order with the 3 approved states....they should stand out easily...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Triple State checkbox scroll order
    By lfox in forum Forms
    Replies: 7
    Last Post: 06-26-2010, 08:19 AM
  2. Verify CSV Field Names Before Importing
    By johnson in forum Import/Export Data
    Replies: 2
    Last Post: 01-26-2010, 06:53 PM
  3. Replies: 1
    Last Post: 02-26-2009, 11:31 AM
  4. Pause state?
    By Zermoth in forum Programming
    Replies: 1
    Last Post: 12-15-2008, 05:05 AM
  5. Quick way to stuff field names into text field
    By kfinpgh in forum Programming
    Replies: 0
    Last Post: 01-04-2007, 01:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums