Nz problem in query totals

[jimbrands]

Hi,

I have a problem with the Nz (nulltozero) function in a query.

The Nz function works well as long as there is a record , even if the field is empty, so far so good, but when there is no entry at all at one of the tables, the the Nz does not function.

Example

Table1 > field A contains no records
Table2 > field B contains one record the number: 10

Query1 > contains : field A and field B and Expression Exp1:Nz([field A],0) + Nz([field B],0)

This query1 gives nu results , but i would like to see:

0 + 10 = 10

does anybody knows the solution?

Thanks

Jim

 

[pbaldy]

I would say it's more accurate to say that there's nothing for the Nz() to function on that to say it doesn't function. Try changing the join between the tables. If you right-click on it in design view, select the appropriate "return all records from...".

 

[jimbrands]

That's indeed correct, it's possible that there is no record in a particular table, so the Nz can't function. I've already tryed to change the joins but this did not solve the problem. In my application i have 3 different tables, so it's possible that one or even two tables have no records

 

[recyan]

Do not know if this is the problem that you are facing ?
Just check if below gives some guidelines :

The tables :
Table1
ID
Field1

Table2
ID
Field2

Table3
ID
Field3

The sub-query :
qryAllIDs

SELECT [ID] FROM Table1 
UNION  
SELECT [ID] FROM Table2 
UNION 
SELECT [ID] FROM Table3

The Final query :

SELECT 
	qryAllIDs.ID, 
	Nz([Field1],0) AS Field1_1, 
	Nz([Field2],0) AS Field2_2, 
	Nz([Field3],0) AS Field3_3, 
	Nz([Field1],0)+Nz([Field2],0)+Nz([Field3],0) AS TheTotal
FROM 
	(
		(
			qryAllIDs 
			LEFT JOIN 
			Table1 
			ON 
			qryAllIDs.[ID] = Table1.[ID]
		) 
		LEFT JOIN 
		Table2 
		ON 
		qryAllIDs.[ID] = Table2.[ID]
	) 
	LEFT JOIN 
	Table3 
	ON 
	qryAllIDs.[ID] = Table3.[ID];

Thanks

 

[pbaldy]

It might help to post a sample of your data and the expected result.

 

[jimbrands]

This presents the situation that I'm dealing with:

The 4 tables are for example:
Tabel1
TAB1ID
Veld1

Tabel2
TAB2ID
Veld2

Tabel3
TAB3ID
Veld3

Tabel4
TAB4ID
Veld4

(the fields Veld1,Veld2,Veld3,Veld4 are numeric type fields)

The querys:
Query4:
SELECT Tabel1.Veld1, Tabel2.Veld2, Nz([Veld1],0)+Nz([Veld2],0) AS Exp
FROM Tabel1, Tabel2;

Query5:
SELECT Tabel3.Veld3, Tabel4.Veld4, Nz([Veld3])+Nz([Veld4]) AS Exp2
FROM Tabel3, Tabel4;

Query6:
SELECT Query4.Exp, Query5.Exp2, Nz([Exp])*Nz([Exp2]) AS Exptotaal
FROM Query4, Query5;


Everything works well as long there is a record (even an empty) in al the 4 tables
When for example table 1 has no entry , there is no calculation at all, but I would like that access performs the calculation with the other 3 tables (which do contain a record).
Thanks,
Jim

 

[jimbrands]

Hereby an example of the data I would like to calculate:

for example I have a project administration and i want to regitrate the project budgets and compare these with the actual realisations, ( I have made this sollution wthin Access 2007 and it works very good and gives all the information I need) There is only one problem when one of the fout tables has no entry the calculation does not work.
for example

Projectnumber 1000

