Settin up Query to Calculate # of Days 1

[awl]

My goal is to be able to calculate # of days between several dates. I know how to calculate # days, however it is setting up the query so that the [DateOfExam]s are displayed from left to right on the same row as the matching [SSN] and [Name]. I have a query that found duplicate records. My column headings are: [SSN], [Name] and [DateOfExam]. There are several records that have the same SSN and Name, and of those records, there are some [DateOfExam]s that are duplicates and some not. I want to display in my next query for the report, the three columns with the [DateOfExam]s displayed from left to right with the calculated # of days between the dates; thus any less than 6 months presents a possible error in the database. Any help would be greatly appreciated. Thanks…… Curtis….

 

[JonFer]

It would help if you post the SQL for the queries you already have, list a few sample rows, and show an example of what you want to see in the output from your final query.

 

[awl]

JonFer: Thanks for responding. In query B, I have shown the SQL and the results. In query C, I have shown what I want Query C to display. Query C would use the results of Query B. I don’t know how to set up Query C. [DOE1] would be the Date of Exam 1. Thanks for your help. Curtis…

B: QryDIR1 Duplicates B:
SELECT [qryDIR1 Duplicates tblMFS M D List A].SSN, [qryDIR1 Duplicates tblMFS M D List A].Name, [qryDIR1 Duplicates tblMFS M D List A].DateOfExam
FROM [qryDIR1 Duplicates tblMFS M D List A]
WHERE ((([qryDIR1 Duplicates tblMFS M D List A].SSN) In (SELECT [SSN] FROM [qryDIR1 Duplicates tblMFS M D List A] As Tmp GROUP BY [SSN] HAVING Count(*)>1 ) And ([qryDIR1 Duplicates tblMFS M D List A].SSN)<>"000-00-0000"))
ORDER BY [qryDIR1 Duplicates tblMFS M D List A].SSN, [qryDIR1 Duplicates tblMFS M D List A].Name;

[SSN] [Name] [DateOfExam]
111-11-1111 Client 1 2/14/2003
111-11-1111 Client 1 6/6/2003
111-11-1111 Client 1 8/1/2003
111-11-1111 Client 1 8/1/2003
123-12-1234 Client 2 3/4/2003
123-12-1234 Client 2 4/30/2003
123-12-1234 Client 2 7/23/2003
999-99-9999 Client 3 7/25/2003
999-99-9999 Client 3 8/19/2003
999-99-9999 Client 3 11/4/2003

C: QryDIR1 Duplicates C:
[SSN] [Name] [DOE1] [DOE2] [DOE3]
111-11-1111 Client 1 2/14/2003 6/6/2003 8/1/2003
123-12-1234 Client 2 3/4/2003 4/30/2003 7/23/2003
999-99-9999 Client 3 7/25/2003 8/19/2003 11/4/2003

[DOE4] [Diff1-2] [Diff2-3] [Diff3-4]
9/26/2003 112 56 0
57 84
25 77

 

[JonFer]

Do you want to see all of a person's DOEs if they have just one situation with less than 6 months between two DOEs?

 

[awl]

Sorry, an error: In my example for Client 1 under Query C, the last [DOE4] should be 8/1/2003 and not 9/26/2003 which will then give me the "duplicate" record I'm looking for and the [Diff3-4] is still correct as 0. Sorry if my example confused you. Curtis...

 

[JonFer]

In the sample records, the 8/1/2003 records for the first SSN are duplicates so there is no way to distinguish them and report both dates in a crosstab. Are there additional fields in the table that make the records unique? Can you add an autonumber field to the table? Does this situation really exist where someone took an exam twice on the same day? If it exists, do you need to see it or is it an error?

I also posted a question above if you want all DOEs for a person or just those closer than 6 months to the next DOE.

 

[awl]

