Results 1 to 4 of 4
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    SUMMARIZING / ROLLUP DATA (A2K S21)

    I am looking for some advice on how to roll up data level by level.

    Table WBS contains a heiriarchial WBS structure. Table Detail contains detailed data for each WBSID. The relationship between Table WBS and Detail is one to many such that one WBS can contain many detail records. See below tables. I need to summarize this data such that if looking at WBSID C.01.01.02.01 I would see res xxx is 5, res yy is 5, for a total amt of 10. WBSID C.01.01 shows xxx = 6, yyy = 5, total = 11. C.01 also shows xxx = 6, yyy=5, total = 11. To do this, I figured to write some code that would start at the lowest level and get all data LIKE that WBSID such as select * from table WBS where WBSID LIKE C.01.01 & * to get all similar WBS elements. The code would then loop through each wbsid in the wbs table to create the summary according to the heirarchial structure.

    Is there an easier way to summarize, rollup data? Your thoughts. If not, can you give me an idea on how to select and loop through each data element of the WBSID table one at a time. I can do this for a listbox or combobox, but haven't tried it with a table. Thanks.


    <table border=1><td>TABLE WBS</td><td>WBSID</td></tr><td>C.01</td></tr><td>C.01.01</td></tr><td>C.01.01.01</td></tr><td>C.01.01.02</td></tr><td>C.01.01.02.01</td></tr>


    <table border=1><td>TABLE DETAIL</td><td>WBSID</td><td>RES</td><td>AMT</td></tr><td>C.01.01.02.01</td><td>XXX</td><td>5</td></tr><td>C.01.01.02.01</td><td>YYY</td><td>5</td></tr><td>C.01.01</td><td>XXX</td><td>1</td></tr></table>
    Regards,

    Gary
    (It's been a while!)

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: SUMMARIZING / ROLLUP DATA (A2K S21)

    I figured out one way to do this is by joining the tables to select all from table WBSID and matching records in detail and then modified the query to do the following:

    <pre>SELECT wbs.wbsid, RES.RES, RES.AMT
    FROM wbs INNER JOIN RES ON RES.WBSID LIKE (wbs.wbsid &'*');
    </pre>



    A second query based on this query can then summarize the data.

    Still want to know if there is a better way to do this. In reality the WBS table contains about 3500 records and the detail table about 75000 records.
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SUMMARIZING / ROLLUP DATA (A2K S21)

    Gary,

    I think your two-step query is probably the most efficient way to do it. It certainly beats looping through the records using DAO or ADO to create the summary.

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: SUMMARIZING / ROLLUP DATA (A2K S21)

    Hans,

    Rats. I was just thinking of looping through the data using DAO as the query in the prior post crashed the server. I guess some fine tuning / experimenting is still to come.
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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