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