Help condensing similar queries into one.

[Opid]

Hello everyone,

I am creating a form in which the user is able to enter the amount of a utility bill per unit number of an apartment complex. Because the bills are out of order, the current and wanted layout of the form is about 20 text fields. One asks for unit number and the other asks for bill amount. Once the process button is clicked I have 10 queries run for each room number. Each query is a simple update query. The criteria box checks the unit number text field that the query is associated with. I was wondering if there is a way to create one query to do this instead of ten. An example of the first query is

 field to update: UtitlityDue table: tblTenant Update to: UtilityDue+[Forms]![frmUtilitiesV2]![UnitOneAmount] criteria: tblTenant![Unit] = [Forms]![frmUtilitiesV2]![UnitOne]

So each query is the same, only difference is replace "UnitOneAmount and UnitOne" with "UnitTwoAmount and UnitTwo"... This way works but I think it is sloppy.

Thanks in advance

[Robeen]

Hi,
Did you get this resolved?
If not, could you rephrase your question and possibly give some more information?
Plus . . . I don't know if I'm missing something but your 'example of the first query' . . . doesn't look like any Access query that I've ever seen.
This is all I see in your 'Code:' section:

field to update: UtitlityDue table: tblTenant Update to: UtilityDue+[Forms]![frmUtilitiesV2]![UnitOneAmount] criteria: tblTenant![Unit] = [Forms]![frmUtilitiesV2]![UnitOne]

. . . it doesn't look like a query to me.
?

[Epidural]

So each query is the same, only difference is replace "UnitOneAmount and UnitOne" with "UnitTwoAmount and UnitTwo"... This way works but I think it is sloppy.

First thing that comes to my mind is to write it in VB. I don't yet know of a way to have variables which aren't hard-coded in Access' queries without it.

I would run a loop along the lines of

Dim counter as Integer = 0
Do
'Do your SQL here
counter = counter+1
Loop Until (counter = 19)

You may find a way to convert counter to a string, not sure if VB has that feature. When using strings, there may not be a quick and dirty way to do it. (Hopefully you got that "counter" would be used as a variable as "Unit"&counter&"Amount" etc)

EDIT: Took a quick (very quick) look and I think this would only work if you were able to change the names to Unit1, Unit2 instead of UnitOne, UnitTwo. VBA doesn't have any auto-convert-to-strings for integers.

[Opid]

Hello,

No I did not get it resolved. The code I typed in represents the query in design form. Basically what is going on is I have a form that has 10 text fields that ask for a unit number and another 10 fields that ask for the amount of the utility bill for that unit. The form isn't associated with any table because it updates two tables by running 10 queries per table. The first 10 update a tenant table and the second 10 update a transactions table. Right now each text field is labeled in this format UnitOne UnitOneAmount, UnitTwo UnitTwoAmount. The first set of 10 queries updates the tenant table. So the ten queries "run" on the ten sets of text fields. So the first query runs on "UnitOne and UnitOneAmount" it updates the tenant table's record whose unit field matches "UnitOne". I have to run 10 seperate queries because I have to use each individual Unit"N" as criteria. I was wondering if one query could accomplish this. That is somehow update the tenant table and transactions table by cycling through each text field, comparing the unit numbers, and going from their. If you need I can let you see the db. Thanks in advance hope this is more clear.

[Robeen]

Yes - it might help to be able to look at your db with a small sampling of data.