Welcome to SPN

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

Sign Up Now!

STRUCTURE confused

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

  1. lmv

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

    Similar Threads Forum Date
    Structure Of The Guru Granth Sahib New to Sikhism May 30, 2016
    Heritage 16th-century Sikh Structures Identified For Preservation In Tarn Taran History of Sikhism Mar 10, 2015
    SciTech Meditation and Brain Structure Breaking News Jan 7, 2014
    World Toilet Trouble: New Technology and Infrastructures with Dr. Doulaye Kone Breaking News Oct 26, 2013
    General Management structure of Sikh religion and the preaching class. History of Sikhism Mar 30, 2013

  3. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    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
    > 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));
    >
     
  4. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    >>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
    ABCD 3/3/06 20 264
    ABCD 3/3/06 0 265
    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));
    >
     
  5. lmv

    lmv
    Expand Collapse
    Guest

    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!
     
  6. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    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

    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!
     
  7. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Lmv,

    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
    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!
     

Share This Page