I have several queries for each gaging stations that are run to update the data table. All of these queries have a similar structure except for range values, site serials, and CountyID. How do I combine these queries to form a single query that would update records for all the gages?
For Gage1:
For Gage2:
For Gage1:
Code:
INSERT INTO [LRN-1705_WLs] ( [Day], CompWL, CountWL, site_serial, CountyID )
SELECT Format([Date_time],"mm/dd/yyyy") AS Expr1, Avg([MPC]-[MPH]-([ch1_data_P]-2.307*(IIf((([ch2_data_P]>=13.71) And ([ch2_data_P]<=15.06)),[ch2_data_P],Null)))) AS CompWL, Count(logger_data.ch1_data_P) AS Expr2, logger_data.site_serial, MPCable.CountyID
FROM logger_data INNER JOIN MPCable ON logger_data.site_serial = MPCable.site_serial
GROUP BY Format([Date_time],"mm/dd/yyyy"), logger_data.site_serial, MPCable.CountyID
HAVING (((logger_data.site_serial)=9100346));For Gage2:
Code:
INSERT INTO [GRV-3342_WLs] ( [Day], CompWL, CountWL, site_serial, CountyID )
SELECT Format([Date_time],"mm/dd/yyyy") AS Expr1, Avg([MPC]-[MPH]-([ch1_data_P]-2.307*(IIf((([ch2_data_P]>=13.5) And ([ch2_data_P]<=14.84)),[ch2_data_P],Null)))) AS CompWL, Count(logger_data.ch1_data_P) AS Expr2, logger_data.site_serial, MPCable.CountyID
FROM logger_data INNER JOIN MPCable ON logger_data.site_serial = MPCable.site_serial
GROUP BY Format([Date_time],"mm/dd/yyyy"), logger_data.site_serial, MPCable.CountyID
HAVING (((logger_data.site_serial)=9100399));
