Reports based on crosstab query from make table query 1

[janereyn]

Complicated Problem.
My database is a student records database. I need to create a report that shows the total number of students applying for a particular course in a particular year categorised by their age. The age categories are Under 21, Between 21 and 25, Between 25 and 30 and Over 30. I've worked that out in as an expression in a query, which is fine. I've tried to base my report on a crosstab query which has the headings Year, Subject and then the above age cats going across the page with the total number of students in each cat shown. However, Access doesn't seem to like basing a report on a crosstab query that uses an expression (agecat) as a column because it is not a real field. So I've now got a form that asks for the criteria Year and Subject - once that's entered you press a button which runs a make table query and puts the correct data into the new table. This all works. Then on the criteria form the user presses another button to run the report. The report runs from a crosstab query which takes its data from the newly made table. This is also fine, except if there are 2 students - 1 under 21 and 1 over 30, the field names on the design of the report only show these 2 groups which you can then drag down onto the report. However, if you add another student aged between 21 and 25 for example, everything works except when you run the report only the previous categories ie under 21 and over 30 are shown as fields on the report because when you previously ran the report those were the only 2 fields coming from the newly made table. Alternatively, if you previously had 3 students - 1 in cat Under 21, 1 in cat Over 30 and 1 in cat Between 21 and 25 which fields you dragged into the design of the report, if you then take away the student say in the Over 30 range and then run the report again, it says that is doesn't recognise the Over 30 field name! So what I want is for the report to somehow incorporate all 4 age cats on it and if there are no ages in those cats, for it to just say 0. Please help if you can as I have spent hours and hours on this problem.

 

[ghubbell]

Hi Jane,
I'll try to explain how I have solved a similar issue:

I have x catagories that must be updated at any chosen interval. I have a table already created but sitting empty or with non current data.
At the click of a command button on a form I run the following:
A delete query: to clean out the table.

An Append query to place the results of a crosstab query to compile the catagories that is based off of a select query (that does the initial summing according to date) in to the existing table.

An Update query for each of the fields that might contain a null value: in my case its quarters (Q1, Q2, Q3, Q4) so 4 update queries, Update to 0, criteria: Is Null.

Now, as the form is based on the table, I requery the form to show the fresh data with Me.Requery.

If you think it's a handful it's not at all. I used to do this using Visual Basic code and it took around 30 seconds to finish. With the queries it takes about 2. I'll show you the operating code below to give you an idea of all the other little details involved:

Private Sub CmdUpdate_Click()
Dim intRV As Integer
On Error GoTo ErrUMD
DoCmd.SetWarnings False
DoCmd.Hourglass True
intRV = SysCmd(acSysCmdInitMeter, "Standby " & CurrentUser() & ", removing old data...", 100)
DoCmd.OpenQuery "qrymachcount"
intRV = SysCmd(acSysCmdUpdateMeter, 16)
DoCmd.OpenQuery "qrymachcountc"
intRV = SysCmd(acSysCmdUpdateMeter, 33)
DoCmd.OpenQuery "qrymachcountd"
intRV = SysCmd(acSysCmdUpdateMeter, 50)
DoCmd.OpenQuery "qrymachcounte"
intRV = SysCmd(acSysCmdUpdateMeter, 66)
DoCmd.OpenQuery "qrymachcountf"
intRV = SysCmd(acSysCmdUpdateMeter, 83)
DoCmd.OpenQuery "qrymachcountg"
intRV = SysCmd(acSysCmdUpdateMeter, 100)

ExitUMD:
DoCmd.Hourglass False
DoCmd.SetWarnings True
Me.Requery
intRV = SysCmd(acSysCmdRemoveMeter)
Exit Sub

ErrUMD:
MsgBox "Please contact the database administrator: a data update error has occured: The values represented may be incorrect.", vbCritical, "Data Update Error."
MsgBox Err.Number & " " & Err.Description, vbInformation, "Data Update error."
Resume ExitUMD
End Sub

(all that SysCmd stuff keeps the status bar up to speed with what's happening.)

The report is based off of the table. It's opened off of the form just like yours.

The key is to create the table first, defining your catagories in the table. Append to it and then handle those Nulls to keep things tidy. By "planting" zeros you can have your report do most math exactly as you desire.

All yours! Enjoy and please write back if you need some more help! Gord
ghubbell@total.net

 

[Guest_imported]

Dear Gord,
I have tried to do what you said, but it's a bit beyond me -have only been doing Access for about 2 months. I don't see how to get the results from a crosstab query into a new table using the update query - it won't let me! Also, when do I get to specify the criteria of Date and Subject?
Help!
Jane

 

[ghubbell]

Hi Jane,
Did you forget your password?
If you would like I'd be pleased to give it a look over for you (my treat!) If your Db isn't too big, copy, compact it and send it over as an attachment to an email. I'll post any changes back here too so everyone else can understand what's cooking! Gord
ghubbell@total.net

 

[janereyn]

Hi
Is 4 MB too much? Won't compact any further!

 

[ghubbell]

Not a problem! Gord
ghubbell@total.net

 

[ghubbell]

Well for all of you who would like to know the ending to this thread, Jane's Db is back home safe and sound. Following my own steps above with only one big difference being the duplicate criteria: Date and Course number. Either-or can be selected via their own combo boxes, each running off of Union queries and containing the word "All". A simple If-Then statement to decide which filter is appropriate based on the values in the combos and away it goes! Thanks Jane, for your confidence and patience! Gord
ghubbell@total.net

 

[janereyn]

Thanks Gord,
You've been unbelievably helpful and you certainly know your stuff.
Thanks a million
Jane

 

[sqlJunior]

I have a problem trying to email a report from a form when using a filter.

I can preview the filtered report with the records filtered as the DoCmd.OpenReport accepts the strFilter as part of the Where clause see Case 2 below:

Select Case Me!grpFilterDrwgs
Case 1
DoCmd.OpenReport "RpDrwg", acViewPreview
Case 2
strFilter = "[PipeNo] = '" & Me!cboLineNo & "'"
DoCmd.OpenReport "RpDrwg", acViewPreview, , strFilter
End Select


It also works OK for printing. However, I can't find out hot to get the DoCmdSendObject to accept the strFilter. I can only send the complete report as in Case 1 below:

Select Case Me!grpFilterDrwgs
Case 1
DoCmd.SendObject acSendReport, "RpDrwg", acFormatSNP, , , , "Piping Drawing Data"
Case 2
strFilter = "[PipeNo] = '" & Me!cboLineNo & "'"
DoCmd.SendObject acSendReport, "RpDrwg", acFormatSNP, , , , "Piping Drawing Data"
End Select

Can anyone tell me how I can get the report to email with only the filtered data?