Queries used as Record Source for Access REPORTS

[jcm3us]

I have an inquiry regarding using Queries in and as the "Record Source" of a Report...

I have a single Access Report named rptDemographics I am trying to gather a variety of information into and display.

I have a quite elaborate and useful union query [with the aid of a developer I might add] that I am using as the REPORT "RECORD SOURCE"

However, the issue of displaying this information on a single Report eludes me...

The query below displays the data well enough as an executed query but to get that information in a usable form into a REPORT...

The QUERY that is used and referenced as the "Record Source" of my REPORT:
SELECT "Industry" as TheAttrib, [Industry] as TheValue, Count(*) as NumOf
FROM tblBusinessData
GROUP BY [Industry]
UNION ALL
SELECT "BusinessTitle", [BusinessTitle], Count(*)
FROM tblBusinessData
GROUP BY [BusinessTitle]
UNION ALL
SELECT "Location", [Location], Count (*)
FROM qryLocation
GROUP BY [Location]
UNION ALL
SELECT "Degree", [Degree], Count (*)
FROM tblDegree
GROUP BY [Degree]
UNION ALL
SELECT "Gender", [Gender], Count (*)
FROM tblPersonalData
GROUP BY [Gender]
UNION ALL
SELECT "Ethnicity", [Ethnicity], Count (*)
FROM tblPersonalData
GROUP BY [Ethnicity];

What I would like to have displayed in my REPORT should be something like:

Industry [example of VALUE counts in DB]
Communications - [7]
Conglomerate - [2]
Construction - [5]
Consulting - [12]
etc...[there are 16 total industry values]

BusinessTitle [example of VALUE counts in DB]
Advisor - [3]
CEO - [4]
CEO & President - [9]
CEO & General Counsel - [4]
etc...[there are 27 total BusinessTitle values]

Location [example of VALUE counts in DB]
Austin - [22]
Dallas/Ft Worth - [34]
etc...[there are 7 different Locations values for report]

UndergraduateDeg [example of VALUE counts in tblDegree.Degree AND tblDegree.University]
UT - [89]
Non-UT - [21]

GraduateDeg [example of VALUE counts in tblDegree.Degree AND tblDegree.University]
UT - [24]
Non-UT - [8]

A suggestion was made to use [based upon the query above]:
Control Source: TheAttrib
Control Source: TheValue
Control Source: NumOf

If I use TheAttrib for the Text-Box Control Source it returns "Industry" which is the value in the first record row of the executed query under TheAttrib

If I use TheValue for the Text-Box Control Source it returns "<BLANK>" which is the value in the first record row of the query under TheValue

If I use NumOf for the Text-Box Control Source it returns "186" which is the value in the first record row of the query under NumOf

I would have thought that since I am seeking a "CountOF" value based upon a criteria that some form of an expression in the "Control Source" of each "Text Box" would be more appropriate...

Something LIKE but not necessarily:
SELECT Count([qry_ReportData].[BusinessTitle]) AS Titles FROM qry_ReportData HAVING [qry_ReportData].[BusinessTitle] = "CEO*"

This should (I would think) return a count value of 14 based upon the below sample table data


THIS IS A SAMPLE OF QUERY RESULTS
TheAttrib TheValue NumOf
Industry 186
Industry 3 1
BusinessTitle 26
BusinessTitle Assistant Dean 1
BusinessTitle Associate VP 3
BusinessTitle Attorney At Law 1
BusinessTitle Advisory Chair 1
BusinessTitle CEO 8
BusinessTitle CEO & Chairman 5
BusinessTitle Retired 1
BusinessTitle CEO & President 6
BusinessTitle CEO-Banking 1
Location 10
Location Amarillo, TX 1
Location Argyle, TX 1
Location Austin, TX 21
Location Bellaire, TX 1
Location Blanco, TX 1
Location Bozeman, MT 1
Degree 4
Degree AB 1
Degree BA 29
Degree BBA 99
Degree BS 20
Degree Honorary MA 1
Degree Honorary PhD 4
Degree JD 13
Gender 2
Gender Female 19
Gender Male 161
Ethnicity 9
Ethnicity African Amer. 4
Ethnicity Caucasian 162
Ethnicity Hispanic 7


Please advise.

 

[jcm3us]

Quote dhookom
Do you understand that a criteria is a filtered group or subset of records?

Yes, I fully realize that the criteria that I am asking is a subset of the query results.

