Ranking Dates Issue: Different Sequence and Blank Cells in Data 1

[awl]

I have a table with 3619 records consisting of Individuals whom have donated [DateDonated]; some individuals have multiple donations, ranging from 1 to 9 donations, with 9 the most donations. I want to display a single record of the individual and his/her donation dates; listing from left to right their donation dates; being the first donation date as “DOV1”. Then I would process to do a crosstab query on this data.

First I created a Ranking Date query as in B below from query A. Query A and B each give me 3619 records, which is the correct number of Donations. No problem here. However in query C, I have three problems in the data displayed:
1) The first two columns are fine: [Name], and [TotalDateDonated]. However, the 3rd column starts with DOV0 and then DOV1, DOV2 … DOV9. Why does the 3rd column start with DOV0?
2) I was hoping that the 1st donation date would be DOV1. Instead, if a person has donated just once, it is listed as DOV9.
3) In many cases I have some records that have blank cells between DOV entries. IE: A person has donated 3 times: DOV6 is the 1st donation date entry, DOV7 is the 2nd donation date entry and then DOV9 is the 3rd donation entry; where DOV8 was skipped. Another example: a person has donated only once, and only DOV4 has that donation date entry and DOV5 through DOV9 are empty. Still one more; there are only 4 records utilizing Column DOV0, the 3rd column; with 6 and 5 donations for each record, there are empty cells under some of the DOV’s. in each of those 4 records.

