Solved

Dynamically set a report field based on a crosstab query, and count the number of records

Posted on 2014-01-27
11
554 Views
Last Modified: 2014-01-27
I am dynamically setting the control sources for report fields based on the results of a crosstab query. It works, but I also need to have a total for each column that will give me the count of the number of records that have data in them (a date). So for example if there were 5 records and 3 of them had dates in the first column, I would see "3" at the bottom of that column in the "SumTxt1" field.

How do I modify this code so that the "SumTxt" fields show the count instead of just showing the contents of the field above?

**************
Private Sub Report_Open(Cancel As Integer)
  Set db = CurrentDb()
j = 1
 Set rs = db.OpenRecordset("TempMatrix_CROSSTAB")
 If j <= 10 Then
 For i = 6 To rs.Fields.Count - 1
 Me("lbl" & (j)).Caption = rs(i).Name
 Me("txt" & (j)).ControlSource = rs(i).Name
 Me("sumTxt" & (j)).ControlSource = rs(i).Name

 j = j + 1
 Next i
 End If
 rs.Close

 End Sub
0
Comment
Question by:MzLiberty
  • 4
  • 4
  • 3
11 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
 Me("sumTxt" & (j)).ControlSource = i -6

Open in new window

0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
IS the code using 1 record of the record set?

Upload a sample database?
0
 

Author Comment

by:MzLiberty
Comment Utility
No, it gets columns 6 to 10 from the crosstab query. That part works, but right now my total row just repeats the first row because I can't figure out how to make it give me a count of the other field instead. If it were a normal report, my field would be [field1] and the total row would be count([field1]). How do I get the count from "rs(i).Name" ?
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
"repeats the first row"
This was the question.

Put rs.MoveNext in proper place.
0
 

Author Comment

by:MzLiberty
Comment Utility
I don't want the next record, I want a count of the records. I have one field that gives me the data from one column of the crosstab query (txt1) and a field in the report footer to give me the count of records in that column (sumtxt1). I want sumtxt1 to be count([txt1]). But txt1= rs(I).Name.  I've tried = "Count(" & rs(I).Name & ")" but that just returns "[Count([MyFieldName])] which of course is not right. How do I get a count from rs(I).Name?
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Did you try my suggestion?  It is using the index of the fields.  It excludes the first 6 fields just like your loop does.
0
 

Author Comment

by:MzLiberty
Comment Utility
I don't need the count of the fields, I need the count of the data in the field.


My report needs:

                         training1     training2     training3
Person1          1/1/2014     4/1/2013
Person2        1/10/2014                          8/1/2013
                             2                    1                    1

I get the headings and the detail lines just fine, where the columns from the crosstab get inserted for the column headings. I just don't know how to get the bottom row, the count.

rs(I).Name gives me "1/1/2014" for the first record, but how do I get count(rs(I).Name)?
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Create a footer section for the report or the report group.  Add a control for each column.  Use the following as the control source:

=Sum(abs(IsDate(fld1)))

=Sum(abs(IsDate(fld2)))

= .....
0
 
LVL 30

Accepted Solution

by:
hnasr earned 500 total points
Comment Utility
If not resolved, upload a sample database.
0
 

Author Comment

by:MzLiberty
Comment Utility
But I don't know what fld1 is, so I can't do that. That's ok, I figured out another way to do it, I added a sub-report with the totals.
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
Please comment why did you select my comment as an answer.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now