Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query question - how to ask? (XO, 2000)

    I think I am getting what I am asking for but I obviously don
    Attached Images Attached Images

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question - how to ask? (XO, 2000)

    Try changing the Current_Child_Name and Old_Child_Name Total Function from

  3. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question - how to ask? (XO, 2000)

    Making that change does give me the desired result provided the textbox is empty. However, If I enter anything into textbox for the search criteria, the query returns nothing. In looking at the query, it seems that changing "group by" to "where" moved the search criteria to the same lines as the adult names. I need to be able to seach each name field independently.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query question - how to ask? (XO, 2000)

    Why don't you post a cutdown version of your database containing just the tables, queries and forms for this exercise.

  5. #5
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question - how to ask? (XO, 2000)

    Done - see attached
    Attached Files Attached Files

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query question - how to ask? (XO, 2000)

    What you need to do with the query is to introduce another couple of columns for Current_name and old_name and make them 'where' columns. Take off the criteria from the group by columns.
    See below:
    SELECT Tbl_Adult.Current_name, Tbl_Adult.Old_name, Count(Tbl_Child.ChildID) AS CountOfChildID
    FROM Tbl_Adult INNER JOIN Tbl_Child ON Tbl_Adult.AdultID=Tbl_Child.AdultID
    WHERE (((Tbl_Adult.Current_name) Like "*" & forms!qry_frm_person!searchbox & "*")) Or (((Tbl_Adult.Old_name) Like "*" & forms!qry_frm_person!searchbox & "*")) Or (((Tbl_Child.Current_Child_Name) Like "*" & forms!qry_frm_person!searchbox & "*")) Or (((Tbl_Child.Old_Child_Name) Like "*" & forms!qry_frm_person!searchbox & "*"))
    GROUP BY Tbl_Adult.Current_name, Tbl_Adult.Old_name;

  7. #7
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question - how to ask? (XO, 2000)

    Perfect - thank you very much.

  8. #8
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question - how to ask? (XO, 2000)

    The attachment was a big help. I created a new form (suffix NEW) that pulls the criteria properly. Hopefully this will do what you need.
    Dashiell
    Attached Files Attached Files

  9. #9
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question - how to ask? (XO, 2000)

    Great! This is a much more elegant and simple solution than what I had - I never thought of not having a subform. There is so much for me to learn! thank you.

Posting Permissions

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