Multiple Update Set Statements or Multiple DML Statements in one

  • Thread starter Thread starter thedudeminds
  • Start date Start date
T

thedudeminds

Hey folks I have two queries that I prefer to use in one statement
rather than two. Both work seperately but when i put them together i
get syntax errors. Please give me the syntax to combine these two:

UPDATE TBL_AvailableUnits SET TBL_AvailableUnits.FMR = 'y'
WHERE TBL_AvailableUnits.BEDROOMS=1 And
TBL_AvailableUnits.COUNTY='Hancock' And TBL_AvailableUnits.RENT<=822
Or TBL_AvailableUnits.BEDROOMS=2 And
TBL_AvailableUnits.COUNTY='Hancock' And TBL_AvailableUnits.RENT<=960
Or TBL_AvailableUnits.BEDROOMS=3 And
TBL_AvailableUnits.COUNTY='Hancock' And TBL_AvailableUnits.RENT<=1251
Or TBL_AvailableUnits.BEDROOMS=4 And
TBL_AvailableUnits.COUNTY='Hancock' And TBL_AvailableUnits.RENT<=1714
Or TBL_AvailableUnits.BEDROOMS=1 And
TBL_AvailableUnits.COUNTY='Harrison' And TBL_AvailableUnits.RENT<=822
Or TBL_AvailableUnits.BEDROOMS=2 And
TBL_AvailableUnits.COUNTY='Harrison' And TBL_AvailableUnits.RENT<=960
Or TBL_AvailableUnits.BEDROOMS=3 And
TBL_AvailableUnits.COUNTY='Harrison' And TBL_AvailableUnits.RENT<=1251
Or TBL_AvailableUnits.BEDROOMS=4 And
TBL_AvailableUnits.COUNTY='Harrison' And
TBL_AvailableUnits.RENT<=1285.5 Or TBL_AvailableUnits.BEDROOMS=1 And
TBL_AvailableUnits.COUNTY='Jackson' And TBL_AvailableUnits.RENT<=763.5
Or TBL_AvailableUnits.BEDROOMS=2 And
TBL_AvailableUnits.COUNTY='Jackson' And TBL_AvailableUnits.RENT<=916.5
Or TBL_AvailableUnits.BEDROOMS=3 And
TBL_AvailableUnits.COUNTY='Jackson' And
TBL_AvailableUnits.RENT<=1261.5 Or TBL_AvailableUnits.BEDROOMS=4 And
TBL_AvailableUnits.COUNTY='Jackson' And TBL_AvailableUnits.RENT<=1804
Or TBL_AvailableUnits.BEDROOMS=1 And TBL_AvailableUnits.COUNTY='Pearl
River' And TBL_AvailableUnits.RENT<=625 Or
TBL_AvailableUnits.BEDROOMS=2 And TBL_AvailableUnits.COUNTY='Pearl
River' And TBL_AvailableUnits.RENT<=748.5 Or
TBL_AvailableUnits.BEDROOMS=3 And TBL_AvailableUnits.COUNTY='Pearl
River' And TBL_AvailableUnits.RENT<=913.5 Or
TBL_AvailableUnits.BEDROOMS=4 And TBL_AvailableUnits.COUNTY='Pearl
River' And TBL_AvailableUnits.RENT<=1288.5;

