Data from two tables (1 Viewer)

herbertioz · Oct 20, 2014

I have two tables, table 1 with these field:
- ID (pk,auto)
- Orders
- Market
- ProductLocalName

Table 2:
- ID(pk,auto)
- Product
- GlobalMarket
- MarketArea
- PlannedOrders

My goal is to SUM the Orders in table 1 (local orders) and compare it with the field PlannedOrders in table 2 and get the difference and also show all the orders in Table 2 as well, not just the localorders with assigned orders. Can someone help me? I hope this is clear enough for you guys.

Its easy to sum the local orders, but not the rest.

plog · Oct 21, 2014

CAn you provide some sample data? Include 2 sets:

A: starting data from your tables
B: expected output based on A

Be sure to include field and table names

herbertioz · Oct 21, 2014

Please see the attached excelsheet.

CazB · Oct 21, 2014

erm.... what is the match? I think your sample data needs looking at!

- Your ProductLocalName in table one is ProductLocalName1, ProductLocalName2
- Your Product in Table2 is Supa1, Supa2, Supa3... etc

and in Table 2, you've got more than one Supa2 against Europe as the market - how is it supposed to know which figure to use as the starting quantity?

Please correct your sample data so that it's clear what should match to what and try again...

but if it should work like I think you mean, then something like this might do the job

Code:

SELECT Table2.ID, Table2.PRODUCT, Table2.GLOBALMARKET, Table2.MARKETAREA, Table2.PLANNEDORDERS, Sum(Table1.Orders) AS LocalOrders, [PlannedOrders]-nz(Sum([orders]),0) AS Balance
FROM Table2 LEFT JOIN Table1 ON (Table2.GLOBALMARKET = Table1.Market) AND (Table2.PRODUCT = Table1.ProductLocalName)
GROUP BY Table2.ID, Table2.PRODUCT, Table2.GLOBALMARKET, Table2.MARKETAREA, Table2.PLANNEDORDERS;

Data from two tables (1 Viewer)

herbertioz

Registered User.

plog

Banishment Pending

herbertioz

Registered User.

Attachments

CazB

Registered User.

Similar threads

Users who are viewing this thread