calculate <sum of> when criteria is filtered 1

[Allilue]

Happy Holidays!

back to work...

i have a form based on a filtered query. the query is based on a table with data for multiple months. the form then requires the selection of one month to draw in data for that month only. but i want to create a field in the form that allows me to calculate the year-to-date number of a particular field in the query...however, this would obviously be the year-to-date for all months and not just the month displaying on the form. what do i need to do to be able to display the month selected, as well as all months?

thanks,
allison

 

[WaltLukeIII]

You need to make a new query which will include all of the months and use a subreport to view this on the form. If you were doing this on a report you could just put a summed field on the report in a header or footer.

Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[JeremyM]

It would probably look better though to use a sub form and not a sub report on the form. If the both have same background and you clean then up, to the naked eye it will be one form with seamless transistion.


Jeremy

 

[Allilue]

Okay, I have created an additional subform based on another query that doesn't limit the month. But on my actual form, there are fields which calculate the sums of the data in the subform. since i have a new subform now, i suppose i need to join queries to be able to sum up the numbers in the new query . i tried this and my numbers get distorted. i'm stuck and i don't think that i know how to explain this any clearer.... any suggestions? help!

 

[WaltLukeIII]

What seems to be distorted about the number is there a pattern to it. If you plug in the same data do you get the same distorted number? What numbers are distorted?

Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[Allilue]

well, i added a field to the original query. this field came from the new query i created to include all months. the numbers that result are distorted for one of the fields in the original query which summed the numbers for the month selected. it seems to be multiplied by 10 for all values. so if it's supposed to be 5600, it's 56000 instead. for the new field i added that is supposed to summ the numbers for all months, i don't quite see a pattern to this one.

 

[WaltLukeIII]

is the data corrupted in the query or just in the form?
Check to see what the properties of the textfield where you ouput the query information perhaps something got accidentally added in somewhere.


Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[Allilue]

nope. the numbers are corrupted in the query as well and the formatting is correct. i think it may be due to the adding a field from the other query. but i don't know how to combine the data from the two queries to display both information on the form. can't i just create a textbox in the form and set a formula to pick up the appropriate data even if it is not from the query the form is based on?

 

[WaltLukeIII]

Is you form a datasheet or does it just list every month right there?


Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[Allilue]

here's the setup:

tblData:
all months, for all names
(for example, there are 10 names and every name has a number for each month. therefore, there are 120 rows of data)

qryByMonth:
input month in the criteria to limit what is displayed on the form.

qryByYear:
month is not filtered so that names show up with the sum of their numbers for every month

frmCentres:
contains a subform based on qryByMonth as well as a subform based on qryByYear. on the actual form, there are calculated fields for the sums of the numbers per name for that month. so if AAA has 10 numbers, the calculated fields would be the sum of those numbers (only for the month selected). i want to add a calculated field that sums the data in the qryByYear as well so that each name has a sum of their numbers for all months.
the form is set up so that a month is displayed based on the criteria input in the query, then a combobox to select the name.

 

[WaltLukeIII]

OK here goes.

Your query by Year should have one at most two fields the total field and the year the total is for. the select statement should be something along the following lines.

SELECT year, Sum(tablename.columnName) AS YearlyTotal FROM tableName
ORDER BY year
year in the statement should be the name of the year field in your table.
the columnName should be the column you want to total

You query by Month should have two at most three fields Year the month and The total for the month

SELECT Year, Month, SUM(tablename.ColumnName) as MonthlyTotal FROM
tablename ORDER BY year, Month

These should be two separate querys that get updated when ever your button is pushed or your action occurs.

If your data is not correct in the query then of course it will not show up correctly in the subforms.

Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[Allilue]

thanks! i had to adjust my second query which contained too much data. i guess that was probably screwing up the data??? now....a more basic question. this form is now based on a query and 3 subforms that are all based on separate queries. i want all of the data on this form to appear on a report with all the content. but i guess you can't create a report from a form. and there is no way i can combine all of those queries. i have tried to create a report from the report wizard and take data from various queries, but i can't get that to work. is there an easier way???

thanks again for the help!!!

 

[WaltLukeIII]

In a report you can just use a textfield that will Total other fields if you use grouping.

For instance You can take and group first by name then by year and then by month. (An example you could group in any order you liked.)
Go to your view menu and "Sorting and Grouping"
setup this how you want it.
then you add a unbound textfield in the footer of a grouping, page or report. then in the control of this textfield you type something along these lines
=Sum([columnName from table])
this will give you total by grouping then if you put one in the report footer it should give you a total by report.

Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl