Solved Help w/ query on previous value (1 Viewer)

foshizzle

Registered User.
Local time
Today, 04:29
Joined
Nov 27, 2013
Messages
277
I have a similar code working for a different query but i cant get this one to match up. I'm trying to obtain the previous value for fields "GallonsReceived" and "GallonsIssued" but the code is literally giving me the value of the "...PriorValue" fields instead of the original, non-calculated fields. Please reference screenshot below.

SQL:
SELECT tblTransactions.TransactionDate, tblTransactions.AirlineCompanyLookup, tblTransactions.GallonsReceived, (SELECT TOP 1 Dupe.GallonsReceived
    FROM tblTransactions AS Dupe
    WHERE Dupe.AirlineCompanyLookup = tblTransactions.AirlineCompanyLookup
    AND Dupe.TransactionDate < tblTransactions.TransactionDate) AS GallonsReceivedPriorValue, tblTransactions.GallonsIssued, (SELECT TOP 1 Dupe2.GallonsIssued
    FROM tblTransactions AS Dupe2
    WHERE Dupe2.AirlineCompanyLookup = tblTransactions.AirlineCompanyLookup
    AND Dupe2.TransactionDate < tblTransactions.TransactionDate) AS GallonsIssuedPriorValue
FROM tblTransactions
ORDER BY tblTransactions.TransactionDate DESC , tblTransactions.AirlineCompanyLookup;
 

Attachments

  • priorvalue.jpg
    priorvalue.jpg
    81.3 KB · Views: 480

CJ_London

Super Moderator
Staff member
Local time
Today, 08:29
Joined
Feb 19, 2013
Messages
16,553
when using TOP, you need to include an ORDER BY (in your subqueries) otherwise it will just be the top randomly selected records
 

foshizzle

Registered User.
Local time
Today, 04:29
Joined
Nov 27, 2013
Messages
277
when using TOP, you need to include an ORDER BY (in your subqueries) otherwise it will just be the top randomly selected records

Ok, I adjusted the code as follows but now receive the error "At most one record can be returned by this subquery"

*edit*
SELECT tblTransactions.TransactionDate,
tblTransactions.AirlineCompanyLookup,
tblTransactions.IssueTypeLookup,
tblTransactions.GallonsReceived,
(SELECT TOP 1 Dupe.GallonsReceived
FROM tblTransactions AS Dupe
WHERE Dupe.AirlineCompanyLookup = tblTransactions.AirlineCompanyLookup
AND Dupe.TransactionDate < tblTransactions.TransactionDate
ORDER BY Dupe.TransactionDate DESC, Dupe.AirlineCompanyLookup) AS GallonsReceivedPriorValue,
tblTransactions.GallonsIssued,
(SELECT TOP 1 Dupe2.GallonsIssued
FROM tblTransactions AS Dupe2
WHERE Dupe2.AirlineCompanyLookup = tblTransactions.AirlineCompanyLookup
AND Dupe2.TransactionDate < tblTransactions.TransactionDate) AS GallonsIssuedPriorValue
FROM tblTransactions
ORDER BY tblTransactions.TransactionDate DESC, tblTransactions.AirlineCompanyLookup;
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:29
Joined
May 7, 2009
Messages
19,169
there is an alternative without ever using ORDER BY.
create a query (Query1):

Code:
SELECT tblTransactions.AirlineCompanyLookup,
tblTransactions.TransactionDate,
tblTransactions.GallonsReceived,
tblTransactions.GallonsIssued,
(SELECT Count("1") FROM tblTransactions AS T WHERE T.AirlineCompanyLookup = tblTransactions.AirlineCompanyLookup AND T.TransactionDate <= tblTransactions.TransactionDate) AS Expr1
FROM tblTransactions;

from Query1, create another query (the final query, qryFinal):
Code:
SELECT Query1.TransactionDate,
    Query1.AirlineCompanyLookup,
    Query1.GallonsReceived,
    Query1_1.GallonsReceived AS GallonsReceivedPriorValue,
    Query1.GallonsIssued,
    Query1_1.GallonsIssued AS GallonsIssuedPriorValue
