Possible to combine two queries?

[Bratlien]

I have two queries that I am interested in combining into one if possible. I'm trying to learn Access and SQL on-the-fly, so feel free to point out any noob mistakes I am making.

The first query simply pulls certain records from a table:

SELECT Sensor5.LaneName, Sensor5.SensorTime, Sensor5.Speed, Sensor5.Volume
FROM Sensor5
WHERE (((Sensor5.LaneName)="NB1" Or (Sensor5.LaneName)="NB2" Or (Sensor5.LaneName)="NB3") AND ((Sensor5.Volume)>0) AND ((Sensor5.SensorDate)="4/17/2007" Or (Sensor5.SensorDate)="4/18/2007" Or (Sensor5.SensorDate)="4/19/2007" Or (Sensor5.SensorDate)="4/20/2007" Or (Sensor5.SensorDate)="4/23/2007" Or (Sensor5.SensorDate)="4/24/2007" Or (Sensor5.SensorDate)="4/25/2007" Or (Sensor5.SensorDate)="4/26/2007" Or (Sensor5.SensorDate)="4/27/2007"));

The second query then takes averages and sums from this first query, grouping by a third field (SensorTime). This results in weeks of data being compiled into a single record for each time interval in a 24-hour period. See below:

SELECT SpeedWeekday5NB.SensorTime, Avg(SpeedWeekday5NB.Speed) AS AvgSpeed, Sum(SpeedWeekday5NB.Volume) AS SumVolume
FROM SpeedWeekday5NB
GROUP BY SpeedWeekday5NB.SensorTime;

Is there any way I can streamline this process by combining the two queries into a more complex single query, or should I leave things as-is? Any advice is much appreciated!

 

[Capo]

Just off the top of my head, you could always build your queries as you wish and then simply create a macro to run them automatically (make sure you turn off warnings, of course).

 

[boblarson]

(make sure you turn off warnings, of course).

Not a good idea (and you don't have to turn off warnings if you use CurrentDB.Execute "querynamehere"), and in this case due to the fact that they are select queries and not action queries (append, update or delete) the warnings are not an issue.

You should be able to create one query and then use it to populate the other one by selecting it in the QBE grid.

 

[Capo]

Can you elaborate on the difference between the two beyond just turning off warnings? Let's say I have a report which is generated through a 20 step query process, I've tested it, know it works, etc., why would I still need warnings on?

Just trying to understand, still a newbie myself.

 

[Bratlien]

You should be able to create one query and then use it to populate the other one by selecting it in the QBE grid.

I've already constructed the second query and it works, but I was just curious whether my queries were simple enough to combine them into one "uber-query" of sorts. It isn't absolutely necessary to the database; just something that could streamline the process and save time on similar projects in the future.

 

[boblarson]

Can you elaborate on the difference between the two beyond just turning off warnings? Let's say I have a report which is generated through a 20 step query process, I've tested it, know it works, etc., why would I still need warnings on?

Just trying to understand, still a newbie myself.

Call each of the queries using CurrentDb.Execute "querynamehere" instead of the DoCmd.OpenQuery command (by the way it's less typing too) and then you don't have to turn off the warnings. Warnings by the way also include the ones you get when you edit a form and then close it and it asks you if you want to save. If you turn off warnings, and don't turn them back on again, you will not get those messages and your design changes won't save unless you specifically hit the save button before closing the form. So, that's why I always tell people to have an error handler when they use the set warnings command to turn them back on, even if they do turn them back on in their code because if the procedure fails at any point it will not turn them back on for you.