MackMan
Registered User.
- Local time
- Today, 02:28
- Joined
- Nov 25, 2014
- Messages
- 174
Hi guys. A quick question...
I know the title is confusing, and so is my problem.
I'll try to explain as best as I can.
I have a totals query called [qryActualSpend] where the dates criteria is
and the results are exactly as I need. it shows all spends between two dates and totals by SubcategoryID
I also have a query [qryPlannedAmount] and again this query shows all planned amounts between two dates and totals by SubCatgoryID and this query works fine also.
However both these queries form part of another query where I need to show all planned amounts between two dates in the future, and obviously there will be no spend for the future...
I've tried 'include All records from qryPlannedAmount' and only those records from 'qryActualSpend' but I get the totalspend as a sum of all records in qryActualSpend from the first records regardless of dates, but the qryPlannedAmount is correct. I've also tried 'Both queries are equal' and 'show all records vice versa' (all three options)
On qryActualSpend I need this to been zero, or null so I can workout the difference between the two amounts totalled by SubCategoryID and give users an idea of a future budgets.
My query is (from the design) attached and also the SQL: (I've simplified the embedded query names)
Is there a way I can show all future planned amounts, and zeros or nulls (I'm not bothered which) in the future for ActualSpends?
Is there a way to return all records from qryActualSpend if no records exist?
I know it sounds complicated, but I've tried to explain it as best as I can
.
qryactualspend.jpg: the query
qryoutput: the result where dates are greater than date() (from the previous queries).
spendandAmount should be zeros or nulls (I've has to hide the actual Catsub names for data protection as these contain names).
I hope this makes sense.
As always I appreciate your very helpful advice.
I know the title is confusing, and so is my problem.
I'll try to explain as best as I can.
I have a totals query called [qryActualSpend] where the dates criteria is
Code:
Between [myformname]![txtstartdate] AND [MyFormName]![txtenddate]
I also have a query [qryPlannedAmount] and again this query shows all planned amounts between two dates and totals by SubCatgoryID and this query works fine also.
However both these queries form part of another query where I need to show all planned amounts between two dates in the future, and obviously there will be no spend for the future...
I've tried 'include All records from qryPlannedAmount' and only those records from 'qryActualSpend' but I get the totalspend as a sum of all records in qryActualSpend from the first records regardless of dates, but the qryPlannedAmount is correct. I've also tried 'Both queries are equal' and 'show all records vice versa' (all three options)
On qryActualSpend I need this to been zero, or null so I can workout the difference between the two amounts totalled by SubCategoryID and give users an idea of a future budgets.
My query is (from the design) attached and also the SQL: (I've simplified the embedded query names)
Code:
SELECT qryBudget3AllBAndFTotsANDBudgetIDAndExpIDPLANNED.CategoryID, qryBudget3AllBAndFTotsANDBudgetIDAndExpIDPLANNED.SubCategoryID, qryBudget3AllBAndFTotsANDBudgetIDAndExpIDPLANNED.CatSub, IIf([qryBudget3AllBAndFTotsANDBudgetIDAndExpIDPLANNED]![SumOfAmount]<0,[qryBudget3AllBAndFTotsANDBudgetIDAndExpIDPLANNED]![sumofamount]*-1,[qryBudget3AllBAndFTotsANDBudgetIDAndExpIDPLANNED]![sumofamount]) AS PlannedAmnt, IIf([qryBudgetSubCategoryGroup_SpendByUser*]![SumofAmount]<0,[qryBudgetSubCategoryGroup_SpendByUser*]![sumofamount]*-1,[qryBudgetSubCategoryGroup_SpendByUser*]![sumofamount]) AS SpendAmnt, CCur(Nz([PLANNEDAMNT],0)-Nz([spendamnt],0)) AS Diff, qryBudget3AllBAndFTotsANDBudgetIDAndExpIDPLANNED.BudgetID, [qryBudgetSubCategoryGroup_SpendByUser*].ExpenseTypeID AS SpendETID, qryBudget3AllBAndFTotsANDBudgetIDAndExpIDPLANNED.ExpenseTypeID AS ExpETID, [qryBudgetSubCategoryGroup_SpendByUser*].Budget60ID
FROM qryBudget3AllBAndFTotsANDBudgetIDAndExpIDPLANNED LEFT JOIN [qryBudgetSubCategoryGroup_SpendByUser*] ON qryBudget3AllBAndFTotsANDBudgetIDAndExpIDPLANNED.SubCategoryID = [qryBudgetSubCategoryGroup_SpendByUser*].SubCategoryID
WHERE ((([qryBudgetSubCategoryGroup_SpendByUser*].Budget60ID)<>7))
ORDER BY qryBudget3AllBAndFTotsANDBudgetIDAndExpIDPLANNED.CatSub;
Is there a way I can show all future planned amounts, and zeros or nulls (I'm not bothered which) in the future for ActualSpends?
Is there a way to return all records from qryActualSpend if no records exist?
I know it sounds complicated, but I've tried to explain it as best as I can
.
qryactualspend.jpg: the query
qryoutput: the result where dates are greater than date() (from the previous queries).
spendandAmount should be zeros or nulls (I've has to hide the actual Catsub names for data protection as these contain names).
I hope this makes sense.
As always I appreciate your very helpful advice.