FROM Query1 LEFT JOIN Query1 AS Query1_1
ON
(Query1.AirlineCompanyLookup = Query1_1.AirlineCompanyLookup)
AND (Query1.Expr1-1 = (Query1_1.Expr1));
 

Attachments

  • TransGallon.zip
    27.3 KB · Views: 455

CJ_London

Super Moderator
Staff member
Local time
Today, 08:29
Joined
Feb 19, 2013
Messages
16,553
now receive the error "At most one record can be returned by this subquery"
that is because your data contains more than one record with the same transactiondate and lookup, you will need a pk or similar to ensure there is only one record.

If Arnel's suggestion does not produce the right result, suggest come back and this time provide some example data and the outcome required as your screenshot does not provide enough information.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:29
Joined
May 7, 2009
Messages
19,169
here is another way, to do it without using Sort Order:
Code:
SELECT
    tblTransactions.TransactionDate,
    tblTransactions.AirlineCompanyLookup,
    tblTransactions.GallonsReceived,
    (SELECT GallonsReceived FROM tblTransactions AS A
        WHERE A.AirlineCompanyLookup = tblTransactions.AirlineCompanyLookup
        AND A.TransactionDate =
            (SELECT MAX(B.TransactionDate) FROM tblTransactions AS B
                WHERE B.AirlineCompanyLookup = tblTransactions.AirlineCompanyLookup AND
                B.TransactionDate < tblTransactions.TransactionDate))
    AS GallonsReceivedPriorValue,
    tblTransactions.GallonsIssued,
    (SELECT GallonsIssued FROM tblTransactions AS A
        WHERE A.AirlineCompanyLookup = tblTransactions.AirlineCompanyLookup
        AND A.TransactionDate =
            (SELECT MAX(B.TransactionDate) FROM tblTransactions AS B
                WHERE B.AirlineCompanyLookup = tblTransactions.AirlineCompanyLookup AND
                B.TransactionDate < tblTransactions.TransactionDate))
    AS GallonsIssuedPriorValue
FROM tblTransactions
ORDER BY tblTransactions.TransactionDate DESC , tblTransactions.AirlineCompanyLookup;
 

foshizzle

Registered User.
Local time
Today, 04:29
Joined
Nov 27, 2013
Messages
277
here is another way, to do it without using Sort Order:
Code:
SELECT
    tblTransactions.TransactionDate,
    tblTransactions.AirlineCompanyLookup,
    tblTransactions.GallonsReceived,
    (SELECT GallonsReceived FROM tblTransactions AS A
        WHERE A.AirlineCompanyLookup = tblTransactions.AirlineCompanyLookup
        AND A.TransactionDate =
            (SELECT MAX(B.TransactionDate) FROM tblTransactions AS B
                WHERE B.AirlineCompanyLookup = tblTransactions.AirlineCompanyLookup AND
                B.TransactionDate < tblTransactions.TransactionDate))
    AS GallonsReceivedPriorValue,
    tblTransactions.GallonsIssued,
    (SELECT GallonsIssued FROM tblTransactions AS A
        WHERE A.AirlineCompanyLookup = tblTransactions.AirlineCompanyLookup
        AND A.TransactionDate =
            (SELECT MAX(B.TransactionDate) FROM tblTransactions AS B
                WHERE B.AirlineCompanyLookup = tblTransactions.AirlineCompanyLookup AND
                B.TransactionDate < tblTransactions.TransactionDate))
    AS GallonsIssuedPriorValue
FROM tblTransactions
ORDER BY tblTransactions.TransactionDate DESC , tblTransactions.AirlineCompanyLookup;

Hm, with this one, I get the same error message "At most one record can be returned by this subquery"
 

foshizzle

Registered User.
Local time
Today, 04:29
Joined
Nov 27, 2013
Messages
277
there is an alternative without ever using ORDER BY.
create a query (Query1):

