Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182

    Ignoring Duplicat Information if it Appears in A separate column


    Hello. I am having difficulty creating a query.

    I have a hypothetical table with the following fields:

    EventNumber
    ActionType
    State1
    State2
    State3

    I want to run a query that will tell me how many times a State was linked to an EventNumber.

    In other words, my date can look like this:
    EventNumber Action Type State1 State2 State3
    1 Alpha Virginia
    2 Alpha Washington Alabama
    3 Beta Nevada
    4 Alpha Virginia Virginia
    5 Gamma New York Florida Maine


    When I run my query, "Virginia" should only be counted TWICE. Not three times. "Virginia" is mentioned once in #1 and twice in #4. I want State2:Virginia to be ignored because State2 already lists "Virginia"

    To put it another way: If State1 = (State2 or State3) THEN IGNORE State2/State OR IF State2 = (State3), then IGNORE State3.

    I hope that makes sense.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Is it too late to correct the design of the table? If not, it should be normalized:

    Fundamentals of Relational Database Design -- r937.com

    If so, you can use a UNION query to normalize it, then run a simple totals query against that.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    Quote Originally Posted by pbaldy View Post
    Is it too late to correct the design of the table? If not, it should be normalized:

    Fundamentals of Relational Database Design -- r937.com

    If so, you can use a UNION query to normalize it, then run a simple totals query against that.

    Its way to late to normalize it. Plus, Im not permitted to make major changes to the table

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Lots of queries.

    Q1 - get list of state1:
    SELECT Table1.id, Table1.State1 FROM Table1 WHERE (((Table1.State1) Is Not Null));

    Q2 - get list of state2:
    SELECT Table1.id, Table1.State2 FROM Table1 INNER JOIN Query1 ON Table1.id = Query1.id WHERE (((Table1.State2) Is Not Null And (Table1.State2)<>Nz([Query1].[State1])));

    Q3 - get list of state3:
    SELECT Table1.id, Table1.State3 FROM (Table1 INNER JOIN Query1 ON Table1.id = Query1.id) INNER JOIN Query2 ON Table1.id = Query2.id
    WHERE (((Table1.State3) Is Not Null And (Table1.State3)<>Nz([Query1].[State1]) And (Table1.State3)<>Nz([query2].[State2])));

    Q4 - get list of all states:
    SELECT * FROM Query1
    UNION SELECT * FROM Query2
    UNION SELECT * FROM Query3;

    Q5 - count them:
    SELECT Query4.State1, Count(Query4.id) AS CountOfid FROM Query4 GROUP BY Query4.State1;

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I don't think all the queries and joins are necessary, as a UNION query will eliminate duplicates anyway (without ALL specified).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Good to know, can you supply the SQL?

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Your Q1 3 times, once for each state field.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Oh right:
    SELECT ID,State1 As StateX FROM Table1 Where Not IsNull(State1)
    UNION SELECT ID,State2 As StateX FROM Table1 Where Not IsNull(State2)
    UNION SELECT ID,State3 As StateX FROM Table1 Where Not IsNull(State3);

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I should have added that my understanding is that this is more efficient in SQL:

    Where State1 Is Not Null

    though the difference might be negligible in smaller tables. IsNull() is a function that has to be called/resolved. Is [Not] Null is native to SQL, and is more flexible (can be used in SQL Server, etc).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    Thanks for the help guys. So to get this straight, all I need to do is use this SQL?

    SELECT ID,State1 As StateX FROM Table1 Where Not IsNull(State1)
    UNION SELECT ID,State2 As StateX FROM Table1 Where Not IsNull(State2)
    UNION SELECT ID,State3 As StateX FROM Table1 Where Not IsNull(State3);

    Can someone explain to me what StateX is referring to?

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    StateX is an alias for the state field created by the query. You need a totals query on top of that, as I mentioned originally.

    SELECT StateX, Count(*) As HowMany
    FROM QueryName
    GROUP BY StateX
    Last edited by pbaldy; 06-08-2017 at 11:46 AM. Reason: fix typo
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    Quote Originally Posted by pbaldy View Post
    StateX is an alias for the state field created by the query. You need a totals query on top of that, as I mentioned originally.

    SELECT StateX, Count(*) As HowMany
    FROM QueryName
    GROUP BY State1
    Sorry. I am still confused. Would I need to create three different StateX queries? So StateX, StateY, and StateZ? To represent the former State1, State2, and State3?

    And furthermore, am I going to be making multiple queries as suggested by aytee111? Also, I shouldve mentioned this before, I am selecting State1 directly from another query. Not directly from a table.

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Put aytee's SQL from post 8 into a query exactly as is and save it. Then run my query from post 11, using the name of the query you just saved in place of QueryName.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    Quote Originally Posted by pbaldy View Post
    Put aytee's SQL from post 8 into a query exactly as is and save it. Then run my query from post 11, using the name of the query you just saved in place of QueryName.
    When I run the second query, I receive an error: Your query does not include the specified expression 'StateX' as part of an aggregate function.

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What exactly is your sql? I edited mine right after posting.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 8
    Last Post: 08-31-2016, 07:55 AM
  2. Replies: 1
    Last Post: 05-28-2016, 07:06 AM
  3. Replies: 3
    Last Post: 04-03-2016, 03:00 PM
  4. Replies: 2
    Last Post: 02-05-2015, 03:51 PM
  5. Replies: 4
    Last Post: 10-15-2014, 05:51 AM

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