Results 1 to 15 of 15
  1. #1
    moneypennie21 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    41

    Grouped query results how to refer to them in Macro

    HI,


    I have a database with several tables related to the main table in a 1:M relationship. I have a select query that pulls information from these tables together. I have a form which enables me to search by any of the following fields by clicking on an associated button for each search combo
    Name
    Task
    Process
    Company
    Customer

    However each person may have multiple tasks, processes or customers assigned to them. I only want the results to bring back the first result as I only want one listing per person and then when the user clicks on the details he can see all of the other tasks and processes for this person. However when I add grouping to the query to show 1st result I find that my search macro no longer works as it was referring to the field name 'task' or 'Process' which no longer exists as in the query it is now 'firstoftask' or 'firstofprocess' is there anyway to refer to these in the macro? I have tried several versions and cannot get it to work.
    Attached Thumbnails Attached Thumbnails Capture.PNG  

  2. #2
    moneypennie21 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    41
    This the macro that sits behind the search by task button for additional info - it n longer works when I add a grouping on the query that the main search form is based onClick image for larger version. 

Name:	Capture.PNG 
Views:	31 
Size:	9.1 KB 
ID:	28848

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I don't use macros... too limiting...


    It sounds like you have multiple buttons that perform searches by clicking on an associated button for each search combo.

    Maybe change the macro Where condition?
    From
    Code:
    = [Tasks]=[Forms].[Main_search_Form]![Task_Combo]
    to
    Code:
    = [FirstOFTasks]=[Forms].[Main_search_Form]![Task_Combo]
    since FirstOFTasks is now the name of the field (query column) that is being searched......?????

    Not really enough info to give a better response...

  4. #4
    moneypennie21 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    41
    HI Steve, I did try a variation on this and it didn't work, However I have been advised to change the field name in the query to Task: as this will keep the field name in the form as well and this has done the trick

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    However I have been advised to change the field name in the query to Task:
    If you have a colon in the field name, you could experience problems.... I'm just saying.....

    Some naming suggestions:
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not use look up FIELDS, multi-value fields or calculated fields in tables.
    Do not begin object names with a number.



    Good luck with your project........

  6. #6
    moneypennie21 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    41
    HI Steve, it actually hasn't solved my problem anyway as I have noticed it creates a different issue! I have attached screen shots of my forms so you can see what I mean. but basically you are correct I have a search form with different buttons on to search by different parameters on the contact details form. the problem is if someone has multiple tasks or processes associated to them I have attached a contact details form to illustrate what I mean, because the query is grouped to show first if I search for a task and someone has it as the second task assigned to them the search results don't show it. can I fix this with code instead of a macro? I just want to hit the 'search by task' button and if any contact in the list has this tasks assigned to them at all I want to see their details in the search results but only once not listed for every task they have which is what was happening previously, does this make sense?
    Click image for larger version. 

Name:	Search form.PNG 
Views:	28 
Size:	17.1 KB 
ID:	28884
    Click image for larger version. 

Name:	Search results form.PNG 
Views:	28 
Size:	8.7 KB 
ID:	28885

    Click image for larger version. 

