Problems creating a report to display totals of several choices in several categories

I am in the process of creating an Access 2003 database for a small personal-support charity. This database holds details of their “Clients”.
.
In order to keep the data readable I have opted to use populated list boxes for several selections on the main form. This is so that the Client table is directly readable by unskilled people (if absolutely necessary) without having to interpret the data contained in many related tables. Also Access 2003 must be used as they do not have access to any later version.
.
The list boxes take the form of {Age Range: “Up to 5”;”6-11”;”12-16”;”17-25”;”26-50”;”51-70”;”70+”} where Age range is the field name (Control Source) and the rest are the values in the dropdown list (Row Source).
.
I have similar arrangements for Gender, Ethnicity, Impairments and several others; Eleven in total
.
For statistical reasons I need to count the number of entries for each option in each of the different fields. I have created a number of separate queries to do this, one for each group of options.

Each of these queries takes the form:
SELECT Clients.Age, Count(Clients.Client_ID) AS CountOfClient_ID
FROM Clients
GROUP BY Clients.Age;
.
Each generates an output as: (Please excuse the hyphens they are used a spacers to format the layout, as the editor seems to strip out any repeated spaces or tabs)
.
Age--------CountOfClient_ID
Up to 5 -------17
71+ -----------19
6 - 11 --------17
51 - 70 -------15
26 - 50 -------15
17 - 25-------- 8
12 - 16 -------15
.
I now need to create a report that brings together the outputs of all of these queries. This, ideally, should be a single report as:
.
Category ----Total for Year
.

Gender
Male ---------16
Female -------18

.
Age-Range
Up to 5 ------17
6 – 11 -------17
12 -16 -------15
17 -25 --------8
26 -50------- 15
51 -70 -------15
71+ ----------19
.
Etc.......

My problem is that when I try to create the report I find that all the data on the report must come from a single query or table, obviously my report data is created from several different queries. If I use the Wizard to create the form and try adding two, or more, query fields to the list of fields I get the error message “You have chosen fields from record sources which the wizard can’t connect. You may have chosen fields from a table and from a query based on that table. If so, try choosing fields from only the table or only the query.”
.
I have tried creating a single query that brings together the several queries and either get a massive output showing all the possible combinations of the fields chosen or the “You have chosen fields from.......etc” error message.
.
I am sure that I have done this before, but as I retired from full-time I.T. some 10 years ago I’m now quite rusty, not to mention forgetful. So any advice would be very useful. I’m fairly sure that it’s relatively easy, but I’m not able to see it at the moment
.
A secondary problem is how to get the initial queries to display their output in the order that the list is in the list box . That is, in the order : “Up to 5”;”6-11”;”12-16”;”17-25”;”26-50”;”51-70”;”70+”. Not as shown in the query output above, this is in alphabetic order, not the order needed.
.
Any, and all, help is welcome. Thanks


Post your answer or comment

comments powered by Disqus
I am in the process of creating an Access 2003 database for a small personal-support charity. This database holds details of their “Clients”.
.
In order to keep the data readable I have opted to use populated list boxes for several selections on the main form. This is so that the Client table is directly readable by unskilled people (if absolutely necessary) without having to interpret the data contained in many related tables. Also Access 2003 must be used as they do not have access to any later version.
.
The list boxes take the form of {Age Range: “Up to 5”;”6-11”;”12-16”;”17-25”;”26-50”;”51-70”;”70+”} where Age range is the field name (Control Source) and the rest are the values in the dropdown list (Row Source).
.
I have similar arrangements for Gender, Ethnicity, Impairments and several others; Eleven in total
.
For statistical reasons I need to count the number of entries for each option in each of the different fields. I have created a number of separate queries to do this, one for each group of options.

Each of these queries takes the form:
SELECT Clients.Age, Count(Clients.Client_ID) AS CountOfClient_ID
FROM Clients
GROUP BY Clients.Age;
.
Each generates an output as: (Please excuse the hyphens they are used a spacers to format the layout, as the editor seems to strip out any repeated spaces or tabs)
.
Age------- CountOfClient_ID
Up to 5 -------17
71+ -----------19
6 - 11 --------17
51 - 70 -------15
26 - 50 -------15
17 - 25-------- 8
12 - 16 -------15
.
I now need to create a report that brings together the outputs of all of these queries. This, ideally, should be a single report as:
.
Category ----Total for Year
.

Gender
Male ---------16
Female -------18

.
Age-Range
Up to 5 ------17
6 – 11 -------17
12 -16 -------15
17 -25 --------8
26 -50------- 15
51 -70 -------15
71+ ----------19
.
Etc.......

