G
Guest
I hope someone can understand my confusion:
I have a report that I need to subtract the "budget" from the "used" for
"Available"
Because this is all in columns on excel I cannot figure out the structure
for Access.
First should the budget table have the (150 categories) as the fields or the
records?
Categories will not change what changes is the budget that goes into each
category on each project.
Example: Main category 260 is made up of categories 260-268
Budget for projID ABCD is
$1000 for cat 260
$20 for cat 261
$20 for cat 262
$20 for cat 263
$20 for cat 264
0 for cat 265
$20 for cat 266
0 for cat 267
$20 for cat 268
---------------------
260TTL = $1120
I have used
$100 for cat 260
$5 for cat 261
$5 for cat 262
$5 for cat 263
$5 for cat 264
0 for cat 265
$5 for cat 266
0 for cat 267
$5 for cat 268
So I want the report to show all of that and then show what is still
available in each category. The above is just an example of what I am trying
to do. I don't know how to structure the budget table. I have tried both
ways. With the category being the field name and the cat#s being the records
and then the ABCDBudget being the fieldname and the amount added to the cat#
record.
Also tried cat#s are the field names and the record is ABCDBudget being the
record.
But I can't figure out how to total the 7 categories into the Main (260) to
get a total and in general I am just confused. Please help!!
Thanks!
SELECT qryPurchaseOrderRpt.ProjectID, qryPurchaseOrderRpt.OrderID,
qryPurchaseOrderRpt.CategoryID, qryPurchaseOrderRpt.MainCatID,
qryPurchaseOrderRpt.CategoryName, qryPurchaseOrderRpt.MainCatName,
[EST_S-84a].LEBAEst_Cost, qryPurchaseOrderRpt.ExtendedPrice,
CCur(Nz([LEBAEst_Cost]-[ExtendedPrice])) AS Available
FROM [EST_S-84a] INNER JOIN qryPurchaseOrderRpt ON [EST_S-84a].Category =
qryPurchaseOrderRpt.CategoryID
GROUP BY qryPurchaseOrderRpt.ProjectID, qryPurchaseOrderRpt.OrderID,
qryPurchaseOrderRpt.CategoryID, qryPurchaseOrderRpt.MainCatID,
qryPurchaseOrderRpt.CategoryName, qryPurchaseOrderRpt.MainCatName,
[EST_S-84a].LEBAEst_Cost, qryPurchaseOrderRpt.ExtendedPrice
HAVING (((qryPurchaseOrderRpt.ProjectID) Like [What Project ID?
Example:LEBA] & "*") AND (([EST_S-84a].LEBAEst_Cost) Is Not Null));
I have a report that I need to subtract the "budget" from the "used" for
"Available"
Because this is all in columns on excel I cannot figure out the structure
for Access.
First should the budget table have the (150 categories) as the fields or the
records?
Categories will not change what changes is the budget that goes into each
category on each project.
Example: Main category 260 is made up of categories 260-268
Budget for projID ABCD is
$1000 for cat 260
$20 for cat 261
$20 for cat 262
$20 for cat 263
$20 for cat 264
0 for cat 265
$20 for cat 266
0 for cat 267
$20 for cat 268
---------------------
260TTL = $1120
I have used
$100 for cat 260
$5 for cat 261
$5 for cat 262
$5 for cat 263
$5 for cat 264
0 for cat 265
$5 for cat 266
0 for cat 267
$5 for cat 268
So I want the report to show all of that and then show what is still
available in each category. The above is just an example of what I am trying
to do. I don't know how to structure the budget table. I have tried both
ways. With the category being the field name and the cat#s being the records
and then the ABCDBudget being the fieldname and the amount added to the cat#
record.
Also tried cat#s are the field names and the record is ABCDBudget being the
record.
But I can't figure out how to total the 7 categories into the Main (260) to
get a total and in general I am just confused. Please help!!
Thanks!
SELECT qryPurchaseOrderRpt.ProjectID, qryPurchaseOrderRpt.OrderID,
qryPurchaseOrderRpt.CategoryID, qryPurchaseOrderRpt.MainCatID,
qryPurchaseOrderRpt.CategoryName, qryPurchaseOrderRpt.MainCatName,
[EST_S-84a].LEBAEst_Cost, qryPurchaseOrderRpt.ExtendedPrice,
CCur(Nz([LEBAEst_Cost]-[ExtendedPrice])) AS Available
FROM [EST_S-84a] INNER JOIN qryPurchaseOrderRpt ON [EST_S-84a].Category =
qryPurchaseOrderRpt.CategoryID
GROUP BY qryPurchaseOrderRpt.ProjectID, qryPurchaseOrderRpt.OrderID,
qryPurchaseOrderRpt.CategoryID, qryPurchaseOrderRpt.MainCatID,
qryPurchaseOrderRpt.CategoryName, qryPurchaseOrderRpt.MainCatName,
[EST_S-84a].LEBAEst_Cost, qryPurchaseOrderRpt.ExtendedPrice
HAVING (((qryPurchaseOrderRpt.ProjectID) Like [What Project ID?
Example:LEBA] & "*") AND (([EST_S-84a].LEBAEst_Cost) Is Not Null));