Combining rows for identical names

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am having a problem with trying to combine some data. Here is an example of
what I have now:

PMgr ExpMW ExpSE ExpTotal IncMW IncSE IncTotal
Smith, John (114.86) (430.32) (545.18)
Smith, John 992.34
100.80 1093.14
Johnson, Jim (47.79) (266.94) (314.73)
Johnson, Jim 149.86
96.03 245.89

I would like to combine the rows for each PMgr:

PMgr ExpMW ExpSE ExpTotal IncMW IncSE IncTotal
Smith, John (114.86) (430.32) (545.18) 992.34 100.80 1093.14
Johnson, Jim (47.79) (266.94) (314.73) 149.86 96.03 245.89

The data comes from two separate crosstab queries. The Exp info comes from a
linked table while the Inc info is manually entered. I am having a brain
freeze and any help would be greatly appreciated. Please let me know if you
need any more info. Thanks.
 
Try a totals (or aggregate) query.

If you are using the query grid,
Put the fields you want in the query
Select View:Totals from the Menu
In the new row Total, leave Group By for the the PMgr and select Sum for all the
other columns

The SQL statement would look like
SELECT Pmgr, Sum(ExpMW) as ExpMWTotal, Sum(ExpSE) as ExpSETotal,
Sum(ExpTotal) as ExpTotalTotal, Sum(IncMW) as IncMWTotal,
Sum(IncSE) as IncSETotal, Sum(IncTotal) as IncTotalTotal
FROM [YourTableName]
GROUP BY Pmgr
 
Thank you very much! Your advice worked very well.

John Spencer (MVP) said:
Try a totals (or aggregate) query.

If you are using the query grid,
Put the fields you want in the query
Select View:Totals from the Menu
In the new row Total, leave Group By for the the PMgr and select Sum for all the
other columns

The SQL statement would look like
SELECT Pmgr, Sum(ExpMW) as ExpMWTotal, Sum(ExpSE) as ExpSETotal,
Sum(ExpTotal) as ExpTotalTotal, Sum(IncMW) as IncMWTotal,
Sum(IncSE) as IncSETotal, Sum(IncTotal) as IncTotalTotal
FROM [YourTableName]
GROUP BY Pmgr


I am having a problem with trying to combine some data. Here is an example of
what I have now:

PMgr ExpMW ExpSE ExpTotal IncMW IncSE IncTotal
Smith, John (114.86) (430.32) (545.18)
Smith, John 992.34
100.80 1093.14
Johnson, Jim (47.79) (266.94) (314.73)
Johnson, Jim 149.86
96.03 245.89

I would like to combine the rows for each PMgr:

PMgr ExpMW ExpSE ExpTotal IncMW IncSE IncTotal
Smith, John (114.86) (430.32) (545.18) 992.34 100.80 1093.14
Johnson, Jim (47.79) (266.94) (314.73) 149.86 96.03 245.89

The data comes from two separate crosstab queries. The Exp info comes from a
linked table while the Inc info is manually entered. I am having a brain
freeze and any help would be greatly appreciated. Please let me know if you
need any more info. Thanks.
Click to expand...
Click to expand...
 
Back
Top