So, the above list is a subset of ALL the 164 queried results. I don't need that total volume, simply
BusinessTitles = 27 of 87 values [some of the 27 are SumsOf to combine/group other values]
Degree = 2 of 2 values
Ethnicity = 3 of 3 values
Gender = 2 of 2 values
Industry = 16 of 16 values
Location = 7 of 54 values [some of the 27 are SumsOf to combine/group other values]

And to Print ONLY this Report list every time. That is why I am trying [and thought I could] HARD CODE this functionality.

The purpose of this selective Report will ultimately be for a Board of Directors and needs to represent what they want to see.


Thank you

 

[dhookom]

So simply stated, you have multiple Business Title values in a single field like: "Advisor/Consultant", "Chairman & President" and "CEO, President & Executive Chairman".

Do you want "Advisor/Consultant" counted twice; once as Advisor and once as Consultant? If only once, then under which title? If more than once, please provide the name of your table and field that contains every unique value for the titles. If you don't have one, you need to create one.

Also, it seems that you want to combine some locations into "Others" while report counts of some by single location. Do you have a table of every unique location or at least a table of those that need to be counted uniquely? If you don't, you need to have some table with records that identify which locations to group together and which to combine as "Other Locations".

Duane
Hook'D on Access
MS Access MVP

 

[jcm3us]

Quote: dhookom
...you have multiple Business Title values in a single field like... Yes

The FORM that all values are input by the user are stored in 2 tables tblPersonalData and tblBusinessData.
- Field BusinessTitle is a FREE FORM Text found within the tblBusinessData
- Field Location is also a FREE FORM Text field and it resides in the tblPersonalData

The value Advisor/Consultant should be a SumOf, that is:
Advisor + Consultant and would produce a single NumOf Value
and a title of Advisor/Consultant

Yes, the Others value in Location field IS a SumOf

I can create a table or would it work just as well with a query??
If I create tables for BusinessTitle and Location, then how would that be used in a Free Form Text field on the INPUT FORM?

Table: tblPersonalData
Field: Ethnicity
Field: Gender
Field: Degree
Field: Location

Table: tblBusinessDataIndustry
Field: Industry
Field: BusinessTitle


Thank you

 

[dhookom]

If you want to combine several locations into one sum, you have to have a table that identifies which locations belong to the "others". If not, you need to identify which locations should be separate.

I would create tables of unique values in each field that you would like to count. Then on your data entry form, you can use combo boxes based on the unique values. This would hopefully prevent different spellings of the same value for instance:
CEO, President & Executive Chairman
CEO, President & Exec Chairman
CEO, Executive Chairman & President
President, CEO & Executive Chairman


Duane
Hook'D on Access
MS Access MVP

 

[jcm3us]

Okay, let's see if I have this correct:

you want me to create unique tables for both
Business Titles
Locations

this is due to combining several of the field values, right?

I am guessing here, but really all I need then for field names is:
tblUniqueTitles
BusinessTitles

tblUniqueLocations
Locations

Or are there other fields that may be necessary for what you are suggesting? [maybe a field for TitleID and LocationID for any reason]

You asked previously:
Quote: dhookom
...you have multiple Business Title values in a single field like... which I resonded Yes

I am seeing a potential problem with this scenario

Currently Location is actually a field in a query that is entered as and is made up of ALL of the following:
City
State
Country

If I have a specific list to select from on the input form [for both Titles AND Locations]. The Titles may not be as big an issue as Locations... it sounds like I may have to redesign the form and tables for those fields.

Thanks

 

[dhookom]

I would immediately change the location field so it is storing only one value, not three. Use the "unique" tables as Row Sources of combo boxes to enter values from a list rather than free text entry.

You don't necessarily need TitleID and LocationID. I think this would add a ton of confusion at the moment.

I don't know if you combining multiple titles together in a single field works for you or not. It depends on how you want to count them.

Duane
Hook'D on Access
MS Access MVP

 

[jcm3us]

Hmm, I see a potential issue starting to arise out of this Location table. That is, Location is actually a field that is created within a query AND NOT a field name in the tblPersonalData as I stated above.

It is a field that is composed of City + State + Country from the tblPersonalData Had I known where you were going with this line of proposed actions for my report I would have clarified a lot sooner...

Would you advise as to how might I resolve where AND what you are trying to accomplish?

Thank you

 

[dhookom]

I am confused regarding what you want. Going back to your first posting in this thread

What I would like to have displayed in my REPORT should be something like[/code]
What about the print preview doesn't look like what you want other than []s around the numbers?

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Sorry, should have been
I am confused regarding what you want. Going back to your first posting in this thread

