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

 

[jcm3us]

So the "attachment" function here does not work as such, hmm

tblPersonalData (there are 46 [FieldNames] in this table)
===============
SSN[Text]PrimaryKey
Title[Text]
FirstName[Text]
LastName[Text]
ACTitle[Text]
....

tblIndustries [only these 2 FieldNames in this table]
=============
IndustryID[Autonumber]PrimaryKey
Industry[Text]

tblBusinessData (there are 20 FieldNames in this table)
=============
SSN[Text]PrimaryKey
Industry[Text]... field displays on form to select values
Company[Text]
BusinessTitle[Text]
...

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

BusinessTitle VALUES [example of counts in DB]
Advisor - [3]
Attorney - [5]
CEO - [4]
CEO & President - [9]
CEO & General Counsel - [4]
Chairman - [12]
Owner - [21]
etc...[there are 27 total BusinessTitle values]

Location VALUES [example of counts in DB]
Austin - [22]
Dallas/Ft Worth - [34]
Houston - [29]
etc...
[seeking 7 different Locations values for report]

tblDegree
SSN[Text]
Degree[Text]
Year[Text]
Major[Text]
University[Text]

UndergraduateDeg VALUE [ex. counts in tblDegree.Degree AND tblDegree.University]
UT - [89]
Non-UT - [14]

GraduateDeg VALUES [ex. counts in tblDegree.Degree AND tblDegree.University]
UT - [44]
Non-UT - [12]

 

[dhookom]

Doesn't tblPersonalData have fields storing values from tblIndustries and tblBusinessData?

Aren't you looking for counts of Industries and Business Title values in tblPersonalData? If so, you haven't told us what fields in tblPersonalData to look at.

Duane
Hook'D on Access
MS Access MVP

 

[jcm3us]

Actually tblBusinessData stores values from tblIndustries

the two tables "tblBusinessData" AND "tblPersonalData" have information that will be reported on the single page report that I am ultimately trying to display[report] for demographic purposes

Industry [field within tblBusiness] specific VALUES below
Communications/Technology [CountOf]
Conglomerate [CountOf]
Construction [CountOf]
Consulting [CountOf]
Consumer (Non-cyclical) [CountOf]
Education [CountOf]
Energy [CountOf]
Financial Services [CountOf]
Law [CountOf]
Manufacturing [CountOf]
Ranch [CountOf]
Real Estate [CountOf]
Transportation [CountOf]
Travel Services [CountOf]
Venture Capitalism [CountOf]
Other/Misc/Retired [CountOf]

Titles [field within tblBusinessData] specific VALUES below
Advisor/Consultant [CountOf]
Attorney at Law [CountOf]
CEO [CountOf] [CountOf]
CEO & President [CountOf]
CEO & General Counsel [CountOf]
CEO & Chairman [CountOf]
CEO, President & Executive Chairman [CountOf]
Chairman [CountOf]
Chairman & President [CountOf]
Chairman Emeritus [CountOf]
Vice-Chairman [CountOf]
COO [CountOf]
Executive Director [CountOf]
Director [CountOf]
Managing Director [CountOf]
Vice President [CountOf]
Executive Vice President [CountOf]
Executive Vice President & COO [CountOf]
Managing Partner [CountOf]
Partner [CountOf]
Global Head [CountOf]
Owner [CountOf]
President [CountOf]
Principal [CountOf]
Private Investments [CountOf]
Retired [CountOf]
Other [CountOf]

Location [field within tblPersonalData] VALUES below
Austin [CountOf]
Dallas/Ft Worth [CountOf Sum]
Houston [CountOf]
San Antonio [CountOf]
Other US Cities [CountOf]
Foreign Countries [CountOf]
Other Texas Cities [CountOf]

Undergraduate Degree [field within tblPersonalData] VALUES
UT [CountOf]
Non UT [CountOf]

Graduate Degree [field within tblPersonalData] VALUES below
UT [CountOf]
Non UT [CountOf]

Gender [field within tblPersonalData] VALUES below
Male [CountOf]
Female [CountOf]

