Show all dates <between> or if no records show all records (1 Viewer)

MackMan

Registered User.
Local time
Today, 08:57
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

Code:
Between [myformname]![txtstartdate] AND [MyFormName]![txtenddate]
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)

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.
 

Attachments

  • qryActualSpend.jpg
    qryActualSpend.jpg
    96.4 KB · Views: 76
  • qryOutput.jpg
    qryOutput.jpg
    62.4 KB · Views: 77

plog

Banishment Pending
Local time
Today, 03:57
Joined
May 11, 2011
Messages
11,611
You have undone your LEFT JOIN by applying criteria to that table in the WHERE clause. So, although you used LEFT JOIN, it is actually an INNER JOIN and showing just matches between the tables because of your WHERE clause.

With that said, I have no idea what you are trying to do. Explanations never work. The good news is, data always does. So provide me with 2 sets of sample data:

A. Starting sample data from the data sources. Included query/table names and relevant field names. Also, provide enough data to cover all cases.

B. Expected results of A. Show me what data you want to ultimately end up with when you feed this new query the data from A.
 

MackMan

Registered User.
Local time
Today, 08:57
Joined
Nov 25, 2014
Messages
174
Plog. Thanks for you reply. I didn't realise that applying criteria in the where clause on the 2nd query undone the LEFT JOIN. I amended the query to include the criteria in the first query, and voila... it's working now exactly as I needed.


Many thanks for you help and explanation, I learned something today.
 

Users who are viewing this thread

Top Bottom