Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Q about unique value queries (2003)

    My records archiving company is really causing me grief with the data they gave me. Multiple, nearly identical, but not exactly the same records for each item. I'm trying to rework the data into some structure that actually makes some sense.

    Unfortunately, I can't use Update Queries to get data to my new table because it's a 1-many join (For some reason, I can't seem to redefine the join as a 1-1 in join properties).

    I'm trying to use a "Unqiue" value queery, but I cannot see any clear way to tell it what field I want to be unique! It's not picking the correct one of course, and actually, I can't tell which field is being "uniqued."

    I'm familar with the usage of "DISTINCT" and tried to insert it into the SQL, but apparently Access has some "special" understanding of that keyword that's a secret even from the documentation, because it kicks back a "syntax error" when I try.

    Any suggestions?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Q about unique value queries (2003)

    If you set the Unique Values property of a query to Yes, the SQL statement <code>SELECT fields FROM ...</code> is changed to <code>SELECT DISTINCT fields FROM ...</code>
    This does not look at any particular field, but at the returned records as a whole: each individual field may contain duplicates, but the combination of all displayed fields is made unique.

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Q about unique value queries (2003)

    Hmm. If I am understanding what you are saying, this won't work in my case, because there is always a non-similar field in my set of records.

    Here's my situation. I have data from my records archiving company that has field like:

    Box ID
    Add Date
    DestroyDate
    Description

    Simple enough, but the problem is that there are multiple records for every BoxID. My obvious "real" key field is duplicated. The non-duplicated data is the "Description" field. Apparently somewhere, sometime, someone made the Description field to small, so if you had a longer description, you had to use more than one record.

    Why this doesn't completely scre up their RI and normalcy, I don't know. I know it's sure screwing with me!

    Anyway, what I want to do is to run update queries to my new table, slurping data from the old table, based on BoxID. But of course, I can't update because of the 1-M.

    Hmm. Since I don't care _which_ record gets returned to me, since all the other data is the same, perhaps I can use the "Top" SQL option.

    And don't ask how I'm sure that all the records for each Box ID have similar data (except for Decription), because I can't prove. Right now, I'm satisfied with expecting that to be true.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Q about unique value queries (2003)

    You could create a Totals query that groups on Box ID, Add Date and DestroyDate, but uses First as Total option for Description. This will return only one Description per Box ID.

    You could also use the Concat function that is attached to <post#=301393>post 301393</post#>. You must copy that function into a module, and you can then concatenate the Description fields per Box ID in the Totals query.

  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Q about unique value queries (2003)

    Hey I got it working! Thanks !

    I've been looking at the concat function, and I think rather than doing that, I am just going to keep the old descriptions in a subform and link to it at this point. I'm not sure reliable or usefull that information is really goin gto be to us at this point.

    I'm in "only fix broken things" mode right now.

Posting Permissions

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