Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-07-2019, 12:45 PM   #1
Roger9
Newly Registered User
 
Join Date: Aug 2019
Posts: 7
Thanks: 5
Thanked 0 Times in 0 Posts
Roger9 is on a distinguished road
Combine queries

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));

Roger9 is offline   Reply With Quote
Old 08-07-2019, 12:49 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,608
Thanks: 50
Thanked 1,052 Times in 1,033 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Combine queries

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 08-07-2019, 12:52 PM   #3
Roger9
Newly Registered User
 
Join Date: Aug 2019
Posts: 7
Thanks: 5
Thanked 0 Times in 0 Posts
Roger9 is on a distinguished road
Re: Combine queries

Ah! Good to know

Roger9 is offline   Reply With Quote
Old 08-07-2019, 12:58 PM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,608
Thanks: 50
Thanked 1,052 Times in 1,033 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Combine queries

Quote:
Originally Posted by Roger9 View Post
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!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Roger9 (08-21-2019)
Old 08-07-2019, 12:59 PM   #5
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,356
Thanks: 112
Thanked 2,843 Times in 2,593 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Combine queries

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 08-07-2019 at 01:06 PM.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Roger9 (08-21-2019)
Old 08-07-2019, 01:03 PM   #6
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,350
Thanks: 10
Thanked 2,271 Times in 2,223 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Combine queries

Quote:
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.
plog is online now   Reply With Quote
Old 08-07-2019, 01:08 PM   #7
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,270
Thanks: 0
Thanked 532 Times in 528 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Combine queries

Why do you have multiple identical tables to begin with? Have 1 table with another field for gage identifier.

Why saving aggregate data?

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 08-07-2019, 06:00 PM   #8
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,149
Thanks: 15
Thanked 1,572 Times in 1,494 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Combine queries

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
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
Roger9 (08-21-2019)
Old 08-21-2019, 08:26 AM   #9
Roger9
Newly Registered User
 
Join Date: Aug 2019
Posts: 7
Thanks: 5
Thanked 0 Times in 0 Posts
Roger9 is on a distinguished road
Re: Combine queries

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!
Roger9 is offline   Reply With Quote
Old 08-21-2019, 08:37 AM   #10
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,608
Thanks: 50
Thanked 1,052 Times in 1,033 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Combine queries

Quote:
Originally Posted by Roger9 View Post
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.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Reply

Tags
combine query , querry

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Combine MS queries bennyboo Queries 4 09-05-2016 05:04 PM
combine 2 queries smile Queries 11 05-21-2008 12:31 PM
Combine queries thart21 Modules & VBA 3 04-28-2008 02:15 PM
Combine two queries Mike Hughes Queries 4 08-03-2007 01:37 PM
combine two sql queries - see example thydzik Queries 4 07-17-2006 10:28 PM




All times are GMT -8. The time now is 07:50 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World