Results 1 to 5 of 5
  1. #1
    Inspirz is offline Novice
    Windows 10 Access 2007
    Join Date
    Oct 2019
    Posts
    4

    MS Access DB 2007 - trying to ger a unique date value based on other column data

    Hi,

    Below is cut and paste off a Select Query output. I'm trying to eliminate the duplicate dates. To provide some context, our company runs some data and then it's saved and loaded to Teradata. In the example below, you can see we have multiple dates (7/9/2019 shows up twice and et cetera) and the reason being is the data (awto_228) was loaded to Teradata, but, then the planner realized she made a mistake and loaded another corrected case (awto_243 - hence the duplicate dates). I can't have the other case (awto_228) scrubbed from the database so what I want is to take the max case name to get my unique row value. However, when I do a MAX in the select query on case name nothing happens. Could someone please help me with this?



    Thanks,
    Mike


    PlantName StartDateTime StreamCode StreamRow PETROStreamDescription NUmber1 Number2 Number3 MaxOfCaseName NAME
    ABC 7/9/2019 abc.def LMNO..W Sour Grapes 3.38032618 1091.12535 -72.5027321 awto_228 ESE 2019 July M1
    ABC 7/9/2019 abc.def LMNO..W Sour Grapes 3.88569597 1250.10222 -76.7584626 awto_243 ESE 2019 July M1
    ABC 7/10/2019 abc.def LMNO..W Sour Grapes 3.27577072 1057.09121 -68.5873664 awto_228 ESE 2019 July M1
    ABC 7/10/2019 abc.def LMNO..W Sour Grapes 3.77450824 1214.32347 -76.7276515 awto_243 ESE 2019 July M1
    ABC 7/11/2019 abc.def LMNO..W Sour Grapes 3.27577072 1057.09121 -68.5873664 awto_228 ESE 2019 July M1
    ABC 7/11/2019 abc.def LMNO..W Sour Grapes 3.77450824 1214.32347 -76.7276515 awto_243 ESE 2019 July M1
    ABC 7/12/2019 abc.def LMNO..W Sour Grapes 3.77450824 1214.32347 -76.7276515 awto_243 ESE 2019 July M1
    ABC 7/12/2019 abc.def LMNO..W Sour Grapes 3.27577072 1057.09121 -68.5873664 awto_228 ESE 2019 July M1
    ABC 7/13/2019 abc.def LMNO..W Sour Grapes 3.77450824 1214.32347 -76.7276515 awto_243 ESE 2019 July M1

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    However, when I do a MAX in the select query on case name nothing happens
    what is the sql to your query - otherwise just speculating what the problem is and what the solution might be

    And please clarify the following:
    what does 'eliminate' mean? you have a copy of the teradata data and want to remove the duplicates from the copy? Or your query interrogates Teradata and you need to exclude the earliest ones?
    is this just to do with this one example (228 v 243) which is all duplicates
    or is there other data e.g (229/230 etc) on your file which you also need to return in your query.
    Do you need a solution that works if it happens again?
    And if so might you potentially get more duplicate sets? (eg, 228/243/250)
    I take it from the data, there is only supposed to be one record per day?

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Usually this is because of Grouping. Many of the other fields are likely designated as groups (have Group By chosen as the aggregate function in the query design grid). In your case, your different Number fields constitute one or more separate groups so you will get a CaseName for each group.

    There is probably more than one approach, but the simplest is usually to develop a query with the minimal number of groups that will return the 'seed' value (in your case, likely CaseName or more specifically, MaxOfCaseName) and use that as a join in the query with the table(s) you are currently using. Then you can SELECT * from a table(s) where a field = what the 1st query returns, or even delete or update based on that join.

    EDIT - not intending to step on any toes here.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Inspirz is offline Novice
    Windows 10 Access 2007
    Join Date
    Oct 2019
    Posts
    4
    Quote Originally Posted by Micron View Post
    Usually this is because of Grouping. Many of the other fields are likely designated as groups (have Group By chosen as the aggregate function in the query design grid). In your case, your different Number fields constitute one or more separate groups so you will get a CaseName for each group.

    There is probably more than one approach, but the simplest is usually to develop a query with the minimal number of groups that will return the 'seed' value (in your case, likely CaseName or more specifically, MaxOfCaseName) and use that as a join in the query with the table(s) you are currently using. Then you can SELECT * from a table(s) where a field = what the 1st query returns, or even delete or update based on that join.

    EDIT - not intending to step on any toes here.
    Thanks! your input actually was right and it was grouping. I just created another query to get the unqiue value.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Glad we could help. If you're done with this one, please mark it as solved.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Create unique id based on date created
    By IS2019 in forum Access
    Replies: 5
    Last Post: 02-14-2019, 08:58 AM
  2. Replies: 8
    Last Post: 02-08-2016, 11:40 AM
  3. Replies: 4
    Last Post: 12-29-2015, 03:25 PM
  4. Replies: 1
    Last Post: 04-15-2010, 02:07 AM
  5. Replies: 3
    Last Post: 09-29-2009, 07:08 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