Mental Block on counting every entry...

[vangogh228]

I have a table of client info that has several fields of demographic information. Related tables have transation information. Each demographic field and transaction type field is limited to a list of entries, but some lists are quite long (25-50 entries).

I would like to develop a query that will allow me to put the information in a report so that each field is listed, and the various entries are shown line-by-line with a count of each. Example:

Gender
Male #
Female #

County
Stark #
Summit #
Wayne #
Tuscarawas #

Education
Grade 8 or less #
Grades 9-11 #
High school grad #
College #
Degree #


And this would continue on down the report... BUT, for some reason, I am stumped as to how to do this easily. I suppose I could write the query with same field in it over and over and set criteria for each entry, but I REALLY don't want to believe that's the easiest way.

HELP!!! Thanks!

 

[The_Doc_Man]

I am going to try to restate your problem. Read this and see if this is what you really wanted...

For every field of demographic info you track, you want to make a report showing the distributions of every value in each demographic field.

So you will obviously have 2 values for gender, and you want counts for each.

You will have a limited number of entries for education, with counts for each level.

You will have a limited number of counties, with counts for each.
-----
OK, if this is what you want, you can't have it in one query. But all is not lost. I will try to explain how to get there from here. If this is NOT what you wanted, then please try to explain it better.

To do what I fed back to you, you need one query for each demographic to be counted plus one more query to bring it all together. Plus, of course, the report. Only the last query is tricky. You can use the query design grid for counting individual demographics and you can use the report wizard for the final report you wanted.

The tedious part:

For each demographic, make a SELECT query that will contain two fields. In the query design grid, you add your data table to the upper area. Drag the demographic field down and drag down whatever you are using as the primary key. (It would be a real shame if you didn't have one, 'cause otherwise this might not work quite right.) Click on the ToolBar's Sigma key to turn this SELECT query into a totals query.

In the column for your chosen demographic, select "GroupBy" - and use "Count" for the column with your primary key. Save this query with a name that indicates which demographic you are counting. For example, this might lead you to create queries named qryCountGender, qryCountCounty, qryCountEducation among your other possibilities. This is tedious because you have to build one of these for EVERY demographic you wanted to count.

The tricky part:

Now you will build a UNION query that, unfortunately, cannot be done with a query grid. So you'll use SQL design view. It might look like this at some point during your development. (I'm assuming that your prime key field is called PK just to give it a name.)

SELECT "Gender" AS Factor, [Gender] AS FacVal, [CountOfPK] AS FacCount from qryCountGender
UNION
SELECT "County" AS Factor, [County] AS FacVal, [CountOfPK] AS FacCount from qryCountCounty
UNION
SELECT "Education" AS Factor, [Education] AS FacVal, [CountOfPK] AS FacCount from qryCountEducation
etc etc etc

Save this as the qryDemoCounts.

The last step:

Now use the query wizard to build a table that has breaks on Factor. Put FacVal and FacCount as elements of your detail area.

You are done! Save it. Savor it.

What this will leave out is any value in any demographic field for which the counts are zero. This is because if you have nothing to count, you have nothing to group, either. If this bothers you, there is a solution but it makes the individual queries MUCH uglier.

 

[vangogh228]

Doc_Man:

ULP!

Thanks so much.... and, yes, that is what I am looking for. I will give it a try this weekend and report back. yowee...

 

[vangogh228]

Doc_Man:

Well, it is 8:10am here, and I have worked on following your instructions since very early this morning. All I can say it "THANKS!" The report looks great and I know all will be VERY happy! I certainly am!!

I do have one follow-up question: Some of the fields I am reporting on are Yes/No fields. In the Union Query and the Report, the Yesses are coming in as "-1" and the Nos are coming in as "0". Is there a way of having the Query and Report show those entries with verbage rather than the numeric entries?


Tom

Thanks again!!

 

[David R]

In the query make a calculated field:

VerboseFieldName: IIF([FieldName]=-1,"True","False")

Then make those fields what your report relies on. You can also do this step in the report fields themselves, dunno if one is faster than the other.

 

[vangogh228]

David: THANKS! I will try that!!!

Actually, I have a second follow-up question: I have gotten an error message "Not a valid alias name" when I run the report based on the Union Query. Now, there are 23 fields separate queries and fields that are referenced by the Union Query. If I cut any two of the Union Select lines out of the query, the report runs OK, but with all 23, I get the error. Is there a limitation that I need to be aware of?

