use different equations in same query field

[KimD]

I am trying to set up a query to calculate flow from a table which contains
date and other relevant information. The calculation changes depending on
the date. For example, the table is called FlowDataRearragned and has the
fields [FlowDate] (formated as short date), [InfluentFlow] and
[EffluentFlow]. I'm calculating [EffluentFlow] from an Update query, but
need to change the equation depending on which month the [FlowDate]
represents. The current SQL looks like this:

UPDATE FlowDataRearranged SET FlowDataRearranged.[EffluentFlow] =
(([FlowDataRearranged]![Influentflow])+(([FlowDataRearranged]![Rain]-(0.194*7))/12)*1200*7.481)
WHERE (((FlowDataRearranged.[Effluent Flow]) Is Null) AND
((FlowDataRearranged.FlowDateandTime) Between #8/1/2008# And #8/30/2008#))

I want to add a portion uses a slightly different equation for Sept, another
for Oct and so on. I need the calculated data to all be in one field, and
ideally dependant only on the month, as the project will span multiple years.
Using "Or" between each additional equation isn't working. Any help will be
greatly appreciated.

Thank you in advance,

 

[John W. Vinson]

I am trying to set up a query to calculate flow from a table which contains
date and other relevant information. The calculation changes depending on
the date. For example, the table is called FlowDataRearragned and has the
fields [FlowDate] (formated as short date), [InfluentFlow] and
[EffluentFlow]. I'm calculating [EffluentFlow] from an Update query, but
need to change the equation depending on which month the [FlowDate]
represents. The current SQL looks like this:

UPDATE FlowDataRearranged SET FlowDataRearranged.[EffluentFlow] =
(([FlowDataRearranged]![Influentflow])+(([FlowDataRearranged]![Rain]-(0.194*7))/12)*1200*7.481)
WHERE (((FlowDataRearranged.[Effluent Flow]) Is Null) AND
((FlowDataRearranged.FlowDateandTime) Between #8/1/2008# And #8/30/2008#))

I want to add a portion uses a slightly different equation for Sept, another
for Oct and so on. I need the calculated data to all be in one field, and
ideally dependant only on the month, as the project will span multiple years.
Using "Or" between each additional equation isn't working. Any help will be
greatly appreciated.

By "dependent on the month" do you mean that the equation is exactly the same
in October 2005, October 2008, and October 2012? If so, I'd create a
twelve-row table with an Integer MonthNo primary key and the parameters for
that month; Join this to your FlowDataRearranged table by joining
Month(FlowDateAndTime]) to MonthNo.

If the parameters change every month and are different in different years,
then use a table with a date/time field for the first day of the month, and
join using DateSerial(Year([FlowDateandTime]), Month([FlowDateandtime]), 1) to
join to the parameters table.

 

[KimD]

J. Vinson: To answer, yes, the equation would be the same in each month of
each year, but will be different for Jan, Feb, Mar and so on. I import the
Influent Flow data monthly, and keep a running table for that. I need to
calculate the effluent flow and perform additional analysis on those values.
The update table query seems to work if I can perform multiple calculations
on one field of the same table. I've seen this done in SAS, and thought it
could be done in Access. I just don't know the SQL language to get there.