Nesting queries into nested queries, into nested queries - help!

[BeardedSith]

Hey all!

@plog has done some amazing work helping me fumble through this SQL mess. Now that he's solved the most pressing issues with my queries, I was hoping I could nab some help with trying to nest them all into one query I can call on demand. Spending roughly ten minutes on this I got myself so confused I had to put it down . Here are my multiple SQL statements:

Working backwards in the chain of queries:

qryEligibility - what I would prefer to have everything else merged into:

SELECT sqryCalc3.CustomerID, IIf([sqryCalc3]![SumOfPurchaseAmount]>[tblOptions]![MaxIssue],[tblOptions]![MaxIssue],[sqryCalc3]![SumOfPurchaseAmount]) AS RealAmount, sqryCalc3.RealDate, IIf([sqryCalc3]![NumPurchases]>[tblOptions]![NumPurchases],1,0) AS IsEligible
FROM tblOptions, sqryCalc3
WHERE (((IIf([sqryCalc3]![NumPurchases]>[tblOptions]![NumPurchases],1,0))=1));

All of these other queries (four in total) are support for the query above:

sqryCalc3

SELECT sqryCalc1.CustomerID, Count(sqryCalc2.MemberID) AS NumPurchases, Sum(Nz([DailyPurchaseTotal],0)) AS SumOfPurchaseAmount, sqryCalc1.RealDate
FROM sqryCalc1 LEFT JOIN sqryCalc2 ON sqryCalc1.CustomerID = sqryCalc2.MemberID
GROUP BY sqryCalc1.CustomerID, sqryCalc1.RealDate;

sqryCalc2

SELECT sqryCalc0.MemberID, sqryCalc0.DatePurchase, Sum(sqryCalc0.PurchaseAmount) AS DailyPurchaseTotal
FROM sqryCalc0 INNER JOIN sqryCalc1 ON sqryCalc0.MemberID = sqryCalc1.CustomerID
WHERE (((sqryCalc0.DatePurchase)>[RealDate] And (sqryCalc0.DatePurchase)>CDate("1/1/" & Year(Date()))))
GROUP BY sqryCalc0.MemberID, sqryCalc0.DatePurchase;

sqryCalc1

SELECT tblRewards.CustomerID, IIf(Max([tblRewards]![IssueDate]<DateSerial(Year(Now()),1,1)),DateSerial(Year(Now()),1,1),Max([tblRewards]![IssueDate])) AS RealDate
FROM tblRewards
GROUP BY tblRewards.CustomerID;

sqryCalc0

SELECT tbPurchases.MemberID, DateValue([PurchaseDate]) AS DatePurchase, tbPurchases.PurchaseAmount
FROM tbPurchases;

BACKGROUND:
These queries are intended to pull and calculate eligibility based on several parameters set forth in tblOptions. These queries use tbPurchases, tblRewards (yes, I left out an "L" in tbPurchases), and tblOptions which only has a single row for options within the database.

I would prefer the ability to conduct all of these calculations within a single query that I can call when necessary. If anyone would like example data, I can provide that as requested.

Thank you in advance!

 

[The_Doc_Man]

In your query qryEligibility you have this: FROM tblOptions, sqryCalc3. The problem is that the WHERE clause doesn't act to restrict the relation between tblOptions and sqryCalc3, so because of that comma (i.e. not a JOIN clause) you are going to get what is called a Cartesian JOIN. This is going to result in (essentially) a matrix of all possible combinations of every record in each of your two record sources. If the table and that query each produced 10 lines, the resultant output would have 100 lines. Since you are building in SQL directly, it doesn't matter whether there are relationships to be exploited. If you give Access the SQL it will do its darndest to execute what you gave it. Wizards never come into play.

The other cases look more or less legal. I.e. Calc2 certain CAN join Calc0 and Calc1 and that should work. Calc3 can join Calc1 and Calc2 and I don't see why that wouldn't work, although you ARE joining Calc1 twice in this sequence. So if you are having any trouble, it would have to be with that final layer for eligibility.

 

[BeardedSith]

Can you think of a way around the issues within tblOptions? I really don't want to hard-set certain aspects of the database (percentage reward, etc.) in case they ever decide to change that in the future.

Outside of that and the Calc1 being twice, is it possible? If not, I'll cope with it. This is more an aesthetics thing anyway.

 

[The_Doc_Man]

Your response confuses me. What issues with tblOptions are we discussing? The FROM clause should JOIN tblOptions with sqryCalc3 on some field, but if that isn't possible then the WHERE clause should restrict returned records in some way. But when I look at the WHERE clause in qryEligibility, that IIF is overly tortuous.

