LW Irving
|
| Posted: 12/17/2004, 5:38 AM |
|
I have a database which has two tables Referral and assessment
I need to make a query where I get a list of people who have been referred  before a certain date but not yet assessed
Regards
Wayne
|
|
|
 |
LW Irving
|
| Posted: 12/17/2004, 5:19 PM |
|
As peterr pointed out this is an old question, I have changed the database
since it was first posted.
It is for a disability service, people are referred then they need to be
assessed within two weeks of the referral, after they have been assessed
they need to be given a Plan within two weeks of the assessment. So there is
a period of time when a number of people have been referred and are in the
referral table but they do not have an assessment or a plan. Firstly I want
a query that returns the people who have been in the referral table for more
than 2 weeks ( that will be a big list with most of the clients) I want to
then find the people in that list who do not have an assessment and only
show those rows.
Of course I will then need a query that finds people who have been referred
for more that 2 weeks that have a record in the assessment table but do not
have a record in the plans table
There are now several tables in the data base however the three that count
are Referral, Assessment and Plan
all are linked by the Referral_ID
Select * from Referral Where date < date - 2weeks
before showing it do a query on assessment table
select from assessment where referral_ID = '3'
if form.recordset.EOF then show this row in the list
Thesecond question concerns updateing a second table afer submitting a form,
I was using this code
dim add_case_sql
add_case_sql = "update plans set " &_
"[Next_Contact]=" & DBConnection1.ToSQL(Reviews.review_date.value, "Date")&_
"where plan_id =" & DBConnection1.ToSQL(reviews.plan_id.value, "Number")
DBConnection1.execute add_case_sql
I haven't continued with that Idea but think the problem was not having ##
characters around the dat in the SQL Query
Regards
Wayne
Not sure if I understand. Your initial question was "I want to update the
next_contact date field in the plans table". Now you wrote "I want a list of
people...".
If these are 2 different questions I recommend creating new forum topic to
avoid confusion.
<LWIrving@forum.codecharge (LW Irving)> wrote in message
news:641c2e156a9132@news.codecharge.com...
>I have a database which has two tables Referral and assessment
>
> I need to make a query where I get a list of people who have been referred
> before a certain date but not yet assessed
>
> Regards
> Wayne
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
|
