Page 1 of 3 123 LastLast
Results 1 to 15 of 33

Need Help with an Access Report for Work

  1. #1
    Herman Dickey is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2016
    Location
    Memphis, TN
    Posts
    29

    Exclamation Need Help with an Access Report for Work

    Hi,



    I am in my third week as Employment Specialist for an organization that helps formerly homeless individuals find employment in order to remain self-sufficient once free services have ended.


    • I have entered all information into three tables:
      • Client Data (ID Number, Name, Case Manager, Program, Entry Date, etc.)
      • Onsite Service (Resume Creation, etc. for each client)
      • Independent Job Search (Job Fairs, Workshops Attended, Etc.)


    I need to pull a report that gives all job search activity for each client to give to case managers on a monthly basis, and I AM STRUGGLING!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,603
    make a query,
    qsAddDates ,
    join all 3 tables on their common field
    tClient.ClientID = tService.ClientID

    make a report from this query.
    If you want it by certain dates, then you need a form with the 2 text boxes: txtStartDate and txtEndDate

    the query would then read these off the form
    select * from qsAddDates where [ServiceDate] between forms!myForm!txtStartDate and forms!myForm!txtEndDate

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,722
    I assume each client can have multiple Onsite and Independent events. I think might need report/subreports if you want to report both Onsite and Independent data. Maybe all services/activities should be in one table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,029
    Advise to NOT use spaces, special characters in object names (save for perhaps underscore _ ). NAME is a reserved word, as is DATE. Also, I'm not seeing any means of linking client ID (ID makes a poor name for a field if you end up with more than one) to anything else, but the other details might just be missing from your post and not your db.
    Maybe check out
    http://access.mvps.org/access/general/gen0012.htm
    https://access-programmers.co.uk/for...d.php?t=225837
    What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  5. #5
    Herman Dickey is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2016
    Location
    Memphis, TN
    Posts
    29
    This is where my Access skills SUCK. Each client has a unique client ID number which is included as a separate column. I hesitate to remove the auto-generated ID number from Access. Should I? I made the ClientID column the primary key on every table. Was I wrong in doing so?

  6. #6
    Herman Dickey is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2016
    Location
    Memphis, TN
    Posts
    29
    Yes. Multiple entries are possible for each client.

  7. #7
    Herman Dickey is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2016
    Location
    Memphis, TN
    Posts
    29
    So, the report would need to be made from the FORMS instead of the QUERIES?

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,722
    If ClientID is primary key in every table then every table can have only 1 record for each client. This is a 1 to 1 relationship.

    If you want to allow multiple Onsite and Independent events for each client then ClientID should be a foreign key in those tables. A primary key is only needed if those tables have a related dependent table.

    Even if autonumber fields are not used as primary key, doesn't hurt to have in table.

    Reports are made with report objects, not FORMS.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Herman Dickey is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2016
    Location
    Memphis, TN
    Posts
    29
    So...

    ClientID (on the main table with all client details) should match CLIENT (on the individual service tables)? I am LOST.

  10. #10
    Herman Dickey is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2016
    Location
    Memphis, TN
    Posts
    29
    It's still not working! I am going to panic soon.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,722
    If you want to provide db for analysis, follow instructions at bottom of my post.

    Have you completed an introductory tutorial book? What you are describing is basic Access functionality.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Herman Dickey is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2016
    Location
    Memphis, TN
    Posts
    29

    Sample Database

    A sample of the database is attached. I would REALLY appreciate some guidance on this. Thanks!
    Attached Files Attached Files

  13. #13
    Herman Dickey is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2016
    Location
    Memphis, TN
    Posts
    29
    Just attached it.

  14. #14
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,029
    I would REALLY appreciate some guidance on this
    Hopefully this will explain the concept. It's of paramount importance to get the entity/attribute relationship right, among other things.
    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.com...ng-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    Entity-Relationship Diagramming: Part I, II, III and IV
    http://rogersaccessblog.blogspot.ca/...ng-part-i.html

    How do I Create an Application in Microsoft Access?
    http://rogersaccessblog.blogspot.ca/...cation-in.html

  15. #15
    Herman Dickey is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2016
    Location
    Memphis, TN
    Posts
    29
    Thanks! I will check these out.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. same code different report won't work
    By mainerain in forum Reports
    Replies: 3
    Last Post: 10-12-2018, 03:27 AM
  2. Replies: 4
    Last Post: 12-06-2014, 08:49 PM
  3. How to work with this type of report
    By Jamaluddin in forum Reports
    Replies: 3
    Last Post: 04-26-2012, 11:37 PM
  4. How do I work as a report in the attachments
    By azhar2006 in forum Access
    Replies: 2
    Last Post: 04-06-2012, 03:09 AM
  5. Access 2007 report does not work in 2010
    By luppi13 in forum Reports
    Replies: 5
    Last Post: 09-07-2011, 03:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums