Combine queries (1 Viewer)

Roger9

New member
Local time
Today, 07:10
Joined
Aug 7, 2019
Messages
7
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:
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));
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:10
Joined
Oct 29, 2018
Messages
21,357
Hi. I could be wrong but I'm not sure you can combine those queries into one because I think an INSERT query can only add records to one table at a time.
 

Roger9

New member
Local time
Today, 07:10
Joined
Aug 7, 2019
Messages
7
Ah! Good to know
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:10
Joined
Oct 29, 2018
Messages
21,357
Ah! Good to know
I guess what you could try is to create a query joining those two tables and see if you can add records to both tables at the same time through that query. If you can, then you might be able to combine your queries after all. I just can't guarantee it since I haven't tried it before. Good luck!
 

isladogs

MVP / VIP
Local time
Today, 14:10
Joined
Jan 14, 2017
Messages
18,186
Agree with DBGs original response - not sure about the suggestion in the second reply.
However you could easily create a procedure to run each query or SQL statement in turn

Also, welcome to AWF
 
Last edited:

plog

Banishment Pending
Local time
Today, 09:10
Joined
May 11, 2011
Messages
11,611
that are run to update the data table.

That's not correct. Those queries you posted are adding data to different tables. Why?

Code:
INSERT INTO [LRN-1705_WLs] ( [Day], CompWL, CountWL, site_serial, CountyID )
...
INSERT INTO [GRV-3342_WLs] ( [Day], CompWL, CountWL, site_serial, CountyID )
...

Your data doesn't seem normalized to me. You seem to be storing data in your table names. Tables should have generic names (e.g. tblGages, tblReadings, etc.). Your table names seem to be data themselves.

Tables with similar structures (both your tables in above have the same 6 fields) but different names which help differentiate the data means you've done it wrong. Instead of have the table name differentiate them, you need all the data in the same table with a field to differentiate them. That means 'LRN-1705_WLS' and 'GRV-3342_WLs' go into the data not the table name and you make the table name generic.

Do that and the INSERT queries can become an INSERT query pretty easily.
 

June7

AWF VIP
Local time
Today, 06:10
Joined
Mar 9, 2014
Messages
5,423
Why do you have multiple identical tables to begin with? Have 1 table with another field for gage identifier.

Why saving aggregate data?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:10
Joined
Feb 19, 2002
Messages
42,970
You can see our confusion. You are making temporary tables and you have selection criteria that is hardcoded. You probably don't need to make temp tables and you should probably not be hardcoding the criteria.

Proceeding with that as the basis, change the query to a select query. You can then export the query, if that is what you are doing with the data or you can use the query in a report or a form. Select queries can be used almost interchangeably with tables. Running make table queries, causes bloat and in the case of your example, it seems to require making a separate table for each site_serial.

Regarding selecting multiple sets of data, the Where clause can contain multiple criteria.

WHERE logger_data.site_serial = Forms!yourformname!site1 OR logger_data.site_serial = Forms!yourformname!site2

To handle criteria, add fields to forms and fill them in so you don't have to keep modifying database objects.

And finally, in these queries, the HAVING should be a WHERE. WHERE is applied before the data is aggregated. HAVING is applied after. HAVING should be applied to aggregated data rather than static data.

HAVING Avg(somefield) > 1000
 

Roger9

New member
Local time
Today, 07:10
Joined
Aug 7, 2019
Messages
7
Thank you all for your input and apologies for late response! I looked at the data and as most of the comments pointed out, there's no need to have an individual query for each gage station. I worked on it and created a single query using switch and IIf function. My approach is very basic and I am sure there's better ways of getting the desired output. I would like to get feedback on ways of improving my query structure. However, I should probably start a new discussion as I would like to add details on what I am trying to work on. Once again, thank you all for the comments!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:10
Joined
Oct 29, 2018
Messages
21,357
Thank you all for your input and apologies for late response! I looked at the data and as most of the comments pointed out, there's no need to have an individual query for each gage station. I worked on it and created a single query using switch and IIf function. My approach is very basic and I am sure there's better ways of getting the desired output. I would like to get feedback on ways of improving my query structure. However, I should probably start a new discussion as I would like to add details on what I am trying to work on. Once again, thank you all for the comments!
Hi. Thanks for the update. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom