ALL Records needed

[Guest]

I need to have all records in the categories field show up from
[E].[LEBAEst_Cost... only 42 of the 189 are showing up. There isn't any info
in the q_sumProjPrice
for the other categories so then the info from the E].[LEBAEst_Cost
doesn't show... which then returns the wrong ttls can you tell me where
to put some criteria that will make it return ALL records from the
E].[LEBAEst_Cost. I have tried changing the join but it doesn't change the
result. I need to have criteria that says to return all records in the
table... even if there is no amount in one or the other table under category.

SELECT q_SumProjPrice.CategoryID, q_SumProjPrice.CategoryName,
q_SumProjPrice.ProjectID, q_SumProjPrice.MainCatID,
q_SumProjPrice.MainCatName, E.LEBAEst_Cost,
q_SumProjPrice.SumOfExtendedPrice,
Sum([E].[LEBAEst_Cost]-[q_SumProjPrice].[SumOfExtendedPrice]) AS
DiffEstimatedAndExtendedPrices
FROM [EST_S-84a] AS E LEFT JOIN q_SumProjPrice ON E.Category =
q_SumProjPrice.CategoryID
WHERE (((q_SumProjPrice.ProjectID) Like [What Project ID? Ex:LEBA] &
"*"))
GROUP BY q_SumProjPrice.CategoryID, q_SumProjPrice.CategoryName,
q_SumProjPrice.ProjectID, q_SumProjPrice.MainCatID,
q_SumProjPrice.MainCatName, E.LEBAEst_Cost,
q_SumProjPrice.SumOfExtendedPrice;

Thanks!!

 

[BruceM]

What happens if you just leave out the WHERE (((q_SumProjPrice.ProjectID)
Like [What Project ID? Ex:LEBA] & > "*"))?

 

[Guest]

It still doesn't give me all of the categories... it gives me records from 3
different projects instead of filtering to the one project I want.

What happens if you just leave out the WHERE (((q_SumProjPrice.ProjectID)
Like [What Project ID? Ex:LEBA] & > "*"))?

I need to have all records in the categories field show up from
[E].[LEBAEst_Cost... only 42 of the 189 are showing up. There isn't any
info
in the q_sumProjPrice
for the other categories so then the info from the E].[LEBAEst_Cost
doesn't show... which then returns the wrong ttls can you tell me where
to put some criteria that will make it return ALL records from the
E].[LEBAEst_Cost. I have tried changing the join but it doesn't change the
result. I need to have criteria that says to return all records in the
table... even if there is no amount in one or the other table under
category.

SELECT q_SumProjPrice.CategoryID, q_SumProjPrice.CategoryName,
q_SumProjPrice.ProjectID, q_SumProjPrice.MainCatID,
q_SumProjPrice.MainCatName, E.LEBAEst_Cost,
q_SumProjPrice.SumOfExtendedPrice,
Sum([E].[LEBAEst_Cost]-[q_SumProjPrice].[SumOfExtendedPrice]) AS
DiffEstimatedAndExtendedPrices
FROM [EST_S-84a] AS E LEFT JOIN q_SumProjPrice ON E.Category =
q_SumProjPrice.CategoryID
WHERE (((q_SumProjPrice.ProjectID) Like [What Project ID? Ex:LEBA] &
"*"))
GROUP BY q_SumProjPrice.CategoryID, q_SumProjPrice.CategoryName,
q_SumProjPrice.ProjectID, q_SumProjPrice.MainCatID,
q_SumProjPrice.MainCatName, E.LEBAEst_Cost,
q_SumProjPrice.SumOfExtendedPrice;

Thanks!!

 

[Allen Browne]

I'm not completely clear what you want, but try:

WHERE ((q_SumProjPrice.ProjectID Is Null) OR
([What Project ID? Ex:LEBA] Is Null) OR
(q_SumProjPrice.ProjectID = [What Project ID? Ex:LEBA]))

Bruce's question was trying to clarify whether the records were actually
there to choose from, so if that suggestion does not work, go ahead and
answer his question.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

It still doesn't give me all of the categories... it gives me records from
3
different projects instead of filtering to the one project I want.

What happens if you just leave out the WHERE (((q_SumProjPrice.ProjectID)
Like [What Project ID? Ex:LEBA] & > "*"))?

I need to have all records in the categories field show up from
[E].[LEBAEst_Cost... only 42 of the 189 are showing up. There isn't any
info
in the q_sumProjPrice
for the other categories so then the info from the E].[LEBAEst_Cost
doesn't show... which then returns the wrong ttls can you tell me where
to put some criteria that will make it return ALL records from the
E].[LEBAEst_Cost. I have tried changing the join but it doesn't change
the
result. I need to have criteria that says to return all records in the
table... even if there is no amount in one or the other table under
category.

SELECT q_SumProjPrice.CategoryID, q_SumProjPrice.CategoryName,
q_SumProjPrice.ProjectID, q_SumProjPrice.MainCatID,
q_SumProjPrice.MainCatName, E.LEBAEst_Cost,
q_SumProjPrice.SumOfExtendedPrice,
Sum([E].[LEBAEst_Cost]-[q_SumProjPrice].[SumOfExtendedPrice]) AS
DiffEstimatedAndExtendedPrices
FROM [EST_S-84a] AS E LEFT JOIN q_SumProjPrice ON E.Category =
q_SumProjPrice.CategoryID
WHERE (((q_SumProjPrice.ProjectID) Like [What Project ID? Ex:LEBA] &
"*"))
GROUP BY q_SumProjPrice.CategoryID, q_SumProjPrice.CategoryName,
q_SumProjPrice.ProjectID, q_SumProjPrice.MainCatID,
q_SumProjPrice.MainCatName, E.LEBAEst_Cost,
q_SumProjPrice.SumOfExtendedPrice;