Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jul 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have enclosed a fictitious database that I need some help with in configuring the reports to do what I want them to do. Here are my questions:

    1. I have 4 reports that I need to create monthly. Instead of clicking on all of them individually and entering the date range for each report, I'd like to create a macro to open them in Print mode and be able to program the macro so that I only have to enter the date range one time instead of 5 times (1 for each report). How can this be done?

    2. Within my report "Assessment Results", I'm determing age groups. This report is still quite young and incomplete, but I can't go further without help. I'd like to be able to break down the "Male" and "Female" categories this way, showing the correct count in each category:
    9 or younger = 7 (7 would be the number of clients 9 or younger)
    10 to 13 = 8 (8 would be the number of clients ages 10 to 13)
    14 to 17 = 3 (3 would be the number of clients ages 14 to 17)
    18+ = 1 (1 would be the number of clients ages 18+)
    How can I accomplish this?

    3. Setting the order of the way a report prints data. I am familiar with the Header/Footer selections for setting the order of the way a form prints, but what if I don't want it in alphabetical or chronological order? What if I want it in an order that is specific to my own agenda? For example, on the "Secondary Problem" report, it alphabetizes the headings. I don't want them that way. I want to see - for example - Grief/Loss 1st on the list, Tobacco Related 2nd on the list, Bullying 3rd on the list, etc. How can I manipulate my report to print the way I want it to?

    4. In the true database I am creating, which will be exactly like this fictitious one, I will need to do much data entry. Sometimes when there is a certain field on the data entry sheet where there's more than one entry for the same person, how can I avoid having to enter that person multiple times to capture all the data? For example, a ficitious person is coming in for reason 1, 5, and 7 (all associated with their own meanings). Is there a way I can enter all 3 of those codes into the "Reason" field instead of having 3 lines of data for the same person? Maybe entering them with colons between, or commas, or something? Somehow that when I enter them, I can still pull all the pertinent information out onto reports as if I entered three lines of data? This process, if possible, will same huge amounts of entry.

    Thank you to ANYONE and EVERYONE who pulls this thread. If you have any questions on what I'm looking for, please don't hesitate to contact me and I will try to further clarify!
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I attach a demo that does much of what you want.

    Notes about some other changes I have made.
    • Don't use special characters in table or field names. I have renamed # Stats to just tblStats
    • Tables should have a primary Key. I have added EventID to tblStats. It is these IDs that I use for counting in the reports.
    • Don't use Date as a field name as it is an Access Reserved word. I have changed it to EventDate.

    Quote Originally Posted by mbetts View Post
    1. I have 4 reports that I need to create monthly. Instead of clicking on all of them individually and entering the date range for each report, I'd like to create a macro to open them in Print mode and be able to program the macro so that I only have to enter the date range one time instead of 5 times (1 for each report). How can this be done?
    Launch the reports from a form where the dates are entered into text boxes on the form. See the demo

    2. Within my report "Assessment Results", I'm determing age groups. This report is still quite young and incomplete, but I can't go further without help. I'd like to be able to break down the "Male" and "Female" categories this way, showing the correct count in each category:
    9 or younger = 7 (7 would be the number of clients 9 or younger)
    10 to 13 = 8 (8 would be the number of clients ages 10 to 13)
    14 to 17 = 3 (3 would be the number of clients ages 14 to 17)
    18+ = 1 (1 would be the number of clients ages 18+)
    How can I accomplish this?
    Add a calculated field to the query that uses if statements to create an Age Group, and use that on the report.

    3. Setting the order of the way a report prints data. I am familiar with the Header/Footer selections for setting the order of the way a form prints, but what if I don't want it in alphabetical or chronological order? What if I want it in an order that is specific to my own agenda? For example, on the "Secondary Problem" report, it alphabetizes the headings. I don't want them that way. I want to see - for example - Grief/Loss 1st on the list, Tobacco Related 2nd on the list, Bullying 3rd on the list, etc. How can I manipulate my report to print the way I want it to?
    Add a sort order field to the SAP Online Reporting table, and use that.

    4. In the true database I am creating, which will be exactly like this fictitious one, I will need to do much data entry. Sometimes when there is a certain field on the data entry sheet where there's more than one entry for the same person, how can I avoid having to enter that person multiple times to capture all the data? For example, a ficitious person is coming in for reason 1, 5, and 7 (all associated with their own meanings). Is there a way I can enter all 3 of those codes into the "Reason" field instead of having 3 lines of data for the same person? Maybe entering them with colons between, or commas, or something? Somehow that when I enter them, I can still pull all the pertinent information out onto reports as if I entered three lines of data? This process, if possible, will same huge amounts of entry.
    Access 2007 now supports multivalued fields, but I never use them as they are not consistent with the rules about proper database design.
    Instead you should separate the data on the child, and the data on the "events" into separate tables. You would then do the data entry via a form/subform combination. First you would choose an existing student or add a new one, then enter the event in the subform.

    [attachment=89475:SAP Online Database_JH.zip]
    Attached Files Attached Files
    Regards
    John



  3. #3
    New Lounger
    Join Date
    Jul 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [attachment=89495:Win-zip file to send.zip]

    John!
    Thank you so much, you were of tremendous help with what I needed to accomplish! I've reworked my reports to reflect your suggestions and changes, and I'm getting exactly what I need!

    I do have some confusion though with the frmLaunchreport you created. I have never worked with one of these, so I don't understand if I'm misunderstanding you or I'm just not doing it right. I created a few more reports and attached them into the frmLaunchReport. But how do I get it to run? When I click on Preview Report, nothing happens. Another question, do I need to in each query set it to prompt me for the date range as I normally do or does the frmLaunchReport actually take care of all of that? I'm reattaching the updated sample database for your referral.

    Thank you again so much! You really enlightened me to things about Access that I didn't even know existed!

    Marlena
    Attached Files Attached Files

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Hi Marlena

    You do not need to put parameter prompts into the queries. The code behind the Preveiw Report button takes the dates from the form, and uses them to restrict the data shown.

    Once I remove the prompts from your queries the reports open fine for me. You should not need to do anything else. The code says to open whatever report has been chosen from the list, and that works for me.
    Regards
    John



  5. #5
    New Lounger
    Join Date
    Jul 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Something just isn't working right. I removed the prompts from my database and tried to use the frmLaunchReport. I am able to type the date range, but then nothing happens when I click on Preview Report. It doesn't even try to do anything. Do you actually see reports come up? I tried putting this into a macro, still nothing when I hit the Preview Report button. I also tried altering the date range on the frmLaunchReport and then going back and manually opening each report, but the date range reported does not reflect my change. For example, I was using a year's worth of data, and I entered just one month. The reports came back with a year's worth. What am I missing?

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Did my original example ever work for you? I thought your reply meant that just the reports you added did not work.

    It sounds like you are bumping into Access security issues..What versions of Access are you using?

    In 2007 you need to run databases from a trusted location, if they contain code.
    You need to go into Access Options and choose the Trust Center and then click Trust Center Settings and choose Trusted Locations and add the location of the database to your trusted locations.
    Regards
    John



  7. #7
    New Lounger
    Join Date
    Jul 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    John,
    Again, let me say that you have been so helpful, and patient with me as I endeavor this Access road! Thank you.

    I was never able to get the original example of formLaunchReport to work for me. That's what was so confusing. I tried the steps you suggest below (and thank you for being very specific with how to access the Trust Center). I was able to reach the point of trying to add the location of my database, but was kicked out due to our IT regulations where I work. I currently have several calls and e-mails into the IT department for someone to help me get the security clearance I need to set up the Trust Center for my database. This is a very frustrating process. I will keep you posted how I fare out with their support.

    One other glitch I'm finding - and this could also be an IT issue on my end - is with the multivalued fields. I was considering various scenarios based on your suggestion to use a separate table for client information and a separate table for all the other valued information. That probably won't work for me for this database after I spoke to the supervisor of the department I am creating it for. So, I began to dabble with multivalued fields just to see what would happen. I followed the exact steps from the microsoft lookup wizard "Create the Multivalued Lookup Based On a Table or Query". The very last step before "FINISH" says to select the box to allow multiple values. There is no checkbox on that screen. I don't know if that makes sense, but all of the steps were completely accurate to that one, then, it almost treated me like I wasn't able to enter multiple values so therefore, it didn't even give me the option of seeing a check box to select it before I hit finish. I did hit finish and saw my dropdown entry box which looked wonderful, I just could not select more than one item due to missing that one step. Does that sound to you like a security issue on my end?

    Thank you again! I hope I haven't taken up too much of your time!
    Marlena

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I never use multivalued fields so I can't be of much help. I don't know where to find the "Create the Multivalued Lookup Based On a Table or Query" wizard either.

    Are you using the mdb format or the accdb format? Multivalued fields are not available in mdbs.
    Regards
    John



  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I believe John has identified the issue - the original database you posted was in the .mdb format, which does not support multi-valued fields. And I totally agree with him as well regarding the use of multi-valued fields being a bad idea. I've had experience using them in a very old database format that was disasterous. The better solution is a simple linked table that is displayed on a form that shows the reasons for the contact. Letting a supervisor (who presumably is not familiar with relational databases table desing) dictate the design of the schema usually causes grief.
    Wendell

Posting Permissions

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