Showing records with no data.

[AccessBeetle]

Hello,
I have a situation as follows.
1. There is field called Project ID which has several Resource categories. Each Resource category has either few amounts of money in different categories or it does not have any amount of money.
2. I am pulling out a report which shows the data related to the resource categories for a specific project ID.
3. My problem is it does not show the resource categories which do not have any data in. Suppose ProjectID: 12ABC1234 has 4 Resource Categories,
RC1: 1A1, it has related data
RC2: 2B2, it has related data
RC3: 3C3, there is nothing associated with category
RC4: 4E4, there is nothing associated with category
My question is how can I show RC3 and RC 4 in my report with the data rows saying N/A or $0??
Thanks.
The query I am currently using is

SELECT lkptbl_ProjectID.ProjectID, Draws.[Grant/Budget Index], Draws.DrawDate, lkptbl_ResourceCategory.ResourceCategory, Budget.[federal calculated total] AS AuthorizedBudgetCoastal, Sum([match calculated total]+[other calculated total]) AS AuthorizedBudgetMatch, Draws.DrawTotalFed AS CurrentDrawCoastal, Draws.DrawTotalMatch AS CurrentDrawMatch, lkptbl_ResourceCategory.ResourceCategoryID, [Grant Information].[data entry date]
FROM lkptbl_ResourceCategory INNER JOIN (lkptbl_ProjectID INNER JOIN ((([Grant Information] LEFT JOIN Budget ON [Grant Information].[Grant Index] = Budget.[Grant/Budget Index]) INNER JOIN qryDrawsTotal ON [Grant Information].[Grant Index] = qryDrawsTotal.[Grant/Budget Index]) LEFT JOIN Draws ON [Grant Information].[Grant Index] = Draws.[Grant/Budget Index]) ON lkptbl_ProjectID.ProjectID_ID = [Grant Information].ProjectID_ID) ON lkptbl_ResourceCategory.ResourceCategoryID = [Grant Information].ResourceCategoryID
GROUP BY lkptbl_ProjectID.ProjectID, Draws.[Grant/Budget Index], Draws.DrawDate, lkptbl_ResourceCategory.ResourceCategory, Budget.[federal calculated total], Draws.DrawTotalFed, Draws.DrawTotalMatch, lkptbl_ResourceCategory.ResourceCategoryID, lkptbl_ProjectID.ProjectID_ID, [Grant Information].[data entry date]
HAVING (((lkptbl_ResourceCategory.ResourceCategoryID)=[forms]![frmSearchFinancialRpt]![cmbResourceCat]) AND (([Grant Information].[data entry date]) Between [forms]![frmSearchFinancialRpt]![txtStartDate] And [forms]![frmSearchFinancialRpt]![txtEndDate])) OR (((lkptbl_ProjectID.ProjectID_ID)=[forms]![frmSearchFinancialRpt]![cmbProjectID]))
ORDER BY Draws.DrawDate;
 

[NeoPa]

Somewhere in your FROM clause you have an INNER JOIN where you need a LEFT JOIN. See SQL JOINs for more on this.

[patjones]

I'm going to say that you would want to change your FROM clause to utilize a LEFT JOIN on resource category:

FROM lkptbl_ResourceCategory LEFT JOIN (lkptbl_ProjectID INNER JOIN ((([Grant Information] LEFT JOIN Budget ON [Grant Information].[Grant Index] = Budget.[Grant/Budget Index]) INNER JOIN qryDrawsTotal ON [Grant Information].[Grant Index] = qryDrawsTotal.[Grant/Budget Index]) LEFT JOIN Draws ON [Grant Information].[Grant Index] = Draws.[Grant/Budget Index]) ON lkptbl_ProjectID.ProjectID_ID = [Grant Information].ProjectID_ID) ON lkptbl_ResourceCategory.ResourceCategoryID = [Grant Information].ResourceCategoryID

Pat

[AccessBeetle]

It gave me error. To solve that I created another query to create a left join and then put that qury in this query. So now it looks like this:

SELECT lkptbl_ProjectID.ProjectID, Draws.[Grant/Budget Index], Draws.DrawDate, Budget.[federal calculated total] AS AuthorizedBudgetCoastal, Sum([match calculated total]+[other calculated total]) AS AuthorizedBudgetMatch, Draws.DrawTotalFed AS CurrentDrawCoastal, Draws.DrawTotalMatch AS CurrentDrawMatch, qryFinancialRptLeftJoin.ResourceCategoryID
FROM (((lkptbl_ProjectID INNER JOIN qryFinancialRptLeftJoin ON lkptbl_ProjectID.ProjectID_ID = qryFinancialRptLeftJoin.ProjectID_ID) LEFT JOIN Draws ON qryFinancialRptLeftJoin.[Grant Index] = Draws.[Grant/Budget Index]) INNER JOIN Budget ON qryFinancialRptLeftJoin.[Grant Index] = Budget.[Grant/Budget Index]) LEFT JOIN qryDrawsTotal ON qryFinancialRptLeftJoin.[Grant Index] = qryDrawsTotal.[Grant/Budget Index]
GROUP BY lkptbl_ProjectID.ProjectID, Draws.[Grant/Budget Index], Draws.DrawDate, Budget.[federal calculated total], Draws.DrawTotalFed, Draws.DrawTotalMatch, qryFinancialRptLeftJoin.ResourceCategoryID, lkptbl_ProjectID.ProjectID_ID
HAVING (((qryFinancialRptLeftJoin.ResourceCategoryID)=[forms]![frmSearchFinancialRpt]![cmbResourceCat])) OR (((lkptbl_ProjectID.ProjectID_ID)=[forms]![frmSearchFinancialRpt]![cmbProjectID]))
ORDER BY Draws.DrawDate;
 

