Access Query - Compare Table

Hello,

I have attached my access file. In this package, we want to compare three quotation given to us by 3 suppliers stored in 3 different tables.

what I want is when the user click compare, it should open a report comparing the total cost for each item.

For example :
Form Name: Add_Quote_From_Supplier_Compare

Choose Request I.D as 3 from the drop down menu. This will populate all the controls in he tab control.

If you notice Item's name as same. Now what I want as soon as the user click compare, it should compare the total cost for all the items in a report. something like

Item Name       Supplier 1        Supplier 2          Supplier 3
--------------------------------------------------------------------
item1                 total_cost        total_cost         total_cost
item2                 total_cost        total_cost         total_cost
item3                 total_cost        total_cost         total_cost
item1                 total_cost        total_cost         total_cost
Compare.mdb
Rahul SehrawatAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tygrus2Commented:
I'm working on a report that uses the query below to summarise the quotes from each supplier for each PO order.

Please note: Your cuurent database contains calculated fields that are redundant eg. Total amount = qty * (unit price * (1 - discount)) so where does it come from and how is it calculated/derived from the other fields. Some relationships are not very clear so can you please describe what fields in each table link to fields in other tables eg. supplierID, request_autogenerate.
SELECT New_PO_Request.request_id, New_PO_Request.supplier1, New_PO_Request.supplier2, New_PO_Request.supplier3, New_PO_Request_Details.qty, Supplier1_Quote.Total_Amount, Supplier2_Quote.Total_Amount, Supplier3_Quote.Total_Amount, New_PO_Request_Details.request_autogenerate
FROM (((New_PO_Request INNER JOIN New_PO_Request_Details ON New_PO_Request.request_autogenerate = New_PO_Request_Details.request_autogenerate) LEFT JOIN Supplier1_Quote ON (New_PO_Request_Details.request_autogenerate = Supplier1_Quote.PO_Request_ID) AND (New_PO_Request_Details.item_id = Supplier1_Quote.item_id)) LEFT JOIN Supplier2_Quote ON (New_PO_Request_Details.request_autogenerate = Supplier2_Quote.PO_Request_ID) AND (New_PO_Request_Details.item_id = Supplier2_Quote.item_id)) LEFT JOIN Supplier3_Quote ON (New_PO_Request_Details.request_autogenerate = Supplier3_Quote.PO_Request_ID) AND (New_PO_Request_Details.item_id = Supplier3_Quote.item_id);

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.