Code:
SELECT tblTransactions.AirlineCompanyLookup,
tblTransactions.TransactionDate,
tblTransactions.GallonsReceived,
tblTransactions.GallonsIssued,
(SELECT Count("1") FROM tblTransactions AS T WHERE T.AirlineCompanyLookup = tblTransactions.AirlineCompanyLookup AND T.TransactionDate <= tblTransactions.TransactionDate) AS Expr1
FROM tblTransactions;

from Query1, create another query (the final query, qryFinal):
Code:
SELECT Query1.TransactionDate,
    Query1.AirlineCompanyLookup,
    Query1.GallonsReceived,
    Query1_1.GallonsReceived AS GallonsReceivedPriorValue,
    Query1.GallonsIssued,
    Query1_1.GallonsIssued AS GallonsIssuedPriorValue
FROM Query1 LEFT JOIN Query1 AS Query1_1
ON
(Query1.AirlineCompanyLookup = Query1_1.AirlineCompanyLookup)
AND (Query1.Expr1-1 = (Query1_1.Expr1));

I get a circular reference when adding to mine but give me some time to compare. thanks!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:29
Joined
Feb 19, 2013
Messages
16,553
if there are two records with max date, it will still error

Without seeing the source data which sounds like it has a calculated value, it is difficult to determine the correct process. If you have a PK for your transactions table, try including it in your order by. But the point is, if you have two records with the same max date and different gallons received - which one do you want?
 

foshizzle

Registered User.
Local time
Today, 04:29
Joined
Nov 27, 2013
Messages
277
if there are two records with max date, it will still error

Without seeing the source data which sounds like it has a calculated value, it is difficult to determine the correct process. If you have a PK for your transactions table, try including it in your order by. But the point is, if you have two records with the same max date and different gallons received - which one do you want?

I see. The final goal is to have a report which displays the following for each airline, as shown on the attached screenshot:
[previous_received],
[new_received],
(Subtotal = [previous_received] + [new_received]),
[previous_issued],
[new_issued],
(Total = Subtotal + [new_issued])

On the attached DB, i have a separate query which does the running total p/airline p/date.
So in the long run, I was trying to bring these two queries together for the report.
 

Attachments

  • Database121.accdb
    1.2 MB · Views: 428
  • calc.png
    calc.png
    31.9 KB · Views: 358

CJ_London

Super Moderator
Staff member
Local time
Today, 08:29
Joined
Feb 19, 2013
Messages
16,553
OK so your qryReportDailySumTransactions is an aggregate query, but you are not summing anything

In your data for example, delta airlines has 6 entries for 24th Sept, and 1 each for 25, 30th Sept and 1 Oct.

Your query will probably work for the last 2, but for the 25th, you are getting 6 records back - hence your error. Based on your data, modify your query to exclude anything before 25th Sept and it will probably work. Alternatively modify your aggregate query to sum gallons issued and gallons received

but you could just do this in one query - note these use none standard joins (look for the < in the join) so cannot be viewed in the query builder. change back to = to view in the query builder, then change back again when done.

Code:
SELECT C.TransactionDate, C.AirlineCompanyLookup, Sum(P.GallonsIssued) AS PriorGallonsIssued, Sum(P.GallonsReceived) AS PriorGallonsReceived
FROM
    (SELECT DISTINCT TransactionDate, AirlineCompanyLookup FROM tblTransactions)  AS C
        INNER JOIN tblTransactions AS P ON (C.AirlineCompanyLookup = P.AirlineCompanyLookup) AND ( P.TransactionDate<C.TransactionDate)
GROUP BY C.TransactionDate, C.AirlineCompanyLookup

The problem you have to get the report you want is with your data dated 24th Sept - you have multiple dates for a number of airlines - they will all have the same prior values - whereas I suspect what you want is a prior value for each record - in which case as previously suggested, you need to bring in the ID to provide further order - so you would want something like this

