Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2013
    Posts
    5

    Unanswered: Generating Report with Points Fields

    I have a report that is purely for points gained from products. Each product is split into 3 (e.g. Investment A, B + C) and there is a indicator that means the points gained can be added up (e.g. I for Investment and S for Savings).

    I have made the report which is many unbound fields. There is a summary section for all points added together with fields Total Amount, Monthly Target, Quarterly Amount and Quarterly Target.

    Then the report is split into each of the 4 products (Each of these have different amounts of points. Number indicates it):
    Investments (10), Savings (15), Pensions (15) and Protection of Life Savings (20).

    Each of these have 4 fields - Total Amount, Target Amount, Quarterly Amount and Quarterly Target.

    I need a way to generate these point scores using the Product_Points field. I already have Quarterly Target and Target Amount because there is a set field called Points_Annual_Target.

    I also don't want it to come up with the 'insert parameters' so all the info is one page.

    Thanks.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry I haven't got a clues what you are on about. do you want to have another try using more English, less techie detail. I can't see where your problem actually lies (you've not clearly said what it is. often I find its the lack of a clear design idea that stops you from coming up with the fiox

    I think what you may need is another table which defines the points rules
    or you can fake that by using some VBA code behind the report.
    for this sort of thing you'd be best of using a table as code can be tricky to maintain over time
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2013
    Posts
    5
    Quote Originally Posted by healdem View Post
    sorry I haven't got a clues what you are on about. do you want to have another try using more English, less techie detail. I can't see where your problem actually lies (you've not clearly said what it is. often I find its the lack of a clear design idea that stops you from coming up with the fiox

    I think what you may need is another table which defines the points rules
    or you can fake that by using some VBA code behind the report.
    for this sort of thing you'd be best of using a table as code can be tricky to maintain over time
    First, thanks for replying, second, sorry for the lack of clarity.

    I basically need a calculation of some kind to add up all the points for all 4 products that have been sold and I then need to be able to do this for each individual product.

    The solution doesn't have to be super complicated as it's for my A2 coursework and I'm pretty close to my deadline.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what you could do is use a dsum for each of the categrories, and assign the value of that to a control, it could even be the source for that control
    limit the rows processed to whatever the group is

    failinmg that I'd resoprt to some VBA behind the relevant events

    declare 4 variables at the top of the report
    zero those variables in each group header (if you have more than one header/footer pairing then you'd need another set of 4 variables per header footer pairing

    add the points value in the reports format event

    in the footer fromat event assign the value of the variabels to the relavnt controls

    or
    I'd drop 4 hidden control onto the reports detail section, one for each type of point
    assign a value to each of those controls
    eg
    =iif(<whateverinidicatesthisissavings>,15,0)
    so say you had a column called RowType and it could be one of "I"nvestment,"S"avings and so om
    =iif(RowType="S",15,0)
    you could use the expression builder to set the control source if you wished

    then in the footer add four controls one for each category and set the source for those controls to be the sum of the detail controls defined above
    add another control to add those 4 values to give a total points score for this group
    you can then repeat the sum bit in other footers

    or you coudl use the IIF in the underlying query so thart uyou have the points tally in the query and use as normal on a report
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2013
    Posts
    5
    Sorry but I have no idea what any of that means or how to even go about it. I'm terrible with databases and I just want to get this done as simply as possible.

    I just need a simple way to fill in the blank boxes in the report using the fields I already have. If I need to add a 1 or 2 more then that is okay I guess.

    I've attached screens of the query I have, the report and 2 tables.
    Attached Thumbnails Attached Thumbnails Prod.png   ProdCatalogue.png   Progress1.png   ProgressQry.png  
    Last edited by ableco; 04-16-13 at 09:06. Reason: Extra info

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So you are a student, don't want to learn, but just want a solution.....
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2013
    Posts
    5
    Well if you could teach me how to fix it then I'm sure that would be fine but right now I don't have the time to learn more stuff when I have to be revising for my final exams.

Posting Permissions

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