My problem is that when I try to create the report I find that all the data on the report must come from a single query or table, obviously my report data is created from several different queries. If I use the Wizard to create the form and try adding two, or more, query fields to the list of fields I get the error message “You have chosen fields from record sources which the wizard can’t connect. You may have chosen fields from a table and from a query based on that table. If so, try choosing fields from only the table or only the query.”
.
I have tried creating a single query that brings together the several queries and either get a massive output showing all the possible combinations of the fields chosen or the “You have chosen fields from.......etc” error message.
.
I am sure that I have done this before, but as I retired from full-time I.T. some 10 years ago I’m now quite rusty, not to mention forgetful. So any advice would be very useful. I’m fairly sure that it’s relatively easy, but I’m not able to see it at the moment
.
A secondary problem is how to get the initial queries to display their output in the order that the list is in the list box . That is, in the order : “Up to 5”;”6-11”;”12-16”;”17-25”;”26-50”;”51-70”;”70+”. Not as shown in the query output above, this is in alphabetic order, not the order needed.
.
Any, and all, help is welcome. Thanks

Hi,

How to create a Report from the result of a Filter?

I have a "Facility Site Assessment" database. There are 92 questions that must be answered for each assessment conducted. Answers are either Yes, No or N/A.

In my questions table, I have the Question number, question text, a Recommendation, Reference and Response. There is a prewritten Recommendation, reference and response for every question. The report needs to display all 92 questions and their answers (Yes, No, N/A), so the contact at the facility knows how they fared on all topics of the assessment. But ONLY if a question is answered "NO", then the recommendation, reference and response need to appear beneath that question, so they have instruction on how to resolve the problem. How do create a report that does that?

I appreciate any guidance!!!

Hello

Let me start by saying I am very new to access. I'll briefly describe what I'm trying to do:

I have a form which populates a table with fields such as: Name, Date, Site #, Unit #, Loads Moved and Hours Worked. I have designed a query which pulls the user inputted data and also makes calculations such as loads/hour and total loads moved.

What I want to do is create a report which displays this information. In my query I use the criteria value to limit the dates included in the report by asking the user for a start date and an end date which works fine and is what I want to do. However when I open the form which I discussed earlier it again asks the user for a start date and an end date which is not what I want. I want user to enter the date normally in the form.

I suspect I have some sort of relationship problem. I hope I am explaining this well enough for someone to give me some valuable feedback.

I have created a report to display data from surveys. The report shows the location the survey was taken and then (by location) the count for every program wanted.

I have attached a screen print. What I am trying to do is put the 'Total number of surveys received:' on the report for each location. The figure shown is incorrect. I just put something there to show you what I am trying to accomplish.

I hope this is enough information to go on. Any suggestions on how to get this number and display it on the report?

TIA!

Hello
I am trying to create a report from a cross tab query, the problem is that the column field in the query changes based on whether data appears in a particular month i.e

run query on 27/03/02
group Jan Feb March
1 1
2 1 1
3 1 1

run query on 27/04/02
group Jan Feb March April
1 1 1
2 1 1 1
3 1 1


If I create a report using the result of the first query it doesn't reflect the data from the second. I need to know how to create the report without resulting to manual intervention to add the additional columns as the output is to be run as a "chron job".

Cheers Chris

I have a table in Access containing file paths to images along with various other data.

I have created a report to display this information along with the picture that the file path points to.

In the detail section of the report i have included the following code in the OnFormat property.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim Image As String
Image = Me![PictureLocation]
MsgBox Image
Me![Image1].Picture = Me![PictureLocation]
End Sub

My problem is that when i run the report I am displayed with the following error message,

Runtime error '2220':

Microsoft Access can't open the file
'../images/imagename.jpg#images/imagename.jpg#'.

Can anyone explain how this is happening and how to avoid it?

Thanks in advance

I have a database with a list of cost centers, the owners of each, and the approvers under each. Some owners are connected to mulitple cost centers.

I need to send a report to each owner, showing a breakdown of their cost centers, and the approvers under each. Their emails are also stored in the database. Is this possible without any manual work?

I'm thinking I need to create a query for each owner, then generate the report from those queries, then email the reports.

Any suggestions on how to code all of that???

Thanks in advance!

I have a report which is comprised of several sub-reports which all format according to a set of variable. There are times when the resulting query produces and empty set.

I need the sub-report to display a black box when the underlying query produces no results.

I then need the black box to be visable in the Report in the location of that sub-report.

Here is the code I'm using:


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

On Error GoTo ErrorHandler

If Me.Ctl45EliteSPremium = 0 Then
Me.[Box4].BackColor = 0

Else
Me.[Box4].BackColor = 16777215

If Me.[% from best] 0.07499999999 And Me.[% from best] < 0.15499999999 Then
Me.[Box4].BackColor = 65535

Else
Me.[Box4].BackColor = 16777215

End If

End If

End If

End If

ErrorHandler:

Exit Sub

End Sub
Thanks for help.

George

Imagine a table with the information like shown below:

TABLE "A":

