tracking dates

[calouste]

Hi,

I have a database that tracks research study participants. Each participant
is supposed to have a baseline, 3 month, and 9 month interview, and we
calculate due dates for the 3 month and the 9 month interviews from the
baseline interview date.

Reports seemed simple enough at first. I created fields for each interview
period- ie InterviewerBL, StatusBL, Interviewer3M, Status3M - and they are in
an Interviews table. Primary key is an autonumber, foreign key is the study
participant's StudyID, which links to a Case table that has the StudyID as
the primary key.

(I realize that there is probably a more efficient/elegant way to do this,
but I don't know code and I had to get this done quickly.)

Presently, I calculate due dates by using DateAdd and the BL interview date
in unbound text boxes for each period in a report.

However, my problem now is that my manager wants a report that sorts first
by interviewer, then by date of interview. They way things are set up in my
database, I can't just pull up an interviewer and look at the interviews
they've done or are assigned to do. What do you suggest I do about this?
Also, because I have information about each period's interview in a different
field, how could I efficiently sort by date, not necessarily by field? For
example, because people are recruited at different times, one person's 9M
interview could come up sooner than another person's 3M.

I hope this makes sense. Thanks for any help you can provide!

 

[Sharkbyte]

A few questions...

1. Are you storing the Interviewer Name with the interview information?
2. Are you storing the dates of the 3 month and 9 month interviews or are
you calculating it every time?

If you are storing the Interviewer's Name, then that sort is easy. The
storing of dates will determine how you put together your report. Stored
dates would be esaier to deal with than calculated, but both are possible.

Sharkbyte

 

[David Lloyd]

As I do not completely understand everything you are doing I will just make
a few suggestions. First, it sounds as though you should create a query to
view either the interviews or assigned interviews for a particular
interviewer. You would create an input parameter for the ID or name of the
interviewer.

For the report you can create a query to sort the table by interviewer and
then by interview date. See the "Sort" line in the Query Designer. By
sorting the data in a query, and then using this query as the Record Source
for the report, your data will already be sorted the way you desire for the
report.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Hi,

I have a database that tracks research study participants. Each participant
is supposed to have a baseline, 3 month, and 9 month interview, and we
calculate due dates for the 3 month and the 9 month interviews from the
baseline interview date.

Reports seemed simple enough at first. I created fields for each interview
period- ie InterviewerBL, StatusBL, Interviewer3M, Status3M - and they are
in
an Interviews table. Primary key is an autonumber, foreign key is the study
participant's StudyID, which links to a Case table that has the StudyID as
the primary key.

(I realize that there is probably a more efficient/elegant way to do this,
but I don't know code and I had to get this done quickly.)

Presently, I calculate due dates by using DateAdd and the BL interview date
in unbound text boxes for each period in a report.

However, my problem now is that my manager wants a report that sorts first
by interviewer, then by date of interview. They way things are set up in my
database, I can't just pull up an interviewer and look at the interviews
they've done or are assigned to do. What do you suggest I do about this?
Also, because I have information about each period's interview in a
different
field, how could I efficiently sort by date, not necessarily by field? For
example, because people are recruited at different times, one person's 9M
interview could come up sooner than another person's 3M.

I hope this makes sense. Thanks for any help you can provide!

 

[calouste]

A few questions...

1. Are you storing the Interviewer Name with the interview information?

The Interviews table has an InterviewerID field that looks up the name from
an Interviewers table.

2. Are you storing the dates of the 3 month and 9 month interviews or are
you calculating it every time?

Calculating, though it wouldn't be hard to store it, I suppose. I've read
that one shouldn't be storing stuff like that so I was trying to do the right
thing.

If you are storing the Interviewer's Name, then that sort is easy. The
storing of dates will determine how you put together your report. Stored
dates would be esaier to deal with than calculated, but both are possible.

Thanks for taking interest in my dilemma, Sharkbyte! I was thinking about
this some more tonight. I've already put the interviewer names in a separate
table, should I continue along those lines and put the interview data in
separate tables by period? This kind of thing is hard for me to wrap my head
around because I got so used to using flat file databases in SPSS.

Karin.

 

[calouste]

Hello David,

That could work if I wanted to look at just one interviewer; I'll try it out
tomorrow, thanks!