Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2013
    Posts
    3

    Unanswered: Grouping Code/Function that works in MS Access - Can't get it to work in SQL Server.

    Howdy and Good Afternoon!

    I'm in bit of a dilemma here re: this here snippet.

    (*Works) MS Access:

    GroupNum: Val(DCount("*","wodq","grp=" & [grp] & " And [ID]<" & [ID]))\4

    (*Doesn't Work) SQL:

    SELECT ID, FacilityPerStream, GRP, WOID,
    (SELECT COUNT(*) AS ANiceCount
    WHERE (dbo.wodq.GRP = dbo.wodq.GRP) AND (dbo.wodq.ID < dbo.wodq.ID)) / 4 AS GroupNum
    FROM dbo.wodq

    *DESIRED RESULT* (GroupNum to move +1 after every 4 w/ the same ID/Facility/GRP/WOID Combo) Example Below:

    ID FacilityPerStream GRP WOID GroupNum
    616 Transformer Technologies 29436 294 0
    617 Transformer Technologies 29436 294 0
    618 Transformer Technologies 29436 294 0
    619 Transformer Technologies 29436 294 0
    620 Transformer Technologies 29436 294 1

    Any help would be loaded w/ appreciation.

    Much Thanks!

    *Attached a few pics for a better idea.
    Attached Thumbnails Attached Thumbnails Grp MSA.bmp   Grp MSA Result.bmp   Grp SQL.bmp  
    Last edited by RaySkidburn; 02-19-13 at 17:20. Reason: Cleaner

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have you copied the SQL that works in Access into SQL server?

    WHERE (dbo.wodq.GRP = dbo.wodq.GRP) AND (dbo.wodq.ID < dbo.wodq.ID)) / 4 AS GroupNum

    don't knwo what you are trying to do but you are settigh a where clause that will alwasy be true as you are comparing the same value on both sides.

    \4 in VBa means using integer division, not sure if that is valid on SQL server
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2013
    Posts
    3
    Quote Originally Posted by healdem View Post
    have you copied the SQL that works in Access into SQL server?

    WHERE (dbo.wodq.GRP = dbo.wodq.GRP) AND (dbo.wodq.ID < dbo.wodq.ID)) / 4 AS GroupNum

    don't knwo what you are trying to do but you are settigh a where clause that will alwasy be true as you are comparing the same value on both sides.

    \4 in VBa means using integer division, not sure if that is valid on SQL server
    Yeah, no such luck at all (rejects the Val and DCount (and surely works differently than JET (been a while since I've even had to use SQL Server for something like this)).

    What I'm trying to do is get every 5th record for the same WorkOrderID/WorkOrderDetailId/Facility combination to jump to the next page of a report (only 4 lines of waste streams allowed on each manifest copy we use).

    To accomplish that I made that little Access snippet and it changes the 5th, 9th, 13th, etc... record to the next GroupNum. It works fine in Access though I have to migrate to SQL Server for web-based reasons and this is the only kink in my chain so far - and the most important as all 3 different types of manifests/bill of lading/etc... can only have 4 line items per page.

    Thanks for your quick reply, sir.

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    You should not be doing this in the database. In tiered architecture, all presentation formatting is done in the presentation layers. Monolithic tools, like ACCESS and COBOL will do everything in the same code module. Having said that, the skeleton for building grouping number is

    SELECT x, y, z,.
    ((ROW_NUMBER() OVER (ORDER BY ??) -1) / @grouping_size)
    AS grp_nbr
    FROM ??
    WHERE ??;

    SQL will do integer math if the divisor and dividend are both integer.

  5. #5
    Join Date
    Feb 2013
    Posts
    3
    Quote Originally Posted by Celko View Post
    You should not be doing this in the database. In tiered architecture, all presentation formatting is done in the presentation layers. Monolithic tools, like ACCESS and COBOL will do everything in the same code module. Having said that, the skeleton for building grouping number is

    SELECT x, y, z,.
    ((ROW_NUMBER() OVER (ORDER BY ??) -1) / @grouping_size)
    AS grp_nbr
    FROM ??
    WHERE ??;

    SQL will do integer math if the divisor and dividend are both integer.
    Thanks a ton - was taking a gander and saw the mentioning of over/row_number, etc. (as well as different opinions/thoughts) but wasn't sure of the skeleton. This is enough to get me started - thanks!

    I agree (obviously) w/ the tiered/formatting/etc.... though I was hoping I could get it all in SQL as that groupnum setup in Access slows down the report by about 10 seconds. If I have to leave that particular query in Access - then so be it; was getting greedy.

Posting Permissions

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