Field type

[Guest]

I am creating a database for suppliers and customers. I know there are two
ways to go about this.

One way is to create a table that has the supplier field and the customer
field. In this table the customer field is of type memo. Since the data is
stored in list form on word documents using this strategy would be time
efficient because i could simply cut and paste the data. However i know that
when it comes to editting this data on a form, it could prove to be more
difficult than the second method.

The second way is to create a linknig table with all of the customers in it.
Each record would then only have one customer associated with it, and
essentially be a many to many relationship. This method would be easier at
the end because adding or deleting a customer would simply involve adding or
deleting one record as opposed to editting the entire field. I have included
an example below to illustrate.

1)
Supplier Customer
Mike's Sears, JCPenny's, Bike Store

2)
Supplier Customer
Mike's Sears
Mike's JCPenny's
Mike's Bike Store

Thank you for any help you can provide.

 

[Jeff Boyce]

Since you are asking in a Microsoft Access newsgroup, I'll assume that you
are considering using a relational database. The first approach is more
like a spreadsheet, so I strongly recommend the second. If you are not
familiar with the notions of normalizing, dig into the topic a bit.

Your second approach reflects the many-to-many relationship it sounds like
you have. If I follow your description, you have:

Suppliers
Customers
and
ValidSupplierCustomer pairs.

That requires three tables in a well normalized design. The third table
holds the IDs from the first two for valid pairs. The third table makes it
easy to find, for example, all the Suppliers for Sears.

Regards

Jeff Boyce
Microsoft Office/Access MVP