Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140

    Unanswered: Update query using Group By

    Mornin' all,
    I'm having a brain fart.
    I've got a query to pull records by the last value assigned to it. Tables are set up using a M:n layout to track disposition changes. I've got it so it'll pull the data correctly, but, I forgot using the Group By (Last) makes it so you can't edit the fields. (All fields from the table being edited have been pulled)

    I've tried nested queries, but, it still sees the group by and kills any edits. I'll post the plain SQL for the primary query. If ya'll need more information let me know and I'll be glad to post the relations for those tables.

    I've got the sinking feeling I'm going to have to use a temp table or open two recordsets to make this work. If anyone has any other ideas I'd be more than happy to try 'em out. Using multiple recordsets wouldn't be the end of the world, but, if there's a cleaner way to go it'd be nice.

    Thanks,

    Sam

    Code:
        rawSQL = "SELECT tbl_Item.Item_ID, Model_ID, Item_Serial_Number, Item_Weight, " _
            & "Item_5R_Barcode, Item_Customer_Barcode, Item_Inactive, Item_Date_Created, " _
            & "Item_Time_Created, Item_User_Created " _
            & "FROM tbl_ItemDepartmentDisposition INNER JOIN (tbl_Item INNER JOIN " _
            & "tbl_ItemDisposition ON tbl_Item.Item_ID = tbl_ItemDisposition.Item_ID) " _
            & "ON tbl_ItemDepartmentDisposition.DeptDisp_ID = " _
            & "tbl_ItemDisposition.DeptDisp_ID " _
            & "GROUP BY tbl_Item.Item_ID, Model_ID, Item_Serial_Number, Item_Weight, " _
            & "Item_5R_Barcode, Item_Customer_Barcode, Item_Inactive, Item_Date_Created, " _
            & "Item_Time_Created, Item_User_Created " _
            & "HAVING Last(tbl_ItemDepartmentDisposition.Disposition_ID) = 16 " _
            & "AND Model_ID = " & CLng(cbo_Model.Column(0)) _
            & " AND Item_Serial_Number Is Null " _
            & "AND Item_5R_Barcode Is Null " _
            & "AND Item_Customer_Barcode Is Null " _
            & "AND Item_Inactive = False;"
    Last edited by SCrandall; 09-30-10 at 13:00. Reason: fix typos
    Good, fast, cheap...Pick 2.

  2. #2
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    LoL Ok, multiple recordsets it is then.

    Sam
    Good, fast, cheap...Pick 2.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not necessarily.
    You are correct - ACE\JET is dumb and won't allow any query containing an aggregate to be updated, even if the aggregate is irrelevant to the update.
    You have a few options. The one I would seriously consider is using DMAX() - it is a means to incorporate a max aggregation function without using the SQL MAX().

    A minor point - only ever put tests on aggregates in your HAVING clause - everything else should be in the WHERE clause - this is more efficient.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump View Post
    A minor point - only ever put tests on aggregates in your HAVING clause - everything else should be in the WHERE clause - this is more efficient.
    tell that to MS Access
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    And your beloved query designer
    TBF, it is still possible to follow best practice in the designer, it just requires extra work.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    Ahh ok, thanks, I haven't used the MAX/DMAX functions much. If they function like the Last() function I'll definitely give 'em a try.
    I will definitely rewrite the query including the WHERE for the generic requirements, now that you mention that I (vaguely) remember hearing that before, I'm usually more busy trying to clean up the MS parenthesis pukefest that I forget things like combining the WHERE/HAVING clauses.

    Thanks again for the insight, I'll give 'er a go.

    Sam
    Good, fast, cheap...Pick 2.

  7. #7
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    Quote Originally Posted by pootle flump View Post
    And your beloved query designer
    TBF, it is still possible to follow best practice in the designer, it just requires extra work.
    LoL I'm lazy, if I can get a program to give me a (somewhat) workable query that I only need to tweak ruther than having to build the entire statement from the ground up...

    Sam, especially when linking 4+ tables...
    Good, fast, cheap...Pick 2.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by SCrandall View Post
    ...the MS parenthesis pukefest
    brilliantly descriptive

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by SCrandall View Post
    LoL I'm lazy, if I can get a program to give me a (somewhat) workable query that I only need to tweak ruther than having to build the entire statement from the ground up...

    Sam, especially when linking 4+ tables...
    I don't blame you when working with Access - I write everything in the designer too. If I am working in SQL Server however I write everything by hand.

    LAST() is a somewhat naughty aggregate function - it is not relationally sound and is proprietary to Access. I expect though that the way you use it is interchangeable with MAX() - you had best confirm this though.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    Ok, I think I've got the primary query sorted (Max did funky things to me...I may not have been using it correctly). After doing some extra research, it appears that the Last() function has some fairly major flaws if you don't arrange your query in exactly the right way, i.e. doesn't necessarily give the last record entered unless you explicitly sort the table.

    I need some verification that I'm attacking this correctly (never used this approach before)

    Am I correct in assuming that as long as I force a sort on the date/time that records were entered, and then look for the last Disposition_ID it will give me the actual last Disposition_ID entered for that Item? Or am I barking up the wrong tree/making this more complicated than it needs to be.

    Here's my currently revised SQL for the query shown in the first post.

    Code:
        rawSQL1 = "SELECT Item_ID, Model_ID, Item_Serial_Number, Item_Weight, " _
            & "Item_5R_Barcode, Item_Customer_Barcode, Item_Inactive, Item_Date_Created, " _
            & "Item_Time_Created, Item_User_Created " _
            & "FROM " _
                & "(" _
                & "SELECT tbl_Item.Item_ID, Disposition_ID, Model_ID, Item_Serial_Number, " _
                & "Item_Weight, Item_5R_Barcode, Item_Customer_Barcode, Item_Inactive, " _
                & "Item_Date_Created, Item_Time_Created, Item_User_Created, " _
                & "ItemDisposition_Date_Created, ItemDisposition_Time_Created " _
                & "FROM tbl_ItemDepartmentDisposition INNER JOIN (tbl_Item INNER JOIN " _
                & "tbl_ItemDisposition ON tbl_Item.Item_ID = tbl_ItemDisposition.Item_ID) " _
                & "ON tbl_ItemDepartmentDisposition.DeptDisp_ID = tbl_ItemDisposition.DeptDisp_ID " _
                & "WHERE Model_ID = " & CLng(cbo_Model.Column(0)) _
                & "AND Item_Serial_Number Is Null " _
                & "AND Item_5R_Barcode Is Null " _
                & "AND Item_Customer_Barcode Is Null " _
                & "AND Item_Inactive = False" _
                & "ORDER BY ItemDisposition_Date_Created, ItemDisposition_Time_Created" _
                & ") AS tbl_ItemDetails " _
            & "GROUP BY Item_ID, Model_ID, Item_Serial_Number, Item_Weight, Item_5R_Barcode, " _
            & "Item_Customer_Barcode, Item_Inactive, Item_Date_Created, Item_Time_Created, " _
            & "Item_User_Created " _
            & "HAVING Last(Disposition_ID) = 16;"

    It *appears* to work as desired, but, I probably don't have enough test data to actually run it through the wringer and see if it breaks...

    Any thoughts/suggestions would be appreciated.

    Thanks again,

    Sam
    Last edited by SCrandall; 10-01-10 at 16:35.
    Good, fast, cheap...Pick 2.

Posting Permissions

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