Update Query in a Subform with Sums

[mjtan]

I have a real-estate database. The Main Form contains all the detail
of a specific property (e.g.Address, Size, Status, Value). I have a
subform that counts each type of size within a certain block, and
groups them by size, status, and value. (ex: Fifty 2-bedroom units
worth 100K that are rented, and there are three 1-bedroom units worth
75K that are vacant) At the end of the year, the company does
revaluation for certain types of properties. For example, all
2-bedroom units in this specific block were worth 100K, but next year
they will be worth 110K.

I'd like to be able to run an update query in this subform that selects
specific groups to update all 2-bedrooms that are currently worth 100K,
to be worth 110K. There are 2 bedrooms that are worth other than
100K, but I need to exclude those.

I thought I could just add an unbound field to contain "new value" for
each group on the subform, and then run an update query to set the
"value" to the "new value", but I don't understand how to pass the
parameters through an update query. How do I make it update the
"value" from the "new value" field?

I'm running Access 2000.

Thanks for any assistance.

Joy

 

[kingston via AccessMonster.com]

In your update query, use something like this in the Update To: box -

[Forms]![FormName]![TextBox1]

If the text control is in a subform, use something like this -

[Forms]![FormName]![SubFormName]![TextBox1]

 

[mjtan]

Thanks for the tip, Kingston. But for some reason, I don't know how to
use this text box in either the update query, or the query that
populates the subform to begin with.

If I include the text box in the original query, I won't be able to
update it, since the query aggregates all the same types of properties,
therefore making it unupdateable. If I put the text box just in the
update query, or just on the form itself, it doesn't know which value
fields from the original query to update. Am I approaching this all
wrong, and do I need to dump all the data into some sort of temp table,
and then perform the update on the original table?

thanks again for any other ideas that you can offer.

Joy

In your update query, use something like this in the Update To: box -

[Forms]![FormName]![TextBox1]

If the text control is in a subform, use something like this -

[Forms]![FormName]![SubFormName]![TextBox1]

I have a real-estate database. The Main Form contains all the detail
of a specific property (e.g.Address, Size, Status, Value). I have a
subform that counts each type of size within a certain block, and
groups them by size, status, and value. (ex: Fifty 2-bedroom units
worth 100K that are rented, and there are three 1-bedroom units worth
75K that are vacant) At the end of the year, the company does
revaluation for certain types of properties. For example, all
2-bedroom units in this specific block were worth 100K, but next year
they will be worth 110K.

I'd like to be able to run an update query in this subform that selects
specific groups to update all 2-bedrooms that are currently worth 100K,
to be worth 110K. There are 2 bedrooms that are worth other than
100K, but I need to exclude those.

I thought I could just add an unbound field to contain "new value" for
each group on the subform, and then run an update query to set the
"value" to the "new value", but I don't understand how to pass the
parameters through an update query. How do I make it update the
"value" from the "new value" field?

I'm running Access 2000.

Thanks for any assistance.

Joy

 

[kingston via AccessMonster.com]

How do you determine which values to update? I mean what is the logic behind
it and how would you create a simple select query based on the logic?. Yes,
you can't update values in a totals query directly, and yes, one way around
this is to create a temporary table to match records on the original table.
However, is there a simpler way to filter the target records? If there isn't,
then so be it. I thought you were just asking how to send a value from a
form to the update portion of an update query.

Thanks for the tip, Kingston. But for some reason, I don't know how to
use this text box in either the update query, or the query that
populates the subform to begin with.

If I include the text box in the original query, I won't be able to
update it, since the query aggregates all the same types of properties,
therefore making it unupdateable. If I put the text box just in the
update query, or just on the form itself, it doesn't know which value
fields from the original query to update. Am I approaching this all
wrong, and do I need to dump all the data into some sort of temp table,
and then perform the update on the original table?

thanks again for any other ideas that you can offer.

Joy

In your update query, use something like this in the Update To: box -

[quoted text clipped - 34 lines]

Message posted via AccessMonster.com

 

[mjtan]

Hi again-

The selection criteria would be something like all 2bds on block 137001
that are now worth 150K will now be worth 160K. The subform basically
looks like:

For Block 137001

Size #of VAC # of Let Value
1KB 3 5 100K
1KB 1 10 110K
2KB 0 3 150K
2KB 2 20 180K