Code:
SELECT C.TransactionID, C.TransactionDate, C.AirlineCompanyLookup, C.GallonsIssued, Sum(P.GallonsIssued) AS PriorGallonsIssued, C.GallonsReceived, Sum(P.GallonsReceived) AS PriorGallonsReceived
FROM
    tblTransactions AS C
    LEFT JOIN tblTransactions AS P ON (P.TransactionID<C.TransactionID) AND (P.TransactionDate<C.TransactionDate ) AND (C.AirlineCompanyLookup = P.AirlineCompanyLookup)
GROUP BY C.TransactionID, C.TransactionDate, C.AirlineCompanyLookup, C.GallonsIssued, C.GallonsReceived
Order BY C.TransactionID

however this is not ideal - what if a month later you realise you have missed a gallons issue for a previous week - the date will be correct, but the ID will be higher - so won't be included. This is normally solved by storing not just the date, but the time as well. Then you can drop the ID from the query since the date/time will be unique.

This may not be possible - perhaps the values you have are just a summary for the day, although with a flight number that seems unlikely. Unfortunately you cannot use flight number as an alternative to the ID since this contains duplicates for the same day.

If all else fails there is another way using another query to provide rownumbers by date/airline but won't go into that now.

Not sure why you have separate fields for gallonsissued and gallons received since this is presumably defined in your issuetypelookup field - note lookups in tables are not a good idea. As with formats, anything which disguises the actual value will come back and bite you at some point in the future. OK for a bit of fun and games, but not for a serious application.
 

foshizzle

Registered User.
Local time
Today, 04:29
Joined
Nov 27, 2013
Messages
277
OK so your qryReportDailySumTransactions is an aggregate query, but you are not summing anything

In your data for example, delta airlines has 6 entries for 24th Sept, and 1 each for 25, 30th Sept and 1 Oct.

Your query will probably work for the last 2, but for the 25th, you are getting 6 records back - hence your error. Based on your data, modify your query to exclude anything before 25th Sept and it will probably work. Alternatively modify your aggregate query to sum gallons issued and gallons received

but you could just do this in one query - note these use none standard joins (look for the < in the join) so cannot be viewed in the query builder. change back to = to view in the query builder, then change back again when done.

Code:
SELECT C.TransactionDate, C.AirlineCompanyLookup, Sum(P.GallonsIssued) AS PriorGallonsIssued, Sum(P.GallonsReceived) AS PriorGallonsReceived
FROM
    (SELECT DISTINCT TransactionDate, AirlineCompanyLookup FROM tblTransactions)  AS C
        INNER JOIN tblTransactions AS P ON (C.AirlineCompanyLookup = P.AirlineCompanyLookup) AND ( P.TransactionDate<C.TransactionDate)
GROUP BY C.TransactionDate, C.AirlineCompanyLookup

The problem you have to get the report you want is with your data dated 24th Sept - you have multiple dates for a number of airlines - they will all have the same prior values - whereas I suspect what you want is a prior value for each record - in which case as previously suggested, you need to bring in the ID to provide further order - so you would want something like this

Code:
SELECT C.TransactionID, C.TransactionDate, C.AirlineCompanyLookup, C.GallonsIssued, Sum(P.GallonsIssued) AS PriorGallonsIssued, C.GallonsReceived, Sum(P.GallonsReceived) AS PriorGallonsReceived
FROM
    tblTransactions AS C
    LEFT JOIN tblTransactions AS P ON (P.TransactionID<C.TransactionID) AND (P.TransactionDate<C.TransactionDate ) AND (C.AirlineCompanyLookup = P.AirlineCompanyLookup)
GROUP BY C.TransactionID, C.TransactionDate, C.AirlineCompanyLookup, C.GallonsIssued, C.GallonsReceived
Order BY C.TransactionID

however this is not ideal - what if a month later you realise you have missed a gallons issue for a previous week - the date will be correct, but the ID will be higher - so won't be included. This is normally solved by storing not just the date, but the time as well. Then you can drop the ID from the query since the date/time will be unique.

This may not be possible - perhaps the values you have are just a summary for the day, although with a flight number that seems unlikely. Unfortunately you cannot use flight number as an alternative to the ID since this contains duplicates for the same day.

