LW Irving
|
| Posted: 01/03/2005, 7:38 PM |
|
sorry about the multiple post but I mixed two questions earlier
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
If it was a shop that had tables "customers" and "Sales" I would be looking for customers who had registerd but not bought anything
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 - 2 weeks
Then before showing it do a query on assessment table
select from assessment where referral_ID = form.referral_id.value
if form.recordset.EOF then show this row in the list (0 records returned from assessment table)
|
|
|
 |
L Irving
|
| Posted: 01/05/2005, 6:14 PM |
|
I just realised that I can't hide rows in a grid
So it has to be in the SQL Where statement
I think I need to modify the CCS select statement
I need to get each ref_id in the referral table and then check them against the assessment table something like below
strSQL = "SELECT * FROM referral,assement
WHERE Ref_ID= '" & referral.ref_id
Set rs = db.Execute()
If rs.EOF Then
'Show this record
Else
goto Next record
End If
Or do I have to use something like
SELECT Referral.*, Assessments.Ref_ID AS Assessments_Ref_ID
FROM Assessments INNER JOIN Referral ON
Assessments.Ref_ID = Referral.Ref_ID
Any help greatly appreciated
|
|
|
|
guest
|
| Posted: 01/05/2005, 10:27 PM |
|
Nearly there
Using the NOT EXISTS operator
select
referral.REF_id,referral.referral_received_date,referral.name,referral.town
from referral
where
not exists ( select assessment_id from assessments where
assessments.ref_id = referral.REF_id )
Now just have to include the date filter
ithink I should modify th where statement
not exists ( select assessment_id from assessments where
assessments.ref_id = referral.REF_id ) AND referral.referral_received_date <
#s_sdate#
|
|
|
|
peterr
Posts: 5971
|
| Posted: 01/05/2005, 11:02 PM |
|
If you're using MS Access then probably this would work:
not exists ( select assessment_id from assessments where
assessments.ref_id = referral.REF_id ) AND referral.referral_received_date <
#{s_sdate}#
Then create SQL "s_sdate" parameter in the data source.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
 |
|
guest
|
| Posted: 01/06/2005, 12:53 AM |
|
It works
not as I thought but it works
SELECT *
FROM Referral
WHERE Referral.Referral_Received_Date < #{Received_Date}# and
not exists ( select assessment_id from assessments where
assessments.ref_id = referral.REF_id )
I had to make Received_Date a text parameter ; I could not get it to work as
a date type
I entered a default date "12/12/2020" I tried using currentdate to no avail
the NOT EXISTS part had to come last for some reason
<LWIrving@forum.codecharge (LW Irving)> wrote in message
news:641da0fa3cb3df@news.codecharge.com...
>
> sorry about the multiple post but I mixed two questions earlier
>
> 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
>
> If it was a shop that had tables "customers" and "Sales" I would be
> looking for
> customers who had registerd but not bought anything
>
> 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 - 2 weeks
> Then before showing it do a query on assessment table
> select from assessment where referral_ID = form.referral_id.value
>
> if form.recordset.EOF then show this row in the list (0 records returned
> from
> assessment table)
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
|
