SQL question on summary

[Pal]

Hi,

I have the following data to query. I do not how to get the output
(below) in a single query statement in Access '02. No problem in
using separately 3 queries. Can anyone help here?

Thanks in advance.

INPUT
-----
Table 1 (Customer)
Customer Category amount
1 A 20
1 B 30
1 C 35
2 A 40
2 B 45

Table 2 (Supplier)
Supplier Category amount
1 A 100
1 B 200
2 A 100
2 B 200

I want the following output:
OUTPUT
------

Category TotalDemandedCustomer TotalProvidedSupplier
A 60 200
B 75 400
C 35 0
Pal

[LoopyNZ]

This SQL does the basics of what you ask:

SELECT [table 1].category, Sum(Nz([Table 1].[amount])) AS
customer_demanded, Sum(Nz([Table 2].[amount])) AS supplier_demanded
FROM [table 1] INNER JOIN [table 2] ON [table 1].category = [table
2].category
GROUP BY [table 1].category;

However this doesn't achieve exactly what you want, because it only
gives a result for categories that appear in both tables. To get all
results from one table (e.g. [Table 1]) and matching results from Table
2, use this instead:

SELECT [table 1].category, Sum(Nz([Table 1].[amount])) AS
customer_demanded, Sum(Nz([Table 2].[amount])) AS supplier_demanded
FROM [table 1] LEFT JOIN [table 2] ON [table 1].category = [table
2].category
GROUP BY [table 1].category;

Ideally, you actually have a separate table that contains ALL
categories, then you could make sure all categories had an amount
against them, eg:

SELECT Categories.category, Sum(Nz([Table 1].[amount])) AS
customer_demanded, Sum(Nz([Table 2].[amount])) AS supplier_demanded
FROM (Categories LEFT JOIN [Table 1] ON Categories.category = [Table
1].category) LEFT JOIN [Table 2] ON Categories.category = [Table
2].category
GROUP BY Categories.category;

Good luck!

------------
LoopyNZ
------------

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Pal]

Hi,

Thanks. But I just tried your first suggest and it does not work.
The totals are double counted as before.

Pal


LoopyNZ <Lo*****@NOguruSPPAM.net.nz> wrote in message news:<41**********************@news.newsgroups.ws> ...

This SQL does the basics of what you ask:

SELECT [table 1].category, Sum(Nz([Table 1].[amount])) AS
customer_demanded, Sum(Nz([Table 2].[amount])) AS supplier_demanded
FROM [table 1] INNER JOIN [table 2] ON [table 1].category = [table
2].category
GROUP BY [table 1].category;

However this doesn't achieve exactly what you want, because it only
gives a result for categories that appear in both tables. To get all
results from one table (e.g. [Table 1]) and matching results from Table
2, use this instead:

SELECT [table 1].category, Sum(Nz([Table 1].[amount])) AS
customer_demanded, Sum(Nz([Table 2].[amount])) AS supplier_demanded
FROM [table 1] LEFT JOIN [table 2] ON [table 1].category = [table
2].category
GROUP BY [table 1].category;

Ideally, you actually have a separate table that contains ALL
categories, then you could make sure all categories had an amount
against them, eg:

SELECT Categories.category, Sum(Nz([Table 1].[amount])) AS
customer_demanded, Sum(Nz([Table 2].[amount])) AS supplier_demanded
FROM (Categories LEFT JOIN [Table 1] ON Categories.category = [Table
1].category) LEFT JOIN [Table 2] ON Categories.category = [Table
2].category
GROUP BY Categories.category;

Good luck!

------------
LoopyNZ
------------

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[LoopyNZ]

Pal,

I'm not sure what the "double-counting as before" is that you mention -
you didn't refer to it in your original post.

Can you post the exact SQL you used and the results it returned?

------------
LoopyNZ
------------

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!