If all else fails there is another way using another query to provide rownumbers by date/airline but won't go into that now.

Not sure why you have separate fields for gallonsissued and gallons received since this is presumably defined in your issuetypelookup field - note lookups in tables are not a good idea. As with formats, anything which disguises the actual value will come back and bite you at some point in the future. OK for a bit of fun and games, but not for a serious application.

Hi CJ. Its actually not too late for me to change the the PK for the transactions table to be date and time; there is no real data yet and have not yet done any other queries based on this table. To clarify: There is a difference between "issued" and "received" fuel and these should be counted separately. The "Issue Type" is the reason for the fuel (on-board, received, defuel, adjustment...). Also, you are correct; I would need to be able to query the sum of each airline for any given day. i.e., Spirit has multiple transactions (could be considered as flights) on 9/24. I need to SUM Spirit's fuel issued while separately summing their fuel received for this date and be able to query it later. Users like to see the difference between the current day and previous day's fuel flowage on the report.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:29
Joined
May 7, 2009
Messages
19,169
hi! i made a Function to calculate Previous Received/Issued as well as Cummulative Received/Issued.
see Query1.
use a Form when opening Query1 (open Form Query1), so that you can Reset all totals (see Open Event of the form).
 

Attachments

  • Database121.zip
    50.4 KB · Views: 501

CJ_London

Super Moderator
Staff member
Local time
Today, 08:29
Joined
Feb 19, 2013
Messages
16,553
Users like to see the difference between the current day and previous day's fuel flowage on the report.
you need to clarify this statement - do you mean literally the previous day? i.e. if today is the 3rd October, the previous day is 2nd October, regardless of whether there was fuel issued or received? Or do you mean the previous day when fuel was issued or delivered - which might have been a week/month/year ago? Your example data implies the latter but you need to be clear what you mean.

I need to SUM Spirit's fuel issued while separately summing their fuel received for this date and be able to query it later.
'storing' this data in a 'master' query is not good practice (your queries will start to run slow when you have a large amount of data), better to calculate as and when required, applying criteria as early in the process as you can which reduces the amount of work the query needs to do.

try this sql - just need to copy and paste
Code:
SELECT C.TransactionDate, C.AirlineCompanyLookup, C.SumOfGallonsIssued AS GallonsIssued, Sum(P.GallonsIssued) AS PriorGallonsIssued, C.SumOfGallonsReceived AS GallonsReceived, Sum(P.GallonsReceived) AS PriorGallonsReceived
FROM 
    (SELECT C.TransactionDate, C.AirlineCompanyLookup, Sum(C.GallonsIssued) AS SumOfGallonsIssued, Sum(C.GallonsReceived) AS SumOfGallonsReceived FROM tblTransactions AS C GROUP BY C.TransactionDate, C.AirlineCompanyLookup)  AS C 
    LEFT JOIN ((SELECT C.TransactionDate, C.AirlineCompanyLookup, Max(P.TransactionDate) AS PriorTransactionDate FROM tblTransactions AS C LEFT            JOIN tblTransactions AS P ON C.AirlineCompanyLookup = P.AirlineCompanyLookup WHERE (((P.TransactionDate)<[c].[TransactionDate])) GROUP BY C.TransactionDate, C.AirlineCompanyLookup)  AS D 
    LEFT JOIN tblTransactions AS P ON (D.PriorTransactionDate = P.TransactionDate) AND (D.AirlineCompanyLookup = P.AirlineCompanyLookup)) ON            (C.AirlineCompanyLookup = D.AirlineCompanyLookup) AND (C.TransactionDate = D.TransactionDate)

GROUP BY C.TransactionDate, C.AirlineCompanyLookup, C.SumOfGallonsIssued, C.SumOfGallonsReceived
 

foshizzle

Registered User.
Local time
Today, 04:29
Joined
Nov 27, 2013
Messages
277
Thanks so much to both of you!! I was actually able to get both these options working!
 

Users who are viewing this thread

Top Bottom