not addind empty cells

[mtagliaferri]

I have the following query, the "Tot Payment" is meant to return the sum of [Duty Pay]+[AmmountAdditionalAllowances], this works fine when there are values in both fields, but if the [AmmountAdditionalAllowances] has no value the qry will not return any value as a Total.

SELECT tblTrip.IDTrip, tblTrip.TripDate, tblTrip.NDays, tblTrip.TAFB, tblTrip.DutyPayRate, [TAFB]*[DutyPayRate] AS [Duty Pay], tblTrip.PSR, tblTrip.DutyType, qryFlightTimeOperation.[Tot Flying Time], qryFlightTimeOperation.[Tot Sectors], qryFlightTimeOperation.[Non Operating Sectors], qryFlightTimeOperation.[Operating Sectors], Sum(tblAdditionalAllowances.AmmountAdditionalAllowances) AS SumOfAmmountAdditionalAllowances, Sum([Duty Pay]+[AmmountAdditionalAllowances]) AS [Tot Payment]
FROM (tblTrip LEFT JOIN qryFlightTimeOperation ON tblTrip.IDTrip = qryFlightTimeOperation.IDTrip) LEFT JOIN tblAdditionalAllowances ON tblTrip.IDTrip = tblAdditionalAllowances.IDTrip
GROUP BY tblTrip.IDTrip, tblTrip.TripDate, tblTrip.NDays, tblTrip.TAFB, tblTrip.DutyPayRate, [TAFB]*[DutyPayRate], tblTrip.PSR, tblTrip.DutyType, qryFlightTimeOperation.[Tot Flying Time], qryFlightTimeOperation.[Tot Sectors], qryFlightTimeOperation.[Non Operating Sectors], qryFlightTimeOperation.[Operating Sectors]
ORDER BY tblTrip.TripDate;

 

[plog]

Use the NZ function (http://www.techonthenet.com/access/functions/advanced/nz.php) on both values of that equation.

 

[mtagliaferri]

Thanks plog! it seems to be working, however I have a weird circumstance some records (NOT ALL) it doubles the dutypay and I can't possibly think of the reason!
See the attachment as its more clear what I intend!

 

[plog]

I don't understand. You are running an aggregate query so its adding up all like records and producing that. My guess is your joined tables have duplicates for their joined fields.

 

[mtagliaferri]

How can I find out?

 

[plog]

Add a new field to the query:

Dupes: TAFB

Then beneath it change the 'Group By' to count. And that will tell you how many records are being rolled up.

 

[mtagliaferri]

Bingo!!! thanks plog!!! I have found the reason why its double and sometimes triple....tblTrip is related to tblAdditionalAllowances as "one to many" therefore if I have 2, 3 or more records of additional allowances I causes to multiply from the tblTrip the DutyPay by the number of records in tblAdditionalAllowances.
Now I am stuck, the sum of the additionalallowances is correct I need to stop DutyPay being multiplied by the number of records related, any suggesions?

 

[plog]

Subqueries can be used to make data unique. You would make an aggregate query (using the Sum/Sigma sign) on a table causing the duplicates and then use that in your final query as opposed to the table itself.

That's about as much as I can help without understanding what you are hoping to accomplish.

 

[mtagliaferri]

Hi plog, basically I have an old database that I created years ago with the help of you all as I have no skills for this!
I have separated the database now from the interface (Front end / back end if I am correct!!!) this will allow me to redesign the interface with improved functionality, said all, this is what I am trying to achieve somehow....
The database is related to airline crew members, I have a table named trips, this table is related to a one to many to a table of flights (every trip contains multiple flights) the table flights is related to a another table as one to many containing all the crew operating specific flights within the trip; the table trip basically calculates according a starts date/time and a finish/time the numbers of hours worked therefore it calculates a payment, however there is a further table linked to the table trip, again one to many, for further payments that are due for various reasons, hence the "tblAdditionalAllowances" mentioned above!! the table trip, may not contain any related flights but may contain additional allowances to be paid
Now what I am trying to achieve is a query that will list all the trips calculating the payment from start date/time to end date/time plus additional allowances (my current query, as mentioned above, double, triples or even more those records that contain more than one additional allowance payment).
Once I solved this issue I have a further issue: the table flights related to the trips have a departure and arrival time and a flight time which is a calculated difference time between the two values (13:00 - 15:53 = 02:53) the query with the list should be able to sum the total of flight time of all those flight records contained in the flight table related to the single trip.
Hope this makes sense!!!

 

[plog]

Sounds like you need to use subqueries on the many side of the relationship. I'm going to simplify your structure to demonstrate

Employees
emp_ID, FName, LName
1, Steve, Jones
2, Sally, Smith

WorkHours
wh_ID, ID_emp, wh_Start, wh_End
1, 1, 11/1/2015 2:00 pm, 11/1/2015 4:00 pm
2, 1, 11/2/2015 1:00 pm, 11/2/2015 7:00 pm
3, 1, 11/3/2015 2:00 pm, 11/3/2015 3:00 pm
4, 2, 11/1/2015 4:00 pm, 11/2/2015 1:00 am
5, 2, 11/2/2015 7:00 am, 11/2/2015 1:00 pm

Expenses
exp_ID, ID_emp, ID_ExpType, exp_Amount
1, 1, 12, $40.12
2, 2, 12, $38.19
3, 1, 8, $10.00


Now suppose you wanted total cost per employee. You would first make a subquery on the WorkHours table to multiply their hourly rate x the hours they worked. Let's suppose everyone makes $10 an hour. Then that subquery would show this:

emp_ID, WorkCost
1, $90
2, $150

You would do the same for your Expenses to get this:

emp_ID, ExpenseCost
1, $78.12
2, $10.00

Then in your final query, to get all the costs you would join the Employees hour to the WorkCost subquery (using a LEFT JOIN) and also the Employees toable to the ExpenseCost subquery.

 

[mtagliaferri]

Thanks plog, I will do some practise with a new DB and try to apply it to the real one, I might need some more help:banghead: