Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    JohnT is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    7

    Using data from a query field in two locations on a form


    I have two tables - [Employees] and [Types](eg Medical, Super etc); a query one to many - with some employees having more different types than others.

    I am trying to output a report with [Employee Name] as a heading, then a subheading ie Medical with [Type] eg showing medical details and then if there are more details for that Employee, another subheading like Super with [Type] showing super details.


    Any help would be appreciated.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,831
    your query will have the 2 tables in it, joined on EmpID.

  3. #3
    JohnT is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    7
    That's correct - 1 to many

  4. #4
    Welshgasman is offline Expert
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    657
    Look at report sorting and grouping.
    Please, please use # when posting code snippets.
    Cross Posting Etiquette: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  5. #5
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,289
    Welcome to the forum...

    Be aware that "TYPE" is a reserved word in Access and shouldn't be used for object names. "TypeS" is not a reserved word, but "Type" and "Types" are not descriptive of what the thing is. Is "Type" a type of car, a procedure, a restaurant, ???

    Problem names and reserved words in Access

    I would suggest you need at least one more table, a junction table.
    One Employee can have many "Types" (1 to many)
    One "Type" can have many Employees. (1 to many)
    So what you have is a many to many relationship - so you need a junction table.



    Something to consider........
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  6. #6
    JohnT is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    7
    Thank for that. The Types Table does have the extra "s".

    I am trying to produce an Employee Profile report with Employee details referenced to an Employees query. The detail section of the report has a list of Types in separate boxes with adjacent information of type data. This is where I am having issues. I am trying to get the Types to filter into the separated boxes. Is there another way. I have attached a pdf for clarity.
    Attached Files Attached Files

  7. #7
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,289
    The pdf is not really helpful. OTOH, the dB would be very helpful. Change/delete any sensitive data. Use names like Mighty Mouse, Daffy Duck, etc.

    So "Types" is Ticket Types?

    I am trying to get the Types to filter into the separated boxes.
    It looks top me like the "Types" are in separate boxes???
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  8. #8
    JohnT is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    7
    Thanks for that Steve.

    I have attached the DB. Hopefully it will make sense. Click on the link: https://drive.google.com/file/d/1gZB...ew?usp=sharing

  9. #9
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,712
    Post 8 was moderated, I'm posting to trigger email notifications.

    I'll delete the duplicate posts.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,289
    I made changes to your dB - for a few tables, I renamed a few fields and removed spaces and special characters.(Should never use spaces in object names).
    The report I modified is "rptEmployeeProfileSS", with a record source named "Query1". (Imaginative,no?).
    I hope this is what you are looking for.

    Naming fields like "Hist No" always makes me wonder if there is a field "Hist Yes"..
    If you mean "Number", "Num" is a better choice.

    I removed the "Layout Formaingt" when I encountered it. IMHO, they are a pain in the neck, only 1 meter lower. (I am tall )


    I did not change the Look Up FIELDS - Look Up TABLES are Good, Look Up FIELDS in TABLES are BAD!! << EDITED

    See The Ten Commandments of Access
    and The Evils of Lookup Fields in Tables


    I still think you need at least one more table, a junction table between Employees and History.
    Tables "DistinctSUBSETTYPES" and "DistinctTYPES" need to be worked on - there are issues.


    I have an Autonumber type PK field in every table. Makes it so much easier to create relationships and RI.......
    Attached Files Attached Files
    Last edited by ssanfu; 04-21-2021 at 01:53 PM. Reason: Fixed brain lock....
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  11. #11
    JohnT is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    7
    Hi Steve,

    Thank you for updating the database, analyzing it and inserting your sample sheets. I will take on board your other suggestions for future database design.

    I've attached a sample of the employee profile. As you can see, the intention is to have identical profile sheets for each employee. Only the relevant employee data needs to be generated under the various Sub Type Headings. It's OK to leave the other headings as blank when there is no data.

    On another note, I'm intrigued as to how you managed to reduce the large size of the database that I originally sent through to you. Any suggestions.
    Attached Files Attached Files

  12. #12
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,289
    Quote Originally Posted by JohnT View Post
    On another note, I'm intrigued as to how you managed to reduce the large size of the database that I originally sent through to you. Any suggestions.
    1. take a copy of your db
    2. compact and repair
    I do this frequently depending on how often I make changes to the objects/code

    For attaching to a post, next step would be
    3. zip the file


    To rRun "Compact & Repair".
    Look for "Database Tools" in the menu , then for "Compact and Repair Database".



    Quote Originally Posted by JohnT View Post
    I've attached a sample of the employee profile. As you can see, the intention is to have identical profile sheets for each employee.
    Since you have not explained what your dB is about, I am still don't know what you mean.

    Do you mean that every employee will have the same TicketType records:
    Drivers License,
    Super,
    Medical,
    Confined space,
    Union Number,
    Etc

    and you only fill in some data for the records??
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  13. #13
    JohnT is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    7
    Hi Steve,

    Again, thanks for that. The intention is to produce a standard employee profile report of one or two pages in length comprising not only of their personal details, in the top section but also a list of the qualifications, tests, licences and tickets that they may have.

    I need to propagate data for each of the subtypes, if you like super, medical or drivers’ licence etc, only if that employee has such data. The form for most may not be entirely filled under the various subtypes but that's OK. I hope that explains it a little.

    John

  14. #14
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,289
    John,

    Sorry,I still not sure what you want. Maybe you could sketch what you want the report to look like.

    I made another report that is one person per page (or 2)..... is this closer?
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  15. #15
    JohnT is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    7
    Steve,

    Thanks for that again Steve. This is close to the final outcome. The form should show/list all subtypes on the sheet whether they have data or not for an individual. I have attached two samples which I hope will give you a clearer idea of the outcome to achieve. This would give us a standardised layout for each of the employee profiles.

    John
    Attached Files Attached Files

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

Similar Threads

  1. Replies: 4
    Last Post: 11-17-2017, 10:24 PM
  2. Replies: 6
    Last Post: 05-06-2015, 12:56 PM
  3. Same query different locations different results
    By RayMilhon in forum Programming
    Replies: 4
    Last Post: 11-06-2013, 06:29 PM
  4. Combining Data from 3 locations into one database
    By jparker1954 in forum Access
    Replies: 5
    Last Post: 08-30-2011, 06:42 AM
  5. Replies: 0
    Last Post: 01-26-2011, 05:51 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
  •  
Other Forums: Microsoft Office Forums - Senior Forums