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.

 

[dhookom]

Set up the Sorting and Grouping in your report:
1st Level:
TheAttrib
Display the Group Header and place a text box
in this section bound to TheAttrib
2nd Level:
TheValue
Place text boxes in the detail section bound to TheValue and NumOf.

If you want a single text box in the detail section rather than two, try this:

Name: txtValAndNumOf
Control Source: =[TheValue] & " - [" & NumOf & "]"


Duane
Hook'D on Access
MS Access MVP

 

[jcm3us]

First, I am not familiar at all with grouping/sorting so I am explaining what I am trying to do & what is observed based on your instructions

Using Access 2007
Select Group & Sort Design button
- lower pane displays with "Group, Sort, and Total header
- "Add a group" and "Add a sort" boxes [buttons] display in this panel

Select/Click "Add a group"
- SubHeader displays with "Group on [select field]" (this looks like a drop list field)

Select/click on "drop list" arrow
- Expression box displays

Double Click expression box
- Expression builder displays

Input expression "TheAttrib" [per your instruction]
- A New Header displays between Page Header and Detail
- New Header name: =[TheAttrib] Header

Created new text box within this new header
- Label box: Industry
- Text Box Control Source: TheAttrib

Select/Click "Add a group"
- SubHeader displays with "Group on [Expression]"

Select/click on "drop list" arrow
- Expression box displays

Double Click expression box
- Expression builder displays

Input expression "TheValue"
- A New Header displays between -[TheAttrib] Header and Detail
- New Header name: =[TheValue] Header

Created Text box in "Detail" Header
- Label:
- Text Box Control Source: TheValue

[This is where I am not sure what and how to "Bind" a single text box to two sources]
you stated: [Place text boxes in the detail section bound to TheValue and NumOf]

Can you bind a Label to a Source? [To display TheValue] that way the Text box is bound to NumOf
Do the steps I am listing here sound correct?

***********
What I had envisioned originally [not using grouping]
[Example]
Label: Industry
TextLabel: Construction TextBoxControlSource: CountOf
TextLabel: Communication TextBoxControlSource: CountOf
TextLabel: Consulting TextBoxControlSource: CountOf
TextLabel: Education TextBoxControlSource: CountOf
TextLabel: Energy TextBoxControlSource: CountOf


Label: Titles
TextLabel: Advisor TextBoxControlSource: CountOf
TextLabel: CEO TextBoxControlSource: CountOf
TextLabel: CEO & President TextBoxControlSource: CountOf
TextLabel: General Counsel TextBoxControlSource: CountOf



Thanks for your assistance.

 

[dhookom]

I don't use Access 2007 however, you should only create one grouping with a header section and one sort. There should only be a group header section on TheAttrib with just the text box and no label (as per my previous post). Then you only have the detail section where you place two text boxes. One bound to TheValue and the other to NumOf.

That's it, no more.

The example you posted isn't possible without sorting and grouping.

Duane
Hook'D on Access
MS Access MVP

 

[jcm3us]

Okay, I got that I should have only one group based upon "TheAttrib" and I am guessing that the Sort function is to allow me to see the values that I desire, right?

Beyond that I am confused...

When I create a Text box there are two objects created:
Text Label AND Text Label

Am I not to use the Text Label Box that is always associated with creating a Text Box? If not, what is done with that object?

Thanks

 

[jcm3us]

One other quick question in addition to and regarding the previous post...

Am I to have "multiple" Text Boxes within the Detail header section OR how am I to show what I desire with only two Text Boxes?

Label: Industry
TextLabel: Construction TextBoxControlSource: CountOf
TextLabel: Communication TextBoxControlSource: CountOf
TextLabel: Consulting TextBoxControlSource: CountOf
TextLabel: Education TextBoxControlSource: CountOf
TextLabel: Energy TextBoxControlSource: CountOf


Label: Titles
TextLabel: Advisor TextBoxControlSource: CountOf
TextLabel: CEO TextBoxControlSource: CountOf
TextLabel: CEO & President TextBoxControlSource: CountOf
TextLabel: General Counsel TextBoxControlSource: CountOf


