my totals query is not grouping

[josekreif]

Name: broken.PNG  Views: 7  Size: 24.1 KB  ID: 21978" class="thumbnail" style="float:CONFIG" />

I have the name and the month grouped. The numbers are all sum

SELECT Customer.[Sales Person], [Delivery].[Month] & ":   " & MonthName([Delivery].[Month],True) AS [Month], Sum(Delivery.[Kgs Sold]) AS [SumOfKgs Sold], Sum(Delivery.[include VA Extended Value]) AS [SumOfinclude VA Extended Value], Sum(Delivery.[net of VAT]) AS [SumOfnet of VAT], Sum(Delivery.[Billed USD]) AS [SumOfBilled USD]
FROM Delivery LEFT JOIN Customer ON Delivery.Customer = Customer.[Customer Name]
WHERE (((Delivery.Customer) Is Not Null))
GROUP BY Customer.[Sales Person], [Delivery].[Month] & ":   " & MonthName([Delivery].[Month],True);

IF IT HELPS

[ranman256]

is data in the [delivery].[month] an actual month name 'july', or is it a DATE, 7/1/15?
IF its a date, then you are getting different years july 2014, and 2015.

or some other field is in the query, not showing, but causing the 2 different 'Feb's.

[josekreif]

is data in the [delivery].[month] an actual month name 'july', or is it a DATE, 7/1/15?
IF its a date, then you are getting different years july 2014, and 2015.

or some other field is in the query, not showing, but causing the 2 different 'Feb's.

Hi thank you for replying.

The month holds only a number, and the delivery only holds information of the current year, 2015.

Here it is in simple terms.

SELECT Customer.[Sales Person], Delivery.Month
FROM Delivery LEFT JOIN Customer ON Delivery.Customer = Customer.[Customer Name]
GROUP BY Customer.[Sales Person], Delivery.Month;

Should I write a procedure to sum these up?

EDIT:

YIKES. Looks like Access doesnt like these names.



This data in entered from China, so I'm guessing there are formatting errors. Something to do with ASCII or similar.

[josekreif]

My hunch was right. This is a ASCII problem

I copy and paste the names and here is what I get

Jia Jianrong: 074 105 097 032 074 105 097 110 114 111 110 103 013 010

Jia Jianrong: 074 105 097 194 160 074 105 097 110 114 111 110 103 013 010

This is a spacebar issue, researching further, it's a known problem "194 160"

For anyone with this issue
Replace([Customer].[Sales Person],Chr(160)," ")