Match Mutliple Columns

[Guest]

Hi,

I have a shipment file where I need to match 3 columns that are the same
(order #, ship date, and cosignee #). Usually there are about 10 rows where
these columns match, I need to locate these and then sum up a fourth column
(weight) to make one shipment from the 10 fragments.

Example:
order # ship date cosignee # weight
123456 05162005 987654321 25.6
123456 05162005 987654321 1648
123456 05162005 987654321 46312

The result would have to read:
order # ship date cosignee # weight
123456 05162005 987654321 47985.6

Any help would be greaty appreciated

Thanks,
Sean Heckathorne

 

[Guest]

Also, I tried using the "SELECT DISTINCT" function from the help menu, but
the help menu does not specify where to write the function, and mentions
nothing of the parameters you must apparently set after trying to run the
query.

 

[Guest]

Also I tried building the "SELECT DISTINCT" function, but I was unsure on
whether to put it in the Order Number, Ship Date, or Cosignee Number column,
or if I should put it in the Weight column, and then group by sum. Also, if
I should put it in one of these columns, does it go in the criteria row, or
the expression (column title) row of the query design window. Aside from
this, when I actually get the query to run, there is a window asking for
perameters, which is not mentioned in the help file.

 

[John Spencer (MVP)]

SELECT [Order#],[Ship Date],[Consignee #], Sum(Weight) as TotalWeight
FROM [YourTable]
GROUP BY [Order#],[Ship Date],[Consignee #]

Paste that into the SQL window of a query.

If you must do it in a grid, then
Build the query to show the fields you want
Then Select Totals from the View Menu
In the new row in the grid, leave all the columns Group BY except the Weight
column.
Set the Weight column to SUM

 

[Guest]

Thanks for taking the time to reply but after a few hours I found another
way. I used "group by" on the 3 columns that needed to be grouped, then
summed the weight. Then I copied the structure of the table and gave it a
primary key, then linked the old table to the new table to root out
duplicates. Just in case anyone else needs to solve a simliar problem.

SELECT [Order#],[Ship Date],[Consignee #], Sum(Weight) as TotalWeight
FROM [YourTable]
GROUP BY [Order#],[Ship Date],[Consignee #]

Paste that into the SQL window of a query.

If you must do it in a grid, then
Build the query to show the fields you want
Then Select Totals from the View Menu
In the new row in the grid, leave all the columns Group BY except the Weight
column.
Set the Weight column to SUM

Hi,

I have a shipment file where I need to match 3 columns that are the same
(order #, ship date, and cosignee #). Usually there are about 10 rows where
these columns match, I need to locate these and then sum up a fourth column
(weight) to make one shipment from the 10 fragments.

Example:
order # ship date cosignee # weight
123456 05162005 987654321 25.6
123456 05162005 987654321 1648
123456 05162005 987654321 46312

The result would have to read:
order # ship date cosignee # weight
123456 05162005 987654321 47985.6

Any help would be greaty appreciated

Thanks,
Sean Heckathorne