Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

ALL Records needed

Discussion in 'Information Technology' started by lmv, Jul 28, 2006.

Tags:
  1. lmv

    lmv
    Expand Collapse
    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!!
     
  2. Loading...


  3. BruceM

    BruceM
    Expand Collapse
    Guest

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

    "lmv" <lmv@discussions.microsoft.com> wrote in message
    news:42C27D6B-BB74-4420-98EA-0DA4B06ED5C9@microsoft.com...
    >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!!
    >
     
  4. lmv

    lmv
    Expand Collapse
    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.


    "BruceM" wrote:

    > What happens if you just leave out the WHERE (((q_SumProjPrice.ProjectID)
    > Like [What Project ID? Ex:LEBA] & > "*"))?
    >
    > "lmv" <lmv@discussions.microsoft.com> wrote in message
    > news:42C27D6B-BB74-4420-98EA-0DA4B06ED5C9@microsoft.com...
    > >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!!
    > >

    >
    >
    >
     
  5. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "lmv" <lmv@discussions.microsoft.com> wrote in message
    news:9B5BFC52-7B26-4905-81B7-6779A7C6A099@microsoft.com...
    > 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.
    >
    >
    > "BruceM" wrote:
    >
    >> What happens if you just leave out the WHERE (((q_SumProjPrice.ProjectID)
    >> Like [What Project ID? Ex:LEBA] & > "*"))?
    >>
    >> "lmv" <lmv@discussions.microsoft.com> wrote in message
    >> news:42C27D6B-BB74-4420-98EA-0DA4B06ED5C9@microsoft.com...
    >> >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;
     

Share This Page