Formula in query to determine "water-content" of a sample.

[Sniper-BoOyA-]

Good morning,

I am working on a database to determine the water-content of a specific sample. And the idea is to make a graph based on a query.

I actually have to make 2 graphs, one based on the NEN(RAW) rules / standards, and on based on the NEN-EN rules / standards.

The RAW graph works great, it does exactly what i want. And that is to show only one proctor, the proctor that has been linked to the sample.

I made a copy of the form and query, and edited the formulas, and the relationships of the tables.

But when i make a graph, i see all the proctors, instead of 1.

So looked into it some more, and it turns out that if i replace the new-made formula to determine the water-content, with the RAW version, it works perfect.

The NEN(RAW) formula is:

watergehproct: IIf(IsNull([schaalnr]);[topwtrgeh];(([massaschlmonnat]-[massa3])/([massa3]-[massaschl]))*100)

The new NEN-EN formula is :

watergehproct: IIf([corrigeren]="Ja";(((([massaschlmonnat]-[massa3])/([massa3]-[massaschl]))*100)*(1-[massaststdrg]/tblproctorNEN!massamnstr))+([watergehstst]*([massaststdrg]/tblproctorNEN!massamnstr));(([massaschlmonnat]-[massa3])/([massa3]-[massaschl]))*100)

Here's the full query of both graphs:

RAW:

SELECT IIf(IsNull([schaalnr]),[topwtrgeh],(([massaschlmonnat]-[massa3])/([massa3]-[massaschl]))*100) AS watergehproct, IIf(IsNull([schaalnr]),[topdrgdichth],((([massacillmonnat]-[massaopv])*100000)/([inhpv]*(100+[watergehproct])))) AS drgdichtheidproct, tblproctor.proctorID
FROM (tblopvklei INNER JOIN tblproctor ON tblopvklei.opvnr = tblproctor.opvnr) INNER JOIN tblproctwtrgeh ON tblproctor.proctorID = tblproctwtrgeh.proctorID
ORDER BY tblproctor.proctorID;

NEN-EN:

SELECT IIf(IsNull([schaalnr]),[topwtrgeh],(([massaschlmonnat]-[massa3])/([massa3]-[massaschl]))*100) AS watergehproct, IIf([Corrigeren]="Ja",(((([massacillmonnat]-tblproctorNEN!massaopv)*100)/([inhpv]*(100+[watergehproct])))*(1-([massaststdrg]/tblproctorNEN!massamnstr)))+(0.9*[dchthdstst]*([massaststdrg]/tblproctorNEN!massamnstr)),(([massacillmonnat]-tblproctorNEN!massaopv)*100)/([inhpv]*(100+[watergehproct]))) AS drgdichtheidproct, tblproctorNEN.proctorID
FROM (queryprctrproefNEN RIGHT JOIN (tblproctorNEN INNER JOIN tblopvklei ON tblproctorNEN.opvnr = tblopvklei.opvnr) ON queryprctrproefNEN.proctorID = tblproctorNEN.proctorID) INNER JOIN tblproctwtrgeh ON tblproctorNEN.proctorID = tblproctwtrgeh.proctorID;

Do you think it could be caused by a relation between the 2 tables in question? First i thought it was caused by the a missing "link" between the motherform and daughter form. But it turns out it is excactly the same. And like i mentioned before, once i replace the formule with the old one, it works perfectly.

Anyways, ive been busting my balls over this one for the past couple of weeks now, and i cant seem to figure this out.

- Ive started all over, in case i missed something in the buildup process.
- I checked the relationships with the RAW version.
- I converted the querie needed in the NEN-EN table into a make-table query, and tried to do it that way, unfortunately it didnt make any difference what so ever.

So ya, if anyone has an idea why that formule aint working, it would be much appreciated.

Cheers!

edit: ive attached 2 screenshots of the graphs, based on the queries above. As you can see graph2 has 13 points and its supposed to have 7.

Proctor 1 : 7 points
Proctor 2 : 6 points

 

[namliam]

It looks like in the first query your doing only inner joins, while in the second your doing a right (outer) join... that could result in extra records returning.

Other than the join's in the first query there is no where that is putting contraints (where/having) on that query, thus... it should only be the join....

 

[Sniper-BoOyA-]

It looks like in the first query your doing only inner joins, while in the second your doing a right (outer) join... that could result in extra records returning.

Other than the join's in the first query there is no where that is putting contraints (where/having) on that query, thus... it should only be the join....

Ya the thing is,

On the new formula to determine the water-content of the sample, needs more data then the original formula, that extra data is provided in another query.

I tried to change the RIGHT JOIN into an INNER JOIN, but it didnt have any effect on the outcome of the graph.

Anyways, thanks for the fast reply. Ill go through the formula, line by line.

QueryprctrproefNEN is the same as tblproctorNEN, but with the new and needed data in it. So the best thing would be to delete the tblproctorNEN in the query, and set it up as:


tblproctwtrgeh --> queryprctrproef --> tblopvklei

with Inner Joins right ?

 

[namliam]

A formula cannot create extra rows, formula's calculate values in rows.

In this case it then seems like your query which probably is joined with other tables? to fetch additional data... is producing the additional rows...

MVG uit Amsterdam

 

[Sniper-BoOyA-]

A formula cannot create extra rows, formula's calculate values in rows.

In this case it then seems like your query which probably is joined with other tables? to fetch additional data... is producing the additional rows...

MVG uit Amsterdam

Ya i was thinking it could be something like that. Which is a pain in the ass because at this point, i really need that query.

I wonder if it can be fixed by converting the query into a "make table " query. And then get the table, set up the relationships and then add it to the query for the graph.

Anyways, ill have a look once more, and if i get across something , ill let you know. If not, Alvast een fijn weekend!

 

[namliam]

Table or query wont make a difference... it is actually a bad Bad BAD, REALLY BAD idea to do a make table

 

[Sniper-BoOyA-]

Table or query wont make a difference... it is actually a bad Bad BAD, REALLY BAD idea to do a make table

First off all, i'd like to apologize for responding this late. But i havent been at work due to the flue .

Anyways, i cant seem to figure out whats wrong.

Like i mentioned before i have a form with 5 subforms, made of tabs.

Tab 1 : Input data
Tab 2 : A tab to calculate if a correction is needed, if yes apply correction and display on tab 1
Tab3: Graph based on tab 1 and 2
Tab4: Add points into graph of needed
Tab5: Highest value of the Proctor point.

Tab 1,2,4 and 5 work like they supposed to. They get filtered by the Proctor ID like they should.

The graph on the otherhand keeps showing both ProctorID's.

Ive changed the relations between the query of tab2, and the tables needed for the graph. But it didnt have any effect on the graph.

Anyways, i have to get back into it a little bit, but i am open for suggestions. Thanks for your time/help.