Welcome to SPN

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

Sign Up Now!

Best Way To Store and Retrieve Data?

Discussion in 'Information Technology' started by Roger Tregelles, Jul 28, 2006.

  1. Roger Tregelles

    Roger Tregelles
    Expand Collapse
    Guest

    Hi Folks,

    I've created a database to keep track of all of our suppliers here (Approved
    Supplier Listing). Our purchasing group wants to add the ability to show all
    of the types of commodities that the supplier can provide (injection
    molding, tooling, packaging, etc.) Each supplier could have several
    commodities to choose from a listing of about 20 choices.

    I had thought about adding 20 true/false checkboxes to the main table and
    then allow the user to select which commodities apply. The problem with that
    is I need to also have a Parameter Query (tied to a Report) where you could
    type in the name of a commodity and this would search all of the suppliers
    for this value. I'm not sure you can create a Parameter Query to search all
    20 fields at once.

    I then thought of creating another table which could store all of the
    commodities a supplier provides using the Parent/Child relationship. Only
    problem here is I would need to create a sub-form off my main form to select
    the commodities and this might look cluttered.

    I've read about lookup tables which can store information from other tables
    and thought this might be a solution. Never having worked with these, I'm
    not sure how I would set it up exactly. Maybe I'm just over-thinking this.
    I'm just looking for a recommendation on how to store this information and
    how to retrieve it easily in simple parameter query by a single keyword
    (commodity type). Thanks in advance for any and all help and direction
    provided.


    Roger Tregelles
    Quality Engineer
     
  2. Loading...

    Similar Threads Forum Date
    Mac mini comes to Best Buy stores (MacCentral) Interfaith Dialogues Apr 6, 2005
    and finally, the best quote of them all Blogs Oct 17, 2015
    Sikhism, the best kept secret in the world Blogs Oct 17, 2015
    He’s The World’s Best Water Photographer… And He’s Just Released These Haunting Images. Sikh Sikhi Sikhism Aug 30, 2015
    General Best English Sikh History Books History of Sikhism Sep 21, 2013

  3. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    What about this for tables --
    Suppliers - an ID field and fields for other information.

    Commodities - ID and types of commodities

    Junction table --
    ID
    SupplierID - foreign key
    CommoditiesID - foreign key
    Active - Yes/No

    One-to-many relation Supplier.SupplierID to Junction.SupplierID.
    One-to-many relation Commodities.CommoditiesID to Junction.CommoditiesID.

    For data entry Supplier main form with Commodities subform. Subform have
    listbox bound to CommoditiesID.

    Commodities main form with Supplier subform to find suplier.

    "Roger Tregelles" wrote:

    > Hi Folks,
    >
    > I've created a database to keep track of all of our suppliers here (Approved
    > Supplier Listing). Our purchasing group wants to add the ability to show all
    > of the types of commodities that the supplier can provide (injection
    > molding, tooling, packaging, etc.) Each supplier could have several
    > commodities to choose from a listing of about 20 choices.
    >
    > I had thought about adding 20 true/false checkboxes to the main table and
    > then allow the user to select which commodities apply. The problem with that
    > is I need to also have a Parameter Query (tied to a Report) where you could
    > type in the name of a commodity and this would search all of the suppliers
    > for this value. I'm not sure you can create a Parameter Query to search all
    > 20 fields at once.
    >
    > I then thought of creating another table which could store all of the
    > commodities a supplier provides using the Parent/Child relationship. Only
    > problem here is I would need to create a sub-form off my main form to select
    > the commodities and this might look cluttered.
    >
    > I've read about lookup tables which can store information from other tables
    > and thought this might be a solution. Never having worked with these, I'm
    > not sure how I would set it up exactly. Maybe I'm just over-thinking this.
    > I'm just looking for a recommendation on how to store this information and
    > how to retrieve it easily in simple parameter query by a single keyword
    > (commodity type). Thanks in advance for any and all help and direction
    > provided.
    >
    >
    > Roger Tregelles
    > Quality Engineer
    >
    >
    >
     
  4. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    You will definitively want a subform (play 'what if' with a new set of
    commodities)
    make your subform "flat" to avoid "cluttering" and/or consider tab pages
    (users tend to accept that!)
    It *would* be possible offcourse to add/remove/hide Controls 'on the fly'
    but I don't think your boss would be to happy about the extra cost.
    Either way *do* use a seperate table for storing supplier -> commodity data
    (trust me)

    HTH

    Pieter

    suggested structure

    Table Supplier
    SUPPLIERID (PK - Autonumber)
    ......
    Table Commodoties
    COMMODOTYID (PK -Autonumber)
    .....

    Table SupplierCommodoties
    SupplierCommodotiesID (PK - Autonumber - Not neccessary, but I allways make
    a PK of the tableName + ID, makes it easier to automate DBA tasks)
    SUPPLIERID
    COMMODOTYID
    ..... (yes, you might want to have extra info in the link (something like
    preffered supplier for that commodity [Rank]....)

    (SUPPLIERID, COMMODOTYID) = (unique index)



    "Roger Tregelles" <roger.tregelles@respironics.com> wrote in message
    news:OQGwJ61eGHA.4932@TK2MSFTNGP03.phx.gbl...
    > Hi Folks,
    >
    > I've created a database to keep track of all of our suppliers here
    > (Approved
    > Supplier Listing). Our purchasing group wants to add the ability to show
    > all
    > of the types of commodities that the supplier can provide (injection
    > molding, tooling, packaging, etc.) Each supplier could have several
    > commodities to choose from a listing of about 20 choices.
    >
    > I had thought about adding 20 true/false checkboxes to the main table and
    > then allow the user to select which commodities apply. The problem with
    > that
    > is I need to also have a Parameter Query (tied to a Report) where you
    > could
    > type in the name of a commodity and this would search all of the suppliers
    > for this value. I'm not sure you can create a Parameter Query to search
    > all
    > 20 fields at once.
    >
    > I then thought of creating another table which could store all of the
    > commodities a supplier provides using the Parent/Child relationship. Only
    > problem here is I would need to create a sub-form off my main form to
    > select
    > the commodities and this might look cluttered.
    >
    > I've read about lookup tables which can store information from other
    > tables
    > and thought this might be a solution. Never having worked with these, I'm
    > not sure how I would set it up exactly. Maybe I'm just over-thinking this.
    > I'm just looking for a recommendation on how to store this information and
    > how to retrieve it easily in simple parameter query by a single keyword
    > (commodity type). Thanks in advance for any and all help and direction
    > provided.
    >
    >
    > Roger Tregelles
    > Quality Engineer
    >
    >
     
  5. Sprinks

    Sprinks
    Expand Collapse
    Guest

    Roger,

    The best way to model this natural one-to-many relationship is with a second
    table & a continuous subform. If your main form is currently full, you could
    place a new Tab control to hold the subform.

    Lookup tables have many problems--MVP Dirk Goldgar considers them evil.
    Search the forum for a discussion. I suggest a combo box which gets its
    values from a second table where the commodities are listed.

    Commodities (Basis of Combo Box)
    ---------------
    CommodityID AutoNumber (Primary Key)
    Commodity Text

    SupplierCommodities (Basis of Subform)
    --------------------------
    SupplierCommodityID AutoNumber (Primary Key)
    SupplierID Long Integer (Foreign Key to Suppliers)
    CommodityID Long Integer (Foreign Key to Commodities)

    Combo Box Properties
    --------------------------
    RowSource SELECT Commodities.CommodityID, Commodities.Commodity
    FROM Commodities ORDER BY Commodities.Commodity;
    ControlSource CommodityID
    BoundColumn 1
    ColumnWidths 0";x" (where x is wide enough to display the longest commodity
    name

    By setting the Bound Column to 1, you will *store* the foreign key. By
    setting the first ColumnWidth to 0", the drop-down list will not display the
    key, and the commodity name will display after selection.

    If you place the combo box with the wizard enabled (View, Toolbox, toggle on
    the button with the wand and stars) & choose Hide Key Field (recommended),
    Access will automatically set these properties.

    Hope that helps.
    Sprinks

    "Roger Tregelles" wrote:

    > Hi Folks,
    >
    > I've created a database to keep track of all of our suppliers here (Approved
    > Supplier Listing). Our purchasing group wants to add the ability to show all
    > of the types of commodities that the supplier can provide (injection
    > molding, tooling, packaging, etc.) Each supplier could have several
    > commodities to choose from a listing of about 20 choices.
    >
    > I had thought about adding 20 true/false checkboxes to the main table and
    > then allow the user to select which commodities apply. The problem with that
    > is I need to also have a Parameter Query (tied to a Report) where you could
    > type in the name of a commodity and this would search all of the suppliers
    > for this value. I'm not sure you can create a Parameter Query to search all
    > 20 fields at once.
    >
    > I then thought of creating another table which could store all of the
    > commodities a supplier provides using the Parent/Child relationship. Only
    > problem here is I would need to create a sub-form off my main form to select
    > the commodities and this might look cluttered.
    >
    > I've read about lookup tables which can store information from other tables
    > and thought this might be a solution. Never having worked with these, I'm
    > not sure how I would set it up exactly. Maybe I'm just over-thinking this.
    > I'm just looking for a recommendation on how to store this information and
    > how to retrieve it easily in simple parameter query by a single keyword
    > (commodity type). Thanks in advance for any and all help and direction
    > provided.
    >
    >
    > Roger Tregelles
    > Quality Engineer
    >
    >
    >
     
  6. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    *not* only Dirk

    Pieter

    "Sprinks" <Sprinks@discussions.microsoft.com> wrote in message
    news:9BFC4CEC-1257-4415-99B1-C394D2E819B4@microsoft.com...
    > Roger,
    >
    > The best way to model this natural one-to-many relationship is with a
    > second
    > table & a continuous subform. If your main form is currently full, you
    > could
    > place a new Tab control to hold the subform.
    >
    > Lookup tables have many problems--MVP Dirk Goldgar considers them evil.
    > Search the forum for a discussion. I suggest a combo box which gets its
    > values from a second table where the commodities are listed.
    >
    > Commodities (Basis of Combo Box)
    > ---------------
    > CommodityID AutoNumber (Primary Key)
    > Commodity Text
    >
    > SupplierCommodities (Basis of Subform)
    > --------------------------
    > SupplierCommodityID AutoNumber (Primary Key)
    > SupplierID Long Integer (Foreign Key to Suppliers)
    > CommodityID Long Integer (Foreign Key to Commodities)
    >
    > Combo Box Properties
    > --------------------------
    > RowSource SELECT Commodities.CommodityID, Commodities.Commodity
    > FROM Commodities ORDER BY Commodities.Commodity;
    > ControlSource CommodityID
    > BoundColumn 1
    > ColumnWidths 0";x" (where x is wide enough to display the longest
    > commodity
    > name
    >
    > By setting the Bound Column to 1, you will *store* the foreign key. By
    > setting the first ColumnWidth to 0", the drop-down list will not display
    > the
    > key, and the commodity name will display after selection.
    >
    > If you place the combo box with the wizard enabled (View, Toolbox, toggle
    > on
    > the button with the wand and stars) & choose Hide Key Field (recommended),
    > Access will automatically set these properties.
    >
    > Hope that helps.
    > Sprinks
    >
    > "Roger Tregelles" wrote:
    >
    >> Hi Folks,
    >>
    >> I've created a database to keep track of all of our suppliers here
    >> (Approved
    >> Supplier Listing). Our purchasing group wants to add the ability to show
    >> all
    >> of the types of commodities that the supplier can provide (injection
    >> molding, tooling, packaging, etc.) Each supplier could have several
    >> commodities to choose from a listing of about 20 choices.
    >>
    >> I had thought about adding 20 true/false checkboxes to the main table and
    >> then allow the user to select which commodities apply. The problem with
    >> that
    >> is I need to also have a Parameter Query (tied to a Report) where you
    >> could
    >> type in the name of a commodity and this would search all of the
    >> suppliers
    >> for this value. I'm not sure you can create a Parameter Query to search
    >> all
    >> 20 fields at once.
    >>
    >> I then thought of creating another table which could store all of the
    >> commodities a supplier provides using the Parent/Child relationship. Only
    >> problem here is I would need to create a sub-form off my main form to
    >> select
    >> the commodities and this might look cluttered.
    >>
    >> I've read about lookup tables which can store information from other
    >> tables
    >> and thought this might be a solution. Never having worked with these, I'm
    >> not sure how I would set it up exactly. Maybe I'm just over-thinking
    >> this.
    >> I'm just looking for a recommendation on how to store this information
    >> and
    >> how to retrieve it easily in simple parameter query by a single keyword
    >> (commodity type). Thanks in advance for any and all help and direction
    >> provided.
    >>
    >>
    >> Roger Tregelles
    >> Quality Engineer
    >>
    >>
    >>
     
  7. Sprinks

    Sprinks
    Expand Collapse
    Guest

    Pieter,

    I realize that all of us who've had any experience with this mis-feature
    cast it an evil eye. But since Dirk is so evangelical on this topic (and
    saved me from hours of grief by convincing me to avoid its use), I wanted to
    give him his 'props'.

    Sprinks

    "Pieter Wijnen" wrote:

    > *not* only Dirk
    >
    > Pieter
    >
    > "Sprinks" <Sprinks@discussions.microsoft.com> wrote in message
    > news:9BFC4CEC-1257-4415-99B1-C394D2E819B4@microsoft.com...
    > > Roger,
    > >
    > > The best way to model this natural one-to-many relationship is with a
    > > second
    > > table & a continuous subform. If your main form is currently full, you
    > > could
    > > place a new Tab control to hold the subform.
    > >
    > > Lookup tables have many problems--MVP Dirk Goldgar considers them evil.
    > > Search the forum for a discussion. I suggest a combo box which gets its
    > > values from a second table where the commodities are listed.
    > >
    > > Commodities (Basis of Combo Box)
    > > ---------------
    > > CommodityID AutoNumber (Primary Key)
    > > Commodity Text
    > >
    > > SupplierCommodities (Basis of Subform)
    > > --------------------------
    > > SupplierCommodityID AutoNumber (Primary Key)
    > > SupplierID Long Integer (Foreign Key to Suppliers)
    > > CommodityID Long Integer (Foreign Key to Commodities)
    > >
    > > Combo Box Properties
    > > --------------------------
    > > RowSource SELECT Commodities.CommodityID, Commodities.Commodity
    > > FROM Commodities ORDER BY Commodities.Commodity;
    > > ControlSource CommodityID
    > > BoundColumn 1
    > > ColumnWidths 0";x" (where x is wide enough to display the longest
    > > commodity
    > > name
    > >
    > > By setting the Bound Column to 1, you will *store* the foreign key. By
    > > setting the first ColumnWidth to 0", the drop-down list will not display
    > > the
    > > key, and the commodity name will display after selection.
    > >
    > > If you place the combo box with the wizard enabled (View, Toolbox, toggle
    > > on
    > > the button with the wand and stars) & choose Hide Key Field (recommended),
    > > Access will automatically set these properties.
    > >
    > > Hope that helps.
    > > Sprinks
    > >
    > > "Roger Tregelles" wrote:
    > >
    > >> Hi Folks,
    > >>
    > >> I've created a database to keep track of all of our suppliers here
    > >> (Approved
    > >> Supplier Listing). Our purchasing group wants to add the ability to show
    > >> all
    > >> of the types of commodities that the supplier can provide (injection
    > >> molding, tooling, packaging, etc.) Each supplier could have several
    > >> commodities to choose from a listing of about 20 choices.
    > >>
    > >> I had thought about adding 20 true/false checkboxes to the main table and
    > >> then allow the user to select which commodities apply. The problem with
    > >> that
    > >> is I need to also have a Parameter Query (tied to a Report) where you
    > >> could
    > >> type in the name of a commodity and this would search all of the
    > >> suppliers
    > >> for this value. I'm not sure you can create a Parameter Query to search
    > >> all
    > >> 20 fields at once.
    > >>
    > >> I then thought of creating another table which could store all of the
    > >> commodities a supplier provides using the Parent/Child relationship. Only
    > >> problem here is I would need to create a sub-form off my main form to
    > >> select
    > >> the commodities and this might look cluttered.
    > >>
    > >> I've read about lookup tables which can store information from other
    > >> tables
    > >> and thought this might be a solution. Never having worked with these, I'm
    > >> not sure how I would set it up exactly. Maybe I'm just over-thinking
    > >> this.
    > >> I'm just looking for a recommendation on how to store this information
    > >> and
    > >> how to retrieve it easily in simple parameter query by a single keyword
    > >> (commodity type). Thanks in advance for any and all help and direction
    > >> provided.
    > >>
    > >>
    > >> Roger Tregelles
    > >> Quality Engineer
    > >>
    > >>
    > >>

    >
    >
    >
     
  8. Roger Tregelles

    Roger Tregelles
    Expand Collapse
    Guest

    A big thanks to Karl, Pieter and Sprinks for the great replies. I'm going to
    create another table to contain the commodity information using a subform on
    the main form to enter the information. I'm leaving for the day now, but
    I'll try it out on Monday. If I get stuck I'll come back to seek everyone's
    guidance. Thanks again to all for the help.

    Roger Tregelles
    Quality Engineer
     
  9. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    xxx
    I wasn't about to put a grievance (and I know I'm by now very much on-and-of
    this dg )
    simply put - he nailed another 'un

    Pieter

    "Sprinks" <Sprinks@discussions.microsoft.com> wrote in message
    news:994108D8-AF5B-4F18-9E77-43223A1D01E5@microsoft.com...
    > Pieter,
    >
    > I realize that all of us who've had any experience with this mis-feature
    > cast it an evil eye. But since Dirk is so evangelical on this topic (and
    > saved me from hours of grief by convincing me to avoid its use), I wanted
    > to
    > give him his 'props'.
    >
    > Sprinks
    >
    > "Pieter Wijnen" wrote:
    >
    >> *not* only Dirk
    >>
    >> Pieter
    >>
    >> "Sprinks" <Sprinks@discussions.microsoft.com> wrote in message
    >> news:9BFC4CEC-1257-4415-99B1-C394D2E819B4@microsoft.com...
    >> > Roger,
    >> >
    >> > The best way to model this natural one-to-many relationship is with a
    >> > second
    >> > table & a continuous subform. If your main form is currently full, you
    >> > could
    >> > place a new Tab control to hold the subform.
    >> >
    >> > Lookup tables have many problems--MVP Dirk Goldgar considers them evil.
    >> > Search the forum for a discussion. I suggest a combo box which gets
    >> > its
    >> > values from a second table where the commodities are listed.
    >> >
    >> > Commodities (Basis of Combo Box)
    >> > ---------------
    >> > CommodityID AutoNumber (Primary Key)
    >> > Commodity Text
    >> >
    >> > SupplierCommodities (Basis of Subform)
    >> > --------------------------
    >> > SupplierCommodityID AutoNumber (Primary Key)
    >> > SupplierID Long Integer (Foreign Key to Suppliers)
    >> > CommodityID Long Integer (Foreign Key to Commodities)
    >> >
    >> > Combo Box Properties
    >> > --------------------------
    >> > RowSource SELECT Commodities.CommodityID, Commodities.Commodity
    >> > FROM Commodities ORDER BY Commodities.Commodity;
    >> > ControlSource CommodityID
    >> > BoundColumn 1
    >> > ColumnWidths 0";x" (where x is wide enough to display the longest
    >> > commodity
    >> > name
    >> >
    >> > By setting the Bound Column to 1, you will *store* the foreign key. By
    >> > setting the first ColumnWidth to 0", the drop-down list will not
    >> > display
    >> > the
    >> > key, and the commodity name will display after selection.
    >> >
    >> > If you place the combo box with the wizard enabled (View, Toolbox,
    >> > toggle
    >> > on
    >> > the button with the wand and stars) & choose Hide Key Field
    >> > (recommended),
    >> > Access will automatically set these properties.
    >> >
    >> > Hope that helps.
    >> > Sprinks
    >> >
    >> > "Roger Tregelles" wrote:
    >> >
    >> >> Hi Folks,
    >> >>
    >> >> I've created a database to keep track of all of our suppliers here
    >> >> (Approved
    >> >> Supplier Listing). Our purchasing group wants to add the ability to
    >> >> show
    >> >> all
    >> >> of the types of commodities that the supplier can provide (injection
    >> >> molding, tooling, packaging, etc.) Each supplier could have several
    >> >> commodities to choose from a listing of about 20 choices.
    >> >>
    >> >> I had thought about adding 20 true/false checkboxes to the main table
    >> >> and
    >> >> then allow the user to select which commodities apply. The problem
    >> >> with
    >> >> that
    >> >> is I need to also have a Parameter Query (tied to a Report) where you
    >> >> could
    >> >> type in the name of a commodity and this would search all of the
    >> >> suppliers
    >> >> for this value. I'm not sure you can create a Parameter Query to
    >> >> search
    >> >> all
    >> >> 20 fields at once.
    >> >>
    >> >> I then thought of creating another table which could store all of the
    >> >> commodities a supplier provides using the Parent/Child relationship.
    >> >> Only
    >> >> problem here is I would need to create a sub-form off my main form to
    >> >> select
    >> >> the commodities and this might look cluttered.
    >> >>
    >> >> I've read about lookup tables which can store information from other
    >> >> tables
    >> >> and thought this might be a solution. Never having worked with these,
    >> >> I'm
    >> >> not sure how I would set it up exactly. Maybe I'm just over-thinking
    >> >> this.
    >> >> I'm just looking for a recommendation on how to store this information
    >> >> and
    >> >> how to retrieve it easily in simple parameter query by a single
    >> >> keyword
    >> >> (commodity type). Thanks in advance for any and all help and direction
    >> >> provided.
    >> >>
    >> >>
    >> >> Roger Tregelles
    >> >> Quality Engineer
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
     
  10. Roger Tregelles

    Roger Tregelles
    Expand Collapse
    Guest

    I have taken the suggestions from everyone and created three tables.
    These are shown below with the relationships (hard to do with text) :)

    The relationship between the tblSupplierCommodities to tblSupplierData
    is a Many to One type. The relationship between the tblSupplierCommodities
    to tblCommodities is also Many to One type

    tblSupplierData
    SupplierID ----------------|
    (many others) |
    |
    tblSupplierCommodities |
    SupplierCommodityID |
    SupplierID----------------|
    CommodityID--------|
    |
    tblCommodities |
    CommodityID--------|
    CommodityName

    My problem is that when I try adding a new commodity to the Supplier table
    (not using forms yet) is that the Commodity only shows up by its ID not the
    Name which is what I really need. How can I get the commodity name to show
    up
    while hiding the ID. I'm assuming I would do this in the
    tblSupplierCommodities
    table by the way I call out the Lookup parameters for this field.
    Any help or direction here would be greatly appreciated.

    Roger Tregelles
    Quality Engineer
     
  11. Rick Wannall

    Rick Wannall
    Expand Collapse
    Guest

    Either make a form with a subform or (if you just can't go on to forms) put
    your SupplierCommodities table in design view and define the lookups to use
    on the two ID columns. Then, even in datasheet view of the table you can
    use pulldown lists.

    If making forms for this is not merited, it's possible that you should use
    Excel, where you automatically get dropdown lists populated from whatever
    data is in the column.

    If you're making an application, then move on to forms. Make a form based
    on the SupplierCommodity table. The main form is bound, and contains two
    combo boxes, one for Supplier, one for Commodities. Each one is bound to
    the ID column of the data it represents, and its dropdown is populated by
    SQL that shows the possible entries from the respective tables.

    On this form, when you move to a new row, you simply select the value you
    want for each column and save the record.

    There's more you can, and surely will do. For instance, you will probably
    have one unbound main form for Suppliers, with a combo box for Suppliers,
    and a subform in which the SupplierID column is hidden, since you're going
    to select a Supplier in the combobox in the main form, and only display
    records for that supplier. Then you'll surely do the same with a main form
    for Commodities, omitting the CommodityID column this time, etc.
     
  12. Roger Tregelles

    Roger Tregelles
    Expand Collapse
    Guest

    Rick,

    Thanks for the great suggestions. I fixed the problem using the Lookup
    Wizard in the backend table to point to the desciption field and show the
    descrition while indexing the ID. I then created a sub-form with the
    information now being displayed correctly. Thanks again for the help.

    Roger Tregelles
    Quality Engineer
     

Share This Page