UPDATE TBL_AvailableUnits SET TBL_AvailableUnits.FMR = 'n'
WHERE TBL_AvailableUnits.BEDROOMS=1 And
TBL_AvailableUnits.COUNTY='Hancock' And TBL_AvailableUnits.RENT>822 Or
TBL_AvailableUnits.BEDROOMS=2 And TBL_AvailableUnits.COUNTY='Hancock'
And TBL_AvailableUnits.RENT>960 Or TBL_AvailableUnits.BEDROOMS=3 And
TBL_AvailableUnits.COUNTY='Hancock' And TBL_AvailableUnits.RENT>1251
Or TBL_AvailableUnits.BEDROOMS=4 And
TBL_AvailableUnits.COUNTY='Hancock' And TBL_AvailableUnits.RENT>1714
Or TBL_AvailableUnits.BEDROOMS=1 And
TBL_AvailableUnits.COUNTY='Harrison' And TBL_AvailableUnits.RENT>822
Or TBL_AvailableUnits.BEDROOMS=2 And
TBL_AvailableUnits.COUNTY='Harrison' And TBL_AvailableUnits.RENT>960
Or TBL_AvailableUnits.BEDROOMS=3 And
TBL_AvailableUnits.COUNTY='Harrison' And TBL_AvailableUnits.RENT>1251
Or TBL_AvailableUnits.BEDROOMS=4 And
TBL_AvailableUnits.COUNTY='Harrison' And
TBL_AvailableUnits.RENT>1285.5 Or TBL_AvailableUnits.BEDROOMS=1 And
TBL_AvailableUnits.COUNTY='Jackson' And TBL_AvailableUnits.RENT>763.5
Or TBL_AvailableUnits.BEDROOMS=2 And
TBL_AvailableUnits.COUNTY='Jackson' And TBL_AvailableUnits.RENT>916.5
Or TBL_AvailableUnits.BEDROOMS=3 And
TBL_AvailableUnits.COUNTY='Jackson' And TBL_AvailableUnits.RENT>1261.5
Or TBL_AvailableUnits.BEDROOMS=4 And
TBL_AvailableUnits.COUNTY='Jackson' And TBL_AvailableUnits.RENT>1804
Or TBL_AvailableUnits.BEDROOMS=1 And TBL_AvailableUnits.COUNTY='Pearl
River' And TBL_AvailableUnits.RENT>625 Or
TBL_AvailableUnits.BEDROOMS=2 And TBL_AvailableUnits.COUNTY='Pearl
River' And TBL_AvailableUnits.RENT>748.5 Or
TBL_AvailableUnits.BEDROOMS=3 And TBL_AvailableUnits.COUNTY='Pearl
River' And TBL_AvailableUnits.RENT>913.5 Or
TBL_AvailableUnits.BEDROOMS=4 And TBL_AvailableUnits.COUNTY='Pearl
River' And TBL_AvailableUnits.RENT>1288.5;
 
Since for one statement you update to 'y', and the other to 'n', you really
can't, when you're using the endless where clauses like you have. If you can
find some way to make the criteria more table driven, you may be able to make
a single query.

For example, maybe employ a table that has the fields, county, bedrooms, and
rent, and somehow compare that to the available units.

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
 
You could probably use an IIF statement to set the value, but I would
recommend that you look at the table-driven solution outlined below.

