• Program cross add in Report (2000)

    • This topic has 10 replies, 3 voices, and was last updated 22 years ago.
    Author
    Topic
    #386412

    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

    Viewing 1 reply thread
    Author
    Replies
    • #670486

      Peter,

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

      • #672698

        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

    • #670481

      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 grin, 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

      • #672700

        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

        • #672736

          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

          • #672742

            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

            • #672747

              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.

            • #672748

              No problem, thank you again.

              Peter

            • #672763

              (Edited by hasse on 01-May-03 23:02. )

              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.

            • #672956

              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

    Viewing 1 reply thread
    Reply To: Program cross add in Report (2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: