How can i sum the totals for a particular month?

[Goat.Cheese]

My "Date" column is a text field that looks like this: DD.MM.YYYY

Since the amount of data is very large, access query returns an error saying that it cant convert a field with more than 65,000 entries.

Anyhoo, I need to find a way to show summed totals for the separate months, and for the life of me, I cant figure that out.

Thanks.

 

[John Big Booty]

You should be able to do it with a Group By query along the date manipulation functions.

 

[Goat.Cheese]

Date manipulation sounds a tad high-brow for me. Could I bother you for some instructions on how to do this?

 

[John Big Booty]

Have a look at the Month() function, you could Group on that (and possibly also your ClientID) and Sum on the field you want the total for.

 

[Goat.Cheese]

I dont believe i see any function that will allow me to create a query regarding "sums" for the entire month.

Clients | SalesTotal | Expr1: month([DateField]) |
Group | Sum |

Initiates a query which so far has taken 20 minutes to reach about 5-10% completion.

Im stuck on this.

 

[John Big Booty]

Are you able to post a copy of your DB ('03 version fro preference and certainly no newer than '07)? or just some representative data.

 

[Pat Hartman]

Having your date stored as text causes a lot of work and makes it impossible to search and sort without using functions. Is there some reason you can't make a date datatype field? To get year and month from the string date use:
Select Right(YourDate,4) & Mid(YourDate,3,2) As YearMonth from yourtable

In reports, you will be given the option of various groupings for date fields. You get no options for string dates because Access doesn't know they are dates.

 

[Goat.Cheese]

Are you able to post a copy of your DB ('03 version fro preference and certainly no newer than '07)? or just some representative data.

I've crudely assembled a paint image of just the 3 tables i need to work with. Unfortunately its access 2010 - is the 2003 better?

http://img52.imageshack.us/img52/8497/gramc.jpg

From left to right, the fields are: date - client name - Sale value

There are a total of 170,729 fields which list the number of individual sales for a year

Anyway - I have created an entirely new table for the date, properly set as "date/type", and im not gona use the date field in the original table (which is set as text)

What I need to do is get the query to show me the total amount of purchases each of the 3600 clients has made within a specified month.

Having your date stored as text causes a lot of work and makes it impossible to search and sort without using functions. Is there some reason you can't make a date datatype field? To get year and month from the string date use:
Select Right(YourDate,4) & Mid(YourDate,3,2) As YearMonth from yourtable

In reports, you will be given the option of various groupings for date fields. You get no options for string dates because Access doesn't know they are dates.

The date issue has been resolved, as i said above. The reason i couldnt convert the original date field is because i cant seem to change the type of a field with more than 65,000 entries. I think i addressed that in the second sentence of the OP

"Select Right(YourDate,4) & Mid(YourDate,3,2) As YearMonth from yourtable"

Where do i use that?

Thanks