Now if I run the report it asks me Enter Parameter Value for Resource Category. ??
Why is that? Is there anything wrong in this query?
Here is the qryFinancialRptLeftJoin

SELECT lkptbl_ResourceCategory.ResourceCategoryID, lkptbl_ResourceCategory.ResourceCategory, lkptbl_ResourceCategory.ResourceCategoryDescription, [Grant Information].[Grant Index], [Grant Information].Status, [Grant Information].RecipientLongName, [Grant Information].RecipientShortName, [Grant Information].[project long title], [Grant Information].[project short title], [Grant Information].[Grant Year], [Grant Information].[CZMA section number], [Grant Information].[type of grant], [Grant Information].Reprogrammed, [Grant Information].extension, [Grant Information].[PDC Federal district], [Grant Information].[PDC State Senate district], [Grant Information].[PDC State House district], [Grant Information].[Staff Index], [Grant Information].Contact1Index, [Grant Information].Contact2Index, [Grant Information].ContactFiscal1Index, [Grant Information].ContactFiscal2Index, [Grant Information].OfficialRepIndex, [Grant Information].OCMFiscal1Index, [Grant Information].ContactCounty, [Grant Information].[project length], [Grant Information].[Tributary strategy issue area], [Grant Information].[Riparian Buffer project issue area], [Grant Information].[GIS project issue area], [Grant Information].[date 306A documentation to NOAA], [Grant Information].[306A NOAA approval date], [Grant Information].[subcontract approval notification], [Grant Information].[subcontract approval required], [Grant Information].[subapproval notification date], [Grant Information].[Impact area], [Grant Information].[project summary], [Grant Information].deliverables, [Grant Information].[data entry date], [Grant Information].[temporary field], [Grant Information].ManagementArea, [Grant Information].[federal returned], [Grant Information].ProjectType, [Grant Information].Acreage, [Grant Information].DistancePreserved, [Grant Information].Competitive, [Grant Information].PublicAccess, [Grant Information].CoastalHabitat, [Grant Information].WaterQuality, [Grant Information].CoastalHazards, [Grant Information].CoastalDependentUses, [Grant Information].UTMEasting, [Grant Information].UTMNorthing, [Grant Information].ResourceTypeID, [Grant Information].ResourceSubcategoryID, [Grant Information].ProjectID_ID, [Grant Information].ActivityCodeID, [Grant Information].GrantNumber, [Grant Information].ProjectTypeNo
FROM lkptbl_ResourceCategory LEFT JOIN [Grant Information] ON lkptbl_ResourceCategory.ResourceCategoryID = [Grant Information].ResourceCategoryID;
 

[patjones]

Well what was the error that resulted from switching to a LEFT JOIN as I outlined in post #3? Perhaps we can still work with that rather than embedding a query within a query.

Pat

[AccessBeetle]

The error is "the sql statement could not be executed because it contains ambiguous outer joins"

[NeoPa]

Jet SQL will not support OUTER JOINs (LEFT JOIN or RIGHT JOIN) to join any groups which include INNER JOINs.

IE. A INNER JOIN B LEFT JOIN C is ok, but A INNER JOIN B LEFT JOIN C INNER JOIN D is not.

[AccessBeetle]

Neopa,
Should I go on the path I mentioned on Post #4 or is it not possible?(I guess it must be possible somehow!!I don't know)
Thanks

[NeoPa]

My thoughts were that you need to go through your FROM clause and find the offending JOIN. Frankly your SQL is very complicated and not well displayed. A nightmare to trawl through. Your effort would best be served simplifying it where possible, and this would start by displaying it in such a way that you can see more easily what connects with what.

I can only really tell you the concepts you need to consider when designing it. I'm afraid I cannot make comments that depend on my understanding what your SQL is trying to do (other than run). My advice would always be to simplify and clarify, rather than to look for ways of avoiding the problem. That way it will only catch you another time.

[AccessBeetle]

Thanks for the advise and thanks for looking into it.
I am trying to solve it, I hope I will one day!!
Wish me luck..

[NeoPa]

Good luck :)

BTW Look for the following in your FROM clause as an example of exactly what I was talking about :

([Grant Information] LEFT JOIN Budget ON [Grant Information].[Grant Index] = Budget.[Grant/Budget Index]) INNER JOIN