What I would like to have displayed in my REPORT should be something like

What about the print preview doesn't look like what you want other than []s around the numbers?

Duane
Hook'D on Access
MS Access MVP

 

[jcm3us]

The post that I provided on 21 Oct 09 21:23
lists the entire list of items expected on the desired REPORT.

Grouped as:
Business Titles
Degree
Ethnicity
Gender
Industry
Location [qry result of City, State, Country]

Each of these groups will have a list of specific criteria

Example Business Titles:
Advisor/Consultant
Attorney at Law
CEO
CEO & President
CEO & General Counsel
CEO & Chairman
CEO, President & Executive Chairman
Chairman
Chairman & President
Chairman Emeritus
Vice Chairman
COO
Executive Director
Director
Managing Director
Vice President
Executive Vice President
Executive Vice President & COO
Managing Partner
Partner
Global Head
Owner
President
Principal
Private Investments
Retired
Other

Each one of these will/should have a numeric value associated with it as will each of the other grouped criteria. I had used [] brackets simply to illustrate that there should be a value. These brackets do not need to be displayed on the resulting REPORT.

I am going to attempt to guess that if I can get some foundation structure as to how to accomplish [query or otherwise, I might be able to adjust as necessary]

I apologize that this is taking as long as it is.

Thank you

 

[dhookom]

If your query looks like your first post

THIS IS A SAMPLE OF QUERY RESULTS

your report will look like you want it. What about the print preview is not how you want it?

Duane
Hook'D on Access
MS Access MVP

 

[jcm3us]

How can a Print/Report view look like a specific selected list from a query? Because what is displayed upon executing that query lists more than I desire in my Report

Even going by your instructions to create my report, the desired results were not displayed.

Bottom line the query lists MORE information than I need.


Thank you

 

[dhookom]

Your sample display shows about 34 records which I assume is a subset of the total number of records in your report's record source.

I assume you want to group some of these TheValues (degrees, Titles, Locations, ...) together for counting purposes. If so, you must have some values saved in your tables that identify which values should be grouped together. For instance if you want the Degrees of AB and BA counted as 30 rather than 1 and 29, then you must have something stored in your tables that says AB and BA are in the same group.

In your first post, you suggested a query like:

SELECT Count([qry_ReportData].[BusinessTitle]) AS Titles
FROM qry_ReportData 
HAVING [qry_ReportData].[BusinessTitle] = "CEO*"

which should actually be:

SELECT TheAttrib, TheValue, NumOf
 FROM qry_ReportData 
WHERE TheAttrib ="BusinessTitle" AND TheValue Like "CEO*";

If you want all of the BusinessTitles your query would look like:

SELECT TheAttrib, TheValue, NumOf
 FROM qry_ReportData 
WHERE TheAttrib ="BusinessTitle";

Typically your report should have no filtering (where clause) and you would specify a WHERE CONDITION in the DoCmd.OpenReport method.




Duane
Hook'D on Access
MS Access MVP

 

[jcm3us]

This is great... You bring up a point that I had not considered that may be what just might work... If I can hopefully get your assistance in completing

If I use the On Open Event Procedure to specify a WHERE CONDITION....
1. What would the structure of that expression be?
2. Where within the code would that occur?
3. Can I have more than one WHERE CONDITION within the "On Open" procedure?

below is strictly a guess
Private Sub Advisor_Consultant_BeforeUpdate(Cancel As Integer)

End Sub


WHERE TheAttrib ="BusinessTitle" AND TheValue Like "CEO*"
WHERE TheAttrib ="BusinessTitle" AND TheValue Like "Pres*"
WHERE TheAttrib ="BusinessTitle" AND TheValue Like "Vice*"
WHERE TheAttrib ="BusinessTitle" AND TheValue Like "Manager*";

Private Sub Report_Open(Cancel As Integer)

End Sub


So, to recap...
If I use a Record Source of your above query that lists ALL of the values available?

Then I might can use an Event Procedure that includes WHERE CONDITION(S) to specify the specific data needed.


Thanks

 

[dhookom]

I generally build a where condition in code and apply it to the DoCmd.OpenReport method. I create a form that allows users to select values in a listbox or combobox or enter values into text boxes. I then loop through the controls to build a where condition.

Search these forum on building where conditions or google on "Access Reports strWhere = "1=1""

Duane
Hook'D on Access
MS Access MVP