Link to home
Create AccountLog in
Avatar of inimicaljords
inimicaljordsFlag for United States of America

asked on

Access 2007 Create a report with totals

I am trying to build a report that will calculate a number of things.  For example, it will give the total number of printers in the database by looping through the SN field.  I would also like it to display the total number by model.  

I am not sure of the best way to do this.  I have a form with a command button that I am trying to pass the value to the report. I understand how to do the loop but I am not sure how to get it to the report.  I am also not sure that this is the best way to achieve my goals.

I would like a report that shows something like this
Total number of Printers = x
Total number of Model1 = x
Total number of Model2 = x
etc.

These values aren't stored in the database and I have to calculate it.  I can get the values with the loop but I don't know how to get those values to the report.  That is what is throwing me a curve ball.

The strArg is obviously not correct in my code.  I made a text box named rptPrintertotal on the report so my theory was to assign rptPrintertotal=x (x being the number of records from loop)

I could be way off base here so let me know.  Thanks!
Dim strArg, x As String
Dim db As Database
Dim rs As Recordset
x = 1
Set db = CurrentDb()
Set rs = db.OpenRecordset("TA")
Do While Not rs.EOF
    If rs![SN] > "" Then
        x = x + 1
    End If
rs.MoveNext
Loop
strArg = "[rptPrinterTotal =] & x" + ";"
MsgBox (strArg)
DoCmd.OpenReport "PrinterTotals", acViewPreview, , , acWindowNormal, strArg

Open in new window

Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

what is TA, is this a table?
if this is a table, create a report using the table as the record source.
* open the report with filter, using the option "Where condition"
Avatar of inimicaljords

ASKER

Yes, it is a table.  I do have the table as the record source for the report.  The where condition is what I am having problems with.

Can you give me an example for my strArg in the code above?
you will not use the strArg, use the Where condition

docmd.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)

If you need any references for Report Design, see this book:
http://www.amazon.com/Microsoft-Office-Access-Reports-Queries/dp/0789736691
<<you will not use the strArg, use the Where condition

docmd.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)>>

If I use this, how do I get the total values on the report?
<If I use this, how do I get the total values on the report?>
The Report wizard in Access will do all of this for you, did you give it a try?
It is very straightforward...

This is why I recommended the book...
It teaches you all of this...

Start the Report wizard
Select your table/query and fields
Select your grouping Field (Model)
Select your Sorts
Click the "Summary Options" button
Select "Sum" for your field
Select the "Summary Only" Option Button
Then finish out the rest of the Wizard's steps

The report may not be exactly as your specified, but it should display the totals you are looking for.
So, at least get this far on your own.

Then we can assist further if you need more help tweaking the report.

JeffCoachman
I tried the report wizard and it only does the sum for numeric values such as part cost.  I am looking for a count of how many printers of each different model there are.  Summary options is not available for this.
ASKER CERTIFIED SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Thanks Jeff!  That example is perfect.  

I had one quick question on your database.  I can't seem to find the code that it is using to loop through the database.  Is it just built into the report?  I see the control source is ="Total Number of " & [Model] & ": " and I understand how that works, but where is the code for the loop? I looked through code builder and didn't see any code.  I assume that it is somehow built into the report.

I will check out that book.

I greatly appreciate the help. =)
That is the beauty of reports, this is all done internally
;-)

The controlsource of: =Count("*")
...counts all the occurrences of the models if it is in the Group Footer.

It counts all instances of All Models if it is in the Report Footer

;-)

Just curious, are you originally a VB6.0 programmer?
Very cool.  I started with vb 4.0 years ago and worked my way up.  I have taken some c, c++ classes as well. Also, have done some web languages with asp and php.  

I greatly appreciate the help though!