Unique sort and group report

[State]

I am trying to generate a query & then the report that counts on one of two groupings of data.

I have one table, tblSurveyResults, which contains the following info regarding a person's primary and secondary ownership goals:

Primary Own. Goal Secondary Own. Goal Age

House Boat 25
House Boat 20
Car Car 16
Boat ATV 33
RV Boat 50
Car Boat 35
Car Car 18
Car Car 22
House Car 23

Also, the table, tblPrimary, contains a list of primary ownership goals (primary key id & name).
Tne table, tblSecondary, contains a list of secondary ownership goals (primary key id & name). This table also contains a foreign key link to tblPrimary (join on tblPrimary's primary key id).

The content in both these tables can change in that a user may add new primary and secondary ownership goals to the tblPrimary and tblSecondary tables via a form.

There is no relationship between tblSurveyResults and the tblPrimary and tblSecondary tables, except that the
tblPrimary and tblSecondary lists (Primary and Secondary Ownership Goals) are presented to the user on a form for selection. Their selections then populate the tblSurveyResults' Primary and Secondary Ownership Goal fields.

I wish to generate a query & report that displays:

-unique primary goals (no duplicates),
-each primary goals' group of related secondary goals,
-a count, by age range, of each secondary goal within each primary goal's group.
-totals, by primary & secondary goals, and age range.

Based on the data above, an example of the desired report is as follows:

PRIMARY Age Range
SECONDARY

Car 15-20 21-30 31-40 40+ Total
Car 2 1 0 0 3
Boat 0 0 1 0 1
Boat
ATV 0 0 1 0 1
House
Boat 0 2 0 0 2
Car 0 1 0 0 1
RV
Boat 0 0 0 1 1

Grand Total: 2 4 2 1 9


Thank you in advance for any assistance provided.

 

[Pat Hartman]

You need a union query to gather the data into a single recordset then a crosstab query to pivot it.

Select PrimaryGoal as Goal, IIf(GoalAge < 15, "<15", IIf(GoalAge >= 15 And GoalAge < 21, "15-20", IIf(GoalAge >= 21 And GoalAge < 31, "21-30", IIf(GoalAge >= 31 And GoalAge < 41,"31-40","40 +")))) As AgeGroup, "x" As DummyField
From YourTable
Union Select SecondaryGoal as Goal, IIf(GoalAge < 15, "<15", IIf(GoalAge >= 15 And GoalAge < 21, "15-20", IIf(GoalAge >= 21 And GoalAge < 31, "21-30", IIf(GoalAge >= 31 And GoalAge < 41,"31-40","40 +")))) As AgeGroup, "x" As DummyField
From YourTable;

The DummyField is there because the Crosstab wizard requires three columns to work.

Then create a crosstab based on the Union query with Goal as the RowHeading, AgeGroup as the ColumnHeading, and use Count for the DummyField.

If you don't like complicated IIf() functions in queries (and I don't. They are hard to read and edit), you can create a function to determine the age group and use that instead.

Public Function CalcAgeGroup(InputAge As Integer) As String
Select Case InputAge
    Case < 15
        "< 15"
    Case >= 15 And < 21
        "15-20"
    Case >= 21 And < 31
        "21-30"
    Case >= And < 41
        "31-40"
    Case Else
        "40 +"
End Select
End Function

Then the union query would be:

Select PrimaryGoal as Goal, CalcAgeGroup(GoalAge) As AgeGroup, "x" As DummyField
From YourTable
Union Select SecondaryGoal as Goal, CalcAgeGroup(GoalAge) As AgeGroup, "x" As DummyField
From YourTable;

 

[State]

Pat:

Thank you; that was most helpful. However, the generated query is counting the primary as well as the secondary items. The end result I would like to have is to show a report displaying the primary items, and then grouping the secondary items under each primary item, counting only the secondary items.

-Paul

 

[Pat Hartman]

In that case you don't need the union query. Just base the crosstab off the underlying table.

For the crosstab, use both PrimaryGoal and SecondaryGoal as RowHeadings.

 

[State]

Thanks, Pat! Now that I look at it, this was much simpler than I originally thought. Thanks again for putting me on the right track.

-Paul