Microsoft Access Database between dates to give total summary

Group by Client Code
https://support.microsoft.com/en-us/...ngle%20record.

Hi Welshgasman,
I don't understand SQL so I don't know what needs to happen? Can you send me a sample code?

Thanks
Kitsa

Have a look here then.

https://www.google.com/search?q=grou...t=gws-wiz-serp

Hi Welshgasman,
I have looked at everything and I still can't get the result I'm after. Still shows each line. Are you able to help me to fix this coding?

Please Login or Register  to view this content.

Thanks
Kitsa

I generally use the QBE window and design mode. TBH most of my queries are trial and error.
This is a query of mine that shows me the carb,sugar and fibre totals per day for my diabetes DB.

Please Login or Register  to view this content.

Note, I use a WHERE and then GROUP BY, whereas you are using GROUP BY and HAVING.

Also good videos on Youtube on a lot os aspects of Access.

https://www.youtube.com/watch?v=YBwGcslqP00

Try

Please Login or Register  to view this content.

However True and False are logical states, not strings as you have there?
Also any time the grouping changes, then you will get a new group. So if your dates are different per record, that would cause that? It is unlikely the amounts would remain the same, so that would be another group.
You can see from my sql that I group by a weeknum, so all records for that week (and there will be several per day) are summed to give me a daily total.

https://www.google.com/search?q=sql+...hrome&ie=UTF-8

It's because you've got the tbl_masterdata.date in your Group By clause, it shouldn't be. And welshgasman is correct, your filter should be in your where clause, not the Having clause

Well it is likely due to your Dsum(), it does not have the same criteria as your WHERE clause?, so you are summing everything?

So how would I do this if I already have a "Where" code and Dsum() won't work?

I can only think of two ways.
The first is to put the same criteria into the DSum(), but that is likely to be difficult syntactically, and Domain aggregate functions are not recommended in queries anyway.
The second is to have another query that sums the amounts with exactly the same criteria.

Then bring that query into this query (do not join them) and bring in the total field. Then you can reference them in your calculation for percentage.

I have just tested option 2 on a db of mine and that appears to work.

Hi Welshgasman,
What you said worked and I finally got it from all your help.

Thanks heaps, couldn't have done it without ya.

Please Login or Register  to view this content.