-1
\$\begingroup\$

For the ADMIN: I wrote this tread on S.O. questions/61183026, but I think that is more correct to post this tread here, if you think no, please scuse me and delete this message.

Hi, I've one xls table with the stock values of goods into my warehouse, in the same file are the stock values from last 3 years. The Product Code are Unique, but the descriprione has been update some time. enter image description here

I need to check the difference from one year to other, if I group by Codice Articolo is easy, but i need to add the description. with the std qry I get one recordset for each different description like: enter image description here

my goal are group the recordset by Codice Articolo and use the last description used, I would practically use the description from the last year for each Codice Articolo. As I wrote, my table are in excel sheet and I post the results into another sheet, I use Microsoft.ACE.OLEDB.12.0 (ADODB.Recordset) This is my simple qry:

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

    Set rs = New ADODB.Recordset
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & ThisWorkbook.FullName & "; Extended Properties=""Excel 8.0;HDR=Yes;"";"

    strsql = "SELECT [Codice Articolo], [Descrizione], 
       Sum(IIf([anno]=2017,[qta],0)) AS Qta2017, 
       Sum(IIf([anno]=2017,[Tot],0)) AS Val2017, 
       Sum(IIf([anno]=2017,[€Pz],0)) AS Cad2017, 
       Sum(IIf([anno]=2018,[Qta],0)) AS Qta2018, 
       Sum(IIf([anno]=2018,[Tot],0)) AS Val2018, 
       Sum(IIf([anno]=2018,[€pz],0)) AS Cad2018, 
       Sum(IIf([anno]=2019,[Qta],0)) AS Qta2019, 
       Sum(IIf([anno]=2019,[Tot],0)) AS Val2019, 
       Sum(IIf([anno]=2019,[€pz],0)) AS Cad2019
       FROM [temp$]
       GROUP BY [Codice Articolo], [Descrizione];"

        rs.Open strsql, cn, adOpenStatic, adLockReadOnly, adCmdUnspecified
        rs.MoveFirst
        Sheets("db").Range("A2").CopyFromRecordset rs

Into my head the code will be like at this:

strsql = "SELECT [Codice Articolo], 
(Select [Descrizione] FROM [temp$] where [anno]= max([anno]) and [Codice Articolo] = [Codice Articolo]) as Descr, 
       Sum(IIf([anno]=2017,[qta],0)) AS Qta2017, 
       Sum(IIf([anno]=2017,[Tot],0)) AS Val2017, 
       Sum(IIf([anno]=2017,[€Pz],0)) AS Cad2017, 
       Sum(IIf([anno]=2018,[Qta],0)) AS Qta2018, 
       Sum(IIf([anno]=2018,[Tot],0)) AS Val2018, 
       Sum(IIf([anno]=2018,[€pz],0)) AS Cad2018, 
       Sum(IIf([anno]=2019,[Qta],0)) AS Qta2019, 
       Sum(IIf([anno]=2019,[Tot],0)) AS Val2019, 
       Sum(IIf([anno]=2019,[€pz],0)) AS Cad2019
       FROM [temp$]
       GROUP BY [Codice Articolo], Descr;"

for obtain one result like this: enter image description here

But, off course it not run.

First I need to understad:

  1. thats I need is not possible
    • I've to search one work around
  2. is the correct way, but I need to change code
    • thank in advance for any suggestions
\$\endgroup\$
2
  • 3
    \$\begingroup\$ Hello, unfortunately it seems your question is off-topic here because this site is for code working as expected, for further details you can check Code Review ontopic. I know it doesn't help but as you already described in your post SO probably is the right site for your question. \$\endgroup\$ Apr 14, 2020 at 9:33
  • 3
    \$\begingroup\$ "But, off course it not run" If the code doesn't work, it's not ready for review. Please take a look at our help center for more information. \$\endgroup\$
    – Mast
    Apr 14, 2020 at 9:39

1 Answer 1

1
\$\begingroup\$

I mistakenly mistaken CodeReview as an "adjustment" of our code, sorry.

I was sure that the solution to my problem was with JOIN QUERY, I can't see the light because I needed two JOIN.

below the solution that I found, I'm not sure is the Best solution, but it works as expected. sorry for the intrusion

strsql = "SELECT t1.[Codice Articolo], t2.Descrizione," & _
"Sum(IIf(t1.[Esercizio]=2017,t1.[NrPz],0)) AS Qta2017," & _
"Sum(IIf(t1.[Esercizio]=2017,t1.[€Tot],0)) AS Val2017," & _
"Sum(IIf(t1.[Esercizio]=2017,t1.[€Pz],0)) AS Cad2017," & _
"Sum(IIf(t1.[Esercizio]=2018,t1.[NrPz],0)) AS Qta2018," & _
"Sum(IIf(t1.[Esercizio]=2018,t1.[€Tot],0)) AS Val2018," & _
"Sum(IIf(t1.[Esercizio]=2018,t1.[€pz],0)) AS Cad2018," & _
"Sum(IIf(t1.[Esercizio]=2019,t1.[NrPz],0)) AS Qta2019," & _
"Sum(IIf(t1.[Esercizio]=2019,t1.[€Tot],0)) AS Val2019," & _
"Sum(IIf(t1.[Esercizio] = 2019, t1.[€pz], 0)) As Cad2019 " & _
"FROM ([temp$] t1 INNER JOIN [temp$] AS t2 ON t1.[Codice Articolo] = t2.[Codice Articolo]) INNER JOIN (SELECT Max([Esercizio]) AS maxdiEsercizio, [Codice Articolo] FROM [temp$] GROUP BY [Codice Articolo]) t3 ON (t2.[Codice Articolo] = t3.[Codice Articolo]) AND (t2.Esercizio = t3.maxdiEsercizio) " & _
"GROUP BY t1.[Codice Articolo], t2.Descrizione;"
\$\endgroup\$

Not the answer you're looking for? Browse other questions tagged or ask your own question.