Retrive informations fro two lists

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hey,
I have a problem, namely to retrive all informations form two lists, the
problem is that when one or more itms ar missing in bout lists:
List 1
item# name qty
1 A 2
3 C
4 D 1

List 2
1 A 5
2 B
3 C
4 D 7
6 F 5

Result - which I am not able to retain
item# name qty
1 A 7
2 B
3 C
4 D 8
6 F 5

Anyone?
 
Are you saying that you want the sum?

Create a query that unions the two lists together:

SELECT [item#], [name], Nz([qty], 0) As quantity
FROM [List 1]
UNION ALL
SELECT [item#], [name], Nz([qty], 0) As quantity
FROM [List 2]

Call that qryUnion

Create a second query that sums that up:

SELECT [item#], [name], Sum([quantity]) As qty
FROM qryUnion
GROUP BY [item#], [name]

In fact, if you're using Access 2000 or newer, you can do this in a single
query:

SELECT [item#], [name], SUM(quantity) AS qty
FROM
(SELECT [item#], [name], nz(qty, 0) AS quantity
FROM [List 1]
UNION
SELECT [item#], [name], nz(qty, 0) AS quantity
FROM [List 2])
GROUP BY [item#], [name]
 
Yes, the thing is that we are buying stuff from two different suppliers which
uses the same unique numer on their products - i need to see the total
purchase for those two suppliers - bouth items which are arne bought from one
of them and from the other one as well as items bought from bouth as totals.

I'll try out your suggestion, and by the way i'm using Access 2003.

Thanks again for replaying

Douglas J. Steele skrev:
Are you saying that you want the sum?

Create a query that unions the two lists together:

SELECT [item#], [name], Nz([qty], 0) As quantity
FROM [List 1]
UNION ALL
SELECT [item#], [name], Nz([qty], 0) As quantity
FROM [List 2]

Call that qryUnion

Create a second query that sums that up:

SELECT [item#], [name], Sum([quantity]) As qty
FROM qryUnion
GROUP BY [item#], [name]

In fact, if you're using Access 2000 or newer, you can do this in a single
query:

SELECT [item#], [name], SUM(quantity) AS qty
FROM
(SELECT [item#], [name], nz(qty, 0) AS quantity
FROM [List 1]
UNION
SELECT [item#], [name], nz(qty, 0) AS quantity
FROM [List 2])
GROUP BY [item#], [name]
Click to expand...
 
Back
Top