Query Equation

[Thorson]

I am creating a database that tracks the movement of all animals and keeps
the inventory of animals at three separate units. When animals move are sold
or die they receive a record number called a "disposition number", these
records go into tblDispositionRecords. tblDispositionRecords has several
fields, two are "Unit" and "NewUnit", this two fields track the animal's
physical location. This table is then queried I use this equation to
calculate the number of animals that either entered the unit or left the unit:

Head: IIf([Unit]="ORR",-[DispHead],[DispHead])

"ORR" is the name of the Unit and "DispHead" is the number of animals listed
for that disposition in tblDispositionRecords. Usually when an animal
receives a disposition number the Animal moves locations and the "Unit" and
"NewUnit" are different so this equation works perfectly. However I have a
case (and this will happen again in the future) where the animal receives a
disposition number but the "Unit" and "NewUnit" remain the same. How can I
change the equation so that if "Unit"="NewUnit" then Head=0?

 

[John Spencer]

Perhaps the following will work for you.

IIF(Unit<>NewUnit,IIf([Unit]="ORR",-[DispHead],[DispHead]),0)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Thorson]

That works great! to get what I wanted because of all the scenerios I do have
to make an intermediate query though.

Thanks for the help!

Perhaps the following will work for you.

IIF(Unit<>NewUnit,IIf([Unit]="ORR",-[DispHead],[DispHead]),0)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I am creating a database that tracks the movement of all animals and keeps
the inventory of animals at three separate units. When animals move are sold
or die they receive a record number called a "disposition number", these
records go into tblDispositionRecords. tblDispositionRecords has several
fields, two are "Unit" and "NewUnit", this two fields track the animal's
physical location. This table is then queried I use this equation to
calculate the number of animals that either entered the unit or left the unit:

Head: IIf([Unit]="ORR",-[DispHead],[DispHead])

"ORR" is the name of the Unit and "DispHead" is the number of animals listed
for that disposition in tblDispositionRecords. Usually when an animal
receives a disposition number the Animal moves locations and the "Unit" and
"NewUnit" are different so this equation works perfectly. However I have a
case (and this will happen again in the future) where the animal receives a
disposition number but the "Unit" and "NewUnit" remain the same. How can I
change the equation so that if "Unit"="NewUnit" then Head=0?