I found the following query on here and found it useful. Having made the query work for my database I would now like to add a parameter to it to filter out records that are not required - the table required for the parameter is called Season with a join from the members table on seasons.idseasons = members.members id no and the parameter would be WHERE (((seasons.Season)=[enter season])).

Select  (tMin.Initial1 + ' and ' + tMax.Initial1) As initial, tMax.surname1 As surname, tMax.[first line address], tMax.[second line address], tMax.town, tMax.postcode, tMax.GroupCount From (Select Distinct Max(initial) As initial1, Max(surname) As surname1,[first line address], [second line address], town, postcode, Count() As  GroupCount From members Group By [first line address], [second line address], town, postcode Having Count() = 2) As tMax Inner Join

   (Select Distinct Min(initial) As initial1, Min(surname) As surname1, [first line address],[second line address], town, postcode, Count(*) As GroupCount
   From members
   Group By [first line address], [second line address], town, postcode
   Having Count(*) = 2) As tMin

           On (tMax.[first line address] = tMin.[first line address]) And 
              (IIf(IsNull(tMax.[second line address]), '', tMax.[second line address]) = IIf(IsNull(tMin.[second line address]), '', tMin.[second line address])) And 
              (tMax.town = tMin.town) And  
               (tMax.postcode = tMin.postcode)

UNION ALL Select Max(initial) As initial1, Max(surname) As surname1, [first line address], [second line address], town, postcode, Count() As GroupCount From members Group By [first line address], [second line address], town, postcode Having Count() = 1 Or Count(*) > 2

ORDER BY surname, initial, town, postcode;

Not sure where the parameter should go within the code. Can you help with this?

I found the following query on here and found it useful. Having made the query work for my database I would now like to add a parameter to it to filter out records that are not required - the table required for the parameter is called Season with a join from the members table on seasons.idseasons = members.members id no and the parameter would be WHERE (((seasons.Season)=[enter season])).

Select  (tMin.Initial1 + ' and ' + tMax.Initial1) As initial, tMax.surname1 As surname, tMax.[first line address], tMax.[second line address], tMax.town, tMax.postcode, tMax.GroupCount From (Select Distinct Max(initial) As initial1, Max(surname) As surname1,[first line address], [second line address], town, postcode, Count() As  GroupCount From members Group By [first line address], [second line address], town, postcode Having Count() = 2) As tMax Inner Join

   (Select Distinct Min(initial) As initial1, Min(surname) As surname1, [first line address],[second line address], town, postcode, Count(*) As GroupCount
   From members
   Group By [first line address], [second line address], town, postcode
   Having Count(*) = 2) As tMin

           On (tMax.[first line address] = tMin.[first line address]) And 
              (IIf(IsNull(tMax.[second line address]), '', tMax.[second line address]) = IIf(IsNull(tMin.[second line address]), '', tMin.[second line address])) And 
              (tMax.town = tMin.town) And  
               (tMax.postcode = tMin.postcode)

UNION ALL Select Max(initial) As initial1, Max(surname) As surname1, [first line address], [second line address], town, postcode, Count() As GroupCount From members Group By [first line address], [second line address], town, postcode Having Count() = 1 Or Count(*) > 2

ORDER BY surname, initial, town, postcode;

Not sure where the parameter should go within the code. Can you help with this?

• ms-access
• parameters
• union

1 Answer 1

Try this where both the two subqueries and the second query have been expanded with an Inner Join and a Where clause:

    Inner Join
        Seasons
        On members.memberid = Seasons.idseasons
    Where
        Seasons.season = [Enter Season]

Select  
    (tMin.Initial1 + ' and ' + tMax.Initial1) As initial, 
    tMax.surname1 As surname, 
    tMax.[first line address], 
    tMax.[second line address], 
    tMax.town, tMax.postcode, 
    tMax.GroupCount 
From 
    (Select Distinct 
        Max(initial) As initial1, 
        Max(surname) As surname1,
        [first line address], 
        [second line address], 
        town, postcode, 
        Count() As  GroupCount 
    From 
        members
    Inner Join
        Seasons
        On members.memberid = Seasons.idseasons
    Where
        Seasons.season = [Enter Season]
    Group By 
        [first line address], 
        [second line address], 
        town, 
        postcode 
    Having Count() = 2) As tMax 

Inner Join

    (Select Distinct 
        Min(initial) As initial1, 
        Min(surname) As surname1, 
        [first line address],
        [second line address], 
        town, 
        postcode, 
        Count(*) As GroupCount
    From 
        members
    Inner Join
        Seasons
        On members.memberid = Seasons.idseasons
    Where
        Seasons.season = [Enter Season]
    Group By 
        [first line address], 
        [second line address], 
        town, 
        postcode
    Having Count(*) = 2) As tMin

    On  (tMax.[first line address] = tMin.[first line address]) And 
        (IIf(IsNull(tMax.[second line address]), '', tMax.[second line address]) = IIf(IsNull(tMin.[second line address]), '', tMin.[second line address])) And 
        (tMax.town = tMin.town) And  
        (tMax.postcode = tMin.postcode)

UNION ALL 

Select 
    Max(initial) As initial1, 
    Max(surname) As surname1, 
    [first line address], 
    [second line address], 
    town, 
    postcode, 
    Count() As GroupCount 
From 
    members 
Inner Join
    Seasons
    On members.memberid = Seasons.idseasons
Where
    Seasons.season = [Enter Season]
Group By 
    [first line address], 
    [second line address], 
    town, 
    postcode 
Having 
    Count() = 1 Or Count(*) > 2
ORDER BY 
    surname, 
    initial, 
    town, 
    postcode;