Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Program cross add in Report (2000)

    A report is built using start and finish dates. It results in a individual name and a number of weeks. Each week is totalled in the report footer. I also want to total the amount from each week for each individual. The query the basis of the report has 52 weeks which in turn comes from a table with 52 weeks in each record. therefore I cannot add a control to the table or query to sum the weeks as until the report is built I do not know what weeks will be in it. As I enter the weeks inthe report I tried to add a result which would have been the summation of the weeks but that does not work in design view (understanably). I assume the solution is to build a query programatically but have not been abale to figure out how.

    Any help greatly appreciated.

    thanks

    Peter

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

    Re: Program cross add in Report (2000)

    Peter,

    Besides the suggestions by Hasse, you might have a look at the databases attached to <post#=35485>post 35485</post#> and <post#=134439>post 134439</post#>. They demonstrate two different approaches to creating reports based on crosstab queries with dynamic column headings, with row totals.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Program cross add in Report (2000)

    Peter,

    I'm not sure if I totally understand your question, so don't be too harsh on me if you don't understand the answer <img src=/S/grin.gif border=0 alt=grin width=15 height=15>, but...
    A try: can you somewhere in your data selection & ... process rank your weeks from 1 to 52 (instead of their absolute values, which you currently use, I presume)? Such constant set of weeks nr. 1 => 52 might be easier to handle and as such a step closer to what you want to accomplish in your report,...

    E.g. Imagine a form with a text/combo box in which you enter/choose the start date of the primary selection. Use that date as a parameter in your query (by adding a calculated field, e.g. "StartDate: [forms]![EntryForm]![EntryControl]"). Derive the ranking of your weeks by comparing them to that StartDate: every 'real, absolute' week (e.g. 03/04/21 - 03/04/27) will be transformed in a 'relative' week number X where X = 1 for first week after (or including, if you wish) that date, X = 2 for second week after ...
    (To avoid negative or too high numbers, you'll have to allow only a definit range of entry values in the text/combo box.) Now, you have a constant value set which you can use in the queries & reports based on it.

    Does this help you in any way?

    If it didn't, can you provide us some more detailed & step by step information about the precise structure (fields used,...) of the tables & queries you start from & use while preparing the data source for your report?

    Hasse

  4. #4
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Program cross add in Report (2000)

    Hans

    Thanks again, have been off this project for a few days. Unfortunately cannot use crosstab as the table I built had all 52 weeks in it so there is not a different record for each week.

    Thanks

    Peter

  5. #5
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Program cross add in Report (2000)

    Hasse

    thank you as you will see from my reply to Hans the weeks are all in one record for each individual so a cross tab approach does not seem to work.

    Thanks

    Peter

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Program cross add in Report (2000)

    Peter,
    is it important that you continue working with this very table? Otherwise I suggest you convert it to a more normalised one, I mean:
    Each record in current table = field1+...+fieldx+week1+week2+...+week52
    => 52 records in converted table = field1+...+fieldx+week.
    The resulting table offers much more possibilities (I think, but the moderators might have more experience with the disadvantages which might occur too). And with a crosstab query based on it, you should be able to restore your original 'table' as well (though not directly editable in that presentation).

    The conversion can be done with a VB procedure. I've started once with a standard form for a collegue which had to do such conversions once in a while. If you're interested, I can post it.
    Greetings,
    Hasee

  7. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Program cross add in Report (2000)

    Hasse

    I have built a heap of other stuff around this table. However I cannot get what I need so I might have to look at alternatives. Would you please postyour conversion then I can have a look at it as I do not have a clue as to how to go about it.

    Thanks

    Peter

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Program cross add in Report (2000)

    Give me half an hour... I need to reinvent it moreless, as I've the CD on which I'ld burned my reusable stuff not available here.

  9. #9
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Program cross add in Report (2000)

    No problem, thank you again.

    Peter

  10. #10
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Program cross add in Report (2000)

    <P ID="edit" class=small>(Edited by hasse on 01-May-03 23:02. )</P>Peter,
    I regret to have the form not available in which you could just enter the variables (table & field names) & just needed to push a button. I'm running out of time here, so I'll give you my method already. If you're a programmer, you'll know what to do. Otherwise, give me some time & I'll post the entire code.

    ORIGINAL TABLE

    tblCrosstab = cr_ID + cr_FldCst + cr_Week01 + ... crWeek52
    with
    cr_ID = primary key
    cr_Week01 - cr_Week52 = 52 week fields
    cr_FldCst = fields which contain info which is the same for all weeks (e.g. name,...)

    GOAL TABLE

    tblConverted = co_ID + co_IDcr + co_FldCst + co_Week + co_Data
    with
    co_ID = Autonumber (for simplicity's sake)
    co_Week = week number (1 => 52)
    co_Data = information stored in the former cr_Week... fields
    Remark: co_Data field type = co_Week... field type

    CROSSTAB QUERY RESTORING ORIGINAL

    TRANSFORM First(tblConverted.co_Data) AS FirstOfco_Data
    SELECT tblConverted.co_IDcr, tblConverted.co_FldCst
    FROM tblConverted
    GROUP BY tblConverted.co_IDcr, tblConverted.co_FldCst
    PIVOT tblConverted.co_Week;

    PROCEDURE

    Principle:
    - define variables for your original & goal table (recordset) & fields
    - fill goal table with original table fields' content by looping through all records of rstOriginal, and (nested) looping through all fields. For each field, evaluate field name (select case) and copy field content/name part to goal table. You have three cases
    (1) field = ID field (copy field content!)
    => co_IDcr = cr_ID
    (2) field = field with general information (copy field content!)
    => co_FldCst = cr_FldCst
    (3) field = variable field (copy both field content & field name!)
    => co_Week = variable in field name
    (eg 01 => 52: derived with function like Cint(Right(field.name,2)))
    co_Data = content of variable field name

    Assumptions:
    - you create the goal table manually
    - you copy the original table's key field to the goal table too
    (1) in the beginning: for controle purposes (?)
    (2) you preserve the possibility to link the 'surrogate' crosstab query with the other tables & queries originally linked to the original table.
    - all field's content should be imported in the new table
    => in case you want to skip certain fields, you can manage this with code

    ps I don't know if it's a good idea, or rather stupid mentioning... but I hope no-one will publish the code & form for own merits without contacting me first. A year and a half ago I started writing an article in which I used the concept (in a more general way) as a tip/article draft for an Access e-zine. The editor asked me if I'ld be interested to use it for a contribution and even if I didn't find time for it yet, it's still on my to do list... (as I'm no 'pro' programmer - it would be great to try out that chance... you don't have so often an idea that's useful for others in a more general way :-)). (From the other hand, I wonder if it's still worth the effort, as it might be general stuff which also can be found in some advanced Access books/white paper. But that's a question any moderator maybe can answer at... though I'm afraid I'll need to give some more information then...)

    I have to run - good luck & let it know if my post is (not) understandable enough for your own use!

    p.s. 030501: I edited some details: former line 11 is now in the end of actual line 12: name (of e.g. athlete, if you're making training schemes) = example of general info, not of an ID! Further, I corrected some wrong field name prefixes which I overlooked.

  11. #11
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Program cross add in Report (2000)

    Hi Hasse

    Just a quick note to let you know I have your post. I am afraid I am not much of a programmer as you may have gathered from my posts, but I think I have the general idea of what you say and will see if I can make it work. I would like to solve my problem with your approach as it will at least mean I have learnt something new.

    thanks for you time and effort.

    Regards

    Peter

Posting Permissions

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