Best Way To Store and Retrieve Data?

[Roger Tregelles]

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

 

[KARL DEWEY]

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.

 

[Pieter Wijnen]

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)

 

[Sprinks]

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

 

[Pieter Wijnen]

*not* only Dirk

Pieter

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

 

[Sprinks]

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

 

[Roger Tregelles]

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

 

[Pieter Wijnen]

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

 

[Roger Tregelles]

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

 

[Rick Wannall]

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.

 

[Roger Tregelles]

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