Solved Show query results even if null or 0 (1 Viewer)

[foshizzle]

I have the following code which is used to run several reports. This code is one part of a larger subset of queries.

This query simply queries for the daily balance stored in a table (for reasons not discussed here). I'd like to use this query for one final report for the current day's balance. The problem is, the daily balance for the current day is not calculated until the after this query has been run. So, when using this query for the current day, the results of the query are blank. In order to use this for the current day (and calculate the current balance), I need the Balance to return 0, or something other than blank. I have tried using NZ for both the Balance and RunBalance with no resolve. I know I can formulate separate queries for this, but I'm trying to re-purpose what I can.

qFUEL_RUNBAL

PARAMETERS [TempVars]![tmpEndDate] DateTime, [TempVars]![tmpAirline] Short;
SELECT tblBalance.TransactionDate, tblBalance.AirlineCompanyLookup, tblBalance.Balance AS Balance, (Select Sum(a.Balance)                     
    From  tblBalance as a                      
    Where  a.TransactionDate =  [TempVars]![tmpEndDate]) AS RunBalance
        FROM tblBalance INNER JOIN tblAirlines ON tblBalance.AirlineCompanyLookup = tblAirlines.AirlineID
        WHERE (((tblBalance.TransactionDate)=[TempVars]![tmpStartDate] Or (tblBalance.TransactionDate) Is Null));

Or, perhaps the problem could be corrected in the main query somehow if RunBal is null:

qFUEL_REPORTTOTALS

SELECT qFUEL_TRANSACTIONSNEW.AirlineCompany,
qFUEL_TRANSACTIONSNEW.AirlineCompanyLookup,
qFUEL_RUNPRVBAL.PrvBalance, qFUEL_TRANSACTIONSNEW.SumReceived,
[PrvBalance]+[SumReceived] AS Subtotal,
qFUEL_TRANSACTIONSNEW.SumIssued,
[PrvBalance]+[SumReceived]-[SumIssued] AS BookBalance,
qFUEL_TRANSACTIONSNEW.TotalReceived,
qFUEL_TRANSACTIONSNEW.TotalIssued,
[RunPrvBalance]+[TotalReceived] AS TotalSubtotal,
[TotalSubtotal]-[TotalIssued] AS TotalBookBalance,
Last(qFUEL_TANKVOLSUM.RunTankVol) AS LastOfRunTankVol,
Last(qFUEL_TRUCKVOLSUM.RunTruckVol) AS LastOfRunTruckVol,
[LastOfRunTankVol]+[LastOfRunTruckVol] AS TotalActualBalance,
[TotalActualBalance]-[TotalBookBalance] AS TotalGainLoss,
Round((([SumIssued]/[RunIssued])*[TotalGainLoss]),0) AS GainLoss,
     (Select Sum(a.SumIssued)                       
         From  qFUEL_TRANSACTIONSNEW as a                        
         Where  a.AirlineCompanyLookup =  AirlineCompanyLookup) AS RunIssued,
     qFUEL_RUNPRVBAL.RunPrvBalance, Last(qFUEL_RUNBAL.RunBalance) AS LastOfRunBalance,
     FormatPercent((([SumIssued])/([RunIssued]))) AS PctAirline,
     [TotalActualBalance]-[LastOfRunBalance] AS InventoryDiff,
     [TotalGainLoss]-[InventoryDiff] AS GainLossDiff,
     [TotalGainLoss]/[RunIssued] AS PctGainLoss,
     [SumIssued]/[RunIssued] AS UsedNumeric,
     qFUEL_TRANSACTIONSNEW.MonthGainLoss0 AS MonthGainLoss,
     [LastOfRunTankVol]+[LastOfRunTruckVol] AS TotalPhysInven,
     [PrvBalance]+[SumReceived]-[SumIssued]+[MonthGainLoss] AS Balance
