Creating a query/form

[Cow Girl]

I have 4 tables, one for each supplier. They all contain the same origin
(rows) and destinations (columns), and the "middle" is the cost to go from
the origin to the destination. I would like to create a query or form where
the user selects the appropriate origin & destination and the result shows 4
dollar amounts (one for each supplier). Any ideas? Or, do I need to do this
in something other than Access?

 

[Rick B]

If each table contains the same type of data, but refers to a different
supplier, then your table structure is flawed. A table name or field name
should not include "data". For example a field name of "TotalJun" TotalJul"
or "John" and "Sue" would be a bad idea. Tables such as "EastCoastDate"
and "WestCoastData" would be bad ideas (assuming the same type of data was
stored in each table).

All your records should be in ONE table. Add a new field to that table
called "Supplier" and then select the supplier for each record.

Once you "normalize" your table structure, you will be able to easily build
a report pulling up one or more products and showing the price that each
supplier charges for the product(s).

 

[Cow Girl]

Rick,

I appreciate your assistance. But, I seem to be having a mental block today,
and am not getting it at the moment. The original data came from Excel. We
created a spreadsheet with the set-up I described. We sent it out to the
suppliers and asked them to fill it in. Now, I'm trying to get it
consolidated so that we can easily determine the best price each carrier
offers. There are 14 origin points and 169 desintation points. How can I
create the one table without re-keying all of their info? Or do we just have
to manually look up their rates? Thanks so much!!

 

[Rick B]

Not sure what you mean by "14 origin points" and "169 destination points".

First, make a backup copy of your database.

If you have 14 separate tables, you could add the new field "Supplier" and
use an update query to plug in the supplier for all the records in TableA.
Then build a query to plug in the supplier name for all the records in
TableB, etc.

Once all the tables include the supplier name, just run an append query to
take all the records from TableA and add them to your new master table.
Then run an append query to take all the records from TableB and add them to
your new master table. Etc.

Once all your data is in one table, delete all the old tables.

You will then have one new master table with each product listed.


You may want to take this a step further and make one MasterProduct Table
that contains your partnumber, description, sell price, weight, etc. Then
create a second table to store the Part Number, SupplierCode, and Cost. In
this scenario you'd have one record for every part in the MasterProduct
table and one or more record for each part in your Pricing table. This
seems a bit more "normalized" but it all depends on what you are doing with
the data, how much it will change over the years, etc.

 

[Cow Girl]

Rick - OK. I think I got it now. Thanks so much!!