Return all records?

A

Ankan

Hi,

I'm using a query to calculate the totalt value of some orders.

How to display ALL orders in the answer. Not only orders with a saldo. I
want the orders without saldo to get saldo = 0.

I want everything to be done in the same query.

TIA.


/Ankan
(remove x to reply)
 
M

mscertified

What is a saldo?
Normally if you want everything you just omit the WHERE clause.

-Dorian
 
J

John Vinson

Hi,

I'm using a query to calculate the totalt value of some orders.

How to display ALL orders in the answer. Not only orders with a saldo. I
want the orders without saldo to get saldo = 0.

I want everything to be done in the same query.
Click to expand...

Please post the SQL of your query. You may be able to use the NZ()
function to get Null values to be treated as zero, but if you don't
have criteria and are doing a simple totals query, this should not be
necessary. Clearly you're doing something ELSE in the query, but we
cannot tell what!

John W. Vinson[MVP]
 
A

Ankan

Excuse my bad english.

My question is simple:

I have two tables. The first containing the orders main (customer etc) info
and the second containing whats articles to be sold.

I want the result to display ALL orders in the first table. Even those which
don't contain any articles (those I want to return "0").

SQL:

SELECT dbo_v_arendeartikel.ArtikelNr, dbo_v_arendeartikel.ArtikelBenamning,
dbo_v_arendeartikel.nAntal, dbo_v_arendeartikel.CPris, [nAntal]*[cPris] AS
Summa, dbo_v_arende.Arende
FROM dbo_v_arendeartikel INNER JOIN dbo_v_arende ON
dbo_v_arendeartikel.Arende = dbo_v_arende.Arende
GROUP BY dbo_v_arendeartikel.ArtikelNr,
dbo_v_arendeartikel.ArtikelBenamning, dbo_v_arendeartikel.nAntal,
dbo_v_arendeartikel.CPris, dbo_v_arende.Arende
ORDER BY dbo_v_arende.Arende;


Thanks and regards.

/A
 
J

John Vinson

Excuse my bad english.

My question is simple:

I have two tables. The first containing the orders main (customer etc) info
and the second containing whats articles to be sold.

I want the result to display ALL orders in the first table. Even those which
don't contain any articles (those I want to return "0").

SQL:

SELECT dbo_v_arendeartikel.ArtikelNr, dbo_v_arendeartikel.ArtikelBenamning,
dbo_v_arendeartikel.nAntal, dbo_v_arendeartikel.CPris, [nAntal]*[cPris] AS
Summa, dbo_v_arende.Arende
FROM dbo_v_arendeartikel INNER JOIN dbo_v_arende ON
dbo_v_arendeartikel.Arende = dbo_v_arende.Arende
GROUP BY dbo_v_arendeartikel.ArtikelNr,
dbo_v_arendeartikel.ArtikelBenamning, dbo_v_arendeartikel.nAntal,
dbo_v_arendeartikel.CPris, dbo_v_arende.Arende
ORDER BY dbo_v_arende.Arende;
Click to expand...

Change the INNER JOIN to LEFT JOIN. This will return all rows in
dbo_v_arendeartikel whether there are matching records in dbo_v_arende
or not.

John W. Vinson[MVP]
 
Top