Update query with true/false

[Radhika]

I have created an update query with the following criteria:
VP OtherGlue on valve/fringe = True
1. When VPOther:Extra Increased Resistance is True
2. When VPOther: Extra Extra Increased Resistance is True
3. When VP Type=VPOther:Increased Resistance and VPType='InHealth
Indwelling' or 'InHealth Low pressure'

I want to include in this SQL a criterion saying that if the obove mentioned
fields are false, VPOtherGlue on valve/fringe = False. Therefore, when the
obove-metioned check boxes are checked, VPOtherGlue on valve/fringe should be
check. If they are not, VPOtherGlue on valve/fringe should not.

How do I go about doing this?

Thankyou,
Radhika

 

[Douglas J. Steele]

If the value of VPOtherGlue is solely dependent upon the values of other
fields in the same row, it shouldn't be stored in the table at all.

Instead, you should create a query that has a computed field VPOtherGlue in
it. You'd put something like this in a blank cell on the Field row of the
query builder:

VPOtherGlue: [VPOther:Extra Increased Resistance] AND [VPOther: Extra Extra
Increased Resistance} AND([VPOther:Increased Resistance] AND
[VPType='InHealth Indwelling' or VPType=''InHealth Low pressure')

Use that query wherever you would otherwise have used the table.

 

[Radhika]

Thank you.

My only concern is that I do need the field in the table. There are other
fields in the table which will be filled in based on the value of VPOtherGlue.

Is there any way in which i can do this in the table?

If the value of VPOtherGlue is solely dependent upon the values of other
fields in the same row, it shouldn't be stored in the table at all.

Instead, you should create a query that has a computed field VPOtherGlue in
it. You'd put something like this in a blank cell on the Field row of the
query builder:

VPOtherGlue: [VPOther:Extra Increased Resistance] AND [VPOther: Extra Extra
Increased Resistance} AND([VPOther:Increased Resistance] AND
[VPType='InHealth Indwelling' or VPType=''InHealth Low pressure')

Use that query wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have created an update query with the following criteria:
VP OtherGlue on valve/fringe = True
1. When VPOther:Extra Increased Resistance is True
2. When VPOther: Extra Extra Increased Resistance is True
3. When VP Type=VPOther:Increased Resistance and VPType='InHealth
Indwelling' or 'InHealth Low pressure'

I want to include in this SQL a criterion saying that if the obove
mentioned
fields are false, VPOtherGlue on valve/fringe = False. Therefore, when the
obove-metioned check boxes are checked, VPOtherGlue on valve/fringe should
be
check. If they are not, VPOtherGlue on valve/fringe should not.

How do I go about doing this?

Thankyou,
Radhika

 

[Douglas J. Steele]

It's almost always a mistake to store calculated values in a table.

As fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed, making
the value WRONG."

If you're determined to do it, though, your Update query would be something
like:

UPDATE MyTable
SET VPOtherGlue = VPOther:Extra Increased Resistance] AND [VPOther: Extra
Extra
Increased Resistance} AND([VPOther:Increased Resistance] AND
[VPType='InHealth Indwelling' or VPType=''InHealth Low pressure')



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you.

My only concern is that I do need the field in the table. There are other
fields in the table which will be filled in based on the value of
VPOtherGlue.

Is there any way in which i can do this in the table?

If the value of VPOtherGlue is solely dependent upon the values of other
fields in the same row, it shouldn't be stored in the table at all.

Instead, you should create a query that has a computed field VPOtherGlue
in
it. You'd put something like this in a blank cell on the Field row of the
query builder:

VPOtherGlue: [VPOther:Extra Increased Resistance] AND [VPOther: Extra
Extra
Increased Resistance} AND([VPOther:Increased Resistance] AND
[VPType='InHealth Indwelling' or VPType=''InHealth Low pressure')

Use that query wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have created an update query with the following criteria:
VP OtherGlue on valve/fringe = True
1. When VPOther:Extra Increased Resistance is True
2. When VPOther: Extra Extra Increased Resistance is True
3. When VP Type=VPOther:Increased Resistance and VPType='InHealth
Indwelling' or 'InHealth Low pressure'

I want to include in this SQL a criterion saying that if the obove
mentioned
fields are false, VPOtherGlue on valve/fringe = False. Therefore, when
the
obove-metioned check boxes are checked, VPOtherGlue on valve/fringe
should
be
check. If they are not, VPOtherGlue on valve/fringe should not.

How do I go about doing this?

Thankyou,
Radhika