UPDATE TBL_AvailableUnits
SET FMR = IIF(TBL_AvailableUnits.BEDROOMS=1 And
TBL_AvailableUnits.COUNTY='Hancock' And TBL_AvailableUnits.RENT<=822
Or TBL_AvailableUnits.BEDROOMS=2 And
TBL_AvailableUnits.COUNTY='Hancock' And TBL_AvailableUnits.RENT<=960
Or TBL_AvailableUnits.BEDROOMS=3 And
TBL_AvailableUnits.COUNTY='Hancock' And TBL_AvailableUnits.RENT<=1251
Or TBL_AvailableUnits.BEDROOMS=4 And
TBL_AvailableUnits.COUNTY='Hancock' And TBL_AvailableUnits.RENT<=1714
Or TBL_AvailableUnits.BEDROOMS=1 And
TBL_AvailableUnits.COUNTY='Harrison' And TBL_AvailableUnits.RENT<=822
Or TBL_AvailableUnits.BEDROOMS=2 And
TBL_AvailableUnits.COUNTY='Harrison' And TBL_AvailableUnits.RENT<=960
Or TBL_AvailableUnits.BEDROOMS=3 And
TBL_AvailableUnits.COUNTY='Harrison' And TBL_AvailableUnits.RENT<=1251
Or TBL_AvailableUnits.BEDROOMS=4 And
TBL_AvailableUnits.COUNTY='Harrison' And
TBL_AvailableUnits.RENT<=1285.5 Or TBL_AvailableUnits.BEDROOMS=1 And
TBL_AvailableUnits.COUNTY='Jackson' And TBL_AvailableUnits.RENT<=763.5
Or TBL_AvailableUnits.BEDROOMS=2 And
TBL_AvailableUnits.COUNTY='Jackson' And TBL_AvailableUnits.RENT<=916.5
Or TBL_AvailableUnits.BEDROOMS=3 And
TBL_AvailableUnits.COUNTY='Jackson' And
TBL_AvailableUnits.RENT<=1261.5 Or TBL_AvailableUnits.BEDROOMS=4 And
TBL_AvailableUnits.COUNTY='Jackson' And TBL_AvailableUnits.RENT<=1804
Or TBL_AvailableUnits.BEDROOMS=1 And TBL_AvailableUnits.COUNTY='Pearl
River' And TBL_AvailableUnits.RENT<=625 Or
TBL_AvailableUnits.BEDROOMS=2 And TBL_AvailableUnits.COUNTY='Pearl
River' And TBL_AvailableUnits.RENT<=748.5 Or
TBL_AvailableUnits.BEDROOMS=3 And TBL_AvailableUnits.COUNTY='Pearl
River' And TBL_AvailableUnits.RENT<=913.5 Or
TBL_AvailableUnits.BEDROOMS=4 And TBL_AvailableUnits.COUNTY='Pearl
River' And TBL_AvailableUnits.RENT<=1288.5, 'y',

IIF(TBL_AvailableUnits.BEDROOMS=1 And
TBL_AvailableUnits.COUNTY='Hancock' And TBL_AvailableUnits.RENT>822 Or
TBL_AvailableUnits.BEDROOMS=2 And TBL_AvailableUnits.COUNTY='Hancock'
And TBL_AvailableUnits.RENT>960 Or TBL_AvailableUnits.BEDROOMS=3 And
TBL_AvailableUnits.COUNTY='Hancock' And TBL_AvailableUnits.RENT>1251
Or TBL_AvailableUnits.BEDROOMS=4 And
TBL_AvailableUnits.COUNTY='Hancock' And TBL_AvailableUnits.RENT>1714
Or TBL_AvailableUnits.BEDROOMS=1 And
TBL_AvailableUnits.COUNTY='Harrison' And TBL_AvailableUnits.RENT>822
Or TBL_AvailableUnits.BEDROOMS=2 And
TBL_AvailableUnits.COUNTY='Harrison' And TBL_AvailableUnits.RENT>960
Or TBL_AvailableUnits.BEDROOMS=3 And
TBL_AvailableUnits.COUNTY='Harrison' And TBL_AvailableUnits.RENT>1251
Or TBL_AvailableUnits.BEDROOMS=4 And
TBL_AvailableUnits.COUNTY='Harrison' And
TBL_AvailableUnits.RENT>1285.5 Or TBL_AvailableUnits.BEDROOMS=1 And
TBL_AvailableUnits.COUNTY='Jackson' And TBL_AvailableUnits.RENT>763.5
Or TBL_AvailableUnits.BEDROOMS=2 And
TBL_AvailableUnits.COUNTY='Jackson' And TBL_AvailableUnits.RENT>916.5
Or TBL_AvailableUnits.BEDROOMS=3 And
TBL_AvailableUnits.COUNTY='Jackson' And TBL_AvailableUnits.RENT>1261.5
Or TBL_AvailableUnits.BEDROOMS=4 And
TBL_AvailableUnits.COUNTY='Jackson' And TBL_AvailableUnits.RENT>1804
Or TBL_AvailableUnits.BEDROOMS=1 And TBL_AvailableUnits.COUNTY='Pearl
River' And TBL_AvailableUnits.RENT>625 Or
TBL_AvailableUnits.BEDROOMS=2 And TBL_AvailableUnits.COUNTY='Pearl
River' And TBL_AvailableUnits.RENT>748.5 Or
TBL_AvailableUnits.BEDROOMS=3 And TBL_AvailableUnits.COUNTY='Pearl
River' And TBL_AvailableUnits.RENT>913.5 Or
TBL_AvailableUnits.BEDROOMS=4 And TBL_AvailableUnits.COUNTY='Pearl
River' And TBL_AvailableUnits.RENT>1288.5,'n',Null))

I think I would probably try developing a table tblFair with
Bedrooms
County
RentCutoff


And use that to decide the value FMR
UPDATE tbl_AvailableUnits As T INNER JOIN tblFair as F
On T.Bedrooms = F.Bedrooms
and T.County = F.County
Set FMR = IIF( T.[Rent] <= F.[Rent],'y','n')

It would certainly be more maintainable then that very long where statement.
All you would have to do would be to edit the table and if you needed to add
checks for 5 bedrooms you would add a record and if the rates changed you
could change the rate in the table or if additional counties were needed
just add records.

AND you really could just do the calculations on the fly instead of storing
the data.



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Just out of curiousity, which solution did you use? I hope the table driven
one.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top