calculating the total of several queries

[Vicky]

Hello

I have a table stock, a table users, a table handsets, a table accessoires
and a table 'vaste' accessoires
(excuse any dutch words like 'vaste' they're just names)

The relationships are like this
Stock table has an ID linked to the ID of the table Handsets
The table users is linked to accessoires and vasteAccessoires.
The ID of accessoires links to several fields in users and the same goes for
vasteAccessoires.

Handsets, Accessoires and vasteAccoires are tables with product info and
have the same type of fields:
ID, Name and Price.

Both the table users and the table stock have a field called Kostenplaats

What I have now is 1 query that has 3 subqueries.
The query is called Totalcosts
And what it does it show per Kostenplaats what kind of costs it has in
Handsets, Accessoires en vasteAccessoires.
So the result would look like this:

Kostenplaats - TotalHandsets - TotalAccessoires - TotalVasteAccessoires
With values under it.

Now I want to have a total of those values, grouped by kostenplaats.
I need to show these values in a report.
I did manage to make a field in my report with the following source:
=[totalHandset] + [totalAccessoires] + [totalVasteAccessoires]
This works just fine, until one or two of those values are missing.

If for example only [totalHandset] and [totalAccessoires] have values, then
it won't display my Total value anymore
Does anyone know a solution for this problem??

 

[KARL DEWEY]

Try this ---
=NZ([totalHandset],0) + NZ([totalAccessoires],0) +
NZ([totalVasteAccessoires],0)

 

[S.Clark]

Not having all of the fact, here's my standard answer for this kind of
situation.

1. Break down the queries into one or more if it helps
2. Create new queries based on existing queries to perform totals. (i.e.
have a detail query that the summary query uses as a basis.)
3. When reporting, consider writing data to a table dedicated to the output
of the report. Write the data to the table using append queries, then run
the query after the data is populated.
4. When totalling, use Nz() around values where a null may occur.
Nz([fieldname]).

 

[Vicky]

If I use that I get a syntax error...
Oh wait...it should be
=NZ([totalHandset]) + NZ([totalAccessoires]) + NZ([totalVasteAccessoires])
Then it works!!
Thanks a lot!!

Try this ---
=NZ([totalHandset],0) + NZ([totalAccessoires],0) +
NZ([totalVasteAccessoires],0)

--
KARL DEWEY
Build a little - Test a little

Hello

I have a table stock, a table users, a table handsets, a table accessoires
and a table 'vaste' accessoires
(excuse any dutch words like 'vaste' they're just names)

The relationships are like this
Stock table has an ID linked to the ID of the table Handsets
The table users is linked to accessoires and vasteAccessoires.
The ID of accessoires links to several fields in users and the same goes for
vasteAccessoires.

Handsets, Accessoires and vasteAccoires are tables with product info and
have the same type of fields:
ID, Name and Price.

Both the table users and the table stock have a field called Kostenplaats

What I have now is 1 query that has 3 subqueries.
The query is called Totalcosts
And what it does it show per Kostenplaats what kind of costs it has in
Handsets, Accessoires en vasteAccessoires.
So the result would look like this:

Kostenplaats - TotalHandsets - TotalAccessoires - TotalVasteAccessoires
With values under it.

Now I want to have a total of those values, grouped by kostenplaats.
I need to show these values in a report.
I did manage to make a field in my report with the following source:
=[totalHandset] + [totalAccessoires] + [totalVasteAccessoires]
This works just fine, until one or two of those values are missing.

If for example only [totalHandset] and [totalAccessoires] have values, then
it won't display my Total value anymore
Does anyone know a solution for this problem??