Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Question Unanswered: combination of two fields as query criteria

    hm, must have done this before, but the brain comes to a halt...

    I simply want to say, "Give me a list of only those entries in table1 that exist more than two times for each combination of field1 and field2"

    (I want the whole listing, no matter how many entries of each combination, as long as it lists only those which exist more than two "times" - ie. in more than two records.)

    Anyone who can kick-start my tired cells today?

    D.

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    select distinct *
    from tableA ta
    where EXISTS
    (select null
    from tableA
    where column = ta.column AND
    field1 = a1.field1 AND field2 = a1.field2
    GROUP BY field1, field2
    HAVING count(*) > 2);
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Question

    Quote Originally Posted by r123456
    select distinct *
    from tableA ta
    where EXISTS
    (select null
    from tableA
    where column = ta.column AND
    field1 = a1.field1 AND field2 = a1.field2
    GROUP BY field1, field2
    HAVING count(*) > 2);
    Thanks, I am trying to fit in my own table and field names, but getting confused about TableA, ta, colunm, field1 and field2... Are some of these referring to the same?

    If the table name is tableA, I assume you give it the alias "ta" in the code above, but then below suddenly a1 appears, should that be either ta (the alias) or simply the table name, tableA?. I have tried that, but get stuck here:
    What is column?
    And how should this line read:
    field1 = a1.field1 AND field2 = a1.field2
    Example: (field1 is CID, and Field2 is SID)
    CID = tableA.CID AND SID = tableA.SID
    ?

    Seems to be something I am missing, or simply wrong with the code
    If that SQL syntax is "valid" then I am certainly learning something new here :-)

    But I have to put "something" in place of column at least.

    D.

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I did not replace all of the table aliases.

    select distinct *
    from tableA ta
    where EXISTS
    (select null
    from tableA
    where column = ta.column AND
    field1 = ta.field1 AND field2 = ta.field2
    GROUP BY field1, field2
    HAVING count(*) > 2);

    column refers to the column that muct be associated with at least 2 combintations of field1 and field2.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Question

    got it working somehow, but I do not understand the logic, need to understand it in order to use it in other scenarios too:

    Now; here is an example that works, gives me the correct output, but I really wish I understood the logic behind the syntax...:

    Code:
    select tb.CID, tb.ric, tb.datet, tb.tid
    from qry_union1 tb
    where EXISTS
    (select null from qry_union1
    where tb.TID = tb.TID AND CID = tb.CID AND ric = tb.ric AND datet = tb.datet
    GROUP BY CID, ric, datet
    HAVING count(*) > 1)
    ORDER BY tb.CID, tb.ric, tb.datet, tb.TID
    WITH OWNERACCESS OPTION;
    Firstly, I want to understand why the specifications within the where EXISTS clause does not work if referring to the qry_union1, but must point to the select fields on top (using the tb alias) that are actually pulled from the very same query..?

    Secondly; if I skip specifying tb in the first part of "where tb.TID = tb.TID ", like this: "where TID = tb.TID ", and keep the rest, which in deed have all the others on that line NOT specifying the tb in the first part, then I get no records... Why is that? (The TID is the table key that is NOT part of grouping identical records based on CID, RIC, dateT.) I might be close to understanding this, but would like some specific explanations, please.

    D.
    Win-XP pro, Access 2002, ADO 2.7, DAO 3.6. English versions of apps/OS.

  6. #6
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Question AND: specifying datepart to get only date, not time

    an,yes, another issue:
    I would like to specify that only the dd.mm.yyyy part of the general date "datet" field is used.

    I am not using this query only from forms/vba, so I need a SQL-valid way of specifying the datepart.

    I got the following code example:
    WHERE myDate >= dateserial(year(anyDate), month(anyDate), day(anyDate)),

    Can someone please but a access-query valid syntax example in the above code? (for the datet part of the where clause in the working example in my previous post in this thread) (ie. one that does not need a date() parametre or other, but within the sql statement fetches only the day, month and year from the stored general date?)

    D.
    Win-XP pro, Access 2002, ADO 2.7, DAO 3.6. English versions of apps/OS.

Posting Permissions

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