FROM qFUEL_TANKVOLSUM, qFUEL_TRUCKVOLSUM,
qFUEL_RUNBAL
INNER JOIN (qFUEL_TRANSACTIONSNEW
    INNER JOIN qFUEL_RUNPRVBAL ON qFUEL_TRANSACTIONSNEW.AirlineCompanyLookup = qFUEL_RUNPRVBAL.AirlineCompanyLookup) ON qFUEL_RUNBAL.AirlineCompanyLookup = qFUEL_TRANSACTIONSNEW.AirlineCompanyLookup
GROUP BY qFUEL_TRANSACTIONSNEW.AirlineCompany, qFUEL_TRANSACTIONSNEW.AirlineCompanyLookup, qFUEL_RUNPRVBAL.PrvBalance, qFUEL_TRANSACTIONSNEW.SumReceived, qFUEL_TRANSACTIONSNEW.SumIssued, qFUEL_TRANSACTIONSNEW.TotalReceived, qFUEL_TRANSACTIONSNEW.TotalIssued, qFUEL_RUNPRVBAL.RunPrvBalance, qFUEL_TRANSACTIONSNEW.MonthGainLoss0;

 

[theDBguy]

Are you getting null results because there are no transactions to balance for that day? If so, you'll need a date table and use an OUTER JOIN to show a zero balance for the missing dates.

 

[ebs17]

An empty query has no record. There is then nothing to get or assign 0 or anything to.
So you should at least write a date into the table before, so that the filter returns something.

The usual method would be a LEFT JOIN of a complete set against an existing set. You know that, but you don't want to use it (because you are proud of what you have created).

The whole process seems strange to me, without being able to justify it in detail.

...
FROM qFUEL_TANKVOLSUM, qFUEL_TRUCKVOLSUM,
qFUEL_RUNBAL
INNER JOIN ...

Here a wicked cartesian product is generated.

 

[plog]

The whole process seems strange to me

Like the whole whole process

1. Cartesian product:

...
FROM qFUEL_TANKVOLSUM, qFUEL_TRUCKVOLSUM,
qFUEL_RUNBAL
...

2. Using an alias defined in the SELECT in that same SELECT:
3. Using Last() at all

Last(qFUEL_TANKVOLSUM.RunTankVol) AS LastOfRunTankVol,
Last(qFUEL_TRUCKVOLSUM.RunTruckVol) AS LastOfRunTruckVol,
[LastOfRunTankVol]+[LastOfRunTruckVol] AS TotalActualBalance,

But the two biggest, reddest flags:

4. Using GROUP BY without any aggregation functions in the SELECT.
5. All the math.

4 tells me that this query isn't JOINED or thought out properly. You got close to what you wanted but then you hade duplicates and didn't know what to do so just GROUP BY the whole thing to cram them down.

5 tells me that the underlying structure is most likely not normalized. Credits and debits should go into the same field, not have seperate ones where you add them together to get the balance. You simply SUM up that one field and the credits all add up and then the debits get subtracted out. That's how you also get the balance, not by storing it then manually adding and subtracting what's necessary.

I feel pursuing this query further is just putting a band aid on a broken bone. I'd really focus on the structure of your tables first.

 

[foshizzle]

I was able to get this working using the suggestion from theDBguy. In doing so, I had to go back and join qFUEL_TANKVOLSUM and qFUEL_TRUCKVOLSUM in qFUEL_REPORTTOTALS. Without going to too much detail, I understand it is hard to determine the method for my madness. I'm not a programmer nor a mathematician but the numbers jive. Thanks for the help

 

[theDBguy]

I was able to get this working using the suggestion from theDBguy. In doing so, I had to go back and join qFUEL_TANKVOLSUM and qFUEL_TRUCKVOLSUM in qFUEL_REPORTTOTALS. Without going to too much detail, I understand it is hard to determine the method for my madness. I'm not a programmer nor a mathematician but the numbers jive. Thanks for the help

Glad to hear you got it sorted out. Good luck with your project.