The subform contains all the different configurations of units(flats)
and their corresponding status and value. The subform filters the
flats by blockid.

The issue is that I can't just change the price from within the subform
because I have an aggregate of the count of units. In order to change
the value, I don't want to have to page through each record in the main
form to change them. The query looks like this for the subform:

SELECT
Property.Size,
Sum(IIf([status]=3857,1,0)) AS Sold, Sum(IIf([status]=3983,1,0)) AS
Vac, Sum(IIf([status]=87,1,0)) AS AST, Sum(IIf([status]=98,1,0)) AS
Assrd, Sum(IIf([status]=145,1,0)) AS Let, Sum(IIf([status]=777,1,0)) AS
Reg, Property.BlockPropID, Property.UnmodVPValue, Count(*) AS total,
Sum(Property.UnmodVPValue) AS valuesum

FROM Property
GROUP BY Property.Size, Property.BlockPropID, Property.UnmodVPValue
HAVING (((Property.BlockPropID) Is Not Null));


I'd like to send the new value from the update query update the main
form, therefore changing the display in the un-updateable recordset.
But how do I build the form so that the user may easily select which
properties to update? Does it need to be in another subform? What
are the benefits of creating a temp table? Is it possible to create a
temp table and drop it automatically after the update has been
performed? I'm not quite sure how Access handles things like this.

Thanks again for your help!


------------------------------------------------------------------

How do you determine which values to update? I mean what is the logic behind
it and how would you create a simple select query based on the logic?. Yes,
you can't update values in a totals query directly, and yes, one way around
this is to create a temporary table to match records on the original table.
However, is there a simpler way to filter the target records? If there isn't,
then so be it. I thought you were just asking how to send a value from a
form to the update portion of an update query.

Thanks for the tip, Kingston. But for some reason, I don't know how to
use this text box in either the update query, or the query that
populates the subform to begin with.

If I include the text box in the original query, I won't be able to
update it, since the query aggregates all the same types of properties,
therefore making it unupdateable. If I put the text box just in the
update query, or just on the form itself, it doesn't know which value
fields from the original query to update. Am I approaching this all
wrong, and do I need to dump all the data into some sort of temp table,
and then perform the update on the original table?

thanks again for any other ideas that you can offer.

Joy

In your update query, use something like this in the Update To: box -

[quoted text clipped - 34 lines]

Message posted via AccessMonster.com

 

[kingston via AccessMonster.com]

An update query based on your criteria would look like:

UPDATE Properties
SET Properties.NewValue=[Forms]![MainForm]![SubForm]![NewValue]
WHERE (Properties.BlockPropID=[Forms]![MainForm]![BlockID])
AND (Properties.UnmodVPValue=[Forms]![MainForm]![SubForm]![CurrentValue])
AND (Properties.Size=[Forms]![MainForm]![SubForm]![Size]);

Add a text box ([NewValue]) and a button in the details section of the
subform. This will be reflected in each record so the user can input a new
value in the text box (you can check that it is a valid value) and click the
button to update the records (you can verify that this is what the user
really wants to do and save the old data just in case). You might have to
add more conditions, e.g. based on [status].

Hi again-

The selection criteria would be something like all 2bds on block 137001
that are now worth 150K will now be worth 160K. The subform basically
looks like:

For Block 137001

Size #of VAC # of Let Value
1KB 3 5 100K
1KB 1 10 110K
2KB 0 3 150K
2KB 2 20 180K

The subform contains all the different configurations of units(flats)
and their corresponding status and value. The subform filters the
flats by blockid.

The issue is that I can't just change the price from within the subform
because I have an aggregate of the count of units. In order to change
the value, I don't want to have to page through each record in the main
form to change them. The query looks like this for the subform:

SELECT
Property.Size,
Sum(IIf([status]=3857,1,0)) AS Sold, Sum(IIf([status]=3983,1,0)) AS
Vac, Sum(IIf([status]=87,1,0)) AS AST, Sum(IIf([status]=98,1,0)) AS
Assrd, Sum(IIf([status]=145,1,0)) AS Let, Sum(IIf([status]=777,1,0)) AS
Reg, Property.BlockPropID, Property.UnmodVPValue, Count(*) AS total,
Sum(Property.UnmodVPValue) AS valuesum

FROM Property
GROUP BY Property.Size, Property.BlockPropID, Property.UnmodVPValue
HAVING (((Property.BlockPropID) Is Not Null));

