multiple sorts on different variables within a report

[Wease]

I'm trying to create a report that sorts on three different date fields by month. I'm creating a membership database where members could pay for their membership, campsite & building permits at three different times. The database keeps three different date fields for each payment type. I want to track all types of payments by month. When I create an access report, It seems I can only group by one of the date variables. For example, the report is sorted/grouped by membership renewal date; thus any member who renewed in January has their campsite & building permit payments included in the January totals even though those payments were made in other months. Is is possible to group a report by month, but on three different date variables?

 

[Remou]

You could try a Union query that selects member details and date field for each of the date fields and then base your report on the query.

 

[Wease]

I'm not sure I follow you. I think I was going down the same road yesterday but couldn't make it work. I was trying to rename the three different date fields to a single common name (i.e. Date) in a union query, but the query won't let me use the same alias for the three different date fields.

 

[Remou]

Something like this:

[tt]SELECT CustID, MembershipDate As SortDate FROM tblTable
UNION ALL
SELECT CustID, BuildingDate As SortDate FROM tblTable
UNION ALL
SELECT CustID, PermitDate As SortDate FROM tblTable[/tt]

Do not use Date as the name for anything, it is a reserved word.

 

[Wease]

Remou, thanks for your help !!! I'm so close I can taste it. I have one final question. My union query now shows one date field, but because I've brought in all the variables such as membership, permit & campsite; I can no longer tell which date actually applies to which payment. I want to create a variable in each select statement to retain what the date represents. For instance, I want to create a vairable called "What". If the date came from the first select statement, I want "What" = M, If the date came from the second select statement, I want "What" = C, and If the date came from the third select statement, I want "What" = B. Can you help me with the SQL to do this?

 

[Remou]

You can create an imaginary field:
[tt]SELECT CustID, MembershipDate As SortDate, "A" As What FROM tblTable
UNION ALL
SELECT CustID, BuildingDate As SortDate, "B" As What FROM tblTable
UNION ALL
SELECT CustID, PermitDate As SortDate, "C" As What FROM tblTable[/tt]

 

[Wease]

O.K., so now my query has a single date field and a field to tell me which payment type. On to the next problem . . . how do I count the number of unique values in a query output column? In my query I have two columsn, one named "Type" and the other named "SortDate". Type contains 5 uniqe values; Single, Family, Campsite, Structure1, Structure2. I'm trying to build a report that shows me the number of unique "Types" by month. I was thinking of using the DCount function, but apparently I'm not using the right syntax. Can someone offer some help?

 

[dglienna]

You can use DISTINCT to find unique records, and then a COUNT(*) to return occurrences.

-David
2006 & 2007 Microsoft Most Valuable Professional (MVP)
2006 Dell Certified System Professional (CSP)

 

[Remou]

Is Unique the word you want? I do not think that a person will have paid a specific fee more than once, or is this possible? Do you want (?):

Report
Single
Alice
Bob
Family
Carmen
Deepak
Campsite
Bob
Carmen
Deepak
Structure1
Alice
Structure2
Bob
Carmen

If so, Count in the group footer should suit.

 

[Wease]

I apologize, unique is probably the incorrect term here. I want a count of how many times Campsite appears for the month, a count of how many times membership appears for the month, etc.

January

Memberships Campsites Permits
22 15 10

February

Memberships Campsites Permits
35 10 2

I was thinking I could build this calculation within the report that reads the query, but I'm not sure of the code needed within the record source.

 

[Remou]

Ok, you need additional queries, I think.

[tt]Select Distinct Campsite, Month From UnionQueryName[/tt]

A DCount on such a query should give you the answer you want.

 

[Wease]

O.K. I created another query using the distinct function and it correctly gives me distinct types by month.

However, If I use the Dcount function in my report, it's counting all the unique types for all months from the query. The report itself already sorts the occurrence of types by month, so what I really want to do is run the Dcount against what the report has sorted, not against the entire query.

For instance, If I create a text box in my report, and set the record source to =Count([Type]) it will give me the count of all types for that month in which the report sorted. I'm thinking that I should be able to use this same logic and say something like =Count([Type]) where [Type] = "Campsite"

If I use DCount(expr, domain, [criteria]), I can only seem to set the domain to the query, when I really want the domain to be the subset created when the report sorted by month already.

 

[PHV]

Perhaps this ?
=Sum(IIf([Type] = "Campsite", 1, 0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886