Tidy up Queries

[Space Cowboy]

Good Morning Good People,

I have ended up with 2 queries feeding data to third query.
I have to run query1 to feed data to query2 (run it) and then feed the data to query3.
It is getting messy and I want simplify to 1 overall query which in essence will run the first two, in order, and thus generate the final query.

I have used a "nesting" process in the past, with other operations, (boolean algebra), is a similar function available in access via SQL?

 

[ebs17]

feed the data to query3

How does this work?

Select queries are simply executed and produce a result. If you use such a query (stored query or subquery as an SQL statement) in the FROM part of another query, this "embedded" query is executed first, so the data is immediately and up-to-date available to the query.

 

[CJ_London]

You can alias the first two queries into the third query

Not sure why you feel the need to ‘run’ the queries in order - sql does that automatically

 

[Space Cowboy]

How does this work?

Select queries are simply executed and produce a result. If you use such a query (stored query or subquery as an SQL statement) in the FROM part of another query, this "embedded" query is executed first, so the data is immediately and up-to-date available to the query.

Apologies for my poor terminology, I run the third query and it looks up data from the second query(which references the first query).

The database I am calling from produces new data every day, so need to run query1 to obtain the new source data to work with in query2 and then query3.

 

[plog]

You can build queries out of queries.

My guess is your first query is a MAKE TABLE query that generated a table--tblQ1. Then the second query is itself a MAKE TABLE query based on tblQ1 that creates a table itself--tblQ2. Then the third query is based on tblQ2 and produces the final data you need. How close am I?

You shouldn't do that. Make your first and second queries SELECT queries and just use them as the sources down the line---dont make any tables.

 

[Space Cowboy]

@ebs17

Can I put a "from" within a "from" ?

Is there anywhere that you could direct me that shows example code?

 

[Space Cowboy]

You can build queries out of queries.

My guess is your first query is a MAKE TABLE query that generated a table--tblQ1. Then the second query is itself a MAKE TABLE query based on tblQ1 that creates a table itself--tblQ2. Then the third query is based on tblQ2 and produces the final data you need. How close am I?

You shouldn't do that. Make your first and second queries SELECT queries and just use them as the sources down the line---dont make any tables.

Hi plog.
Theses are all select queries.
And the reason that I want to "tidy up"
I would rather take the pain now and sort it out rather than keep running them all each day

 

[Space Cowboy]

You can alias the first two queries into the third query

Not sure why you feel the need to ‘run’ the queries in order - sql does that automatically

Hi Cj
do you know where I could find an example code for aliasing?

 

[CJ_London]

you don't make it easy for us to help - provide the sql to your 3 queries for context

here is an example
Q1: SELECT * FROM tableA
Q2: SELECT * FROM Q1
Q3: SELECT * FROM Q2

alternative for Q3 with aliased queries
SELECT *
FROM (SELECT * FROM (SELECT * FROM tableA) AS Q1) AS Q2

 

[Space Cowboy]

q1 code
SELECT decrace.drdate, decrace.drid, decrace.drcname, CCur(Mid([drprize],1)) AS PrizeConvert
FROM decrace INNER JOIN course ON decrace.drcname = course.cname
WHERE (((decrace.drdate)=Date()) AND ((course.ccountry)="GB"))
ORDER BY decrace.drcname, CCur(Mid([drprize],1)) DESC;

q2 code
SELECT Q1.drcname, Sum(Q1.PrizeConvert) AS SumOfPrize
FROM Q1
GROUP BY Q1.drcname;

q3 code
SELECT Q2.drcname, DCount("*","Q2","SumofPrize>=" & [sumofprize]) AS DayRank, Q2.SumOfPrize, Q1.PrizeConvert, DCount("*","Q1","PrizeConvert>=" & [PrizeConvert] & " AND drcname='" & [Q2].[drcname] & "'") AS PrizeRank
FROM Q2 INNER JOIN Q1 ON Q2.drcname = Q1.drcname
ORDER BY DCount("*","Q2","SumofPrize>=" & [sumofprize]), DCount("*","Q1","PrizeConvert>=" & [prizeconvert] & " AND drcname='" & [Q2].[drcname] & "'");


