Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

DB RELATIONSHIP PROBLEM!

Discussion in 'Information Technology' started by sbshelp, Nov 9, 2005.

  1. sbshelp

    sbshelp
    Expand Collapse
    Guest

    hey guys - i am creating a inventory tracking db in ms2000.

    i am going to track the location of items and then in another table/form
    tell what is needed to be purchased.

    thing is - some items in inventory have cat numbers and some dont.
    question -

    how do i match up the items which do not have cat #s to the table of items
    to be ordered?

    thanks guys?

    auto number and then make person fill in cat number when ordering?

    a pushed input?
     
  2. Loading...

    Similar Threads Forum Date
    Relationship with Creator Blogs Oct 17, 2015
    I'm open-minded about romantic relationships... how do I explain to family? Love & Marriage Aug 22, 2015
    Arts/Society What are your thoughts on the interaction of male / female relationship of marriage? Language, Arts & Culture Oct 15, 2013
    Relationship advice Love & Marriage Sep 30, 2012
    Need advice...Sikhi in relationships Love & Marriage Nov 17, 2011

  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Tue, 8 Nov 2005 12:56:06 -0800, "sbshelp"
    <sbshelp@discussions.microsoft.com> wrote:

    >how do i match up the items which do not have cat #s to the table of items
    >to be ordered?


    I have no idea; how would you do it *without* a computer?

    What information do you have available about these items? How would a
    cat # be assigned normally?

    John W. Vinson[MVP]
     
  4. sbshelp

    sbshelp
    Expand Collapse
    Guest

    hey john - i have a computer - cat# is the catalog number for the items.
    i think that i will assign auto nnumber (indexed -no dupes) to each and then
    make the user assigna cat# when prompted. thanks!


    "John Vinson" wrote:

    > On Tue, 8 Nov 2005 12:56:06 -0800, "sbshelp"
    > <sbshelp@discussions.microsoft.com> wrote:
    >
    > >how do i match up the items which do not have cat #s to the table of items
    > >to be ordered?

    >
    > I have no idea; how would you do it *without* a computer?
    >
    > What information do you have available about these items? How would a
    > cat # be assigned normally?
    >
    > John W. Vinson[MVP]
    >
     
  5. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 9 Nov 2005 06:22:02 -0800, "sbshelp"
    <sbshelp@discussions.microsoft.com> wrote:

    >hey john - i have a computer - cat# is the catalog number for the items.
    >i think that i will assign auto nnumber (indexed -no dupes) to each and then
    >make the user assigna cat# when prompted. thanks!


    DON'T use Autonumber for this purpose.

    Autonumbers have one purpose only - to provide a guaranteed (almost,
    there are bugs) unique key. They will always have gaps and can become
    random (if you Replicate the database for example).

    I'd suggest using a Combo Box to select the item, and use the combo's
    Not In List event to open a form to add a new item if the desired item
    isn't on the list. The Form could have code like this in its
    BeforeInsert event to implement a "roll your own" autonumber:

    Private Sub Form_BeforeInsert(Cancel as Integer)
    Me!Cat = NZ(DMax("[Cat]", "[ItemsTable]")) + 1
    End Sub

    Suggestion: if you're using # in a fieldname, don't. It's a date
    delimiter and can cause problems.

    John W. Vinson[MVP]
     
  6. sbshelp

    sbshelp
    Expand Collapse
    Guest

    john! thanks so much. i will try this.
    so i need'nt a unique identifier?
    here is an example.

    in location 2c there is a flask. the flask has no unique catalog number.

    i need to catalog that flask and its location.

    now - the use would go in the db via form looking for that flask from combo
    box and will find its location.

    the user may have to order another flask of teh same kind BUT doesnt have
    the catalog number on MY database.

    i need to match these up because the budget needs to be on db too.

    see where i am coming from?



    "John Vinson" wrote:

    > On Wed, 9 Nov 2005 06:22:02 -0800, "sbshelp"
    > <sbshelp@discussions.microsoft.com> wrote:
    >
    > >hey john - i have a computer - cat# is the catalog number for the items.
    > >i think that i will assign auto nnumber (indexed -no dupes) to each and then
    > >make the user assigna cat# when prompted. thanks!

    >
    > DON'T use Autonumber for this purpose.
    >
    > Autonumbers have one purpose only - to provide a guaranteed (almost,
    > there are bugs) unique key. They will always have gaps and can become
    > random (if you Replicate the database for example).
    >
    > I'd suggest using a Combo Box to select the item, and use the combo's
    > Not In List event to open a form to add a new item if the desired item
    > isn't on the list. The Form could have code like this in its
    > BeforeInsert event to implement a "roll your own" autonumber:
    >
    > Private Sub Form_BeforeInsert(Cancel as Integer)
    > Me!Cat = NZ(DMax("[Cat]", "[ItemsTable]")) + 1
    > End Sub
    >
    > Suggestion: if you're using # in a fieldname, don't. It's a date
    > delimiter and can cause problems.
    >
    > John W. Vinson[MVP]
    >
     
  7. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 9 Nov 2005 09:41:07 -0800, "sbshelp"
    <sbshelp@discussions.microsoft.com> wrote:

    >john! thanks so much. i will try this.
    >so i need'nt a unique identifier?


    On the contrary. If you want to store information about an item and
    look it up later, you DO need a unique identifier. Otherwise how can
    you distinguish that item from other items?

    You can use an autonumber if it's kept "under the hood" as a link to
    other tables. My concern was that you should not use an autonumber in
    a way which exposes its value to users.

    >here is an example.
    >
    >in location 2c there is a flask. the flask has no unique catalog number.
    >
    >i need to catalog that flask and its location.
    >
    >now - the use would go in the db via form looking for that flask from combo
    >box and will find its location.
    >
    >the user may have to order another flask of teh same kind BUT doesnt have
    >the catalog number on MY database.
    >
    >i need to match these up because the budget needs to be on db too.
    >
    >see where i am coming from?


    Well, you do need a unique ID. But again... how can your database
    uniquely identify a 500ml Erlenmeyer flask in Room 204, as distinct
    from the other (much dirtier) 500ml Erlenmeyer flask also in Room 204?
    Your combo box needs to be based on SOMETHING - a table of all your
    inventory, cataloged and uncataloged. Maybe the Cat# field could be
    made optional, but the table must have SOME sort of unique identifier
    in addition if you're going to do so. And you'll need some combination
    of fields to allow the user to select *this* flask as distinct from
    *this other* flask - you know better than I what that combination
    might be.

    John W. Vinson[MVP]
     

Share This Page