Query to Compare Multiple Tables (1 Viewer)

[tmyers]

How can you build a query to compare fields from several tables? In my particular case, I would like to compare prices from 3-5 tables and group them by product. So it would show product A has a price from vendor a, b and c then show product B etc etc.

I think I can only compare two tables at once, then I would have to make a couple other queries and do a union if I understand what I have read correctly. Can anyone offer some insight on this one?

 

[theDBguy]

Hi. You should be able to compare multiple tables, but it all depends on their relationships. Can you show us your table structure?

 

[tmyers]

O boy. I am about to be scolded again for my structure .

My vendor tables. They are all just linked together via JobID, in simple 1 to 1 relationships. I am trying to comparing each tables [Price] field based upon the [Type] field.

For a given job, each one should have lets say a "Type A" and a price for it. I want my query to show for "Type A", vendors 1, 2 and 3 have prices x, y, z. For any given job, there can be only 1 vendor, so a comparison isn't needed, or all 7 could be used (the "Other" table is an odd ball, kind of just a general catch all when something isn't supplied by our main 6 vendors).

I am trying to structure this to be used to cherry pick the lowest price for a given type. So having the query just return the lowest price for a given type would possibly also suffice. I know the boss would prefer to see all prices side by side however.

 

[MajP]

This would be so much easier if you combine all of these tables into one table. Just add a field to identify Vendor. This can all be done without having to re enter data. Just do an insert query into a new table which can also insert the value for the new vendor field.

 

[isladogs]

Your screenshot shows 7 tables with identical structures which is not how a database should be setup.
That should be obvious to you if for no other reason than you having to create an 'Other' table as a catch all
All that data should be in one table with an additional text field to identify the VendorType.

Recommend you merge the data into one table before you do anything else.

EDIT MajP answered whilst I was typing!

 

[tmyers]

Knew I was going to get yelled at for it . Will work on restructuring them. Might make me able to figure out the query after that.

 

[tmyers]

Ok, I restructured that mess and combined all of them into one table. Now how would I design the query to compare the prices?

Above is the new combined table. Below is a snip of its contents.

So as an example, Type H1 would have multiple manufacturers (from different vendors) and I would want to compare their price. So Vendor 1 shown has a price of $1,840 but say vendors 2 and 3 have a price of $2,150 and $1,750 respectively for the same type H1. How could I have a query that will show Type H1, prices 1, 2 and 3 then possibly have the last field show the lowest value?

 

[MajP]

Would sorting by Type and Price give you what you want? That would show you all prices for each type. Then you can build a separate query to pull lowest price by type.

SQL - Find the name and price of the cheapest item(s)

SQL Exercise, Practice and Solution: From the following table, write a SQL query to find the cheapest item(s). Return pro_name and, pro_price.

www.w3resource.com

 

[tmyers]

I don't know. This is probably one of the slightly more complex queries I have tried. I will try that and see what happens.

 

[Pat Hartman]

Now that all the data is in a single table, you can create a crosstab query. After you use the wizard to build the query, open it in design view and add criteria that refreences a form field so you can produce a comparison for just one product if you don't want to see all of them.

This query is rational as long as you have only a few vendors. If you had hundreds of vendors and thousands of products, the query would fail because it would produce too many columns so you would need to select a specific product or perhaps class of product so that the number of vendors would be limited to a dozen or so. It really doesn't matter how many rows the query produces. With the crosstab, you only worry about the number of columns you end up with.

 

[tmyers]

Total columns should never exceed 7. The 6 normal vendors, then the 1 catch all "other". It took a large chunk of the day, but it have it working minus an issue I am trying to sort out.

The problem is on vary large jobs, there could be multiple instances of a single type. Say Type A consisting of 5 parts, which each have their own price. The cross tab only shows the lowest for the given type, so even though a complete unit may be over a thousand dollars, it only shows the $12 trim.

I can't think of a work around on that one just yet.

 

[Pat Hartman]

Can't help you with out the database or a lot more information.

 

[tmyers]

An example would be like below.

This is a vendor quote for a rather large job. Several types consist of multiple parts that each have their own cost. Per the boss, he wants to see a total for a type. So in the above example for this vendor, I would need to show that type AA has a TOTAL cost of $409. There are instances where the vendor will do it for us, so it becomes a non issue but this happens often enough that I have to account for it.

The current query I have doing this is:

The above works until I start having multiple parts within the same type. Would I need to make another query to total everything together, then base the above crosstab off of that?

 

[Pat Hartman]

The crosstab can only do one calculation. You have it bringing in the min value and you want it to sum the data.

You might get closer if you build a query to do do the selection and the other calculation and then use that inside the cross tab.