STRUCTURE confused

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!!
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/12834-structure-confused.html
Thanks!

SELECT qryPurchaseOrderRpt.ProjectID, qryPurchaseOrderRpt.OrderID,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12834
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));

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

Lmv,

Just one deep table, with these fields...
ProjID
Category
Budget
Used

In a query, you can make a field to group by, like this...
MainCat: [Category]-[Category] Mod 10
or...
MainCat: Int([Category]/10)*10

--
Steve Schapel, Microsoft Access MVP

lmv wrote:
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12834
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12834
> 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));
>

>>First should the budget table have the (150 categories) as the fields or the
records?
NO! Do not use a separate field for each category but like how you listed
your bueget. Add a column for dates likes --
projID ActionDate Amount Category
ABCD 3/3/06 1000 260
ABCD 3/3/06 20 261
ABCD 3/3/06 20 262
ABCD 3/3/06 20 263
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12834
ABCD 3/3/06 20 264
ABCD 3/3/06 0 265
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12834
ABCD 3/3/06 20 266
ABCD 3/3/06 0 267
ABCD 3/3/06 20 268
Then for pay outs/expenses --
projID ActionDate Amount Category
ABCD 3/3/06 -100 260
ABCD 3/3/06 -5 261
ABCD 3/3/06 -5 262
ABCD 3/3/06 -5 263
ABCD 3/3/06 -5 264
ABCD 3/3/06 -5 266
ABCD 3/3/06 -5 268

Use a totals query to find the balance.

"lmv" wrote:

> 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));
>

Ok I wil try this but could you explain what this does so I uderstand WHY it
works...

> MainCat: [Category]-[Category] Mod 10
> or...
> MainCat: Int([Category]/10)*10
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12834

Thanks!

Lmv,

Sorry, I guess I have made an assumption here - perhaps the reality is
more complicated. But if your MainCat is 260 to cover Categories
260-268, and MainCat 270 covers categories 270-279 or whatever, then...
For [Category]=263 (for example)
[Category] Mod 10 (i.e. 263 Mod 10) = 3
so, [Category]-[Category] Mod 10 (i.e. 263-3) = 260
So whatever the Category, you can easily calculate its MainCat.

The other suggestion is similar...
For [Category]=263 (for example)
[Category]/10 (i.e. 263/10) = 26.3
Int(26.3) = 26
Int(26.3)*10 (i.e. 26*10) = 260

An even simpler way is...
MainCat: [Category]\10*10
( \ is an integer divisor, so 263\10 = 26 )

--
Steve Schapel, Microsoft Access MVP
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12834

lmv wrote:
> Ok I wil try this but could you explain what this does so I uderstand WHY it
> works...
>
>> MainCat: [Category]-[Category] Mod 10
>> or...
>> MainCat: Int([Category]/10)*10
>
> Thanks!

Lmv,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12834

WHen I indicated to put the 'Used' amount in your table, this is not
correct if you are recording each individual expense... then that will
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12834
be in another table (ProjID, Category, Amount), and a query will be used
to summarise the Used amount for each Category.

--
Steve Schapel, Microsoft Access MVP

lmv wrote:
> Ok I wil try this but could you explain what this does so I uderstand WHY it
> works...
>
>> MainCat: [Category]-[Category] Mod 10
>> or...
>> MainCat: Int([Category]/10)*10
>
> Thanks!