Group By Date
- Thread starter mrssevans
- Start date
M
mission2java_78
Guest
mrssevans said:
You're killing me here...
one minute you say it works..the next you say its not working.
Whats the error? Why cant you run it? You need to be more specific.
Send me your database...
Jon
You think I'm killing you...? This mess only gets worse.
Anyway...the query does work and the date grouping works, what is happening is that the data that is summed in the query is not calculating correctly. If you look at the sums2 query and look at january "h" it should be 1620 and it's not. I will attach the database. Sorry for the trouble.
Anyway...the query does work and the date grouping works, what is happening is that the data that is summed in the query is not calculating correctly. If you look at the sums2 query and look at january "h" it should be 1620 and it's not. I will attach the database. Sorry for the trouble.
M
mission2java_78
Guest
No no no...
Use Sum in your report not your query...
Look here is all you need to do...query out the right data onto a query like you have. Then use GROUP BY in the report and group by whatever you want to group by "Month" "Year" Employee or whatever. Than in the footer of that group you do:
Sum([InvCount]) or Sum([NameOfYourField])
...Dont mess around summing in your query...your report can do this.
Its 7pm...if you want you can hire me to do the whole thing
. You also got to be careful. I noticed you did not have a primary key to keep your records unique. Do you really want to do that? I suggest you use an AUTONUMBER in your Invoice Table so you can reference back to specific records. Your AutoNumber could be used in place of your invoice number since it is to be unique. Or you can use both. Be careful also with datatypes "number" vs. "text"...you probably could change some of them.
Let me know what you think. I could do it all in say 1 hour...the problem is time is money and I dont have a lot of either one right now.
Jon
Use Sum in your report not your query...
Look here is all you need to do...query out the right data onto a query like you have. Then use GROUP BY in the report and group by whatever you want to group by "Month" "Year" Employee or whatever. Than in the footer of that group you do:
Sum([InvCount]) or Sum([NameOfYourField])
...Dont mess around summing in your query...your report can do this.
Its 7pm...if you want you can hire me to do the whole thing
. You also got to be careful. I noticed you did not have a primary key to keep your records unique. Do you really want to do that? I suggest you use an AUTONUMBER in your Invoice Table so you can reference back to specific records. Your AutoNumber could be used in place of your invoice number since it is to be unique. Or you can use both. Be careful also with datatypes "number" vs. "text"...you probably could change some of them.Let me know what you think. I could do it all in say 1 hour...the problem is time is money and I dont have a lot of either one right now.
Jon
Jon thanks for all the advise. I will give it a try. The problem with changing the data types and such in the tables is that all are linked to another database that I have no control over. We are not putting any data into this system but only pulling it out. I hope that helps so that I don't look totally ignorant. Anyway, thanks and I will give that a try. se
M
mission2java_78
Guest
mrssevans said:
Ok,
Just remember you dont need to do your sums in that query...the query should just pull of your records. Than in your report's footer of the group you are grouping by you simply do a Sum([YourField])...
For instance say your report showed the following:
(Group By Date / Year) Jan 02
Mike B 20
Steve R 30
Gina K 10
Martha T 11
[in the footer of this group Date / Year Footer you sum it]
Sum([YourField])= 71
Feb 02 .........
Similar threads