Does the code you showed
SELECT *
FROM (SELECT * FROM (SELECT * FROM tableA) AS Q1) AS Q2
go at the top so that it is run first ?
then my q3 code?

 

[ebs17]

Q2 code modified

SELECT Q1.drcname, Sum(Q1.PrizeConvert) AS SumOfPrize
FROM (SELECT ...) AS Q1
GROUP BY Q1.drcname;

detailed

SELECT
   Q1.drcname,
   SUM(Q1.PrizeConvert) AS SumOfPrize
FROM
   (
      SELECT
         decrace.drdate,
         decrace.drid,
         decrace.drcname,
         CCur(Mid([drprize], 1)) AS PrizeConvert
      FROM
         decrace
            INNER JOIN course
            ON decrace.drcname = course.cname
      WHERE
         decrace.drdate = Date()
            AND
         course.ccountry = "GB"
   ) AS Q1
GROUP BY
   Q1.drcname

The query execution always begins with the evaluation of the FROM part. If a query is contained there, it is executed first.
When nested, the execution always takes place from the inside out. That is why I have omitted the ORDER clause of Q1 in this example because it no longer plays a role and only means additional and unnecessary effort.

Otherwise, the query shown is the same as your Q2 code.

You could use the SQL statements from Q1 and Q2 in the same way in query Q3: but this would make the overall SQL statement too confusing for most people, and there are very few advantages to doing so.
So when you call Q3, Q1 and Q2 will be called and executed automatically, Q3 will then use this provided data.

The calculation of DayRank and PrizeRank is systematically very complex, so that you can run into performance problems with larger numbers of records. This is made worse by the fact that in Q1 there is an expression like CCur(Mid([drprize],1)) AS PrizeConvert.
A price should come from the table as a currency value and not have to be converted first. Calculating the table value prevents the use of a possible index and will therefore cost performance.
As the queries are executed from the inside out, problems with the inner queries are also passed on to the outside and often even multiplied.

 

[cheekybuddha]

CCur(Mid([drprize],1)) AS PrizeConvert

What table is field drpize from?

What is the datatype of that field? Is it really a string?

 

[Space Cowboy]

What table is field drpize from?

What is the datatype of that field? Is it really a string?

There appears to be an idiosyncrasy within the data set, current(live data) uses a string, But yet historical data uses a number.
I am trying to run the same set of queries in both the Live data and then again in historical data.
I am in the very early stages and trying to create a historical dataset that I can then work with and expand to incorporate other data that is relevant.

The drpize field contains a "£" sign and would not calculate, I Assumed it was a string without actually checking(if I am honest I dont know how to, I am completely new to all this) and when proffered the solution it worked.

Thankfully the "live" analysis is only 50-100 records so performance(I assume) will not be impacted. It is working very well, not instant but less than a second.

 

[cheekybuddha]

What table is field drpize from?

With that table, turn it to Design View.

Look at that field and post back here the datatype for the field (and hopefully the answer to the above question too! )

 

[Space Cowboy]

With that table, turn it to Design View.

Look at that field and post back here the datatype for the field (and hopefully the answer to the above question too! )

Hopefully for you maybe.
When I check properties it only states "source"

 

[Pat Hartman]

Can I put a "from" within a "from" ?

No, Queries are interchangeable with tables for most actions. So, if you save the first two select queries, then when you create the third query, you can select the names of the other two saved querydefs and they will pull the data from the linked tables.

 

[Space Cowboy]

With that table, turn it to Design View.

Look at that field and post back here the datatype for the field (and hopefully the answer to the above question too! )

Happened on this by accident, but now I know how to do it.
Data type is "Short Text"