Combine 2 or more unlike tables - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Combine 2 or more unlike tables

John Miller

6 years ago

Richard hope you are doing well. I have a question is threads a way to combine 2 or more tables that do not have common fields into 1 new table? I have a table that has counties and state, another table you have a list of products/services you perform, another table that has clients. The new table will have the company name counties listed once and then all the products listed once per county. A report would look like this:
Company name

County  St.      Product1     Product2  Product3.......
A        WV       $10.       $15.        $20.
B        WV       $15.       $18.50      $17.

And so on there are 55 counties and 21 products and 2 states clients about 150. Product pricing based on client,county,state,product.
Right now I have created this table by hand, we continue to add products and clients and I would like to know if this can be done or is doing it by hand the best way. I can not get my thought light bulb to come home it's not burned out.LOL
Thank you in advance.

Richard Rost @Reply

6 years ago

Well, that's a unique question. I wouldn't combine them into one table like that. Maybe a series of form/subforms or cascading combo boxes where you pick the state, then the available counties show in the next box, then the available products show in a subform. Displaying the products horizontally like that is always troublesome because how do you know how many columns to make? Now you're talking Excel. Access is a database that likes records to be in rows. Yeah, you could use a PivotTable, but that's not a great solution.

	Richard Rost @Reply 6 years ago
You would want to make relationships based on the things that bring this data together. County would be related to State obviously. Product price is based on what? County? There's your lookup strategy. I'm actually preparing a lesson on customer-specific pricing which might help you. Stay tuned.

	John Miller OP @Reply 6 years ago
Richard thanks for your suggestion and insight I will continue to use my old way. I kind of figured there was no way of doing it without having relationships between all tables needed, but thought I'd ask. Thank you very much for what you do. I look forward to seeing the lesson on Customer-specific pricing.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View