HELP! Report SQL

  • Thread starter Thread starter Guest
  • Start date Start date
G

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] & "*"));
 
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])
Click to expand...
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;
 
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 said:
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])
Click to expand...
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;
Click to expand...
 
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.


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 said:
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])
Click to expand...
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;
Click to expand...
Click to expand...
 
Hi thanks again for the response,
This is the part I don't know how to do...
Then add the aggragate and expression.
Click to expand...

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]
& "*"));
 
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 said:
Hi thanks again for the response,
This is the part I don't know how to do...
Then add the aggragate and expression.
Click to expand...

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]
& "*"));
Click to expand...
 
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
Click to expand...

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Qry not returning all records 2
Combine 2 Reports 2
STRUCTURE confused 5
UNION rpt?? 3
ALL Records needed 3
Help with a Monthly Summary Report 3

Back
Top