Thanks for any help!!

Tom

 

[Pat Hartman]

If the Union query is running fine by itself, I'm not sure what is causing the problem in the report. But, a work-around would be to create a make-table query that takes the union query recordset and turns it into a table. Then base the report on the table. I don't like this solution because temp tables just clutter a db and you'll have to make sure your code runs the make table prior to opening the report, but if the error is caused by a bug in the report writer, it may be your only option.

 

[The_Doc_Man]

In response to David R's suggestion, part II - no, you cannot do this in the report. Because in the report, you have a problem with only SOME of the fields being true/false. For the fields that are NOT true/false, This will be a wrong answer.

The IIF idea is correct but it has to go in the individual counting queries. In the "Group By" columns FOR THOSE DEMOGRAPHICS THAT ARE TRUE/FALSE ONLY, place the IIF rather than the actual field name for that counting query.

The ALIAS NAME is the thing in front of the colon in the query grid or the name behind the AS in SQL view. You may have chosen a name that is a keyword. (Or heck, I might have advised you to use one) but there is another possibility.

It comes to my mind is that you MIGHT be running into a string-length issue. Queries have limits on string size (darn it!) that could cause an ALIAS NAME to become truncated to something not valid in that context. And I recall that sometimes there is a (small) difference in the way a query runs when within a report vs. running independently as though it were a SELECT in datasheet mode.

Try going back through the individual counting queries to have the shortest possible names. Instead of qryCountEducation, try qryCntEdu. That kind of name shortening.

Also, (and again, this is tedious but not hard...) go through the queryies to shorten the field names.

Instead of using [Education] and just selecting "Group By" use Edu: [Education]. This of course means shortening the name in the UNION query. And instead of letting Access dictate the name as CountOfPK, use PKCnt: [PK] and the "Count" option. Go back and shorten these names in the UNION query, too.

If it works when you remove ANY two queries then it must be a string length problem. Nothing else would account for that.

Now, if THAT doesn't do it, there is one other way...

Split the UNION query into TWO UNION queries. About down the middle should be OK.

Now make the master union query that does a

SELECT Factor, FacVal, FacCount FROM UQ1
UNION
SELECT Factor, FacVal, FacCount FROM UQ2;

Base your report on THIS query.

If all else fails, do what Pat suggested - make a temporary table to hold the clutter. I might build a macro to pre-erase the table before populating it. It should be possible to delete the entire table and then create it again with a make-table query called by a Macro. Or just select the table and delete it from the Tables pane, then shift over to the Query pane to make the new table. Up to you as to how automated you want to get.

 

[vangogh228]

David, Pat, Doc:

Thanks SO much for the input. ALL OF YOU have been extremely helpful. I believe the 'character limit' theory has weight, since it didn't matter which two of my 23 lines I cut to make the union report run. It must be somewhere around 3000 characters. I like the idea of shortening the names, and I'm going to have to pay more attention to that in all subsequent databases. I'm afraid I've gotten into a very bad habit of making the names descriptive and verbose.

In the end, the option I chose was to break out the union query into two: one that held all the Yes/No fields and one that held all the others. In my underlying queries of the Yes/No fields, I put in a criteria of "YES" so that I would not get a No line in the report. That way, I simply did not add any grouping levels to the report and all I got was the field name and the number of Yesses. In the other report, I got the field name, the individual entries from the drop downs (if there were any records using that entry) and the number of records.

Fellas: Again, thank you SO much. And Doc... I have printed out your posts from this thread and put them in my Hall of Fame file.

Tom

 

[The_Doc_Man]

Tom, got your e-mail of thanks. You are quite welcome and I'm glad to help. I regret that I cannot reply to you through mail. I work at a Dept. of Defense site. Our mail contact must be very strictly limited. (We ARE at war, you know...) Believe it or not, the forum ISN'T a problem whereas E-mail IS a problem. Go figure. But it has to do with incoming vs. outgoing initiation of contact. Also, our security people scan the e-mail but don't bother with forums nearly so much as long as we don't download.

Anyway, I acknowledge your letter of thanks.

regards,
Rich "The_Doc_Man"

(not to be confused with the poster who signs himself Rich.)