create query that totals clients amounts

[headlock]

Hi there, Hope you can help. I want to create a query (to base my report on) that totals up the amount of money a client has paid over the year. My table is simple, client id, date, and amount they paid. I can run a report/query by date ie. yearly but can't figure out how to just get the total amount to show for each client instead of every payment. Thanks in advance.

 

[John Big Booty]

Perhaps this link will help you.

 

[headlock]

Thanks for your quick response. It does help thanks but only if I take the date criteria out. If I put the date in and want to run the query >#01/01/2012# And <#31/12/2012# with the between 2 dates options I get the results all separated again. Help!

 

[John Big Booty]

What you may need to do is to collect the data you wish to total in a query, and use the results of that query in a second query where you can then total it.

 

[JHB]

I think you have a "Group By" on your date field in the query. It has to be "Where"

Else show us the Query

 

[Brianwarnock]

You also need to include = on both date tests

Brian

 

[Pat Hartman]

>#01/01/2012# And <#31/12/2012#

When you use literal dates, you need to use the US date format which is mm/dd/yyyy.

 

[RainLover]

Try not using a Total in the query.

Instead create your report with your Client Details in their own section.

Then use a Sum in the report insteat of the query. (Untested)

 

[Brianwarnock]

When you use literal dates, you need to use the US date format which is mm/dd/yyyy.

Doesn't that depend on whether he is coding in the design grid or SQL ?

Brian

 

[RainLover]

Doesn't that depend on whether he is coding in the design grid or SQL ?

Brian

Brian

It would take you less than a Minute to work this out. And the answer is that you could Type 2/2 in the Design and it would automatically convert.

 

[Brianwarnock]

Brian

It would take you less than a Minute to work this out. And the answer is that you could Type 2/2 in the Design and it would automatically convert.

Sorry what are you actually saying to me, that I could have tested it?

1 I'm on an iPad
2 my access on my steam driven PC is 2002 sp3 I always wonder if things have changed in later releases especially when I find myself querying responses from people such as Pat.

Brian

 

[RainLover]

Brian

Because you are on an iPad does this meat that you never test your own advice to others.

What is the use of that.

BTW. There is nothing much wrong with 2002. I would far prefer that to 2007 or 2010.

 

[Brianwarnock]

I don't post much in the techie areas these days as what little knowledge I had is rusty and potentially obsolete after 7 years of retirement, but if I do and it's more than a simple observation I do stoke the boiler and go on the pc and test, just have before responding to what I saw as a simple reply but was giving the op a bit of concern.

Brian

 

[Pat Hartman]

Doesn't that depend on whether he is coding in the design grid or SQL ?

Given that I live in the US, I don't have a problem with dates but my understanding from all the posts I've read is that a string date in a query MUST be mm/dd/yyyy format. If the query references a form field, no transaltion is necessary because even though the form fields shows a date string in the default Windows format, dates are stored internally as double precision numbers so that's what Access passes to the query.

Select ...
From ...
Where MyDate <= Forms!myform!myDate;

As long as Forms!myform!myDate is defined as a date datatype, there will be no conversion issue. But if you use a string, you must use US sequence.

Where MyDate <= #12/10/2012#; -- Dec 10, 2012 NOT Oct 12, 2012

 

[Brianwarnock]

Pat

If I code in the design grid I must use local ,ie UK format, if I switch to SQL I can see that the system has converted it to US format, which is what I must use when coding in SQL, or VBA of course.

Brian

 

[Pat Hartman]

Sometimes Access is just too smart for its own good and ours