Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283

    Appending record with highest number into table

    Hello,

    I am trying to write an append query to append records that have the highest number in one field(Field1) by using the Max() function with criteria in another field(Field2) being 178 or 179. But for some reason The max function is not working for me. it is still appending multiple records having the 178 criteria for example with different values in Field1. Is there another way to pull the highest number?

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    The Max function is an Aggregate Function that works across records within a single field.
    If you want to compare two separate fields and return the larger value, one way to do that is like this:
    Code:
    IIF([Field1]>[Field2],[Field1],[Field2])

  3. #3
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    I'm not comparing Field1 and Field2 together to get the max number. Field1 is the number in a text field which I want the max value from and Field2 is other criteria I want to pull certain records.

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Please post the SQL code of your query.

  5. #5
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    INSERT INTO Table4 ( ID, Field1, Field2, Field3, Field4 )
    SELECT Table1.ID, Max(Table1.Field1) AS MaxOfField1, Table1.Field2, Table1.Field3, Table1.Field4
    FROM Table3 INNER JOIN (Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID) ON Table3.Field2 = Table1.Field2
    GROUP BY Table1.ID, Table1.Field2, Table1.Field3, Table1.Field4
    HAVING (((Table1.Field3)="178" Or (Table1.Field3)="179"));

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    That is because of your Grouping. You are Grouping not just by ID, but by Fields2, Fields3, and Fields4.
    So the only records that would be combined/collapsed by your Aggregate Query are ones in which all 4 of those fields are EXACTLY the same.

    Can you do us a favor, and post a small data sample along with your expected results, so we can clearly see what it is you are trying to accomplish?

  7. #7
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    ID Field1 Field2 Field3 Field4
    1 11111 Y0002_1 Mark Y
    2 11112 Y0002_2 Jess Y

  8. #8
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    And my expected result is to append the record with the highest Field1 number to Table4

  9. #9
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    In that example, none of your fields are the same, so nothing would be grouped/reduced!
    Grouping only eliminates records when certain fields have the same values for multiple records.

    Are you just saying that in your entire table, you want to find the record that has the highest value Field1, and then write that entire record to Table4?
    So you will only ever be writing one, single record to field 4?
    Is Field1 unique? If not, what happens if multiple records have the same "max" value?

  10. #10
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    Okay then, forget the Grouping, sorry. There can be records with the same ID but different field1 values. So I want to append that record with the highest Field1 value to Table4, and I'm appending all of the fields to Table4. And no there won't be any records of the same value in Field1.

  11. #11
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Try this:
    Code:
    INSERT INTO Table4 ( ID, Field1, Field2, Field3, Field4)
    SELECT ID, Field1, Field2, Field3, Field4
    FROM Table1
    WHERE Field1 in
    (SELECT Max(Table1.Field1) AS MaxOfField1
    FROM Table1
    WHERE Table1.Field3 In ("178","179"));

  12. #12
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    it works on the sample tables and such I gave you but when I try to apply it to my code it doesn't work..

  13. #13
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    How exactly does it not work?
    If it works against the sample tables, that means that the logic is solid, and there is some difference between the sample your provided and your actual data. Your job is to find out what that difference is.
    A few places to start:
    - Is the ID field in Table4 an Autonumber field?
    - What Data Type are Field1 and Field3? Based on the code you posted in post #5, I assumed Field1 is Numeric and Field3 is Text.

  14. #14
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    They are all text fields. And this is the code that won't let me run it:

    (SELECT Max(dbo_AET_DTRR_T.(TransactionDate) AS MaxOfTransactionDate
    FROM dbo_AET_DTRR_T
    WHERE dbo_AET_DTRR_T.ReplyCode In ("178","177"))));

  15. #15
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Try running these two standalone queries separately, and see if you are able to run each one:

    Query1:
    Code:
    SELECT Max(dbo_AET_DTRR_T.(TransactionDate) AS MaxOfTransactionDate
    FROM dbo_AET_DTRR_T;
    Query2:
    Code:
    SELECT dbo_AET_DTRR_T.ReplyCode
    FROM dbo_AET_DTRR_T 
    WHERE dbo_AET_DTRR_T.ReplyCode In ("178","177");

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

Similar Threads

  1. Replies: 8
    Last Post: 02-08-2017, 07:58 PM
  2. Replies: 15
    Last Post: 12-22-2016, 12:16 PM
  3. Replies: 5
    Last Post: 03-03-2016, 08:13 PM
  4. Replies: 12
    Last Post: 12-25-2015, 03:25 PM
  5. Replies: 2
    Last Post: 12-05-2013, 12:09 PM

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