Odbc Import - Group like data

[Davesmind]

I need a little help trying to grouping like data, but I also need to sum 2 columns of the data. To give you a better understanding I will show you an example of the data I am pulling in:

 

I am running SQL query against our main system and need to generate a report from the data. Unfortunately I am having trouble summing the last two columns and grouping the data, as I cannot create a temp table to group and sum at the end. Because of this, i receive all of the data as single lines from multiple sources.

 

 

ITEM CODE     VENDOR     QUANTITY    UNIT COST     6 MONTH      12 MONTH

Part1000          Bobs Parts     15                $2.00                  1                     0

Part1000          Bobs Parts     15                $2.00                  0                     1

Part1000          Bobs Parts     15                $2.00                  0                     1

Part1500          Bobs Parts      3                  $40.00               1                     0

Part1500          Bobs Parts      3                  $40.00               0                     0

Part1500          Bobs Parts      3                  $40.00               0                     1

 

I need to group and sum the information base on the data above. It should come out looking like this:

 

ITEM CODE   VENDOR   QUANTITY    UNIT COST    6 MONTH     12 MONTH

Part1000        Bobs Parts         15                $2.00                1                     2

Part1500        Bobs Parts          3                 $40.00              1                     1

 

I have used sub summaries, but this runs into a whole different issue totaling the unit costs and amounts. If i can group the data it also reduces my record count. 

 

Does anyone have a better solution of grouping the data either prior to importation to Filemaker or after importing the data. Currently I get the first table with no issues. I just think it would be easier for the data to come into FM combined and then I can work with the data, but I'm not sure how to keep it simple without running through the data multiple times. There are also a considerable amount of records being pulled.

 

Any help would be appreciated.