What you wrote:

WHERE (((IIf([sqryCalc3]![NumPurchases]>[tblOptions]![NumPurchases],1,0))=1))

What you need

WHERE [sqryCalc3]![NumPurchases]>[tblOptions]![NumPurchases]

You were using the IIF to produce either 1 or 0, which means that was behaving like a Boolean expression, and then selecting the TRUE side of the IIF. So... make it a Boolean expression and stop simulating it.

Other than a question of the logic involved, there is no issue of that multiple JOIN to the same thing in two different layers. It is certain legal, and if that is the way the relationship goes, it should not be an issue.

 

[Lightwave]

On a bit of a tangent please be aware that I think Access at least 2003 (I suspect it is the same for all other versions) could only handle about 13 nested iifs in a single SQL statement before it errors.

I was once involved in a ETL project and had to replace a lot of values in a table I ended up writing a function that could batch a translation table into update statements each with IIFs taken from a table. I went ahead and created a statement with about 80IIFs in it. Access said NO.

Function to create nested IIFs

As a result of this limitation I adapted the above code so I could split say what would have been a 120 IIF statement into 10 x 12 IIF statements

Create batches of IIF statements..

It was only afterwards that I realised that I could have used the translation table as a dependent table and just joined on the column and essentially aliased the values much like a lookup on a form. I was very focused on numbers being Foreign Keys but really a text variable can also form the basis of a foreign key. A really wow moment that would have been a much more elegant solution for the discrete value translation problem I was facing. Clearly this is not relevant to this case as all your nestings appear to be different and they appear to be ranges.

That ETL project introduced me to writing VBA to create SQL - which is a great little trick for hammering out repetative SQL on big projects.

I have had a soft spot for code generators every since.

 

[CJ_London]

a trick to develop nested queries using the query builder is to do the following

1. create your first query in the query builder e.g. SELECT ID, LastName FROM myTable
2. go to the sql builder and modify as follows SELECT ID FROM (SELECT ID, LastName FROM myTable) Q1
3. return to query builder and repeat.

with regards your first query

SELECT tblRewards.CustomerID, IIf(Max([tblRewards]![IssueDate]<DateSerial(Year(Now()),1,1)),DateSerial(Year(Now()),1,1),Max([tblRewards]![IssueDate])) AS RealDate
FROM tblRewards
GROUP BY tblRewards.CustomerID;

an alternative could be written like this to avoid iifs

SELECT CustomerID, Max(dateadd("d",(year([IssueDate])<year(date()))*(datepart("d",date())-1),[IssueDate])) AS RealDate
FROM tblRewards
GROUP BY CustomerID;

 

[cheekybuddha]

Does your query sqryCalc1 work correctly?

The condition in the IIf() for RealDate looks wrong.

At present it is:

IIf(
  Max([tblRewards]![IssueDate]<DateSerial(Year(Now()),1,1)),   --   <-- Not really going to test a comparison
  DateSerial(Year(Now()),1,1),
  Max([tblRewards]![IssueDate])
) AS RealDate

Oughtn't it be:

IIf(
  Max([tblRewards]![IssueDate]) < DateSerial(Year(Now()),1,1),
  DateSerial(Year(Now()),1,1),
  Max([tblRewards]![IssueDate])
) AS RealDate

Assuming so, your whole SQL can be written:

SELECT
  t3.CustomerID,
  IIf(
    t3.SumOfPurchaseAmount > tblOptions.MaxIssue,
    o.MaxIssue,
    t3.SumOfPurchaseAmount
  ) AS RealAmount,
  t3.RealDate,
  IIf(t3.NumPurchases > o.NumPurchases, 1, 0) AS IsEligible
FROM
  tblOptions o,
  (
    SELECT
      t1.CustomerID,
      COUNT(r2.MemberID) AS NumPurchases,
      SUM(Nz(DailyPurchaseTotal, 0)) AS SumOfPurchaseAmount,
      t1.RealDate
    FROM (
      SELECT
        r.CustomerID,
        IIf(
          MAX(r.IssueDate) < DateSerial(Year(Now()), 1, 1),
          DateSerial(Year(Now()),1,1),
          MAX(r.IssueDate)
        ) AS RealDate
      FROM tblRewards r
      GROUP BY r.CustomerID
    ) t1
    LEFT JOIN (
      SELECT
        t0.MemberID,
        t0.DatePurchase,
        SUM(t0.PurchaseAmount) AS DailyPurchaseTotal
      FROM (
        SELECT
          p.MemberID,
          DateValue(p.PurchaseDate) AS DatePurchase,
          p.PurchaseAmount
        FROM tbPurchases p
      ) t0
      INNER JOIN (
        SELECT
          r.CustomerID,
          IIf(
            MAX(r.IssueDate) < DateSerial(Year(Now()), 1, 1),
            DateSerial(Year(Now()),1,1),
            MAX(r.IssueDate)
          ) AS RealDate
        FROM tblRewards r
        GROUP BY r.CustomerID
      ) t1_2
              ON t0.MemberID = t1_2.CustomerID
      WHERE t0.DatePurchase > t1_2.RealDate
        AND t0.DatePurchase > CDate("1/1/" & Year(Date()))
      GROUP BY
        t0.MemberID,
        t0.DatePurchase
    ) t2
          ON t1.CustomerID = r2.MemberID
    GROUP BY
      t1.CustomerID,
      t1.RealDate   
  ) t3