DATA1 DATA2
------------------------
A1 B1
A1 B2
A1 B3
A2 B2
A2 B4
A3 B1
A3 B2
A3 B4

I want to create a report with the next information:
DATA1 DATA2
------------------------
A1 B1
A2 B2
A3 B1

It is, only the first row of each distinct DATA1. Can anyone help me on SQL or MS Access design??

Thanks once again!

I have an ACCESS database that I use to track our employees who 'graduate" from our training programs. I used the lookup wizard in the field used for their assigned dept. Problem I have is that when I create a report to list graduates by dept I get the ID# back instead of the dept's name...any ideas how can I solve this problem. When I run the query I can see the dept's name but when create report based on that query I get the Dept's ID# back... Any help will be greatly appreciated...

I want to create a report that should count the number of yes, no, and NAs, for a number of questions and then show which are among the top 10.

for example a questionnaire that would have several people filling out the questionnaire. I want to create a report that will see which questions got the top ten yes, no, an na.

QuestionID field, Question Field, Answer Field
1 Blah Blah 1 y
2 Blah Blah 2 n
3 Blah Blah 3 na
4 Blah Blah 4 y
5 Blah Blah 5 n
6 Blah Blah 6 na
7 Blah Blah 7 y
8 Blah Blah 8 y
9 Blah Blah 9 n
10 Blah Blah n
11 Blah Blah y
12 Blah Blah y
13 Blah Blah y
14 Blah Blah y
15 Blah Blah y

I am creating a report that shows gains and losses among codes. I have the gain figured out but cant get the loss, it just repeats the gain number since the gain number is assigned a loss date.

For example, in February I gained 2 code 10's (gains have an expiration of 2099) and lost 1 code 3.

I have attached an excel file of the data and 2 pivot tables that i was using to check my data.

Can anyone help me build this? Attached Files INTERNAL LOB MOVES (GRADE CHANGE).zip (47.7 KB, 3 views) Reply With Quote 09-12-2011, 10:49 AM #2 Robeen VIP Windows XP Access 2010 32bit Join Date Mar 2011 Location Tulsa, Oklahoma. Posts 1,500 I'm having trouble understanding what you need to do.
Could you explain with a little more detail?

Hello All,
I have a form and in that form I have a drop list and I would like to create a report to the from the reference I selected.

