Contact Database Relationships

[abowlofsoda]

I am a database and Access newbie. I am using Access 2007 to build a database of Contacts. These contacts are businesses that carry 10 products we are looking for. Some businesses carry 1 of them and some carry all 10 (and many of them somewhere in between).

I want to create queries that search for 1 of those 10 Products based on City or State, that way we can contact local business first to check on availability.

I have a Contacts table modified off the defaul Contacts template. It has a Business ID, Business name, Business Phone, state, city, and what Products they cary (each product is its own column ex. Product1, Product2,... thru 10)

I have a City, State table that I purchased and downloaded. It does not have any ID column. Tried to add a autonumbered ID field but it wouldn't let me as there is already data in the table. There are too many cities and states to number them manually.

I have a Product table that has Product ID for each of the 10 products. This table will expand over time as more Products are added.

I am trying to wrap my brain around how to search for a Product in a particular City, and show me the Business w/Phone that would carry said Product.

I been studying Relationships but I just don't get it.

Tables

Business ID
Business Name
Business Phone
Product1
Product2
Product3
Product4
Product5
Product6
Product7
Product8
Product9
Product10



(this one has no ID yet)
City
State
ZIP



ProductID
Product


Any direction will help. I been reading and reading and watching youtube videos but haven't been able to figure it out.

 

[CraigDolphin]

I have a Contacts table modified off the defaul Contacts template. It has a Business ID, Business name, Business Phone, state, city, and what Products they cary (each product is its own column ex. Product1, Product2,... thru 10)

Well, there's your problem

What you need is a table of businesses, a table of products, and a junction table that links products to businesses.

ie

Businesses
BusinessID (auto, pk)
BusinessName
BusinessPhone
BusinessFax
etc

Products
ProductID (auto, pk)
ProductName

BusinessProducts
BusinessProductID (auto, pk)
BusinessID
ProductID

This normalizes the table design. Table normalization is critical to making a successful relational database.

Thus, instead of adding a column to the business table for each product, you simply add a row to the BusinessProducts table for each product that a particular business carries. And the products table will contain 10 rows (one for each particular product). This approach has the advantage that you can add more types of products if required in the future without having to modify your forms/queries etc. It also allows you to build a query that combines information from the businesses and businessproducts tables, such as the business name/id, city, and the productID's. This lets you use filters or parameter queries to restrict the list to user-specified cities. It would even allow you to limit the query results to businesses with 2 or more 'required' products (although this is beyond your stated request).

You could even store the last known proce for the product at each store in another field in the busineesproducts table which might help you when deciding which business to call first.

 

[abowlofsoda]

Thanks for the reply!


Is the 'BusinessProducts' table something I am going to have to manually enter, after every time someone adds a new entry to the 'Business' table or is there some way to have that information update automatically into that table?

 

[CraigDolphin]

Usually, you'd use a form bound to the businesses table, with a subform bound to the businessproducts table.

The subform would be in continuous form view so you could see/add several rows at a time.
The subform would be linked to the main form using the businessID field as the master/child key. You'd have the businessid field in the subform (but it can be invisible). The user would select the productid using a combo control that is bound to the productid field, but displays the product name.

However, if the data has already been entered for each business then you can copy/paste it into the table provided that the information is organised correctly and that the id values referenced are already present in the other two tables.

 

[abowlofsoda]

Thanks again. I'll work on that!

 

[abowlofsoda]

Your post have got me far. I'm well on my way with subforms and combo boxes.

I do have a problem tho. After renaming the ID field in the Contacts table to BusinessID- I now have a broken macro that is used on a form I have to open another form of the Contact Details.

You can see exactly what it is by opening the Contacts template. I cannot figure out where in the macro to update this from ID to BusinessID.

I tried changing it back to plain 'ol ID and it works again but kinda need it to be BusinessID for the junction relationship.

Help!

 

[CraigDolphin]

To be honest I've never used any Macros in access so I'm not sure I can help you. I use vba and code my own using the vba editor. You could always delete the command button on the other form, and create a new one and setup a new macro for the new button using the new field names.

 

[abowlofsoda]

I figured the macro out finally after and day and a half of long hours of tinkering!

On my subform for ProductID.. I'm using the combo box on the ProductID column. When I select the Product from the combo and hit enter to get to the next row- I change the combo there to add another Product that biz carries and the previous combo box changes to reflect the one being edited.
And so on for every row that is added.

What am I overlooking?

 

[CraigDolphin]

Sounds like the rowsource for the subform is not correctly bound to the businessproducts table.

 

[abowlofsoda]

I am highlighting businnessproducts table, going to create, and selecting Multiple Items.
From there I am dragging and dropping the newly created (sub)form into a Contact Details form I have. This form is just a nicer layout of each record, to make it easier for the end user.

To jump back a step and forget about combos for a second...
I am just seeing ALL entries from the businnessproducts table. How do I set it so I am only seeing the information the Products for the current open record?

EDIT: Reading back on a prior post of yours, I sorted that question out. Now to figure out how to add the combo to do what I need.

 

[CraigDolphin]

With regard to the combo control.

Check some things for me.

For the form: Check the RecordSource property of the FORM. Make sure it is set to the table BusinessProducts.

For the combo control:
Check the Control Source property: make sure it is set to ProductID
Check the Row Source property: make sure it says something like

Select ProductID, ProductName From Products

Check that the column Count property is set to 2
Check that the Column widths property is set to 0;1" (the second number can be whatever, but the first should be 0)
Check that the Bound Column prperty is set to 1.

If all those are set correctly, then you'll need to post a copy of the database so I can figure out what's going on.