Select distinct rows from ms query results

[scottomlinson]

Each month we need to calculate rebates to certain customers. Currently this is done by hand by running a report, keying the values into a spreadsheet that has blank cells for data entry and calculation cells.
I can pull the transactional detail from the database using MS Query via ODBC. The data looks like this:
A B C D E F G H I
1 date invnum cust qty price cost extprice extcost prftamt
2 3/1/11 515784 123 1512 3.068 3.063 4638.82 4631.26 7.56
3 3/1/11 515784 123 7434 2.832 2.827 21053.09 21015.92 37.17
4 3/1/11 515806 155 1512 3.068 3.063 4638.82 4631.26 7.56
5 3/1/11 515806 155 4810 2.832 2.827 13621.92 13597.87 24.05


We have a tab for each customer. On Customer 123's tab the result we are looking for is:

A B C D E F G
1 Date Invnum Amt Profit Cost Basis Rebate
2 3/1/11 515784 25691.90 44.72 25647.18 256.47 128.24
3
4
5

I want 1 row for each invoice number (B-Invnum), invoice date (A-Date) with the sum of extended price (C-Amt), sum of extended cost (E-Cost). The other columns are calculations.
How do I retrieve those single summed rows from the data? There could be 1, 2, 3 or 4 lines for a given invoice number.

thanks in advance!

 

[GQue]

Are you pulling the data into a sheet then looking for it to be copied out to each customer tab based off the cust column in the data sheet?

Or are you looking to pull the information in line by line from the DB and as it is read, put it into the next available row for that customer?

I personally would do it as it is reading the data, and go to the cust sheet based on that value, find the next row and add in the information.

    Sheets("CUSTOMER " & CUST_VAR).Select
    Range("A2").Select 'Header column
    Selection.End(xlDown).Select
    MyNextRow = Selection.Row
    Cells(MyNextRow, 1) = DATE_VAR
    Cells(MyNextRow, 2) = INVNUM_VAR
    Cells(MyNextRow, 3) = QTY_VAR
    'ETC...

Hope that helps...

 

[scottomlinson]

Are you pulling the data into a sheet then looking for it to be copied out to each customer tab based off the cust column in the data sheet?
Yes.

Or are you looking to pull the information in line by line from the DB and as it is read, put it into the next available row for that customer? No

I personally would do it as it is reading the data, and go to the cust sheet based on that value, find the next row and add in the information.

    Sheets("CUSTOMER " & CUST_VAR).Select
    Range("A2").Select 'Header column
    Selection.End(xlDown).Select
    MyNextRow = Selection.Row
    Cells(MyNextRow, 1) = DATE_VAR
    Cells(MyNextRow, 2) = INVNUM_VAR
    Cells(MyNextRow, 3) = QTY_VAR
    'ETC...

Hope that helps...

actually I am more than a little baffled at the code. I see "MyNextRow"... and it seems like I am putting the data in columns? I would like to have a row in my customer tab for each distinct invoice in the raw data. a distinct invoice could have 1,2,3 or 4 lines in the raw data depending on the products on the invoice and I want the sum of the extended cost and extended price for those invoice lines.
am i making sense?

 

[GQue]

That snippet was just to get you started.

I will outline it as best I can without writing all the code for you...

First you will have your import code that pulls from the DB. You can use ADODB (be sure to add the reference).

That will allow you to run the query against your database. Then as each line comes in, you can use the data from that row returned from the database to populate your customer sheets.

That MyNextRow is a variable that is storing the next available line on the customer tab so you can insert your data.

I am assuming that your workbook is already setup with the customer tabs as you stated and you are accessing a database that you can query.

Below is an example of what I am talking about...

Sub GetInfo()
Application.ScreenUpdating = False
    Set dbConn = New ADODB.Connection
        myConn = "PROVIDER=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;"
        myConn = myConn & "DATA SOURCE=SERVERXX;INITIAL CATALOG=MyDatabase;"
    dbConn.Open myConn
    Set myRS = New ADODB.Recordset
    With myRS
        .ActiveConnection = dbConn
        .Open "select * from myRebates"
    End With
    a = sRow
    Do Until myRS.EOF
        Call FillCustomerTab(myRS.Fields(0).Value, myRS.Fields(1).Value, myRS.Fields(2).Value, myRS.Fields(3).Value)
        myRS.MoveNext
    Loop
    myRS.Close
    dbConn.Close
Application.ScreenUpdating = True
End Sub
 
Sub FillCustomerTab(myDate As Date, myCustomer As String, myQnty As Long, myPrice As Long)
Sheets(myCustomer).Select
Range("A2").Select 'Header column
Selection.End(xlDown).Select
MyNextRow = Selection.Row
Cells(MyNextRow, 1) = myDate
Cells(MyNextRow, 2) = myQnty
Cells(MyNextRow, 3) = myPrice
End Sub

Hope that gets you moving in the right direction and clears up the confusion I caused...

 

[GQue]

I neglected to mention that that code I just gave you is assuming you have Windows Authentication to your SQL Server...