budget workinghours internal Euro 5.000,- (tabel 1)
budget workinghours external Euro 7.000,- (Tabel 2
budget use of internal material Euro 10.000,- (tabel 3)
budget use of external (purchase) material 20.000,- (tabel 4)

So I would like access tot calculate

5.000,- + 7.000,- = 12.000,-
and
10.000,- + 20.000,- = 30.000,-

total project budget = 42.000,-

Everything goes well , except when for instance there are no internal workinghours on this project, (so there is no data entry in table1) the querys will not function at all, but I need to get this output:

+ 7.000,-= 7.000,-
and
10.000,- + 20.000,- = 30.000,-

total project budget = 37.000,-

Remark, on a project there are several possibilities or there are no internal hours , only external working hours or no workinghours att all and only material use. or only material use and no working hours , so every combination is possible.....

Greetings Jim

 

[pbaldy]

When I said sample I was hoping for a database with the sample data in it. Your queries have no join, so you'll get. Cartesean product. Is that what you want?

 

[jimbrands]

This is the actual query:

SELECT [PP-VC budget uren p kostenplaats periode form].ProjectId, [PPproductie projecten].Projectnummer, [PP-VC budget uren p kostenplaats periode form].Kostenplaatscode, [PP-VC budget uren p kostenplaats periode form].[Kostenplaats omschrijving], [PP-VC budget uren p kostenplaats periode form].Periode, Sum([PP-VC budget uren p kostenplaats periode form].Exptotal) AS SomVanExptotal
FROM [PP-VC budget uren p kostenplaats periode form] INNER JOIN [PPproductie projecten] ON [PP-VC budget uren p kostenplaats periode form].ProjectId = [PPproductie projecten].ProjectId
GROUP BY [PP-VC budget uren p kostenplaats periode form].ProjectId, [PPproductie projecten].Projectnummer, [PP-VC budget uren p kostenplaats periode form].Kostenplaatscode, [PP-VC budget uren p kostenplaats periode form].[Kostenplaats omschrijving], [PP-VC budget uren p kostenplaats periode form].Periode, [PP-VC budget uren p kostenplaats periode form].Kostenplaatscode;


but I think this is to complicated to discuss in this way. (i already tried everything by changing the joins, but this is no solution for this problem)

so I made up an example wich represents the problem that access querys çannot function when of the tables has no records. with the Nz function I do can solve the Null value problem but when there is no record at all in one off the two tables, I can not find a solution.

I will try once again to explain:

There are two tables:

Table1
field1 =10

Table2
field2 = 20

There is one query:

SELECT Table1.Field1, Table2.Field2, Nz([field1],0)+Nz([Field2],0) AS Exp
FROM Table1, Table2;

It works well if both tables have a value, but if one the two tables has no record at all, the query will not work.

The Nz function gives no solution for this problem. when there is no record in one of the two tables.

Grt Jim

 

[pbaldy]

You apparently can't post a sample db illustrating the problem?

 

[jimbrands]

See DB3

2 tables
1 query

it calcultates field 1 + filed2, with the Nz function it works even if there is no entry in the table1, but there is a record with no value in it.

now delete de the record in table1, so the table is empty.

now the query doesn't work, but i would like the query to calculate the empty table1 + table2 and give me the result

i hope this clears the question

 

[pbaldy]

You never answered the Cartesian product question. Put 2 records in each table and run the query. Is it returning what you expect? Or should there be a relationship between the tables?

 

[jimbrands]

I made a better example , as you can see the total query shows two records for project 1000 and project 2000 and makes a calculation for both records.

now delete de null record for project 2000 in table1

then there will be no more calculation for project 2000 even if there is a record with a value in table2, and this must be shown,

this can also be the other way around that there is a record in table2 and no record in table1

 

[pbaldy]

How about this?

SELECT Qry3.ProjectId, Qry3.Projectnumber, Qry2.Field1, Qry3.Field2, Nz([Field1],0)+Nz([Field2],0) AS Exp
FROM Qry2 RIGHT JOIN Qry3 ON Qry2.Projectnumber = Qry3.Projectnumber;

 

[jimbrands]

A right join is not the solution, because when it is the other way around and for example table2 has no record for project 2000 but table1 do has a record then the query will not function.

In my project system it concerns 4 different tables, so there are many possiblities for example:

project 1000 table1 has a record /table2 has a record/table3 has no record/ table4 has record

project 2000 table1 has no record/ table2 has no record/table3 has a record/ table4 has no record

so there are many possibilities, a right or left join does not give the sollution.

The problem is that access querys need a record otherwhise the calculation will not function.

I found several threads about this problem on the internet, but no solution so far, sombody was taking about the IIF funtion?

Any other sugestions?

Grt jim

 

[jimbrands]

please have a look at the example database3

I made 4 tables , the totalquery1 works well, so now deletate the empty record in table3 for project 2000

then the query makes no calculation for project 2000 although there are entries in the other 3 tables for project2000

note : for every project it will be different wich table contanis a record and which table does'nt

so with inner and outher joins it cannot be solved

Grt Jim

 

[spikepl]

If you want qualified help on this issue then present the problem in full:

1. proper table names and field names
2. an explanation in plain English what the content of each table is and what it is you are seeking
3. and a screenshot of the tables and relations.

Your previous posts showing the SQL, and your lack of response to Paul's question about certesian product, would indicate that there might be some comprehension problems, and a perhaps a structural issue with the data. Your latest db (post #16) shows a relation which is set up incorrectly.

Just because the system may have produced what you wated in some instances with little data, is not necessarily a proof that the whole setup is correct

Prove me wrong or prove me right, but show something that outsiders can wrap their head around.

 

[jimbrands]

I made the sample datadase3(which contains the 4 tables and 4 querys and the totalquery/ and all the relations involved), to make things clear but appearently I don't seem to achieve to explain the problem , the Cartesean matter is not an issue.


Everythings is working well in my project system, I can make projects, I can make budgets for working hours , budgets for used materials, budgets for invoiced project amounts, and on the other hand i can registrate the actual realisation of working hours/used materials/project invoices on every project.

the system compares the budgets and the realisations , shows me the differences, the system then calculates the the Percentage Off Completion for each project and gives me the projecttotals , and produces the amounts off WIP work in progress, which is exported into the financial system .


Also with many projects, and many data everything works perfect.


Only one problem remains

as in the example database3: all the four tables need to have a record for every project, otherwise the query does'nt function


This output shows the Querttotal1 when all the four tables do have a record:


Querttotal1ProjectIdProjectnumberField1Field2Field3Field4Exp110001020885123220005010506116

This is perfect, this is wat i need.


But now I delete for example Field3 in table3 for project "2000"(so there is no record for project 2000 in table3)

Then this is the result of the query:

Querttotal1ProjectIdProjectnumberField1Field2Field3Field4Exp110001020885123

so there is no calcultaion for project 2000 even that field1/field2/field4 do contain a value

But this is what I need (so even if one of the tables has no record for a certain project, the calculation has to be made with the tables/fields wich do have record for this certain project)



ProjectIdProjectnumberField1Field2Field3Field4Exp110001020885123220005010666



I hoped I cleared myself by now

 

[jimbrands]

the screen shots off the output do not function??

:banghead: