Total query

[MikeJD]

I am trying to design a query to total income from a number of contracts grouped by customer name. I am working in Design View as I am not able to code in SQL. I have set the the 'total' row to count numbers of contracts and total income grouped by Customer Name but the name may be slightly different for various divisions of a customer and these are listed separately. In a report I can combine the records for these by grouping on, say, the first 5 characters but I also want to sort the result on income which I can't do in a report. Is it possible to group by number of characters in a query ?

 

[shacket]

You can use the Left argument to get the left-most number of letters. To get the first five letters of the customer name, you would type:

CustomerNamePart: Left([CustomerName],5)

I can't help but think that you need a Primary Key to identify each customer, instead of Customer Name, or part thereof. Are you familiar with Primary Keys, what they are and have you looked into whether or not you can add them to your tables?

 

[MikeJD]

Thanks, I'll try this tomorrow.
Regards the Primary key, this would not be easy as the data already exists in another database from which it is imported and the customer names are used for addresses eg McAlpine Construction, McAlpine Pipelines, etc. but for the present purposes I wish to group them together.

 

[MikeJD]

I have tried to enter the suggested code in the SQL for the query but I can't get the syntax correct.
I have tried the following :

GROUP BY [CCDT].[CUSNAM]Part: Left([CCDT].[CUSNAM],5)

and I get an error message 'Syntax error (missing operator) in query expression' and it highlights the 'Part'.

I would appreciate any further comments.

 

[shacket]

Your SQL would look something like this:


SELECT Left([CUSNAM],5) AS Part
FROM CCDT
ORDER BY Left([CUSNAM],5);