I'd like to send the new value from the update query update the main
form, therefore changing the display in the un-updateable recordset.
But how do I build the form so that the user may easily select which
properties to update? Does it need to be in another subform? What
are the benefits of creating a temp table? Is it possible to create a
temp table and drop it automatically after the update has been
performed? I'm not quite sure how Access handles things like this.

Thanks again for your help!

------------------------------------------------------------------

How do you determine which values to update? I mean what is the logic behind
it and how would you create a simple select query based on the logic?. Yes,

[quoted text clipped - 29 lines]

Message posted via AccessMonster.com

 

[mjtan]

Hi again, Kingston-

When I put a new text box into the existing subform, I cannot enter any
values into it. It tells me the form is read-only. I've checked all
the obvious "can edit" settings, but I think it's because my record-set
is un-updateable.

I assume that the update query goes behind the button, right? Perhaps,
I need the update query to fire off first, before I put a NewValue in?
But will the query know which group of records I'm selecting, just by
default of being on the correct line in the detail section?

I'm sorry to be so thick right now, but when I first read your reply,
it made perfect sense, and now that I've tried it, and it didn't work,
I confused myself again! It seems that it should be pretty
straightforward.....

thank you so much for your tenacity!

Joy

An update query based on your criteria would look like:

UPDATE Properties
SET Properties.NewValue=[Forms]![MainForm]![SubForm]![NewValue]
WHERE (Properties.BlockPropID=[Forms]![MainForm]![BlockID])
AND (Properties.UnmodVPValue=[Forms]![MainForm]![SubForm]![CurrentValue])
AND (Properties.Size=[Forms]![MainForm]![SubForm]![Size]);

Add a text box ([NewValue]) and a button in the details section of the
subform. This will be reflected in each record so the user can input a new
value in the text box (you can check that it is a valid value) and click the
button to update the records (you can verify that this is what the user
really wants to do and save the old data just in case). You might have to
add more conditions, e.g. based on [status].

Hi again-

The selection criteria would be something like all 2bds on block 137001
that are now worth 150K will now be worth 160K. The subform basically
looks like:

For Block 137001

Size #of VAC # of Let Value
1KB 3 5 100K
1KB 1 10 110K
2KB 0 3 150K
2KB 2 20 180K

The subform contains all the different configurations of units(flats)
and their corresponding status and value. The subform filters the
flats by blockid.

The issue is that I can't just change the price from within the subform
because I have an aggregate of the count of units. In order to change
the value, I don't want to have to page through each record in the main
form to change them. The query looks like this for the subform:

SELECT
Property.Size,
Sum(IIf([status]=3857,1,0)) AS Sold, Sum(IIf([status]=3983,1,0)) AS
Vac, Sum(IIf([status]=87,1,0)) AS AST, Sum(IIf([status]=98,1,0)) AS
Assrd, Sum(IIf([status]=145,1,0)) AS Let, Sum(IIf([status]=777,1,0)) AS
Reg, Property.BlockPropID, Property.UnmodVPValue, Count(*) AS total,
Sum(Property.UnmodVPValue) AS valuesum

FROM Property
GROUP BY Property.Size, Property.BlockPropID, Property.UnmodVPValue
HAVING (((Property.BlockPropID) Is Not Null));

I'd like to send the new value from the update query update the main
form, therefore changing the display in the un-updateable recordset.
But how do I build the form so that the user may easily select which
properties to update? Does it need to be in another subform? What
are the benefits of creating a temp table? Is it possible to create a
temp table and drop it automatically after the update has been
performed? I'm not quite sure how Access handles things like this.

Thanks again for your help!

------------------------------------------------------------------

How do you determine which values to update? I mean what is the logic behind
it and how would you create a simple select query based on the logic?. Yes,

[quoted text clipped - 29 lines]

Message posted via AccessMonster.com

 

[kingston via AccessMonster.com]

If you're sure the text box is unbound and have checked all of the "can edit"
settings, then you might try having the button open a dialog box asking for a
new value. Once the user entered a new value, you present a confirmation and
then run the update query.

Yes, the update query will operate based on the values in the current record
in the subform. IOW, [Forms]![FormName]![SubFormName]![Field] only returns
one value (where the record cursor is).

Hi again, Kingston-

