Re: Retrive informations fro two lists

From: Douglas J. Steele (NOSPAM_djsteele_at_NOSPAM_canada.com)
Date: 02/27/05 

Date: Sun, 27 Feb 2005 14:11:06 -0500

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] 

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Nils Magne" <loevrak@online.no> wrote in message 
news:83A71A8D-EE8A-4E99-AEA1-CA9AA13D9F01@microsoft.com...
> 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?
> -- 
>
> Nils Magne Løvrak (Norway)

Relevant Pages

  • RE: Weird Problem with a Subreport
    ... Ok for the sorting i will try to explain it to you. ... query by writing the SQL I didnt include the Place holder field, ... Do you have a text box in the Goup Header? ... I have a header of shutdown in front of the whole list but the two lists ...
    (microsoft.public.access.reports)
  • RE: Weird Problem with a Subreport
    ... Now I need to make reports for the lists for all ... a save as for the reports and just change which query they are getting the ... and used sorting and grouping to sort by the placeholder and all is well!!!! ... Do you have a text box in the Goup Header? ...
    (microsoft.public.access.reports)
  • RE: Weird Problem with a Subreport
    ... Do you have a table that lists all the tests for the year by month? ... you could filter your query by the month identifier. ... and used sorting and grouping to sort by the placeholder and all is well!!!! ... Do you have a text box in the Goup Header? ...
    (microsoft.public.access.reports)
  • Re: Question on joins
    ... single-table FROM clauses and infixed notation in all my multi-table ... Both my first step (attempting to find the join conditions in the ugly ... comma-seperated query) and your show clearly ... and WHERE clause lists to take advantage of the Law of Proximity in my ...
    (microsoft.public.sqlserver.programming)
  • RE: Weird Problem with a Subreport
    ... Ok for the sorting i will try to explain it to you. ... query by writing the SQL I didnt include the Place holder field, ... Do you have a text box in the Goup Header? ... I have a header of shutdown in front of the whole list but the two lists ...
    (microsoft.public.access.reports)