ACCESS FEED: Re: [MS_AccessPros] Column Headings in Crosstab Query Reports

Kamis, 13 Agustus 2015

Re: [MS_AccessPros] Column Headings in Crosstab Query Reports

John:

YOU ROCK!!!! Thank you soooo much! This works perfectly!!!

Karen

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Duh. It just occurred to me that there's an even simpler solution involving no code.

You can add an IN clause to a Crosstab query to explicitly specify the names of the columns that you always want produced. If nothing exists for the column, it will return blank (Null) values.

TRANSFORM Count([qryNFP Rep Producation].HICN) AS CountOfHICN

SELECT [qryNFP Rep Producation].[Agent Name], Count([qryNFP Rep Producation].HICN) AS [Total Of HICN]

FROM [qryNFP Rep Producation]

GROUP BY [qryNFP Rep Producation].[Agent Name]

PIVOT Day([qryNFP Rep Producation]![AllDates])

IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 226, 27, 28, 29, 30, 31);

You can generate the IN clause in query Design view by filling in the Column Headings property of the query in the Properties window.

This will *always* generate days 29, 30, and 31, even for months that do not have those days. When a month doesn't have one of those days, the column values will be blank.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Aug 13, 2015, at 5:02 PM, John Viescas JohnV@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Karen-

Build the report on a month that has 31 days. Open the report in Design view, open the Properties window, and select the text box for the 29 value. Change its name to txt29 and clear the Control Source property. Do the same for the 30 and 31 controls, naming them txt30 and txt31, respectively.

Now select the Detail section and change the On Format property to [Event Procedure] - and then click the Build (…) button next to the property to open up the VBA editor with skeleton code for that event.

What we're going to do is attempt to assign the 29, 30, and 31 values to the renamed controls "by hand", but tell Access to ignore any errors. Fill in the code like this:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
' Skip any errors
On Error Resume Next
' Attempt to assign the 29 value
' (will fail only in February in non-leap years)
Me.txt29 = Me![29]
' Attempt to assign the 30 value
' (will fail only in February)
Me.txt30 = Me![30]
' Attempt to assign the 31 value
' (will fail in February, April, June, September, and November)
Me.txt31 = Me![31]

End Sub

What the code is doing is attempting to assign to the appropriate text box the value in the field in the underlying recordset. The code will error if 29, 30, or 31 is not in the recordset, but the On Error just ignores that.

There are more elegant solutions, but this is the simplest for your purposes.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Aug 13, 2015, at 4:45 PM, k2j1203@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Happy day everyone! I hope that everyone is doing well. I need some help with a report that I'm trying to create.

I have a crosstab query that I created. I want to create a report with the results because I want to add other subreports to the report. Here is the background information.

I have two tables: AllEnrollments and tblAgents. I need to count the reps sales, e.g. number of members within a date range. I have a query that has member data linked to the agents table by RepID. I named the query [qryNFP Rep Production]. The query result has 4 columns: RepID, Agent Name, AllDates (which is the sale date) and HCIN (which is the unique identifier). I created the crosstab from this query so that the row headings are the Agent names, the column headings are the days of the month (ex. 1, 2, 3...) and the value as the count of HCIN. Here is the code for the crosstab query.

TRANSFORM Count([qryNFP Rep Producation].HICN) AS CountOfHICN
SELECT [qryNFP Rep Producation].[Agent Name], Count([qryNFP Rep Producation].HICN) AS [Total Of HICN]
FROM [qryNFP Rep Producation]
GROUP BY [qryNFP Rep Producation].[Agent Name]
PIVOT Day([qryNFP Rep Producation]![AllDates]);

From here I had Access create a report. The resulting report looks great. Now comes the part where I'm stuck. The field names are the day (1, 2, 3...) so that I can run this report for any month without have to change the field names. The problem is that all of the months don't end on the same day. So, if I create the report for 30 days, the 31st date doesn't get added and if I create the report for 31 days, Access is looking for a field named 31 which not all of the months have.

Is there a way to make this report work the way that I want it to?

I really appreciate your help.

Karen