Welcome to SPN

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

Sign Up Now!

HELP! Report SQL

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

Tags:
  1. lmv

    lmv
    Expand Collapse
    Guest

    I have 2 reports (code included) I can get them both to work seperately but I
    need a report that will combine the info.
    The Cost in the EST_S84 needs to then subtract the info in the extendedprice
    field to make the "difference" field which I guess would be
    =[LEBAEst_Cost]-[ExtendedPrice] but I get null errors and it isn't totalling
    the info right. (Though I don't know the diffence between

    =[LEBAEst_Cost]-[ExtendedPrice]
    =Sum ([LEBAEst_Cost]-[ExtendedPrice])

    Which returns different results. But I don't know why.

    I have tried to put the 2 qry's into one but it doesn't work. Also, the MAIN
    category field is the 260, 270, 280 etc BUT sometimes there is currency in
    those fields so I had thoght of making an unbound 260TTL field that would
    calculate since these figures do not need to be stored. If I haven't included
    enough information please let me know as I only have a couple of days to get
    this figured out.

    THANKS so much!
    --------------Budget ESTIMATED SQL

    SELECT DISTINCT [EST_S-84a].Category, [EST_S-84a].LEBAEst_Cost,
    qryCategoryLookup.CategoryName, qryCategoryLookup.MainCatID,
    qryCategoryLookup.MainCatName
    FROM [EST_S-84a] INNER JOIN qryCategoryLookup ON [EST_S-84a].Category =
    qryCategoryLookup.CategoryID
    ORDER BY [EST_S-84a].Category;

    ----------BUDGET SPENT
    SELECT qryPurchaseOrderRpt.ProjectID, qryPurchaseOrderRpt.CategoryID,
    qryPurchaseOrderRpt.MainCatID, qryPurchaseOrderRpt.CategoryName,
    qryPurchaseOrderRpt.MainCatName, qryPurchaseOrderRpt.ExtendedPrice
    FROM qryPurchaseOrderRpt
    WHERE (((qryPurchaseOrderRpt.ProjectID) Like [What Project ID? Example:LEBA]
    & "*"));

    The code I tried below that doesn't work ON ALL RECORDS... if there is only
    1 record in the extendedPrice it works If there are numerous records then
    each record gets an estimated line and that is wrong as the category should
    only have 1 instance of the amount but I don't know how to combine the
    DISTINCT into the SQL

    SELECT qryPurchaseOrderRpt.CategoryID, qryPurchaseOrderRpt.CategoryName,
    qryPurchaseOrderRpt.ProjectID, qryPurchaseOrderRpt.MainCatID,
    qryPurchaseOrderRpt.MainCatName, [EST_S-84a].LEBAEst_Cost,
    Sum(qryPurchaseOrderRpt.ExtendedPrice) AS SumOfExtendedPrice
    FROM [EST_S-84a] RIGHT JOIN qryPurchaseOrderRpt ON [EST_S-84a].Category =
    qryPurchaseOrderRpt.CategoryID
    GROUP BY qryPurchaseOrderRpt.CategoryID, qryPurchaseOrderRpt.CategoryName,
    qryPurchaseOrderRpt.ProjectID, qryPurchaseOrderRpt.MainCatID,
    qryPurchaseOrderRpt.MainCatName, [EST_S-84a].LEBAEst_Cost,
    qryPurchaseOrderRpt.OrderID
    HAVING (((qryPurchaseOrderRpt.ProjectID) Like [What Project ID?
    Example:LEBA] & "*"));
     
  2. Loading...


  3. Michael Gramelspacher

    Michael Gramelspacher
    Expand Collapse
    Guest

    In article <1290A4F3-E3A7-49AE-B6CF-3E59DE7AAA76@microsoft.com>,
    lmv@discussions.microsoft.com says...
    > I have 2 reports (code included) I can get them both to work seperately but I
    > need a report that will combine the info.
    > The Cost in the EST_S84 needs to then subtract the info in the extendedprice
    > field to make the "difference" field which I guess would be
    > =[LEBAEst_Cost]-[ExtendedPrice] but I get null errors and it isn't totalling
    > the info right. (Though I don't know the diffence between
    >
    > =[LEBAEst_Cost]-[ExtendedPrice]
    > =Sum ([LEBAEst_Cost]-[ExtendedPrice])
    >

    Maybe:

    SELECT PO1.CategoryID,
    PO1.CategoryName,
    PO1.ProjectID,
    PO1.MainCatID,
    PO1.MainCatName,
    E.LEBAEst_Cost,
    SUM(PO1.ExtendedPrice) AS SumOfExtendedPrice,
    SUM(E.LEBAEst_Cost - PO1.ExtendedPrice) AS
    DiffEstimatedAndExtendedPrices
    FROM [EST_S-84a] AS E
    RIGHT JOIN qryPurchaseOrderRpt AS PO1
    ON E.Category = PO1.CategoryID
    WHERE (((PO1.ProjectID) LIKE [What Project ID?
    Example:LEBA] & "*"))
    GROUP BY PO1.CategoryID,
    PO1.CategoryName,
    PO1.ProjectID,
    PO1.MainCatID,
    PO1.MainCatName,
    E.LEBAEst_Cost,
    PO1.OrderID;
     
  4. lmv

    lmv
    Expand Collapse
    Guest

    Thank you for the response. The code you provided works but returns the same
    results as mine.

    The main problem "I think" is that the Category field is not returning ALL
    of the records in the LEBAEst_Cost column of the one table. AND it is putting
    a value in the category field each time the Extendedprice has a value (within
    that category) so that when you sum it is adding the Est_cost more than 1
    time.

    Does that make sense?
    Thanks!

    "Michael Gramelspacher" wrote:

    > In article <1290A4F3-E3A7-49AE-B6CF-3E59DE7AAA76@microsoft.com>,
    > lmv@discussions.microsoft.com says...
    > > I have 2 reports (code included) I can get them both to work seperately but I
    > > need a report that will combine the info.
    > > The Cost in the EST_S84 needs to then subtract the info in the extendedprice
    > > field to make the "difference" field which I guess would be
    > > =[LEBAEst_Cost]-[ExtendedPrice] but I get null errors and it isn't totalling
    > > the info right. (Though I don't know the diffence between
    > >
    > > =[LEBAEst_Cost]-[ExtendedPrice]
    > > =Sum ([LEBAEst_Cost]-[ExtendedPrice])
    > >

    > Maybe:
    >
    > SELECT PO1.CategoryID,
    > PO1.CategoryName,
    > PO1.ProjectID,
    > PO1.MainCatID,
    > PO1.MainCatName,
    > E.LEBAEst_Cost,
    > SUM(PO1.ExtendedPrice) AS SumOfExtendedPrice,
    > SUM(E.LEBAEst_Cost - PO1.ExtendedPrice) AS
    > DiffEstimatedAndExtendedPrices
    > FROM [EST_S-84a] AS E
    > RIGHT JOIN qryPurchaseOrderRpt AS PO1
    > ON E.Category = PO1.CategoryID
    > WHERE (((PO1.ProjectID) LIKE [What Project ID?
    > Example:LEBA] & "*"))
    > GROUP BY PO1.CategoryID,
    > PO1.CategoryName,
    > PO1.ProjectID,
    > PO1.MainCatID,
    > PO1.MainCatName,
    > E.LEBAEst_Cost,
    > PO1.OrderID;
    >
     
  5. Michael Gramelspacher

    Michael Gramelspacher
    Expand Collapse
    Guest

    It seems you want only the records from both tables that match. That
    would be an inner join. Maybe start with the two tables joined to get
    all of the records you need from both tables. Just a basic query to
    insure you are getting all the records you need and no more. Then add
    the aggragate and expression.


    In article <0F39BE72-D9F1-4ECE-9631-FF892788B7B7@microsoft.com>,
    lmv@discussions.microsoft.com says...
    > Thank you for the response. The code you provided works but returns the same
    > results as mine.
    >
    > The main problem "I think" is that the Category field is not returning ALL
    > of the records in the LEBAEst_Cost column of the one table. AND it is putting
    > a value in the category field each time the Extendedprice has a value (within
    > that category) so that when you sum it is adding the Est_cost more than 1
    > time.
    >
    > Does that make sense?
    > Thanks!
    >
    > "Michael Gramelspacher" wrote:
    >
    > > In article <1290A4F3-E3A7-49AE-B6CF-3E59DE7AAA76@microsoft.com>,
    > > lmv@discussions.microsoft.com says...
    > > > I have 2 reports (code included) I can get them both to work seperately but I
    > > > need a report that will combine the info.
    > > > The Cost in the EST_S84 needs to then subtract the info in the extendedprice
    > > > field to make the "difference" field which I guess would be
    > > > =[LEBAEst_Cost]-[ExtendedPrice] but I get null errors and it isn't totalling
    > > > the info right. (Though I don't know the diffence between
    > > >
    > > > =[LEBAEst_Cost]-[ExtendedPrice]
    > > > =Sum ([LEBAEst_Cost]-[ExtendedPrice])
    > > >

    > > Maybe:
    > >
    > > SELECT PO1.CategoryID,
    > > PO1.CategoryName,
    > > PO1.ProjectID,
    > > PO1.MainCatID,
    > > PO1.MainCatName,
    > > E.LEBAEst_Cost,
    > > SUM(PO1.ExtendedPrice) AS SumOfExtendedPrice,
    > > SUM(E.LEBAEst_Cost - PO1.ExtendedPrice) AS
    > > DiffEstimatedAndExtendedPrices
    > > FROM [EST_S-84a] AS E
    > > RIGHT JOIN qryPurchaseOrderRpt AS PO1
    > > ON E.Category = PO1.CategoryID
    > > WHERE (((PO1.ProjectID) LIKE [What Project ID?
    > > Example:LEBA] & "*"))
    > > GROUP BY PO1.CategoryID,
    > > PO1.CategoryName,
    > > PO1.ProjectID,
    > > PO1.MainCatID,
    > > PO1.MainCatName,
    > > E.LEBAEst_Cost,
    > > PO1.OrderID;
    > >
     
  6. lmv

    lmv
    Expand Collapse
    Guest

    Hi thanks again for the response,
    This is the part I don't know how to do...
    >Then add the aggragate and expression.


    Below are my 2 qry... I don't know how to join them

    --------------Budget ESTIMATED SQL

    SELECT DISTINCT [EST_S-84a].Category, [EST_S-84a].LEBAEst_Cost,
    qryCategoryLookup.CategoryName, qryCategoryLookup.MainCatID,
    qryCategoryLookup.MainCatName
    FROM [EST_S-84a] INNER JOIN qryCategoryLookup ON [EST_S-84a].Category =
    qryCategoryLookup.CategoryID
    ORDER BY [EST_S-84a].Category;

    ----------BUDGET SPENT
    SELECT qryPurchaseOrderRpt.ProjectID, qryPurchaseOrderRpt.CategoryID,
    qryPurchaseOrderRpt.MainCatID, qryPurchaseOrderRpt.CategoryName,
    qryPurchaseOrderRpt.MainCatName, qryPurchaseOrderRpt.ExtendedPrice
    FROM qryPurchaseOrderRpt
    WHERE (((qryPurchaseOrderRpt.ProjectID) Like [What Project ID? Example:LEBA]
    & "*"));
     
  7. lmv

    lmv
    Expand Collapse
    Guest

    Some more info is that there are 258 records in the ExtendedPrice and there
    are 189 categories. The problem is that in the 258 EP (purchase order
    records)categories may be the same how do I filter to show ALL of the
    categories even if there are no EP records AND only have the category price
    TOTAL 1x in the Estimated BUDGET total.

    ESTIMATED BUDGET...........ACTUAL SPENT.............DIFFERENCE
    EST_COST-----------------EXTENDEDPRICE----------
    -----"------------------------EXTENDEDPRICE----------
    -----"------------------------EXTENDEDPRICE----------AMT LEFT TO SPEND

    I appreciate your help sorry if I am not making it clear. But , I have been
    looking at it for 3 days!

    "lmv" wrote:

    > Hi thanks again for the response,
    > This is the part I don't know how to do...
    > >Then add the aggragate and expression.

    >
    > Below are my 2 qry... I don't know how to join them
    >
    > --------------Budget ESTIMATED SQL
    >
    > SELECT DISTINCT [EST_S-84a].Category, [EST_S-84a].LEBAEst_Cost,
    > qryCategoryLookup.CategoryName, qryCategoryLookup.MainCatID,
    > qryCategoryLookup.MainCatName
    > FROM [EST_S-84a] INNER JOIN qryCategoryLookup ON [EST_S-84a].Category =
    > qryCategoryLookup.CategoryID
    > ORDER BY [EST_S-84a].Category;
    >
    > ----------BUDGET SPENT
    > SELECT qryPurchaseOrderRpt.ProjectID, qryPurchaseOrderRpt.CategoryID,
    > qryPurchaseOrderRpt.MainCatID, qryPurchaseOrderRpt.CategoryName,
    > qryPurchaseOrderRpt.MainCatName, qryPurchaseOrderRpt.ExtendedPrice
    > FROM qryPurchaseOrderRpt
    > WHERE (((qryPurchaseOrderRpt.ProjectID) Like [What Project ID? Example:LEBA]
    > & "*"));
    >
     
  8. Michael Gramelspacher

    Michael Gramelspacher
    Expand Collapse
    Guest

    In article <506B1F76-CA1C-40AD-B056-4EBE025ABA39@microsoft.com>,
    lmv@discussions.microsoft.com says...
    > Some more info is that there are 258 records in the ExtendedPrice and there
    > are 189 categories. The problem is that in the 258 EP (purchase order
    > records)categories may be the same how do I filter to show ALL of the
    > categories even if there are no EP records AND only have the category price
    > TOTAL 1x in the Estimated BUDGET total.
    >
    > ESTIMATED BUDGET...........ACTUAL SPENT.............DIFFERENCE
    > EST_COST-----------------EXTENDEDPRICE----------
    > -----"------------------------EXTENDEDPRICE----------
    > -----"------------------------EXTENDEDPRICE----------AMT LEFT TO SPEND


    You are showing multiple purchase orders applying to a budget category.
    Seems you need to sum the purchase orders applying to each category and
    substract the sum from the amount estimated for the category. Try it.
     

Share This Page