Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2012
    Posts
    9

    MS ACCESS 2007 Forms

    Hi ,

    I am pretty new to ACCESS.
    I have a table which has the following columns Key Number, Txn Status, Region, Plan 12-13(Qty 1), Dec 12(Qty2), March 13(Qty 3), Jun 13(Qty 4), Segment 1, Segment 2, Segment 3, Segment 4,Segment 5, Segment 6, Segment 7, Segment 8,..........Segment 104.

    Now my basic idea is to group by Txn Status and Region where Txn Status is "Plan".
    Under Txn Status there are two values Plan and Approved.
    In my form I want Region, Sum of Qty1, Sum of Qty2, Sum of Qty3, SUm of QTY4, Sum of all segments (All this will be group by of Region and Txn Status where Txn Status will be "Plan")
    In the footer I need the sum of sum that is . I need the SUM of all the columns .
    I have attached the sample data of my base table. This is just a sample my actual data consists of 198 rows and the segments are not just 4 but 104.

    JUST To get a better picture
    All these datas are something related with networks. Plan 12-13 is the forecasted amount of segments that are needed for the period 2012 to 2013. As u said the Dec 12, Mar 13, Jun13 these are quarter related datas that how many segments that are needed for each quarter.
    Suppose we have a place A and Place B. The two can be connected via many segments of network. So the amount of capacity needed for each segment is given under these columns. For the time being NO more segments will be added.

    It would be helpful if i get a response as soon as possible
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Quote Originally Posted by Confuzzed View Post
    I have a table which has the following columns Key Number, Txn Status, Region, Plan 12-13(Qty 1), Dec 12(Qty2), March 13(Qty 3), Jun 13(Qty 4), Segment 1, Segment 2, Segment 3, Segment 4,Segment 5, Segment 6, Segment 7, Segment 8,..........Segment 104.
    This table really needs to be redesigned before you go any further. Do some reading on normalization, it will make everything you try to do from this point forward a lot easier.

    Quote Originally Posted by Confuzzed View Post
    In my form I want Region, Sum of Qty1, Sum of Qty2, Sum of Qty3, SUm of QTY4, Sum of all segments (All this will be group by of Region and Txn Status where Txn Status will be "Plan")

    This is very difficult to accomplish with your current design. However, if you took all of the segments out of your main table and moved them into a table, that was vertical instead of horizontal, and joined to the main table then it's easy.

    The segment table would look something like this: (assuming KeyNumber is a unique ID on the main table)

    KeyNumber (to link back to the main table)
    SegmentNumber
    SegmentValue

    Then all you would need to do is join this with your main table and query for the sum of SegmentValue grouped by Region (or grouped by Region and SegmentNumber if you want the totals of each Segment)

    The same concept applies for the quantities. The additional issue with the qty columns as currently designed, is your table will be very quickly outdated. You would have to change the table structure and all related forms, query, reports, etc. every year! That's not good.
    Last edited by sps; 07-19-12 at 12:06. Reason: grammar and spelling...

    Steve

  3. #3
    Join Date
    Jul 2012
    Posts
    9
    Hi, Sorry that I posted somewhere else too..
    Now the requirements for the form are :
    Region|Qty1(Sum)|Qty2(SUM)|Qty3(Sum)|Qty4(Sum)|Seg ments (SUM)
    All the sum will be based on the region
    I have attached the table example. Example for table.zip and the next tab has the way I need the form...
    The form is done by grouping the region and the status.
    The last column(sum of segments) =the sum of all teh segments present in a region with the same status.

  4. #4
    Join Date
    Jul 2012
    Posts
    9
    Hi To make things clear .... I have a table which I have attached in my previous post. based on that table i have created a query "Select txn status, region, sum(Plan 12-13), Sum(12-Dec), Sum(Mar-13), Sum(13-Jun), Sum(Segment1), Sum(Segment 2), Sum(Segment3), Sum(segment 4) From Sample table
    group by txn status, region
    having txn status="Plan"

    then from this I created the form as shown in the excel

  5. #5
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    and what is it you need?

    you've ignored the advice from the first post and went off in your own direction, but you never state a question, did you get it resolved?

    Steve

  6. #6
    Join Date
    Jul 2012
    Posts
    9
    Hi,

    I have solved the problem. I had split the table into 2. Now I have another doubt. I have created my base two tables from an Excel Sheet. Now I wanna automate the import of data from the excel into the tables. Can Someone HELP me out in this.

  7. #7
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Since this is an entirely new question you should start a new thread. And you would be better off in the Access forum rather than the design forum, you're more likely to get the help you want.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •