Reports---Running Totals through Queries 1

[JBuckley2000]

Hey everyone,

I was hoping I could finish a report by the end of the day and I was needing some help. I have a report which is based on a query and I was needing to calculate the different defects I have under the DEFECT field which result from the parameters chosen when I start the report. The name of the query is (Sum2-By Prod Date and All Defect Codes). How the heck do I do this at the end of the report for each defect? I hope someone can help me out because I can only find examples on how to do totals for tables and that isn't working for me. Thanks in advance. JSF

Jason Facey
jfacey@lithonia.com

 

[WaltLukeIII]

You are trying to get your total in the Query Correct. If so Try something like this

SELECT DISTINCTROW [table Name].[Column Name], [Table Name].[Column name], ... ,
Sum([table name].[column Name]) AS [column name in query]
FROM [table name ]

If you want to get totals for ceartain areas then use this part also

GROUP BY [Table Name].[Column name], ...

This Part will Sort the Query

ORDER BY [Table Name].[Column Name],...

The End of the Query

;

Walter III


Walt III
SAElukewl@netscape.net

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

 

[JBuckley2000]

Hey Walt,

I was trying to have an actual total box to show on the report using DCount, which works for most reports, but I couldn't do it since it was ran through a query. I want a total for each specific defect that comes up after the report is ran, but I am unsure about the code. I think DCount wiuld be much more simple, but I need some help on that. I think it would too difficult to show what you wrote in the report. Any ideas on DCount with a report?
Thanks,, JSF

Jason Facey
jfacey@lithonia.com

 

[WaltLukeIII]

The code up above is for your query. However if you just want to put a textbox on a report with a total for a grouping. Try this as the textfields control Source.
=Sum([Field name])
This will give you a total for a ceartain group you also have to use grouping to get this to work. If you don't understand I can explain more.

Walter III

Walt III
SAElukewl@netscape.net

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

 

[G123]

i can´t make a .mde file
This error appeares - Compile error in hidden module: <module name>
What can i do.
I need help Urgent.

 

[JBuckley2000]

Hey Walt,

I have tried that before and it works, but I am needing specific totals in the one field...I have the defects 01 through 13 appear in the DEFECT field. I want to count the total for each specific number and have them listed at the end of the report. The DCount function is supposed to do this using a table as your basis, but mine is using a report that is created through a query, so I am stuck---I hope you understand what I mean by this.
Thanks, JSF

Jason Facey
jfacey@lithonia.com

 

[MichaelRed]

My experiecne w/ the domain aggregate functions is that they are SLOOOOOOOOOOOOOOOOOW. It is almost always better for my purposes to generate a seperate recordset (e.g. query) with sums/totals/averages and use a subreport to place these values in a report (detail footer or report footer). This also adds a great deal of flexability, since the summary query may be referenced in any part of the report, e.g. you may place the aggregate values in the header as well or instead of the footer).



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[JBuckley2000]

Hey Michael,

How is it you are always there to help me? Anyhow, DCount seems to be running fine, but an error comes at the end saying I am not suppling the correct criteria or table from where the totals are coming from. I just wanted to know how to set it up using DCount because it is the most simple thing to do and easiest to trouble shoot for later users. I still hope someone can show me DCount...someone tried last week, but he gave me the incorrect criteria, please help. JSF

Jason Facey
jfacey@lithonia.com

 

[MichaelRed]

Jason,

If you want someone to help with the specific function &quot;DCount()&quot;, you need to provide some more info. What EXACTLY is the expression you tried (even though it did not work). What EXACTLY did Ms. Access complain about (what was the error, what was highlighted, was it a compile time or run time error. What EXACTLY ...

BTW my docs (and experience) say that DCOunt works with recordsets (table or query), not just tables.




MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[JBuckley2000]

Hey Michael,

Well what I have is a report that I generated through a query, which is through a table of data that is about 10 years old with over 96000 records. The table consists of specific information about the defects we have in our plant such as the date they are received, the production month and year, the defect code, the defect description and so on. What i am trying to do is in the report, I choose a beginning date and end date to when the defect was added to the records and it pulls up all within the date. I want to count up the number of each specific defect and have a total for each defect like so:

Defect 01 = 25
Defect 02 = 123
Defect 03 = 987
Defect 04 = 153
And so on...up till Defect 13.
Then a grand total of all of them at the end.
I have asked someone before and he gave me this code:

(=dcount(&quot;defect&quot;, &quot;tablename&quot;, &quot;[defect] = 'leaking oil'&quot

*this example assumes the field name is 'defect' and the defect that I want to count is 'leaking oil'

I asked the programmmer about a week ago about whether &quot;tablename&quot; had to be specific to the table I am using, but he never got back to me. Is this possible to run through a report? I hope some of this makes sense and thanks for helping. JSF

Jason Facey
jfacey@lithonia.com

 

[MichaelRed]

Well, lets start w/ the &quot;official&quot; def for the DCount function:

[blue]
DCount(expr, domain[, criteria])

The DCount function has the following arguments.

Argument Description
expr An expression that identifies the field for which you want to count records. It can be a string expression identifying a field in a table or query, or it can be an expression that performs a calculation on data in that field. In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.
domain A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name.
criteria An optional string expression used to restrict the range of data on which the DCount function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DCount function evaluates expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise the DCount function returns a Null.[/blue]

Also, re the examples, all field names are enclosed in the square brackets (&quot;[expr]&quot, so your expr should look more like:

XXX = DCoount(&quot;[Defect]&quot;, &quot;tablename&quot;, &quot;[Defect]&quot; = 'leaking oil')


There may be some syntax re the single quoted value (leaking oil), where the single quote needs to be enclosed in double quotes?

Of Course the XXX is just for &quot;Syntax&quot; here. In your report =, leave this off and place the rest in the controlsource for the text box. Also, you DO need to substitute the real valu for your tablename in the italics.

Also, you need to create one of these &quot;expressions for EACH defect/text box and an additional one for the total. This, instead of a single query to get all the defect counts at once?





MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over