I'jm sorry for overlooking your earlier post. a.) I do want to see all of a person's DOEs and then I can also select only those that are less than 6 months. b.) The duplicate sample provided is a true example. Situation: The a staff member will open the database, enter the 3 required items, [Name], [SSN] and [DOE] on the form. Something comes up where the staff may have to close out and then that individual either may have not remmeber the correct spelling of the name or not sure if the record is in there, and they rush to start a new record, thus a duplicate. I have individuals with different levels of understanding databases. c.) The [MFSID#] is the unique autonumber for each record/exam. When the database was created, there is no subdatasheet. Each client's record is in one table and if the same client comes in later for another exam, a new record is created. A client may come in as a follow-up where the original Date of Exam record will be used for additional data entry. Thank you.

 

[JonFer]

You'll have to do this in a few steps. First create this query which will select each person and rank the dates (1=most recent). It compares on the date for the ranking and uses the [MFSID#] field to break date "ties." Check out the results and make sure the ranking is correct.

SELECT a.SSN, a.Name, a.DateOfExam, a.[MFSID#], Count(b.DateOfExam) as DateRank
FROM [qryDIR1 Duplicates tblMFS M D List A] as a Inner Join
[qryDIR1 Duplicates tblMFS M D List A] as b
On a.SSN=b.SSN
WHERE a.SSN<>"000-00-0000" And
Format(b.DateOfExam,"yyyymmdd")& "-" & [MFSID#] >=Format(a.DateOfExam,"yyyymmdd") & "-" & [MFSID#]

You can use this query as the basis for a crosstab and set the criteria on the rank to be the most recent 4 or 5 dates. For the ColumnHeading, use:

"DOE" & DateRank

The ranks are in the reverse order from what you wanted (makes it easy to select the top dates) but you can always switch the rank around by using

"DOE" & 5-DateRank (where 5=number of dates included)

Then write a select query against the crosstab which calculates the difference between the ranked dates.

 

[awl]

JonFer: Thanks for taking the time in what you have done. I’m new at this particular kind of query design, and if I may ask a couple of questions. First I copied your suggestion directly into a new query. The first error msg “The specified field [MFSID#] could refer to more than one table listed in the FROM clause of your SQL Statement”. I inserted the query source in front of both [MFSID#] in the WHERE clause. When I tried to run the query, the error msg “You tried to execute a query that does not include the specific expression ‘SSN’ as part of the aggregate function.”. I’m stuck here. I looked at the design of the query and sure, two sources a and b with the inner join of the SSN is shown. Question: what is the purpose of “a” and “b”; could “a” be substituted for [qryDIR1 Duplicates tblMFS M D List A] and “b” makes a duplicate source? I have provided my copy of the SQL statement if it helps.

SELECT a.SSN, a.Name, a.DateOfExam, a.[MFSID#], Count(b.DateOfExam) AS DateRank
FROM [qryDIR1 Duplicates tblMFS M D List A] AS a INNER JOIN [qryDIR1 Duplicates tblMFS M D List A] AS b ON a.SSN = b.SSN
WHERE (((a.SSN)<>"000-00-0000") AND ((Format(.[DateOfExam],"yyyymmdd") & "-" & [qryDIR1 Duplicates tblMFS M D List A].[MFSID#])>=Format([a].[DateOfExam],"yyyymmdd") & "-" & [qryDIR1 Duplicates tblMFS M D List A].[MFSID#]));

 

[lespaul]

Since you are using an aggregate function (in this case COUNT, but it could be SUM or AVG) EVERY field in your select clause needs to be included in the GROUP BY clause. You should be able to run this query:

SELECT a.SSN, a.Name, a.DateOfExam, a.[MFSID#], Count(b.DateOfExam) AS DateRank
FROM [qryDIR1 Duplicates tblMFS M D List A] AS a INNER JOIN [qryDIR1 Duplicates tblMFS M D List A] AS b ON a.SSN = b.SSN
WHERE (((a.SSN)<>"000-00-0000") AND ((Format([b].[DateOfExam],"yyyymmdd") & "-" & [qryDIR1 Duplicates tblMFS M D List A].[MFSID#])>=Format([a].[DateOfExam],"yyyymmdd") & "-" & [qryDIR1 Duplicates tblMFS M D List A].[MFSID#]))
GROUP BY a.SSN, a.Name, a.DateOfExam, a.[MFSID#];

HTH

Leslie

 

[JonFer]

Sorry - goofed on the first query. Forgot the group by clause and didn't specify the table alias for MFSID in the Where:

SELECT a.SSN, a.Name, a.DateOfExam, a.[MFSID#], Count(b.DateOfExam) as DateRank
FROM [qryDIR1 Duplicates tblMFS M D List A] as a Inner Join
[qryDIR1 Duplicates tblMFS M D List A] as b
On a.SSN=b.SSN
WHERE a.SSN<>"000-00-0000" And
Format(b.DateOfExam,"yyyymmdd")& "-" & b.[MFSID#] >=Format(a.DateOfExam,"yyyymmdd") & "-" & a.[MFSID#]
Group by a.SSN, a.Name, a.DateOfExam, a.[MFSID#]

 

[awl]

Lespaul I used your coding and the following dialog msg appeared: Enter Parameter Value: qryDIR1 Duplicates tblMFS M D List A.MFSID#. So I used JonFer’s code. JonFer: your updated query works great, the result:

SSN Name DateOfExam MFSID# DateRank
000-00-0000 Sample One 1/30/2004 859 1


Now, I tried to create a crosstab query. I got an error reading: “Too many crosstab column headers” when I created the crosstab query using the following: Rowheadings – Name; Column Headings – DateOfExam (gives me only 1 field value to select); Interval – Date; Number to Calculate – DateRank – Count. I got stuck when you suggested to use the two Column Headings “DOE” & DateRank and I didn’t know where to place the 5-DateRank.

Provided is the SQL of the [bold]new crosstab query[/bold] which gives me the Name, Total Of DateOfExam and Rankings 1, 2, 3 (if this is what your suggesting my result should be). How do I get the Rankings and DateOfExams to match up? I appreciate your help, so far.

TRANSFORM Count([qryDIR1 Duplicates tblMFS M D List C].DateOfExam) AS CountOfDateOfExam
SELECT [qryDIR1 Duplicates tblMFS M D List C].Name, Count([qryDIR1 Duplicates tblMFS M D List C].DateOfExam) AS [Total Of DateOfExam]
FROM [qryDIR1 Duplicates tblMFS M D List C]
GROUP BY [qryDIR1 Duplicates tblMFS M D List C].Name
PIVOT [qryDIR1 Duplicates tblMFS M D List C].DateRank;

Name Total Of DateOfExam 1 2 3
Sample One 1 1

 

[JonFer]

You have to type in the expression for the ColumnHeading field and you would just use ONE of the Column expressions I showed. This one converts the ranks to be 1=earliest which is what you probably want:

"DOE" & 5-DateRank

The full SQL should be like this:

TRANSFORM Min(c.DateOfExam) AS DateOfExam
SELECT c.Name, Count(c.DateOfExam) AS [Total DateOfExams]
FROM [qryDIR1 Duplicates tblMFS M D List C] as c
Where DateRank <=5
GROUP BY c.Name
PIVOT "DOE" & 5-c.DateRank;

 

[awl]

Thank you very much. The latest crosstab query suggestion works fine. A question though, when I view the crosstab query in design view, the second Column heading is: Expr1: "DOE" & 5-c.DateRank 1) Why does the Expr1 display in the Design View, and 2) when I run the query, the first DOE1 is not displayed as a Column Heading? I showed the examples below indicating data entry mistakes: Sample 1 A and B is the same person, he had 3 exams, using the same SSN, however the Name was misspelled once thus creating an empty cell under DOE4 of Sample 1 A. Sample 2 does catch my duplicate entry as also Sample 3 gives me a duplicate. This is what I was looking for.

Name Total DateOfExams DOE2 DOE3 DOE4
Sample 1 A 2 18-Dec-02 13-Apr-04
Sample 1 B 1 13-Apr-04
Sample 2 2 23-Feb-04 23-Feb-04
Sample 3 3 27-Jan-03 13-Feb-04 13-Feb-04


If I may, the SQL below is the select query I created in Counting # of days/months between exams and giving me only those that are 6 months or less; I know 30 is not the exact # of days per month. In viewing the results of this query, I also find data entry mistakes: the Name is listed with only 1 DOE due to the SSN was incorrectly entered for the same person. I can see that if maybe the Database was originally setup correctly, maybe the errors would have been fewer. However, I am still interested in the two issues 1) and 2) above if these will present a problem later. Thanks.

SELECT [qryDIR1 Duplicates tblMFS M D List C_Crosstab].Name, [qryDIR1 Duplicates tblMFS M D List C_Crosstab].DOE2, [qryDIR1 Duplicates tblMFS M D List C_Crosstab].DOE3, [qryDIR1 Duplicates tblMFS M D List C_Crosstab].DOE4, DateDiff("d",[DOE2],[DOE3])/30 AS [Diff2 to 3], DateDiff("d",[DOE3],[DOE4])/30 AS [Diff3 to 4]
FROM [qryDIR1 Duplicates tblMFS M D List C_Crosstab]
WHERE ((([qryDIR1 Duplicates tblMFS M D List C_Crosstab].[Total DateOfExams])>1)) OR (((DateDiff("d",[DOE2],[DOE3])/30)<6)) OR (((DateDiff("d",[DOE3],[DOE4])/30)<6));

 

[JonFer]

The "Expr1" is just a default label for displaying the field in the Query Design window. It has no effect on the output.

DOE1 is not there because I gave the wrong conversion formula to reverse the ranks. Using 5-DateRank only works if everyone had 4 or more dates. If there were only 2 dates, you'd need to use 3-DateRank (3-2=1 and 3-1=2) to reverse the order.

To keep from complicating things further, just use:

"DOE" & c.DateRank

and keep the criteria as DateRank <=5. Then you'll have the most recent date as DOE1 but it is easy to adjust your calculations to be the difference between DOE2 and DOE1 instead of the reverse (which is the same number of days anyway). Save the order of the columns in your crosstab result so DOE5 is first, then DOE4, etc, if that helps.

By the way, you can calculate the number of days between two dates by just subtracting them:

DOE1-DOE2 = number of days between

You can also just check that "DOE1-DOE2 < 180" and save yourself the division by 30.

 

[awl]

JonFer With this one Posted question, I have learned a lot and say thank you very much for your professionalism in responding and also as a Programmer, you brought your level of expertise down to a layperson as myself to understand the process. I’m sure there was a lot of effort involved. Thank you.