Customer Sales Data

[Jeff G.]

Is there a way to merge duplicate names while at the same time adding the
sales entry associated with the name entry.

(Customer/Sales data was imported from Excel. If John Smith was a customer
4 times, there would be four "john smith" rows each with a different sales
purchase amount in a separate column. I am trying to combine the 4 rows into
a single john smith row but with the combined total sales amount of the 4
different purchases)

Thanks!

 

[Michel Walsh]

Sure.

SELECT name, SUM(sale)
FROM tableName
GROUP BY name



There is a rule for 'group by' or 'total' queries: every listed field in the
select clause must either be in the group by list, either finally
aggregated. As example, you cannot use:


SELECT name, department, SUM(sale)
FROM tableName
GROUP BY name


but you can use either:


SELECT name, department, SUM(sale)
FROM tableName
GROUP BY name, department


either


SELECT name, FIRST(department), SUM(sale)
FROM tableName
GROUP BY name


as example.




Hoping it may help,
Vanderghast, Access MVP

 

[Jeff G.]

Thank you. I'm not getting this to work. (I'm fairly new to Access) Is
this function performed using SQL Design?

{I was able to get close using the report wizard but then unable to work
with data or export to excel]

 

[Michel Walsh]

You can type it in SQL view of a query design.

You can do it graphically to, in the design view of a query design: Click
on the summation button on the toolbar, a new line, total, appears in the
grid. Drag the field name in the grid, keep the proposed GROUP BY. Drag the
field sale in another column of the grid, change the proposed group by to
SUM. That's all.


Hoping it may help,
Vanderghast, Access MVP

Thank you. I'm not getting this to work. (I'm fairly new to Access)
Is
this function performed using SQL Design?

{I was able to get close using the report wizard but then unable to work
with data or export to excel]

Sure.

SELECT name, SUM(sale)
FROM tableName
GROUP BY name



There is a rule for 'group by' or 'total' queries: every listed field in
the
select clause must either be in the group by list, either finally
aggregated. As example, you cannot use:


SELECT name, department, SUM(sale)
FROM tableName
GROUP BY name


but you can use either:


SELECT name, department, SUM(sale)
FROM tableName
GROUP BY name, department


either


SELECT name, FIRST(department), SUM(sale)
FROM tableName
GROUP BY name


as example.




Hoping it may help,
Vanderghast, Access MVP