• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

Access Query - Compare Table


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
Rahul Sehrawat
Rahul Sehrawat
1 Solution
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Tackle projects and never again get stuck behind a technical roadblock.
Join Now