What may seem to be one of the problems in looking at the records where there are blank cells and some DOV’s are skipped, similar individuals have the “same last 4 SSN’s. The [SSN] field in the table requires that 4 of an individual’s SSN and is formatted as @@@-@@-@@@@ with a default of 0. I deleted the WHERE (((a.SSN)<>"0000") in query B and still I have the same problems. (I hope my explanation has been clear).

Query Name A: qryRC2001 Ind Visits A
SELECT [tblRed Cross 2001].[DonorID#], [LastName] & "," & Space(1) & [FirstName] & Space(1) & [Initial] AS Name, [tblRed Cross 2001].SSN, [tblRed Cross 2001].OfficeSymbol, [tblRed Cross 2001].DeptService, [tblRed Cross 2001].Phone, [tblRed Cross 2001].DateDonated
FROM [tblRed Cross 2001];

Query Name B: qryRC2001 Ind Visits B
SELECT a.Name, a.SSN, a.OfficeSymbol, a.DeptService, a.Phone, a.DateDonated, a.[DonorID#], Count(b.DateDonated) AS DateRank
FROM [qryRC2001 Ind Visits A] AS a INNER JOIN [qryRC2001 Ind Visits A] AS b ON a.SSN = b.SSN
WHERE (((a.SSN)<>"0000") AND ((Format(.[DateDonated],"yyyymmdd") & "-" & .[DonorID#])>=Format([a].[DateDonated],"yyyymmdd") & "-" & [a].[DonorID#]))
GROUP BY a.Name, a.SSN, a.OfficeSymbol, a.DeptService, a.Phone, a.DateDonated, a.[DonorID#];

Query NameC: qryRC2001 Ind Visits C
TRANSFORM Min(c.DateDonated) AS DateDonated
SELECT c.Name, Count(c.DateDonated) AS [Total DateDonated]
FROM [qryRC2001 Ind Visits B] AS c
WHERE (((c.DateRank)<=10))
GROUP BY c.Name
PIVOT "DOV" & 10-c.DateRank;

Any suggestions would be helful! Curtis….

 

[dhookom]

First Query (caution-slow moving query):
SELECT [DonorID#], [LastName] & "," & Space(1) & [FirstName] & Space(1) & [Initial] AS Name, SSN, OfficeSymbol, DeptService, Phone, DateDonated,
DCount("*","[tblRed Cross 2001]","[DonorID#]=" & [DonorID#] & " AND DateDonated<=#" & DateDonated & "#") as VisitNum
FROM [tblRed Cross 2001];

Then create a crosstab from this query where VisitNum is the column heading and First Of DateDonated is the value.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[JonFer]

I would suggest using a self-join for the initial query instead of DCount() but otherwise what Duane said.

SELECT a.[DonorID#], a.[LastName] & "," & Space(1) & a.[FirstName] & Space(1) & a.[Initial] AS Name, a.SSN, a.OfficeSymbol, a.DeptService, a.Phone, a.DateDonated,
Count(b.DateDonated) as VisitNum
FROM [tblRed Cross 2001] as a Inner Join [tblRed Cross 2001] as b On a.[DonorID#]=b.[DonorID#]
Where b.DateDonated <= a.DateDonated;

If a donor has two records with the same donate date, you'll have some empty columns but all of the dates will still show up. This can be taken care of but since your table isn't normalized, it will take a few additional queries.

 

[dhookom]

JonFer,
Have you tried creating a crosstab from the results of a query like you suggested? I guess I could try it out myself. I have tried this with subqueries and it never works. That's why I have resorted to the ever-ugly DCount().

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[JonFer]

I, too, had problems trying to use a subquery instead of DCount for the ranking but was successful with a self-join. All three SHOULD work.

 

[awl]

Duane: Yes, it is a slow moving query; and slower if I build other queries. I copied your suggestion which gives me 3619 records and the last 2 columns: [DateDonate] and [VisitNum]. The column [VisitNum], has all 1’s in the 3619 records. I then created a crosstab and it gives me 3 columns as listed below. This is where I need help. I’m not sure how to manipulate the data so that I know which donations were a 1st Visit = DOV1, 2nd Visit = DOV2, and so on to the 9th Visit, from the data below.


TRANSFORM First([qryRCTest 1].DateDonated) AS FirstOfDateDonated
SELECT [qryRCTest 1].[DonorID#], First([qryRCTest 1].DateDonated) AS [Total Of DateDonated]
FROM [qryRCTest 1]
GROUP BY [qryRCTest 1].[DonorID#]
PIVOT [qryRCTest 1].VisitNum;

DonorID# Total of DateDonated 1
1 1/19/2001 1/19/2001
2 1/10/2001 1/10/2001
3 1/10/2001 1/10/2001

JonFer: I know the table is not normalized, it is how I inherited the data. I tred your suggestion and the error message displayed: “You tried to execute a query that does not include the specific expression ‘DonorID#’ as part of the aggregate function.”

Duane/JonFer: I tried to follow the discussion in thread# 701-837734 dtd May 10, 2004, which was my thread and JonFer patiently provided the steps in Ranking dates. If what I am looking for: displaying a single record with all of that individual’s donation dates, left to right, 1st Visit first and so on; does not apply here, then I’m hoping to get the DCount(), as the best solution for this particular situation to work.

 

[lespaul]

To solve this error:

“You tried to execute a query that does not include the specific expression ‘DonorID#’ as part of the aggregate function.”

you need to add DonorID to the Group By Clause of your query.

For instance, if you have:

SELECT FIELD1, SUM(FIELD2) FROM tableName GROUP BY FIELD1

if you need to add another field to the select clause, you need to add the same field to the group by:

SELECT FIELD1, FIELD3, SUM(FIELD2) FROM tableName GROUP BY FIELD1, FIELD3



Leslie

 

[dhookom]

awl,
Is DonorID# the primary key of [tblRed Cross 2001]? Or, are there multiple records in the table with the same DonorID#?

See if this query produces a variety of values in the VisitNum Column.

SELECT [DonorID#], [LastName] & "," & Space(1) & [FirstName] & Space(1) & [Initial] AS Name, SSN, OfficeSymbol, DeptService, Phone, DateDonated,
DCount("*","[tblRed Cross 2001]","[SSN]=" & [SSN] & " AND DateDonated<=#" & DateDonated & "#") as VisitNum
FROM [tblRed Cross 2001];

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[JonFer]

awl - I forgot to add the Group By clause in the self-join. If you fix that, it should work and be faster than the DCount option.

As Leslie explained, the Group By should include everything except the field with the Count() function. If there is a chance for different names or SSNs for the same DonorID, then you can use "Max(a.SSN) as SSN" (similarly for name or phone) in the Select and remove that field from the Group By.

 

[awl]

Sorry, I have been out of the office.

JonFer: Provided is the edited SQL for Query A (hopefully I understood Leslie’s suggestion), which gives me the same results as Duane’s except that the VisitNum is right aligned and Duane’s VisitNum is left aligned; there is still a 1 in all of the VisitNum column. Should there be a 1 for each record? Query B below is the crosstab and the most right column heading displays a 1, and the results repeats the donation date, 3619 records. From Query A how do I get a single record listing all of the individual’s donation dates?

A) SELECT a.[DonorID#], a.LastName & "," & Space(1) & a.FirstName & Space(1) & a.Initial AS Name, a.SSN, a.OfficeSymbol, a.DeptService, a.Phone, a.DateDonated, Count(b.DateDonated) AS VisitNum
FROM [tblRed Cross 2001] AS a INNER JOIN [tblRed Cross 2001] AS b ON a.[DonorID#] = b.[DonorID#]
WHERE (((a.DateDonated)<=[a].[DateDonated]))
GROUP BY a.[DonorID#], a.LastName & "," & Space(1) & a.FirstName & Space(1) & a.Initial, a.SSN, a.OfficeSymbol, a.DeptService, a.Phone, a.DateDonated;

B) TRANSFORM First([qryTest JonFer 1].DateDonated) AS FirstOfDateDonated
SELECT [qryTest JonFer 1].[DonorID#], [qryTest JonFer 1].Name, [qryTest JonFer 1].SSN, First([qryTest JonFer 1].DateDonated) AS [Total Of DateDonated]
FROM [qryTest JonFer 1]
GROUP BY [qryTest JonFer 1].[DonorID#], [qryTest JonFer 1].Name, [qryTest JonFer 1].SSN
PIVOT [qryTest JonFer 1].VisitNum;

Duane: 1) Yes, [DonorID#] is the primary key for [tblRed Cross 2001]; there are multiple individuals whom have donated several times, each donation date is of course a separate [DonorID#]. I know the db was not set up properly when I received it; there should be only 1748 individual records with 3619 donations (subrecords), instead I have 3619 individual records. 2) I tried your new query and I get #Error in each of the VisitNum column, plus dialog box: Data type mismatch in criteria expression.

 

[JonFer]

awl - The problem with QueryA as you saw is that DonorID represents a donation and not a person. Switching to SSN to track people should give you the correct results to use in the crosstab:

SELECT a.[DonorID#], a.[LastName] & "," & Space(1) & a.[FirstName] & Space(1) & a.[Initial] AS Name, a.SSN, a.OfficeSymbol, a.DeptService, a.Phone, a.DateDonated,
Count(b.DateDonated) as VisitNum
FROM [tblRed Cross 2001] as a Inner Join [tblRed Cross 2001] as b On a.[SSN]=b.[SSN]
Where b.DateDonated <= a.DateDonated
Group By a.[DonorID#], a.[LastName] & "," & Space(1) & a.[FirstName] & Space(1) & a.[Initial], a.SSN, a.OfficeSymbol, a.DeptService, a.Phone, a.DateDonated

In the crosstab, you can use Max([Name]) so it won't generate multiple rows if the name was entered differently for the same SSN. You might need to do the same for OfficeSymbol, DeptService, and Phone if they vary for an SSN. You can also add an index on SSN to speed up the first query.

 

[awl]

JonFer: I was working other projects, sorry for not responding earlier. I have used your suggestion and yes, the [VisitNum] displays the number of visits for that individual, which is what I can use. Now, if you don’t mind, can you provide a sample of the coding for the Max[Name] crosstab, for with the query below I am getting some records that have blank cells between the donations due to some individuals having the same Last 4 SSN. Pls include [OfficeSymbol], [DeptService], and [Phone] since there were some variations made. A question though with the query below: Why would the 1st of the [DOV] columns be titled [DOV0], which consists of 5 records? Thank you.

TRANSFORM Min(c.DateDonated) AS DateDonated
SELECT c.Name, Count(c.DateDonated) AS [Total DateDonated]
FROM [qryJonFer 8-23] AS c
WHERE (((c.VisitNum)<=10))