0

Sorry hard to figure out the subject.

I have following SQL query which needs to be converted to MS Access. Since Access doesnt have row_number() or it is used with transform, I cant get the desired results. Is there some workaround to get same results.

Original table/query result:

enter image description here

Next SQL query:

[SELECT *
   FROM (SELECT d.loc_area,
                d.loc_rack,
                d.loc_height,
                d.loc_place,
                d.locvrc,
                row_number() OVER(PARTITION BY d.loc_area, d.loc_rack, d.loc_place
                                   ORDER BY d.loc_height) rn_pivot
           FROM (select locvrc,
                        SUBSTR(stoloc, 0, 2) loc_area,
                        SUBSTR(stoloc, 4, 2) loc_rack,
                        SUBSTR(stoloc, 6, 1) loc_place,
                        SUBSTR(stoloc, -2) loc_height
                   FROM locmst) d)
  PIVOT (MAX(loc_height) AS EXT, MIN(locvrc) AS UNIQ FOR rn_pivot IN ('1' AS HEIGHT_1, '2' AS HEIGHT_2, '3' AS HEIGHT_3, '4' AS HEIGHT_4, '5' AS HEIGHT_5, '6' AS HEIGHT_6, '7' AS HEIGHT_7, '8' AS HEIGHT_8))
  ORDER BY LOC_AREA,
        LOC_RACK,
        LOC_PLACE]

It gives me nice pivot where it summarize three first row and lay down rest 2columns (height, locvrc) in the same row: Pivot

Question is how to do this in Access. Im linking my sql table to Access table and try to perform the query from that table. Transform which Im trying:

TRANSFORM First(loc_height) AS FirstHeight
SELECT loc_area, loc_place, loc_rack
FROM 
    (SELECT
            loc_area,
            loc_place, loc_rack,
loc_height
        FROM tbl_labels
    )
GROUP BY loc_area, loc_place, loc_rack
PIVOT loc_height

It does the job, but outcome is not what is required. Also this is missing the locvrc, cant get the multiple value to work: Access_Pivot There is blanks, or the height is now in the columns and it shows all height that is in query.

Any idea how to convert this to similar what in SQL?

Thanks a lot in advance!

Br Jan

Jan00
  • 45
  • 5
  • 1
    Argh. My recommendation is to find a database other than MS Access to move the code to. Why take multiple steps backwards when there are free databases that are more compliant and easier to use? – Gordon Linoff Sep 23 '20 at 12:11
  • 2
    I suggest you run your original query as **Pass-Through query** in Access. You can use the server SQL, and get the result in Access. – Andre Sep 23 '20 at 13:02
  • Thank you for the comments, I agree with different database might be better option, but right now we have so much in access and this is just addon function so have to deal with this. I will give the pass through a shot, was intend to test that, just wished there would be better way so I could use my already exist table which are made by forms :) thanks! – Jan00 Sep 23 '20 at 17:53
  • 1
    In Access, use of DCount() can generate a row sequence number for each group. Review https://stackoverflow.com/questions/64032273/ms-access-or-excel-convert-data-from-rows-to-columns-in-unknown-range-of-colum/64033149#64033149. Then one way to transform on multiple data points is to build two CROSSTAB queries then join them in a SELECT. – June7 Sep 24 '20 at 19:05

0 Answers0