product tables

L

ladyhawkrose

hello I want to create a table with multiple products from different
distributors. Should I create a seperate tables for the products from the
different distributors or can I create one table and add all the products
into the one table
thanks
 
G

Gwen H

It sounds like you're trying to figure out how to handle a one-to-many
relationship - each single distributor has multiple products. If so, create
two tables: (1) Distributors and (2) Products. In the Distributors table,
give each distributor a unique ID number. You can use an autonumber field to
do this. In the Products table, along with various fields to hold details
about the product include a field that holds the unique distributor ID from
the Distributors table. You can make this field a lookup field, so that when
you come to this field during data entry you will have a list of distributors
from which to choose. If you don't make the distributor ID field in the
Products table a lookup field, then you'll need to define a relationship
between the two tables based on the Distributor ID fields in each table.

Using this approach saves you from having to enter information about each
distributor more than once in a table that holds your product information.

GwenH
Master Certified MOS
 
L

ladyhawkrose

ok so far I have over 500,000 products from two distributors, Ive created a
distributors table with id. I should also include that id feild into my
products table.
Also I have many excel sheets to import what would be the easiest way to set
up the import so that I can use just one table
thanks
 
G

Gwen H

Do all the Excel sheets have the same columns as your table? If so, make sure
the Excel columns are in the same order as the columns in your table. Then
select the data and copy it. Switch over to your table, place your cursor in
the last row of the table, and from the Edit menu select "Paste Append". This
will paste all the data from the spreadsheet into the database table.

Or, you can copy and paste the Excel spreadsheet into your database, and
Access will turn it into a table. Then you can use an Append query to specify
which fields in the converted Excel spreadsheet need to be inserted into
which fields of the existing Access table.
 
V

Van T. Dinh

It is possible and may even be more appropriate to use a Many-to-Many
relationship between Distributors and Products. For example, you may want
to source a particular Product from different Distributors. OTOH, each
Distributor can supply a number of different Products.

ladyhawkrose will need to find out exactly what the requirements are and
decide on the relationship and the Table Structure.

Regarding "lookup" Fields, I would not recommend them for new Access users
as they hide the true working relationship and can only confuse new users.
See he Access Web article http://www.mvps.org/access/lookupfields.htm.
 
G

Gwen H

Gee, when I was a new Access user I didn't find lookup fields confusing ...
but many-to-many relationships made my head hurt. Because MS decided to give
us multiple ways of doing the same thing, we have the freedom to choose what
works best for us. And the wonderful thing about forums like this is that
several people can post how they would approach the problem, and the person
who needs help can choose what works best for them and the problem they're
trying to solve. But when we choose to criticize **suggestions** from other
people who are simply trying to help, nothing positive is accomplished.
 
V

Van T. Dinh

Sorry that you chose to think that my post criticised your suggestion while
it was trying to point out there is another possibility.

If you read my post carefully, it starts with:

"It is possible .... "

and the second paragraph says:

"ladyhawkrose will need to find out exactly what the requirements are and
decide on the relationship and the Table Structure."

Clearly, my post advises the O.P. to decide what's best for his / her
requirements, be it One-to-Many or Many-to-Many relationship. In effects,
what it says is that One-to-Many or Many-to-Many relationships are both
correct but are used in different circumstances.
 
Top