Início / coding / Perguntas / 79422705
Accepted
Eliz A Purves
Asked: 2025-02-08 13:57:03 +0800 CST

Como adicionar um parâmetro a uma consulta de união

  • 772

Encontrei a seguinte consulta aqui e achei útil. Tendo feito a consulta funcionar para meu banco de dados, agora gostaria de adicionar um parâmetro a ela para filtrar registros que não são necessários - a tabela necessária para o parâmetro é chamada Season com uma junção da tabela de membros em seasons.idseasons = members.members id no e o parâmetro seria 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;

Não tenho certeza de onde o parâmetro deve ir dentro do código. Você pode ajudar com isso?

ms-access

1 respostas

  1. Best Answer

    Experimente isto:

    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;
    • 0

relate perguntas