If I am only to have the two Text Boxes TheValue AND NumOf Only ONE RECORD VALUE of my data is listed and displayed NOT what I am desiring...

 

[dhookom]

You don't need label controls. DELETE THEM ALL! You may need to ungroup them or something in Access 2007.

Your first post gave a sample of the query output. Clearly "Industry" is a value of the field [TheAttrib] which can be easily displayed with a text box in the [TheAttrib] group header section (as I have stated over and over).

I also stated you need two text boxes in your detail section.

I'm not sure what is so difficult with understanding my instructions. I've tried to be very clear.

[tt] [blue]
==Page Header====================================

==TheAttrib Header===============================
[TheAttrib] <=Text Box
==Detail=========================================
[TheValue] [NumOf] <=Text Boxes
=================================================
[/blue][/tt]


Duane
Hook'D on Access
MS Access MVP

 

[jcm3us]

I am not saying you have been unclear at all. What I am saying is I am not sure how the instructions you have given and that I have followed are going to give me the results I am seeking. Based upon what I am observing and NOT observing. What you have instructed me to perform works [for a single record row of data count], however it DOES NOT display ALL of the field data [counts].
... that is why I am confused.

What I have displaying currently is as you describe above in your thread postin with the Page Header, TheAttrib Header, etc and that works for a single record row only.
What actually displays is a selected record row from somewhere in the middle of the list that the newly build query generates.

