Merging Different Tables

[#1]

Dear All,

Kindly give me code for below query. We are working as an intermediate between customer and supplier.

In our system, we have below data for each customer and each supplier..

Customer Table:

Customer ID     Customer Name     Customer Currency     Customer Country

Where Customer ID is unique.

Supplier Table:

Supplier ID     Supplier Name     Supplier Currency     Supplier Country

Supplier ID is unique.

Based on prices available from different suppliers on respective brands, we are sending quotation to our customers. These data are saved in below table. Here Our Part NO is unique but can be associated with more than one brand. We can say one part number is having three or more different quality. Example shown below. But there will be only one Our Part NO for each Customer Part NO

Our Part NO    Brand    Quoted Price     Customer Part NO

   ABCXYZ            A                  10                         1234 567

   ABCXYZ            B                  20                         1234 567

   ABCXYZ            C                  30                         1234 567

Quotation Table:

Customer ID     Our Part NO     Customer Part NO     Brand     Quoted Price

Also, for each supplier, we have purchase cost table.

Supplier Price Table:

Supplier ID     Our Part NO     Brand     Supplier Part NO     Purchase Cost

Based on our quotations, customers are sending orders as below...

Customer Order Table:

Customer ID     Customer Sales Order NO     Customer Part NO     Brand     Qty

Finally we are sending orders to our supplier based on customers' orders. We have below data stored for our Purchases/Sales.

Purchase Order Table:

Purchase Order NO     Supplier ID     Supplier Part NO     Our Part NO     Brand     Qty     Customer ID     Customer Sales Order NO

I need code to get table with following columns.

Final Table:

Purchase Order NO

Supplier ID

Supplier Name

Supplier Currency

Supplier Country

Supplier Part NO

Our Part NO

Brand

Qty

Purchase Cost

Purchase Total

Customer ID

Customer Name

Customer Currency

Customer Country

Customer Part NO

Customer Sales Order NO

Quoted Price

Invoice Total

Where Purchase Total = Qty * Purchase Cost and Invoice Total = Qty * Quoted Price

Also, if possible, I want unique line number in Final Table.

Thanks in advance.

[#2]

Look into (Left/Right/etc) Join Load. Depending on if you're missing some data in fields, you might prefer one type of Join over another. Create a new table by loading from a resident table, and then joining the existing ones (2 at a time): if X is the new table, and you need to combine tables A, B, and C into X - join X + A -> XA, XA + B -> XAB, XAB + C -> XABC.

So you load the needed fields from A into X with "Load ...fields... Resident A;" Then, for example, "Left Join (X) Load ...fields... Resident B;", and so forth.

For the fields that are calculated:

Qty * [Purchase Cost] AS [Purchase Total],

Qty * [Quoted Price] AS [Invoice Total]

Hope this helps,

Siarhei K.