When I put a new text box into the existing subform, I cannot enter any
values into it. It tells me the form is read-only. I've checked all
the obvious "can edit" settings, but I think it's because my record-set
is un-updateable.

I assume that the update query goes behind the button, right? Perhaps,
I need the update query to fire off first, before I put a NewValue in?
But will the query know which group of records I'm selecting, just by
default of being on the correct line in the detail section?

I'm sorry to be so thick right now, but when I first read your reply,
it made perfect sense, and now that I've tried it, and it didn't work,
I confused myself again! It seems that it should be pretty
straightforward.....

thank you so much for your tenacity!

Joy

An update query based on your criteria would look like:

[quoted text clipped - 67 lines]

Message posted via AccessMonster.com

 

[mjtan]

When I put an unbound text box into the subform, and then type in a new
value, *every* line for this textbox in the subform gets filled with
this new value. I thought that by assigning the textbox "NewValue"
(from update query), it is by default a "bound textbox".

If you recall, the subform groups by type, value and status, which
means the update query may be updating more than one record at a time.
Do you see that causing a problem with the update query? What I'm
trying to do is give my users an easy way to update the values for a
group of flats at the same time. They need to be able to see the group
of flats, their sizes, their statuses, and their current values in
order to know which to update. I may be barking up the wrong tree,
here...but I'm running out of ideas how to make this process simple.

Thanks for bearing with me, Kingston.

Joy

If you're sure the text box is unbound and have checked all of the "can edit"
settings, then you might try having the button open a dialog box asking for a
new value. Once the user entered a new value, you present a confirmation and
then run the update query.

Yes, the update query will operate based on the values in the current record
in the subform. IOW, [Forms]![FormName]![SubFormName]![Field] only returns
one value (where the record cursor is).

Hi again, Kingston-

When I put a new text box into the existing subform, I cannot enter any
values into it. It tells me the form is read-only. I've checked all
the obvious "can edit" settings, but I think it's because my record-set
is un-updateable.

I assume that the update query goes behind the button, right? Perhaps,
I need the update query to fire off first, before I put a NewValue in?
But will the query know which group of records I'm selecting, just by
default of being on the correct line in the detail section?

I'm sorry to be so thick right now, but when I first read your reply,
it made perfect sense, and now that I've tried it, and it didn't work,
I confused myself again! It seems that it should be pretty
straightforward.....

thank you so much for your tenacity!

Joy

An update query based on your criteria would look like:

[quoted text clipped - 67 lines]

Message posted via AccessMonster.com

 

[kingston via AccessMonster.com]

I'm sorry, I should have realized that you're using a continuous subform.
What I'd do then is just add a button and not a textbox (even though it would
work - yes, having all of the textboxes reflect a new value would be alarming)
.. The button would open a form (a dialog box) with a textbox and a message.
The message would be a confirmation like:

String = "Update all " & [Forms]![MainForm]![SubForm]![Size] & " properties
for Block " & [Forms]![MainForm]![SubForm]![Block] & " and current value " &
[Forms]![MainForm]![SubForm]![Value] &" to a new value of " & Me.NewValue &"?
"

Do you see how the variables are picked from the current record on the
subform; again, [Forms]![MainForm]![SubForm]![Value] only returns 1 value -
the current one? (Me.NewValue is just the textbox in the new dialog form -
you can add a procedure to verify this as well.) Add as many variables as
needed to clearly identify the records involved. Since you will be using the
exact same values for your update query, there will be no misinterpretation;
it is key that this set of values forms a complete and unambiguous identifier
for the records to be updated. The user then can click OK or Cancel, and
you'll run the update query accordingly.

When I put an unbound text box into the subform, and then type in a new
value, *every* line for this textbox in the subform gets filled with
this new value. I thought that by assigning the textbox "NewValue"
(from update query), it is by default a "bound textbox".

If you recall, the subform groups by type, value and status, which
means the update query may be updating more than one record at a time.
Do you see that causing a problem with the update query? What I'm
trying to do is give my users an easy way to update the values for a
group of flats at the same time. They need to be able to see the group
of flats, their sizes, their statuses, and their current values in
order to know which to update. I may be barking up the wrong tree,
here...but I'm running out of ideas how to make this process simple.

Thanks for bearing with me, Kingston.

Joy

If you're sure the text box is unbound and have checked all of the "can edit"
settings, then you might try having the button open a dialog box asking for a

[quoted text clipped - 31 lines]