Currently when I select the reference item it will pull up all forms with the reference that is selected (This works Great.
What I would like to do is take this to another level. Instead of it opening the form I would like it to open a report. I tried removing "frmShipping" and replacing it with "rptShipping" but I keeping on getting an error. I created a report named rptShipping which is based on a query.

I here is the code I used to open the forms.


Private Sub ReferenceLookup_AfterUpdate()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmShipping"

stLinkCriteria = "[Reference]=" & "'" & Me![ReferenceLookup] & "'"
DoCmd.Close acForm, "frmReferenceLookUp", acSaveNo
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub


This is still new to me so be gentle.
Thanks
Art

Hi,

I was wondering if anyone knows how to create a report in Microsoft Access 2002 that will display which fields are blank in the database for each client. Please check the below screen shots for more information.






You can see from the above image the John Smith is missing data for TelNo and NI Number and the other 2 clients are missing data for 3 fields. I would like a report to display what fields are blank for each client. The below image is what I would like the report to display.



Thanks,
Chris

I would like to create a report using queries instead of tables. I go a blank design and all that comes up is the tables for me to use how do I get the queries instead.

Hi,

I am working on a database which will enable fellow employees to record drawing issues. The report will need to show the details of each drawing and each recipient involved in the issue.

My form (see first .jpg) allows the user to select the drawings and the recipients. However, I would like them to be able to access a report based on the single record which they have entered. So far, I have been able to create a report which shows all of the information, although some of the fields are repeated often (see second .jpg). I would like the report to show a single list of drawings selected, with all of their details located from their table (drawing table) and the same with the recipients.

For example, I would like one section as follows:
Drawing Nubmer, Drawing Title, Current Revision, Revision Description

And the other:
Recipient, Association



Any help would be appreciated.
Thanks in Advance.

Hi All,

I am trying to generate a report from the click of a button on a form and I am stuck. Here is what I have:

Function GetZipCodes(RepName As String)

Dim db As Database
Dim wrkJet As Workspace
Dim SQL As String
Dim qdfTemp As QueryDef
Dim rstZipCodes As Recordset
Dim X As Variant
Dim Rpt As Report


Set wrkJet = CreateWorkspace("NewJetWorkspace", _
"admin", "", dbUseJet)
Set db = wrkJet.OpenDatabase("Youth Conference.mdb")
Set Rpt = CreateReport

SQL = "SELECT zip_code FROM ZipCodes where rep_name = '" & RepName & "'"
Set qdfTemp = db.CreateQueryDef("", SQL)

Set rstZipCodes = qdfTemp.OpenRecordset()
With rstZipCodes
Do While Not .EOF
X = !zip_code
MsgBox X
Rpt.RecordSource = X
.MoveNext
Loop
.Close
End With

db.Close
wrkJet.Close
End Function

Instead of X being printed in a message box, I want X (and the corresponding rep_name) to be printed on a form----how do i go about doing this? (rep_name is duplicated in the table and a rep_name can be associated with many zip_codes. I am allowing the user to select the rep_name from a drop down list. When this is selectd, I want to find all zip_codes the rep_name is associated with. I only want the rep_name selected to appear on the report once, followed by all the zip_codes under him. Can anyone help me with this?


Thanks,
Mays

It seems that this would be easy, and it may be, but I cannot seem to find a way to only display certain text within a field. I have 5 fields that I am querying for and opening a report to display the results. I want to only show certain text out of one field due to all the garbage data within that field. Any help would greatly appreciated. Thanks everyone!

Hi, I am new to the forum. Thanks in advance for your help.

I have a table with a year's worth of data. I want to be able to create a report to display monthly the number of new customers. So typically it is a case of diminishing returns. I may have 100 new customers in Jan, some of those customers may return in Feb, but I only want to count the customers who are unique for February. I need to do this for the entire year. I thought of creating a new "unmatched" query for each month but there must be an easier way. Also thought of a crosstab query which could display the months easily.

Any thoughts?

Here is a sample of what my table looks like

Customer DateAdded Current Status
ABC 1/1/2010 Pending
ABC 1/15/2010 New
XYZ 1/30/2010 Pending
XYZ 2/4/2010 New

In this example, I would have one new customer in January, and one new customer in Feb. This is what I want to query, and eventually the report to display.

Thanks!

I have created a report to display total sales values for each month, the original orders are entered using the actual date, e.g. 03/08/2004. The report runs off a query which lists each individual sale, I have then attempted to group each sale into a monthly sales total. I have been able to convert the dates into the format I require for my report i.e. mmmm yyyy but I cant total the results with the same month and year together as one record. Please note I do not want to group all the results for each month together I want to calculate a total and display only this figure. Can anyone help?

e.g. This is what I have

Customer Name: Stationary World

Product Order Date Value
Blue Pens January 2004 £300
Blue Pens January 2004 £500
Blue Pens January 2004 £150
Red Pens February 2004 £100
Red Pens February 2004 £300


What I really want is:

Product Order Date Value
Blue Pens January 2004 £950
Red Pens February 2004 £400

I want a report to display records as per this...
-----------------------
Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer)
Dim RecHisto As Recordset
Dim Requete As String

Requete = "select FinPlage from historic where DateAction= (select max(DateAction) from Historic where ActionSurInterface = 'CHARGEMENT')"
Set RecHisto = DBEngine(0)(0).OpenRecordset(Requete, dbOpenSnapshot)
If Not RecHisto.NoMatch Then
'Text80.Value = RecHisto.Fields(0)
End If
RecHisto.Close

End Sub
-------------------------
But I also want fields from Historic to print on this report - I can make a sub report, but how do I ask it to print only the dates from Historic and the report pulled as per above code?

I am trying to create a report to show totals for several different inspections types based on a date range.

Currently, I only have three fields in the query, inspection type (group by), inspection type (count) and inspection date (where, date range).

I am trying to use a Dlookup to show the totals on the report. If I remove the date parameter from the query and only get the grand total for each inspection type from the table then the Dlookup will work. If the date parameter is left in the query than all I get is an #error on the report .

Should I be using something besides a Dlookup to show these to show these totals for the proper date range.

This is the query I am using;

SELECT inspections.inspection_type, Count(inspections.inspection_type) AS CountOfinspection_type
FROM inspections
WHERE (((inspections.inspection_date)>=[begining date] And (inspections.inspection_date)

Hi All

Could anyone please help me to solve my problem.I have created a form in MS Access that has 2 comboboxes named startdate and enddate,I pop up both of them with calendar control so that when i click on a combobox,a calendar appears ..from which we can select any date. I have created a report that contains DRS ID,DateTime Requested,Deadline etc. Now I want to display only those records from a report whose DateTimeRequested value is between Startdate and Enddate of comboboxex.

I have written the following code but it neither display the result nor any error message.

Private Sub Command1_Click()

Dim stDocName1 As String
Dim stDocName2 As String
Dim mysCriteria As String
Dim topLabelOnReport As String

stDocName1 = "MI - DocumentRequests with RequestDate,Deadline,DateSentOrNotSen"

'Set the Date Range
mysCriteria = "Datevalue(DateTimeRequested)>=#" & Format(Me.StartDate.Value, "MM/DD/YY") & "# And" & _
" Datevalue(DateTimeRequested)


Not finding an answer? Try a Google search.