Query Problems. 3

[lars7]

Hi There,
The following code works only if all the fields have value but some of the values will be added later, is there a way that it can show the total for Game1For - Game1Against and then the others when they are added.

GoalDifference: [Game1For]-[Game1Against]+[Game2For]-[Game2Against]+[Game3For]-[Game3Against]

Hope this makes sense, thanks in advance.

 

[gmmastros]

I'm not real sure, but try...

GoalDifference: Nz([Game1For])-Nz([Game1Against])+Nz([Game2For])-Nz([Game2Against])+Nz([Game3For])-Nz([Game3Against])

NZ should return a zero if the value in the field is null. If it's not null nz returns the value in the field.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[dhookom]

lars7,
Are these your actual field names? If so, you should normalize your table structure.

When using Nz(), you should really enter the default value like Nz([Game2For][red],0[/red])

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lars7]

Hi there,
Thanks for replying. Both you suggestions worked.

Golom:

Yes these are the field names I chose, I'm not very sure what is ment with normalize, could you tell.

 

[dhookom]

There are lots of resources on the web that pertain to normalization. Check out Jeff Conrad's links at

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101.

Basically I would have created a table of gamescores
[tt]
tblGameScores
===================
GameNum For Against
1 3 2
2 1 2
3 2 1
[/tt]


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Another link about normalisation:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Thanks everyone,
For the help and the Info.