Name:	contact details form.PNG 
Views:	28 
Size:	31.8 KB 
ID:	28887
    Attached Thumbnails Attached Thumbnails contact details form.PNG  

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    If you only want to show one record returned from the search, make the form a single occurrence. In fact, have both!

    Main form, showing only one record, doesn't matter if this is the first or last or any in between. Its Default View property will be Single.
    Subform showing all the results returned from the search, with Default View set to Continuous or Datasheet.

    No grouping required, the query will be a straight select query.

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Rather than using macros, I would build a filter using VBA code. Here is an example of "filtering on the fly" that I use and modify:
    http://www.allenbrowne.com/ser-62code.html

    --------------------------------------------------------------------

    I've been looking at your Post#1 & the images in Post #6.
    the more I look at the relationship image, the more I am convinced there is something "off" about the relationships. Maybe it is because I don't know what your "business process" is........

    Would you explain what you do/track? layman terms - no computer jargon.


    Your main tables appears to has employee(?) info. The main table is linked to others tables in 1-to-many relationships.
    I understand about "Comments_table". One records in the main can have many records in the comments table.
    Can one record in the main table have many Teams in the "Teams_table"?

    Lets say that main table has a record with the Contact_ID = 1 and the Full_Name = "Donald Duck". You enter some tasks/processes/teams/comments/customers.
    Then you add a new record: Contact_ID = 2 and the Full_Name = "Mickey Mouse" and enter some tasks/processes/teams/comments/customers.
    Do you then enter a new record with Contact_ID = 3 and the Full_Name = "Donald Duck", entering some tasks/processes/teams/comments/customers that are different than where Contact_ID = 1?

    With records where Contact_ID = 1 and Contact_ID = 3, I would expect a separate table for, well, lets call them employees, because record 1 & 3 deal with the same person.

    And how do you tell records apart with no dates in the main table?


    The table structure just doesn't seem to be normalized (to me).

  9. #9
    moneypennie21 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    41
    Hi Steve,

    after reading your post I realised that you are correct and one person should only have one team. I have corrected this relationship to 1:1 and made the Contact ID primary key on Team table. However in answer to your question I would expect 1 person to only have one contact ID and therefore one record. One record in the main table may have multiple tasks, comments, Processes, associated group email address - this is because although one person sits in a specified team they may be responsible for several tasks which sit across different processes in our business. The user may not know the team they are looking for hence the need to be able to search for task.

    This is why task, process, comments, group email address are on separate tables with 1:M relationship's however I want every task/Process/group email address/comment to show on the contact card for the specified person/contact ID which is why I have added them as subforms. However my issue is if one person has 2 tasks assigned to them as in the above example where the user is responsible for Test task 99 and Test Task 200 if I use the search by tasks button and search for Test task 200 (the second tasks in the list assigned to this person) this contact id/person is not shown in the results as the search is only searching the first tasks assigned for some reason - suspect this has to do with the FIRST grouping on the query but not sure. If I take the grouping off the query this person is shown in the results twice - once for every task assigned to them but the record is the same. If I search for them by name or company or any other variable they are shown in the results 4 times - once for each process and once for each task assigned. Again each record is the same - what I want is one result returned regardless of how many tgasks/process/comments this person has assigned to them and then when I click on it want to see the contact details card for this person/contact id which shows everything that is assigned to them on the sub forms.

    Hope this makes sense!

    Appreciate to someone in the know I may have gone about this in a strange way but I am very new to Access and still learning!

    Jen

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I have corrected this relationship to 1:1 and made the Contact ID primary key on Team table.
    Nope, still not right. Should not have a 1:1 relationship.

    You are still explaining HOW you are doing things, not WHAT the process is.
    Try telling me what the business is, like I am a 10 year old. NO computer jargon.

    Are you tracking members? Tasks?



    How many teams are there?
    How many tasks are there?


    Which of these statements are true?
    -----------------------------
    One member can have many tasks?
    One task can be assigned to many members?
    One task is assigned to one member?

  11. #11
    moneypennie21 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    41
    HI Steve,

    sorry got caught up on other things!

    Right, this is a contacts database but the issue is that the users may not always know the name of the person they need, they may know what task they want signing off or looking at or they may know what process they are dealing with but not the person or the task. They may also want to view all of the contacts from a particular company or team. so one contact may be from one company and one team but work on several processes and tasks.

    so I need to be able to assign more than one task/process to each contact but only have them listed in the search results once when a user searches by any of the parameters Task, Company, Name, Process.

    Does this make sense>

    Thanks

    Jen

  12. #12
    moneypennie21 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    41
    Also to answer your question about which statement is true

    One member can have many tasks and one task can be assigned to many members are both true.

    Thanks

    Jen

  13. #13
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    So given that I don't know anything about your project, I did play around and create a dB.
    Remember, there was not a lot of info on how things fit and work together.

    Click image for larger version. 

Name:	Relationship1.jpg 
Views:	17 
Size:	67.7 KB 
ID:	29053

    Is this anywhere close??
    Attached Files Attached Files

  14. #14
    moneypennie21 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    41
    HI Steve, it is very similar but there are some differences in the relationships. I have attached a version of my database for your reference. my issues are
    1, if a contact has more than one task assigned to them and yoTESTING DATABASE.zipu use the search by task it will only search the first tasks associated to contacts and not show them in the results if you have searched for a tasks that is assigned to them as a secondary or later task
    2, if a contact has more than one process assigned to them then they are shown twice in the results.

    Not sure how to fix either of these!

  15. #15
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I've tried several ways to get this to work, but no joy.

    I still think it is a problem with the table structure/relationships.
    And/or my understanding of the problem is not clear enough...

    Still looking at it....

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 05-20-2016, 05:55 AM
  2. Replies: 7
    Last Post: 01-19-2016, 05:28 PM
  3. Retun grouped results of a query
    By cbenisch in forum Access
    Replies: 3
    Last Post: 05-28-2013, 06:27 PM
  4. Replies: 10
    Last Post: 08-01-2012, 11:32 AM
  5. Replies: 2
    Last Post: 04-19-2012, 12:53 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