That is, why all along even from the last thread that I desire to see within a single report multiple points of data counts based upon specific criteria. At this point nothing is or has addressed the specific criteria to display individual counts [yes they display in the query, but NOT in the report. I desire EACH Specific individual record row to be dispalyed (Based upon specific criteria]

example: [this would be within the Industry "Group"]
TextLabel: Construction TextBoxControlSource: CountOf
TextLabel: Communication TextBoxControlSource: : CountOf
TextLabel: Consulting TextBoxControlSource: : CountOf
TextLabel: Education TextBoxControlSource: : CountOf
TextLabel: Energy TextBoxControlSource: : CountOf

Example: [this would be within the Titles "Group"]
TextLabel: Advisor TextBoxControlSource: CountOf
TextLabel: CEO TextBoxControlSource: CountOf
TextLabel: CEO & President TextBoxControlSource: CountOf
TextLabel: General Counsel TextBoxControlSource: CountOf


I apologize for any efforts that may not be functioning as I would like based upon what it is that you are trying to assist me with. [I know that you are only going by what I am telling you as to how to resolve my issues] I am NOT a programmer at all, so the nomenclature, semantics, and sometimes the communications may not be as clear and concise as desired by both of us and that may possibly be part of what is getting in the way as well.


Thank you for your assitance AND patience.

 

[dhookom]

Are you even observing the report print preview? You will generate a group section for every different value of [TheAttrib] and a detail section for every record. That's how reports work. I expect you might be confused by a design or layout view or something.

Your print preview will look very much like the bottom of your first post however it will look more published. If you don't observe every record, then you having something wrong with your query or the report's filter.

Duane
Hook'D on Access
MS Access MVP

 

[jcm3us]

Wow, I am getting a LOT closer... not just by your last post. However, you did give me an idea that allowed the visual display to occur. I am almost where I am desiring the final display.

Evidently the display is sorting alphabetically by "TheAttrib" which is fine, but then there doesn't seem to be any order to "TheValue" field it seems to be totally random, which I don't guess is too big of an issue for reporting. But really and more importantly, is there a way to select specific "TheValue" records as I have asked before using some form of a Select statement?
[there is actually more data than is truly desired]

Thanks for all of your assistance...

 

[dhookom]

From about a week ago

Set up the Sorting and Grouping in your report:
1st Level:
TheAttrib
Display the Group Header and place a text box
in this section bound to TheAttrib
2nd Level:
TheValue

Apparently you want to filter the report, there are lots of methods for doing this based on if you want a range of values, values based on a specific attrib, a single value, or whatever. I would expect you would want a specific attrib which the user could select from a combo box.

Duane
Hook'D on Access
MS Access MVP

 

[jcm3us]

I have been desiring to, as you call it, filter the report all along...

The Grouping is as follows:
Group On TheAttrib
with A on Top again, this is not an issue as this point using your query as it is currently

...it is at the next level TheValue. That is why I have been trying to understand (as I am not a programmer) and get to display with ONLY specific data not to have a functional control box or other DB user interaction. It is diffucult for me to understand why I cannot have a query [that you built for me, thank you] then within the report have something similar to a series of text boxes within the Detail area that include SELECT or EXPRESSIONS or something along those lines within the control source properties within each of the Text Boxes. [Part of why I keep asking this is when this DB was built almost 10 years ago a report was created doing that type of creation.]

I have given this example several times and even in the last thread I gave the entire specific list.

Here is a partial list in the format desired:
Label: Industry
TextLabel: Construction TextBoxControlSource: CountOf
TextLabel: Communication TextBoxControlSource: CountOf
TextLabel: Consulting TextBoxControlSource: CountOf
TextLabel: Education TextBoxControlSource: CountOf
TextLabel: Energy TextBoxControlSource: CountOf


Label: Titles
TextLabel: Advisor TextBoxControlSource: CountOf
TextLabel: CEO TextBoxControlSource: CountOf
TextLabel: CEO & President TextBoxControlSource: CountOf
TextLabel: General Counsel TextBoxControlSource: CountOf

Thank you

 

[jcm3us]

... Oh, by the way I inherited this DB as I am the only individual here that has any Access experience at all. I am doing the best that I can with the skills I have.

That is why I also would like to minimize any user interaction. I simply would like to have a Report to be self-generating based upon whatever data is in the DB at any given time.

 

[dhookom]

You should stop thinking about "labels" since you should actually be using "text boxes" to display Industry and Titles. Don't these values display on your report as created?

Try create a query like:

SELECT TheAttrib, TheValue, NumOf
FROM [YourUnionQueryName]
WHERE TheAttrib IN ("Industry","Titles");

Is this like what you want to display in your report? I assume you want to make this dynamic so your users can select any combination of TheAttrib values. If so, do you think you would like to have a multi-select list box of all the different values of TheAttrib. The user could click one or more TheAttrib values and run a report of only the selected TheAttrib values?

Part of why I keep asking this is when this DB was built almost 10 years ago a report was created doing that type of creation.

I think your report was created wrong. Based on what I think you are asking for, it shouldn't be that difficult.

Duane
Hook'D on Access
MS Access MVP

 

[jcm3us]

You ask if the values of Industry and Titles display... Yes, "TheAttrib" values display with a list of ALL available values for each [BusinessTitles, Degree, Ethnicity, Gender, Industry, Location] alphabetically.

I am simply selecting [or attempting to] specific criteria.


I have tried using your above query [with modifications]
SELECT TheAttrib, TheValue, NumOf
FROM qryReportData
WHERE TheAttrib IN ("Industry","Titles");

However, I am not sure where this is and will be used... [I tried it as the Record Source for the REPORT that resulted in only a single record row display (the first record row of the qryReportData "UnionQuery" previously created]

Then I tried it as the Control Source for TheValue which then prompts me to input some criteria (I guess this is what you are calling a Control Box) if I enter a value of [example] CEO, the resulting Report displays
Business Title
CEO NumOf value
CEO NumOf value
CEO NumOf value
CEO NumOf value
CEO NumOf value
Repeats 87 iterations (this is the total number of values for available Business Titles)

Degree
CEO NumOf value
CEO NumOf value
CEO NumOf value Repeats 17 iterations
This continues through all of the TheAttrib values each time repeating itself

Then I tried the query as the Control Source of NumOf results displayed [using the same example of CEO]

Business Title
List if TheValues NumOf value = CEO
List if TheValues NumOf value = CEO
Repeats for 87 iterations (this is the total number of values for available Business Titles)
Degree
List if TheValues NumOf value = CEO
Repeats 17 iterations
This again continues through all of the TheAttrib values each time repeating itself

This does NOT seem normal to me, at all

Yes, I am checking the display in both the Print View AND the Report View with the same results.

So, I guess I need to know where this query is being applied.

I understand [or at least I think I do] why it is that you are not wanting me to use the Label box portion when I create a TextBox field. I am just not used to performing or building a report in that fashion.

Thank you

 

[dhookom]

I tried it as the Record Source for the REPORT that resulted in only a single record row display (the first record row of the qryReportData "UnionQuery" previously created

Did you look at this in Print Preview?

What I provided was a possible Record Source for your report. I only wanted you to try it with a print preview to see if it provides a display similar to what you are asking for. You can't use a SELECT statement/query as a control source.

Do you understand that while in design view, you may see only a few sections (possibly Report Header & Footer, Page Header & Footer, Group Header, and Detail). In design view, there should only be about 3 text boxes and possibly no label controls?

I am simply selecting [or attempting to] specific criteria.

Can you explain what you mean by "specific criteria" with something like: "I would like my users to be able to select/limit a report to all the titles and/or all the locations and/or all the degrees etc"

Duane
Hook'D on Access
MS Access MVP

 

[jcm3us]

Quote (dhookom)
Did you look at this in Print Preview?
Yes, both Print and Report View

Quote (dhookom)
Can you explain what you mean by "specific criteria"...

The end result is for the user to simply select this Demographic report from the available REPORTs listed in the DB. It then displays in the Report View [typically by default of double-click]. Then to Print the viewed display.

So, therefore ALL criteria has already been selected using predetermined queries or by whatevery means WITHOUT any user entering any criteria. All the user has to do is DISPLAY then PRINT

All of the criteria was given in the previous thread.

Thank you [I hope that clarifies your questions]

 

[dhookom]

The print preview will show multiple copies/renderings of the detail section (one per record in the record source which I believe is "qryReportData"). Is this what you are experiencing or not?

You should only need one report and one query to display any or all of this information. Do you understand and/or agree with this statement?

I'm not going to look back at a previous thread. I'm too lazy. Just respond with something like: "I would like my users to be able to select/limit a report to all the titles and/or all the locations and/or all the degrees etc".

What do you expect a user to see on a screen/form in order to select records to print in your report?

Duane
Hook'D on Access
MS Access MVP

 

[jcm3us]

User should view in either Print or Report mode, then user should be able to view AND NOT: interact simply print...

TheAttrib = BusinessTitle
TheValue = Advisor/Consultant..... NumOf = Count where TheValue = [Advisor/Consultant]
TheValue = Attorney at Law..... NumOf = Count where TheValue = [Attorney at Law]
TheValue = CEO..... NumOf = Count where TheValue = CEO
TheValue = CEO & President..... NumOf = Count where TheValue = [SumOf CEO + President]
TheValue = CEO & General Counsel..... NumOf = Count where TheValue = [SumOf CEO + General Counsel]
TheValue = CEO & Chairman..... NumOf = Count where TheValue = [SumOf CEO + Chairman]
TheValue = CEO, President & Executive Chairman..... NumOf = Count where TheValue = [SumOf CEO + President + Executive Chairman]
TheValue = Chairman..... NumOf = Count where TheValue = [Chairman]
TheValue = Chairman & President..... NumOf = Count where TheValue = [SumOf Chairman + President]
TheValue = Chairman Emeritus..... NumOf = Count where TheValue = [Chairman Emeritus]
TheValue = Vice-Chairman..... NumOf = Count where TheValue = [Cice-Chairman]
TheValue = COO..... NumOf = Count where TheValue = [COO]
TheValue = Executive Director..... NumOf = Count where TheValue = [Executive Director]
TheValue = Director..... NumOf = Count where TheValue = [Director]
TheValue = Managing Director..... NumOf = Count where TheValue = [Managing Director]
TheValue = Vice President..... NumOf = Count where TheValue = [Vice President]
TheValue = Executive Vice President..... NumOf = Count where TheValue = [Executive Vice President]
TheValue = Executive Vice President & COO..... NumOf = Count where TheValue = [SumOf Executive Vice President + COO]
TheValue = Managing Partner..... NumOf = Count where TheValue = [Managing Partner]
TheValue = Partner..... NumOf = Count where TheValue = [Partner]
TheValue = Global Head..... NumOf = Count where TheValue = [Global]
TheValue = Owner..... NumOf = Count where TheValue = [Owner]
TheValue = President..... NumOf = Count where TheValue = [President]
TheValue = Principal..... NumOf = Count where TheValue = [Principal]
TheValue = Private Investments..... NumOf = Count where TheValue = [Private Investments]
TheValue = Retired..... NumOf = Count where TheValue = [Retired]
TheValue = Other..... NumOf = Count where TheValue = [Other]
otherwise there is a list of 87 values


TheAttrib = Degree
TheValue = UT..... NumOf = Count where TheValue = [UT]
TheValue = Non-UT..... NumOf = Count where TheValue <> UT]

TheAttrib = Ethnicity
TheValue = African American..... NumOf = Count where TheValue = [African American]
TheValue = Anglo..... NumOf = Count where TheValue = [Anglo]
TheValue = Hispanic..... NumOf = Count where TheValue = [Hisplanic]

TheAttrib = Gender
TheValue = Male..... NumOf = Count where TheValue = [Male]
TheValue = Female..... NumOf = Count where TheValue = [Female]

TheAttrib = Industry
TheValue = Communications/Technology..... NumOf = Count where TheValue = [Communications/Technology]
TheValue = Conglomerate..... NumOf = Count where TheValue = [Conglomerate]
TheValue = Construction..... NumOf = Count where TheValue = [Construction]
TheValue = Consulting..... NumOf = Count where TheValue = [Consulting]
TheValue = Consumer..... NumOf = Count where TheValue = [Consumer]
TheValue = Education..... NumOf = Count where TheValue = [Education]
TheValue = Energy..... NumOf = Count where TheValue = [Energy]
TheValue = Financial Services..... NumOf = Count where TheValue = [Financial Services]
TheValue = Law..... NumOf = Count where TheValue = [Law]
TheValue = Manufacturing..... NumOf = Count where TheValue = [Manufacturing]
TheValue = Ranch..... NumOf = Count where TheValue = [Ranch]
TheValue = Real Estate..... NumOf = Count where TheValue = [Real Estate]
TheValue = Transportation..... NumOf = Count where TheValue = [Transportation]
TheValue = Travel Services..... NumOf = Count where TheValue = [Travel Services]
TheValue = Venture Capitalism..... NumOf = Count where TheValue = [Venture Capitalism]
TheValue = Other/Misc/Retired..... NumOf = Count where TheValue = [Other/Misc/Retired]

TheAttrib = Location
TheValue = Austin..... NumOf = Count where TheValue = [Austin]
TheValue = Dallas/Ft Worth..... NumOf = Count where TheValue = [SumOf Dallas + Ft Worth]
TheValue = Houston..... NumOf = Count where TheValue = [Houston]
TheValue = San Antonio..... NumOf = Count where TheValue = [San Antonio]
TheValue = Other US Cities..... NumOf = Count where TheValue <> [Austin +Dallas + Ft Worth + Houston AND State <> Texas]
TheValue = Foreign Countries..... NumOf = Count where TheValue = [Foreign Countries]
TheValue = Other Texas Cities..... NumOf = Count where TheValue = [Austin +Dallas + Ft Worth + Houston AND State = Texas]
otherwise there is a list of 54 values

This is the total of All of the criteria desired for the ONE report needed

Thank you

 

[dhookom]

Do you understand that a criteria is a filtered group or subset of records? For instance your report might be filtered to the count of Genders and Industries. Do your users want this functionality or do they want to print every record in the report's record source?

What is wrong with the way the report previews/prints right now?

Duane
Hook'D on Access
MS Access MVP