Multiple Queries into a single report 1

[SXSteve]

Hi, hope someone can help here as I'm really new to access and the report functions.

I have a field in a table called 'Type' - this field contains information about types of projects in the table.

Some examples of the type field are:

House Extension
Loft Conversion
Windows
Loft Conversion & House Extension
Conservatory
Porch
Porch & Conservatory

As you can see - sometimes the type is more than one item.

I am trying to create a report (with not much luck) that will break down the types into individual projects and count them - so for the above list I would be after:
Conservatory - 2
House Extension - 2
Loft Conversion - 2
Porch - 2
Windows - 1

I thought the best way would be to make individual queries for each type. And then make a report and get the data from the various queries into the detail field using the count function in a text box with the criteria
=Count([Extensionquery]![TYPE]) for House Extension
=Count([Windowsquery]![TYPE])for windows etc
But I just get #error when I try this.

Anyone have any ideas on how to get around this - or even a better way to do it?

Thanks in advance

Steve

 

[SXSteve]

Seems I may have a workaround - thought i'd put it up here incase anyone else ever has the same sort of query.

Instead of using the count function - I used the dcount function like this:

=dcount("*" , "Extensionquery")
=dcount("*" , "Windowsquery")

And it is thankfully returning the values I need.

I know this is probably not the best way to do this (I'd imagine there would be a way to query and count just from the report) But for now i'm happy to just have a solution.

Steve

 

[dhookom]

You shouldn't really be storing multiple types in a single field. However you can create a fairly efficient solution by creating a table of all unique types:
[tt][blue]
tblProjectTypes
ProjType text PK values like Conservatory, House Extension,...
[/blue][/tt]

Then add this table to a query containing your table with the multiple value field. Add the ProjType field to the query fields/grid. Set the criteria under your [Type] field to:
[tt][blue]
Like "*" & [ProjType] & "*"
[/blue][/tt]

This will create multiple records for each Type with multiple values. You can then easily count the number of types in a totals query.



Duane
Hook'D on Access
MS Access MVP

 

[jcm3us]

additional question regarding the concepts of this thread...

I have a table that includes several fields that originate from data of other tables

tblPersonalData
field1 = Industry
field2 = BusinessTitle

tblIndustry
fields = list of various industries

tblBusinessTitle
fields = list of various titles [CEO, Pres, VP...]

If I desired to build a single report that looked like:

Industry
Construction - 2
Law - 15
Ranching - 28
Management - 2

Business Titles
CEO - 7
President - 12
Vice President - 22
COO - 2
Manager - 3

 

[jcm3us]

additional question regarding the concepts of this thread...

I have a table that includes several fields that originate from data of other tables

tblPersonalData
field1 = Industry
field2 = BusinessTitle

tblIndustry
fields = list of various industries

tblBusinessTitle
fields = list of various titles [CEO, Pres, VP...]

If I desired to build a single report that looked like:

Industry
Construction - 2
Law - 15
Ranching - 28
Management - 2

Business Titles
CEO - 7
President - 12
Vice President - 22
COO - 2
Manager - 3

Additional note:
[There may be instances of the following also]
CEO & President - 2
COO & Vice President

 

[dhookom]

Do you actually have a field named "fields"?
I would create a query for Business Titles and a query for Industries. You can then either combine them into a union query or use one or more subreports.

Duane
Hook'D on Access
MS Access MVP

 

[jcm3us]

Thank you for responding.

No. there is NOT a field named field1 or field2 I simply used that as a name here to describe.

They are data fields within the table tblPersonalData.
They are selected using a drop list on an input form that allows the user to select from a list similar to what I listed above. That selection gets stored in the table tblPersonalData.

Wouldn't a query for each of Business Titles and Industries be a redundant duplication of the tables?

I am not familiar with union queries [their function or purpose and how to use them effectively]


Thanks

 

[dhookom]

I'm not sure where you got the idea "a query for each of Business Titles". My suggestion was to create "a query for Business Titles and a query for Industries". That's two queries total.

If you want "to build a single report" then you could possibly use a union query to combine the two queries I suggested.

Duane
Hook'D on Access
MS Access MVP

 

[jcm3us]

Thank you for the response

No, there is NOT any field1 OR field2 within any created table that I am working with. That was simply a label that I used here to illustrate my issue.

I am using a form to input certain data into tables. I.e. the tblIndustry and tblBusinessTitle are tables that are used to select specific selections on the input form then stored in the table tblPersonalData.

I am now trying to create a single report that can count [or display the counts] of what is selected.

Thanks

 

[jcm3us]

Hmm. This crazy thread does not display my submitted posts upon pressing submit...

 

[dhookom]

Try create a union query with syntax like the following. You will need to change some "simple labels" to the actual field names.

SELECT "Business" as Attr,Field1 as TheValue, Count(*) As NumOf
FROM tblPersonalData
GROUP BY Field1
UNION ALL
SELECT "Industry",Field2, Count(*)
FROM tblPersonalData
GROUP BY Field2;

Duane
Hook'D on Access
MS Access MVP

 

[jcm3us]

Ok, [some semantics and grammar may be getting in the way]

But hopefully if I understand you correctly

Create a query for each "Business" type [there is a totel of 24 different business types] where each query counts each type.
qryBus01 = where CountOfCEO = 7
qryBus02 = where CountOfPresident = 12
qryBus03 = where CountOfVP = 22
qryBus04 = where CountOfCOO = 2
qryBus05 = where CountOfManager = 3

join using UNION QUERY for Business

then do the same for Industry [there is a total of 16 different industry types]

 

[dhookom]

No, just try create the one query I suggested. I have never suggested create one per business or industry. If you have stored multiple values in a single field, you might need a total of three queries.

If you continue to be confused, please provide your actual significant table and field names. Some sample records and desired output might end all confusion.

Duane
Hook'D on Access
MS Access MVP

 

[jcm3us]

Hmm, well having done that: [If I understand what I am doing using your union query, I am selecting a count of all CEO's in the Construction Industry] Is that right??

SELECT "BusinessType" as CEO, Count(*) As NumOf
FROM tblPersonalData
GROUP BY CEO
UNION ALL
SELECT "Industry", Construction, Count(*)
FROM tblPersonalData
GROUP BY Construction;

[Although I am not sure why I am joining two criteria (CEO & Construction) in my example into one query, but okay I am going along with what you are instructing] However, I receive a message box stating "The number of columns in the two selected tables or queries of a union query do not match."



If I "separate" the query into independent queries:

SELECT "Business" as CEO, Count(*)
FROM tblPersonalData

and a second query of

SELECT "Industry" as Construction, Count(*)
FROM tblPersonalData

There are count values that are returned for each.

What is the function of "As NumOf"? [it returns the same value with and without this statement...]

 

[dhookom]

Ok, you are confused so please respond to my request

If you continue to be confused, please provide your actual significant table and field names. Some sample records and desired output might end all confusion.

If you count the commas in each SELECT of my suggested union query, do they match yours? Do each of your SELECTs in your union query have the same number of commas/columns?

Have you committed spreadsheet by having columns with names like "CEO", "President", and "Vice President"?

Duane
Hook'D on Access
MS Access MVP

 

[jcm3us]

Ok, I understand and got the comma issue. [I wasn't sure what and where to edit my info from that of what you instructed]

Ultimately I wish to have one single report that will list the following

Industry [this is a table that is listed and selected within a form using a drop list]
Communications - 7 [total count from DB records]
Conglomerate - 2 [total count from DB records]
Construction - 3 [total count from DB records]
Consulting - 12 [...]
etc...[there are 16 total industry types]

BusinessTitle [I was using Business earlier in my descriptions]
Advisor - 3 [...]
Attorney - 5 [...]
CEO - 4 [...]
CEO & President - 9 [this is a count of an individual that holds 2 titles]
CEO & General Counsel - [similar to above]
Chairman - 12 [...]
Owner - 21 [...]
etc...[there are 27 total BusinessTitle types]

Location
Austin - 22 [...]
Dallas/Ft Worth - 34 [this is a sum of Dallas + Ft Worth]
Houston - 29 [...]
[there are 7 different Locations to list

UndergraduateDeg
UT - 89 [...]
Non-UT - 14 [...]

GraduateDeg
UT - 44 [...]
Non-UT - 12 [...]

I am not sure I understand your question:
Have you committed spreadsheet by having columns with names like "CEO", "President", and "Vice President"?

[What do you mean by a committed spreadsheet?]


Is there a limit to a UNION QUERY?? [of how many fields can be grouped]
Is or will this query that I am building going to be the "Control Source" for the REPORT??
How would the counts for each individual area above be created and displayed?? [using some form of a query I would guess, but what might they look like?]


Thanks for all of your assistance.

 

[dhookom]

I don't think you have answered my question

If you continue to be confused, please provide your actual significant table and field names. Some sample records and desired output might end all confusion.[/code]
I would expect an answer like:
[tt][blue]
tblPersonalData
===============
NameOf1stField
NameOf2ndField
NameOf3rdField
....

tblIndustries
=============
NameOf1stField
NameOf2ndField
[/blue][/tt]
If the names of the fields aren't self-documenting, you might want to provide sample values to the right or a very brief description.

Committing spreadsheet would be having multiple field names like "CEO" or other position names. If you can just provide the significant table and field names, I think we could get this wrapped up rather quickly.

Duane
Hook'D on Access
MS Access MVP

 

[jcm3us]

You are correct.. I have not answered your question

Brcause, I am not sure I understand your question:
Have you committed spreadsheet by having columns with names like "CEO", "President", and "Vice President"?

[What do you mean by a committed spreadsheet?]


I am submitting a few images to hopefully give you an idea of what I have and am trying to accomplish

 

[jcm3us]

I hope that these will give you an idea of what I am trying to accomplish.

What you describe is similar to what I describe just WITHOUT any count values of each field:

tblPersonalData need to display count values
===============
NameOf1stField Count of 1stField
NameOf2ndField Count of 2ndField
NameOf3rdField Count of 3rdField
....

tblIndustries
=============
NameOf1stField Count of 1stField
NameOf2ndField Count of 2ndField

Thanks for your assistance

 

[dhookom]

We can't see images on your local hard drive.

So, you have fields named "NameOf1stField", "NameOf2ndField", "NameOf3rdField"?

I can't believe these are your actual field names.

It's not that difficult to go to the design view of your tables and type the actual table and field names into a reply. Then copy some actual records in your reply also.

Duane
Hook'D on Access
MS Access MVP