Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84

    Unanswered: Select DISTINCT anomaly?

    I have a table [Combined Wells Data] with four columns:
    Well Name - Text 50
    Year - Text 4
    Easting - Double, 2 decimal places
    Northing - Double, 2 decimal places

    Each well has a set of coordinates for each year it was checked.

    I am trying to execute a select distinct query on the table to get a listing of unique well names and their coordinates . The problem is that I'm getting well names returned more than once (I have 347 unique well names) using the following query:
    SELECT DISTINCT [Combined Wells Data].[Well Name], [Combined Wells Data].Easting, [Combined Wells Data].Northing

    Most of the easting and northing coordinates for the duplicate well names are the same, yet are being displayed multiple times. I've also used the following query:
    SELECT DISTINCT [Combined Wells Data].[Well Name], Count([Combined Wells Data].Year) AS CountOfYear, [Combined Wells Data].Easting, [Combined Wells Data].Northing

    It produces the same result. Anybody got an explanation or a way to get around this? I really need to find wells with multiple sets of coordinates.
    Regards,
    Terry

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Lightbulb Mulit year so multi data

    if you get more then 1 record off the same Well there has to be more records in 1 year or same data in a different year


    set in your where statement

    >
    where datepart("yyyy",[Combined Wells Data].Year )=2002

    >





    and you will only get the info off 2002 and unique

  3. #3
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84

    Re: Mulit year so multi data

    Originally posted by Marvels
    if you get more then 1 record off the same Well there has to be more records in 1 year or same data in a different year


    set in your where statement

    >
    where datepart("yyyy",[Combined Wells Data].Year )=2002

    >





    and you will only get the info off 2002 and unique
    In the first query, I'm not using the year field. I get duplicate records where the values all look to be the same (i.e. well name matches with another well name, easting matches another easting for the same well name, northing matches another northing ...) The duplicate records shouldn't be repeated if the values are the same.

    I'm wondering if there's something in the data that isn't represented for the coordinates (i.e. some kind of rounding on the double numbers). How would I find out if this is occuring?
    Regards,
    Terry

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I would try running the SELECT DISTINCT on one field at a time, to
    see which one is causing the problem.

    You are probably on the right track with the rounding thing...
    Inspiration Through Fermentation

  5. #5
    Join Date
    Apr 2003
    Location
    Manchester, NH
    Posts
    57

    try this

    try putting brackets around your distinct clause so it only selects distinct well names. right now you are asking it to select distinct all of that information, instead of just asking for distinct well names and then the other corresponding information on those distinct records. That should fix the problem.

    Dalynch

  6. #6
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84

    Re: try this

    Originally posted by dalynching
    try putting brackets around your distinct clause so it only selects distinct well names. right now you are asking it to select distinct all of that information, instead of just asking for distinct well names and then the other corresponding information on those distinct records. That should fix the problem.

    Dalynch
    My problem is that I'm searching for wells that have more than one location (easting and northing values). The select distinct query is checked for duplicates on well name to identify when a well has more than one location.

    My problem is that the query selects a set of supposedly distinct well name, easting and northing values that are visually identical. I'm wondering why that would be?

    Interestingly, if you look at the group-by query in my original post, when I change the group by clause on easting and northing to:
    SELECT DISTINCT [Combined Wells Data].[Well Name], Min([Combined Wells Data].Easting) AS MinOfEasting, Min([Combined Wells Data].Northing) AS MinOfNorthing

    I get my list of 347 unique well names and locations. The real problem is that I still need to check for wells listed in more than one location.
    Regards,
    Terry

  7. #7
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84

    Checking into rounding erros

    I created the following two queries:
    SELECT DISTINCT [Combined Wells Data].[Well Name], Max([Combined Wells Data].Easting) AS MaxOfEasting, Max([Combined Wells Data].Northing) AS MaxOfNorthing

    and

    SELECT DISTINCT [Combined Wells Data].[Well Name], Min([Combined Wells Data].Easting) AS MinOfEasting, Min([Combined Wells Data].Northing) AS MinOfNorthing

    Then ran a calculation query that would display the differences between location minimums and maximums:

    SELECT [Well names and maximum coordinate values].[Well Name], [Well names and maximum coordinate values]![MaxOfEasting]-[Well names and minimum coordinate values]![MinOfEasting] AS [Easting Diff], [Well names and maximum coordinate values]![MaxOfNorthing]-[Well names and minimum coordinate values]![MinOfNorthing] AS [Northing Diff]
    FROM [Well names and maximum coordinate values] INNER JOIN [Well names and minimum coordinate values] ON [Well names and maximum coordinate values].[Well Name] = [Well names and minimum coordinate values].[Well Name]
    WHERE ((Not ([Well names and maximum coordinate values]![MaxOfEasting]-[Well names and minimum coordinate values]![MinOfEasting])=0)) OR ((Not ([Well names and maximum coordinate values]![MaxOfNorthing]-[Well names and minimum coordinate values]![MinOfNorthing])=0));

    What I got was a list of locations that either had a 0.000000000116415321826935 difference between their min and max easting values or 0.000000000232830643653870 difference in nothing min and max values. There's got to be some kind of conversion/rounding error going on here! It's too consistent. By the way, my partner in crime asked if 0.000000000232830643653870 is twice the value of 0.000000000116415321826935. YUP!

    Any ideas?
    Regards,
    Terry

  8. #8
    Join Date
    Apr 2003
    Location
    Manchester, NH
    Posts
    57
    can you zip up the db and post it, i'll take a look at it this evening?

    Dalynch

  9. #9
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Exclamation Expl

    It does not matter if you do not select the year. If the year is a part off the table and you don't set it in the statement, the info you will get from your SQL is a view from your table but WITHOUT the year info. So if your table consist off info about unique wells (or not) with extra info but in different years. Your query will show just the info that you ask.


    ie
    if you set : SELECT DISTICT NAME , BIRTHDATE FROM X
    (and you exclude the year it will offcourse give you double info)
    But if you ask WHERE year = 2001 youll only get info off that range
    Table X

    Name birthdate CheckYear
    ____ _______ __________

    PersonA 5-20-1969 2000
    PersonA 5-20-1969 2001
    PersonB 01-5-1969 2000
    PersonB 01-5-1969 2001

  10. #10
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84
    I'm going back a ways in time here, but I figured I needed to clear up some SQL syntax issues. The following is from Microsoft's Access Help file:
    --------------------------------------------------------------------------
    DISTINCT Omits records that contain duplicate data in the selected fields. To be included in the results of the query, the values for each field listed in the SELECT statement must be unique. For example, several employees listed in an Employees table may have the same last name. If two records contain Smith in the LastName field, the following SQL statement returns only one record that contains Smith:
    SELECT DISTINCT
    LastName
    FROM Employees;

    If you omit DISTINCT, this query returns both Smith records.

    If the SELECT clause contains more than one field, the combination of values from all fields must be unique for a given record to be included in the results.

    --------------------------------------------------------------------------

    I was only looking for wells that had more than one location (Easting and Northing or X,Y). The use of distinct points out a rounding error on the DOUBLE type, otherwise I would have got ten records for each well (total of 3470 records, 347 wells and ten years of data on each one).
    Regards,
    Terry

  11. #11
    Join Date
    Apr 2004
    Posts
    182
    Distinct only works if you query one field at a time

  12. #12
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84
    That's simply not true. Read the whole excerpt from Microsoft on Disticnt queries with multiple fields.
    Regards,
    Terry

  13. #13
    Join Date
    Apr 2004
    Posts
    182
    When you use DISTINCT where you are selecting multiple fields, the combination of the selecteds fields must be unique to be displayed.

    Quote below from Microsoft
    DISTINCT Omits records that contain duplicate data in the selected fields. To be included in the results of the query, the values for each field listed in the SELECT statement must be unique. For example, several employees listed in an Employees table may have the same last name. If two records contain Smith in the LastName field, the following SQL statement returns only one record that contains Smith:
    SELECT DISTINCT
    LastName
    FROM Employees;

    If you omit DISTINCT, this query returns both Smith records.

    If the SELECT clause contains more than one field, the combination of values from all fields must be unique for a given record to be included in the results.

    The output of a query that uses DISTINCT is not updatable and does not reflect subsequent changes made by other users.

  14. #14
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84
    I need to use DISTINCT in this query and I need to use all three fields. I knew how to use DISTINCT when I created the query and fully expected at least one set of "duplicate" records to be in the query results. I knew I had one well that would qualify - I was looking for others.

    I am trying to find instances of where there is more than one location for a well. Since a well is really in only one physical location, this query should help identify wells that need to have some data corrected (when used in a select duplicates query).

    My problem is that Access seems to be creating a miniscule rounding error (on the coordinate fields) that is falsely reporting multiple locations for wells. The size of the rounding error seems to be consistent. I'm really wondering if anyone has experienced the same situation and extent of error.

    Please take a closer look at post #7 of this thread.
    Last edited by spraguetr; 05-14-04 at 09:59.
    Regards,
    Terry

  15. #15
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm not sure what is causing this, but you could use ROUND as a hack to get around it.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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