Ethnicity [field within tblPersonalData] VALUES below
African American [CountOf]
Anglo [CountOf]
Hispanic [CountOf]

thanks for all the assistance

 

[dhookom]

What exact table(s) and fields contain the values you want to count. I really don't care to see all of the actual values in the lookup tables. I still don't understand your tables, fields, and relationships. This is ultra-frustrating. I'm not sure what you don't understand about providing simple table structures (field names).

If you want the count of locations, the following should provide the values.

SELECT Location, Count(*) as NumOf
FROM tblPersonalData
GROUP BY Location;

Duane
Hook'D on Access
MS Access MVP

 

[jcm3us]

This is also frustrating to myself as I have been giving you the field names and table names all along.

Let's try this a different manner...
I have two main tables tblBusinessData and tblPersonalData that info is being stored. The tables have a relationship link of SSN and that same FN is also a Primary key within each individual table. Each table has fields that I am trying to report demographics.

<table>.<fieldname>
tblBusinessData.industry
tblBusinessData.titles
tblPersonalData.location
tblPersonalData.degree
tblPersonalData.gender
tblPersonalData.ethnicity

You had asked for data values previously, that is why I listed all of the desired values above. Using the listed values, I am seeking each value to return a CountOf

Thank for your patient assistance. I am and have been attempting to explain all of what you ask in terms that you ask, however not everyone is a "programmer" and knows all of the terminology of a programmer.

 

[MajP]

take a look at FAQ700-6905

This will document all tables and relationships and provide Duane the details to allow him to help you.

 

[jcm3us]

the queriy you just provided:

SELECT Location, Count(*) as NumOf
FROM tblPersonalData
GROUP BY Location;

returns a "Enter Parameter Value" box...

although that might work for a single value from a query however, I don't see how that would do any good for a report that has multiple data values...

 

[dhookom]

My query suggestion of:

SELECT Location, Count(*) as NumOf
FROM tblPersonalData
GROUP BY Location;

should have worked since you have a stated your structure includes
[tt][blue]
tblPersonalData.location
[/blue][/tt]
It was only the first step in attempting to get the structure of your tblPersonalData since it took you 12 posts and 20 days to provide what looks like actual table and field names.

I can't imagine why you would be getting an "Enter parameter" if your table and field names match your SQL statement. If this is actual:
[tt][blue]
tblBusinessData.industry
tblBusinessData.titles
tblPersonalData.location
tblPersonalData.degree[/blue][/tt]
Then the following sql should work in a query:

SELECT "Industry" as TheAttrib, [Industry] as TheValue, Count(*) as NumOF
tblBusinessData
GROUP BY [Industry]
UNION ALL
SELECT "titles", [titles], Count(*)
tblBusinessData
GROUP BY [titles]
UNION ALL
SELECT "location", [location], Count(*)
tblBusinessData
GROUP BY [location]
UNION ALL
SELECT "degree", [degree], Count(*)
tblBusinessData
GROUP BY [degree];

If this doesn't work, please provide the actual error message(s). If you can post a screen shot or send me a compacted and zipped mdb file I would be willing to look at it to complete this thread.

Duane
Hook'D on Access
MS Access MVP

 

[jcm3us]

Thank you for all of you assistance, yet if you look back in my initial post I stated the tables and several not all of the fields in question. Yes over time I believe that the end result has been reached... using your query last posted [edited to include all of the field names etc and more importantly the missing FROM portion of the section of the query.

Edited for content results in

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];

Based upon what I am viewing, this does give me a possible result that I am presuming I use as the "Record Source" in my report and subsequently I can use to count the needed VALUES.

One last thing... what might the count of "Control Source" expressions look like within my report?

I have tried with no luck
= Count(TheValue) WHERE TheValue ="Advisor*";

 

[dhookom]

For control sources, you should just use:
Control Source: TheAttrib
Control Source: TheValue
Control Source: NumOf
I would have expected you to try this. If it didn't work, you need to tell us what didn't work about it.


Duane
Hook'D on Access
MS Access MVP