WHERE IIf(t3.NumPurchases > o.NumPurchases, 1, 0) = 1;

However, to be honest, the whole query doesn't make much sense!

As The_Doc_Man points out you have a cartesian join, and also sqryCalc3 joins sqryCalc2 to sqryCalc1 even though sqryCalc2 already includes a join to sqryCalc1.

Are you able to post a cut down db with just the relevant tables/queries and enough representative data?

 

[BeardedSith]

Hey guys, sorry I haven't been able to get back to this for a few days, been busy around the house.

I'll work my way backwards here.
@cheekybuddha - The first expression and the second expression are the same, aren't they? Minus the spacing? To answer your question, it works perfectly. I also tried your example SQL statement but I'm getting Syntax errors in the JOIN operation. It then highlights this part in the Query Design tool: Expr1: t3.CustomerID. I don't really think that's the problem, but either way, Access is automatically "repairing" (Is that really the right word?) the SQL syntax to be this:

SELECT t3.CustomerID AS Expr1, IIf(t3.SumOfPurchaseAmount>tblOptions.MaxIssue,o.MaxIssue,t3.SumOfPurchaseAmount) AS RealAmount, t3.RealDate AS Expr2, IIf(t3.NumPurchases>o.NumPurchases,1,0) AS IsEligible
FROM tblOptions AS o, (SELECT
      t1.CustomerID,
      COUNT(r2.MemberID) AS NumPurchases,
      SUM(Nz(DailyPurchaseTotal, 0)) AS SumOfPurchaseAmount,
      t1.RealDate
    FROM (
      SELECT
        r.CustomerID,
        IIf(
          MAX(r.IssueDate) < DateSerial(Year(Now()), 1, 1),
          DateSerial(Year(Now()),1,1),
          MAX(r.IssueDate)
        ) AS RealDate
      FROM tblRewards r
      GROUP BY r.CustomerID
    ) t1
    LEFT JOIN (
      SELECT
        t0.MemberID,
        t0.DatePurchase,
        SUM(t0.PurchaseAmount) AS DailyPurchaseTotal
      FROM (
        SELECT
          p.MemberID,
          DateValue(p.PurchaseDate) AS DatePurchase,
          p.PurchaseAmount
        FROM tbPurchases p
      ) t0
      INNER JOIN (
        SELECT
          r.CustomerID,
          IIf(
            MAX(r.IssueDate) < DateSerial(Year(Now()), 1, 1),
            DateSerial(Year(Now()),1,1),
            MAX(r.IssueDate)
          ) AS RealDate
        FROM tblRewards r
        GROUP BY r.CustomerID
      ) t1_2
              ON t0.MemberID = t1_2.CustomerID
      WHERE t0.DatePurchase > t1_2.RealDate
        AND t0.DatePurchase > CDate("1/1/" & Year(Date()))
      GROUP BY
        t0.MemberID,
        t0.DatePurchase
    ) t2
          ON t1.CustomerID = r2.MemberID
    GROUP BY
      t1.CustomerID,
      t1.RealDate   
  )  AS t3
WHERE (((IIf([t3].[NumPurchases]>[o].[NumPurchases],1,0))=1));

@CJ_London - I tried just plugging in each query into it's specific area and things got all sorts of messed up. I think the JOINs and logical tests are causing things to go wonky. This mass of SQL is far beyond anything I could easily plug & play with.

 

[cheekybuddha]

>> The first expression and the second expression are the same, aren't they? Minus the spacing? <<
No, check the brackets

I'll have to look at the SQL again when I have a bit more time later.

 

[BeardedSith]

>> The first expression and the second expression are the same, aren't they? Minus the spacing? <<
No, check the brackets

I'll have to look at the SQL again when I have a bit more time later.

I see it now, thanks! Is it weird that the old SQL worked just fine?