Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table field lookup question (2000/sp3)

    Once again I come to you with a more advanced question.. I sure appreciate the great folks on this forum!

    I have a table of parts... fairly simple:

    idsPartIndex
    tlkpPartNumberID
    tlkpPartLocationTypeID
    tlkpPartLocationID
    txtPartLocationNotes
    lngPartQty

    I am trying to organize my data properly, and here is what I'm doing for now, unless I can figure out something better).. This table lists all parts in the system, along with where they are located. Duplicates allowed because you might have two bags of the same part that you store on the same shelf...

    The problem is that a part location can be anything of the following: a storage shelf number, a project being built, the name of an engineer, etc. I would like to have the shelf numbers in a table, the project names in a separate table, engineer's names in yet another table, etc.

    I would like to be able to use tlkpPartLocationTypeID to identify WHAT table should be reference for the tlkpPartLocationID field. Can I do that in a table? Should I organize my data a little differently?

    Thanks in advance,
    ..dane

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table field lookup question (2000/sp3)

    One possibility would be to create ONE table containing all possible "locations", with a LocationTypeID field to specify whether it is a shelf number, project ID or engineer ID.

    On the parts form, you could have two combo boxes: the first to select a location type, the second to select a location. This combo box would be filtered to display only locations of the selected LocationTypeID in the After Update event of the first combo box.

    Another possibility is to use separate tables. You would need to have an ID field of the same type in each of these tables, preferably a number field. A separate table would be used to link the LocationTypeID to the corresponding table:

    <table border=1><td>LocationTypeID</td><td>Description</td><td>TableName</td><td align=center>1</td><td>Shelves</td><td>tlkpShelves</td><td align=center>2</td><td>Projects</td><td>tlkpProjects</td><td align=center>3</td><td>Engineers</td><td>tlkpEngineers</td></table>
    On the parts form, you would again have two combo boxes, one to select the location type, the other one to select a specific location. In the After Update event of the first combo box, the row source of the second combo box would be set to the table corresponding to the selected LocationTypeID.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table field lookup question (2000/sp3)

    HansV,

    Thanks for the quick reply. I have started going down the path of one table with a locationTypeID and the location name... I'm going to see how that goes. It just might go swimmingly well (and less complicated)

    Thanks again,
    ..dane

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •