Select all available / non absent (1 Viewer)

[perlfan]

Hi there - I'm trying to select translators who are available today. For this I'm checking against the entered absence times - how can I list also translators for whom no absence times where entered? (these are obviously available...) Thanks for help! FRANK

SELECT DISTINCT Language_Combinations_Prices.Trans_ID AS [" & lbl_transID & "], Translators.lastname AS [" & lbl_lastname & "], Translators.firstname AS [" & lbl_firstname & "], " & eval_val & " AS [" & lbl_eval & "], Language_Combinations_Prices.ppw AS [" & lbl_ppw & "], Language_Combinations_Prices.ppl AS [" & lbl_ppl & "], Language_Combinations_Prices.pph AS [" & lbl_pph & "], Language_Combinations_Prices.ppp AS [" & lbl_ppp & "], translator_absence_times.startdate_absence, translator_absence_times.enddate_absence
FROM ((Language_Combinations_Prices INNER JOIN Translators ON Language_Combinations_Prices.Trans_ID = Translators.ID_Trans) INNER JOIN evaluation ON Translators.evaluation = evaluation.evaluationID) INNER JOIN translator_absence_times ON Translators.ID_Trans = translator_absence_times.translator_ID
WHERE ((Not (translator_absence_times.startdate_absence)<=Date()) AND (Not (translator_absence_times.enddate_absence)>=Date()) AND ((Language_Combinations_Prices.SourceLanguage)="EN") AND ((Language_Combinations_Prices.TargetLanguage)="DE"))
ORDER BY Translators.lastname;

 

[sneuberg]

Maybe you could just check the translator_absence_times.startdate_absence for being null like:

SELECT DISTINCT Language_Combinations_Prices.Trans_ID AS [" & lbl_transID & "], Translators.lastname AS [" & lbl_lastname & "], Translators.firstname AS [" & lbl_firstname & "], " & eval_val & " AS [" & lbl_eval & "], Language_Combinations_Prices.ppw AS [" & lbl_ppw & "], Language_Combinations_Prices.ppl AS [" & lbl_ppl & "], Language_Combinations_Prices.pph AS [" & lbl_pph & "], Language_Combinations_Prices.ppp AS [" & lbl_ppp & "], translator_absence_times.startdate_absence, translator_absence_times.enddate_absence
FROM ((Language_Combinations_Prices INNER JOIN Translators ON Language_Combinations_Prices.Trans_ID = Translators.ID_Trans) INNER JOIN evaluation ON Translators.evaluation = evaluation.evaluationID) INNER JOIN translator_absence_times ON Translators.ID_Trans = translator_absence_times.translator_ID
WHERE (
    	(
		IsNull(translator_absence_times.startdate_absence)
    	 	Or (Not (translator_absence_times.startdate_absence)<=Date() )
    	 	AND (Not (translator_absence_times.enddate_absence)>=Date() )
	) 
     	AND ((Language_Combinations_Prices.SourceLanguage)="EN ")
     	AND ((Language_Combinations_Prices.TargetLanguage)="DE ")
      )
ORDER BY Translators.lastname;

 

[perlfan]

What if I have not entered any absence times for a translator? Actually they should be selected if I use WHERE (((translator_absence_times.startdate_absence) Is Null)), am I right? However this Select results in 0 results. When I use WHERE (((translator_absence_times.startdate_absence) Not Is Null)) I get the two translators for whom I have entered absence times.

@sneuberg: WHERE ((Not (IsNull([translator_absence_times].[startdate_absence]))=False)) also results in zero translators even though there are many with no absence times entered.

 

[sneuberg]

What if I have not entered any absence times for a translator? Actually they should be selected if I use WHERE (((translator_absence_times.startdate_absence) Is Null)), am I right?.

Yes it's better to use the SQL Is Null than the function IsNull() like I had it, but I don't know why this isn't working. It seems like it should albeit getting parenthesis in the right place is sometimes tricky for me. Could you upload your database or a stripped down version of it. It'd be easier for me to get this right in the query builder.

 

[perlfan]

Unfortunately it would take me hours to provide my database - otherwise I'd have done it right away.

I now have tried LEFT JOIN which works pretty well but still has one problem - when there are several absence times entered for one translator, that translator gets listed if though he is not available (because only one row doesn't meet the criteria, but the others do....).

SELECT DISTINCT Language_Combinations_Prices.Trans_ID AS [" & lbl_transID & "], Translators.lastname AS [" & lbl_lastname & "], Translators.firstname AS [" & lbl_firstname & "], " & eval_val & " AS [" & lbl_eval & "], Language_Combinations_Prices.ppw AS [" & lbl_ppw & "], Language_Combinations_Prices.ppl AS [" & lbl_ppl & "], Language_Combinations_Prices.pph AS [" & lbl_pph & "], Language_Combinations_Prices.ppp AS [" & lbl_ppp & "], translator_absence_times.startdate_absence
FROM ((Language_Combinations_Prices INNER JOIN Translators ON Language_Combinations_Prices.Trans_ID = Translators.ID_Trans) INNER JOIN evaluation ON Translators.evaluation = evaluation.evaluationID) LEFT JOIN translator_absence_times ON Translators.ID_Trans = translator_absence_times.translator_ID
WHERE (((translator_absence_times.startdate_absence)>Date())) OR (((translator_absence_times.startdate_absence) Is Null))
ORDER BY Translators.lastname;

 

[perlfan]

In the Access Expression Builder I successfully created the query - however when I copy and adapt this into VBA it results a syntax error. Can somebody help me with this:

The working query in the Access Expression Builder:
SELECT DISTINCT Language_Combinations_Prices.Trans_ID AS [" & lbl_transID & "], Translators.lastname AS [" & lbl_lastname & "], Translators.firstname AS [" & lbl_firstname & "], " & eval_val & " AS [" & lbl_eval & "], Language_Combinations_Prices.ppw AS [" & lbl_ppw & "], Language_Combinations_Prices.ppl AS [" & lbl_ppl & "], Language_Combinations_Prices.pph AS [" & lbl_pph & "], Language_Combinations_Prices.ppp AS [" & lbl_ppp & "]
FROM ((Language_Combinations_Prices INNER JOIN Translators ON Language_Combinations_Prices.Trans_ID = Translators.ID_Trans) INNER JOIN evaluation ON Translators.evaluation = evaluation.evaluationID) LEFT JOIN translator_absence_times ON Translators.ID_Trans = translator_absence_times.translator_ID
WHERE (((translator_absence_times.startdate_absence)>Date() Or (translator_absence_times.startdate_absence) Is Null) AND ((translator_absence_times.enddate_absence)<Date() Or (translator_absence_times.enddate_absence) Is Null) AND ((Language_Combinations_Prices.SourceLanguage)="EN") AND ((Language_Combinations_Prices.TargetLanguage)="DE"))
ORDER BY Translators.lastname, " & eval_val & ";

The query with the syntax error in VBA:
datequery = date
availabletranslator = "SELECT DISTINCT Language_Combinations_Prices.Trans_ID AS [" & lbl_transID & "], Translators.lastname AS [" & lbl_lastname & "], Translators.firstname AS [" & lbl_firstname & "], " & eval_val & " AS [" & lbl_eval & "], Language_Combinations_Prices.ppw AS [" & lbl_ppw & "], Language_Combinations_Prices.ppl AS [" & lbl_ppl & "], Language_Combinations_Prices.pph AS [" & lbl_pph & "], Language_Combinations_Prices.ppp AS [" & lbl_ppp & "]" & _
" FROM ((Language_Combinations_Prices INNER JOIN Translators ON Language_Combinations_Prices.Trans_ID = Translators.ID_Trans) INNER JOIN evaluation ON Translators.evaluation = evaluation.evaluationID) LEFT JOIN translator_absence_times ON Translators.ID_Trans = translator_absence_times.translator_ID" & _
" WHERE (((translator_absence_times.startdate_absence)> " & datequery & ") AND ((translator_absence_times.enddate_absence)<" & datequery & ") AND ((Language_Combinations_Prices.SourceLanguage)='EN') AND ((Language_Combinations_Prices.TargetLanguage)='DE')) OR (((translator_absence_times.startdate_absence) Is Null) AND ((translator_absence_times.enddate_absence) Is Null))" & _
" ORDER BY Translators.lastname, " & eval_val & ";"

Set rs = db.OpenRecordset(availabletranslator)

 

[sneuberg]

I'd just open the query in VBA rather than try to adapt a query that complex into a string.

 

[perlfan]

Thank you, however this has been a format error. I needed to convert the date to US date format in order to use it in the query.

I now attached the problematic query in a small db. You can see the problem when you look at translator1 who shouldn't be available today, but for whom were entered also absence times in the future why he is considered available again.

So how can I select only the translators who are available today?

 

[sneuberg]

Thanks for posting a sample database. That makes things a lot easier.

In the attached database I solve this problem by first making a query (qryAbsentTranslators) which returns the absent translators which also include translators who have a record in the translator_absence_times with Null dates. I guess that would mean they are permanently absent. I wouldn't do it that way but whatever. The SQL for this is

SELECT translator_absence_times.ID, translator_absence_times.translator_ID, translator_absence_times.startdate_absence, translator_absence_times.enddate_absence
FROM translator_absence_times
WHERE (((translator_absence_times.startdate_absence)<=Date()) AND ((translator_absence_times.enddate_absence)>=Date())) OR (((translator_absence_times.startdate_absence) Is Null) AND ((translator_absence_times.enddate_absence) Is Null));

Then I create an unmatched query by making an outer join between the translators table and the qryAbsentTranslators query which shows which translators are available. I named this qryAvailableTranslators and its SQL is

SELECT translators.ID_Trans, translators.firstname, translators.lastname, translators.street, qryAbsentTranslators.translator_ID
FROM translators LEFT JOIN qryAbsentTranslators ON translators.ID_Trans = qryAbsentTranslators.translator_ID
WHERE (((qryAbsentTranslators.translator_ID) Is Null));

You might be tempted to try and combine these queries but I think you will find that if you do that the WHERE clause on the translator_absence_times will negate the effect of the outer join.

I don't understand what the formatting error is, albeit maybe I don't see it because of my US regional settings. Could you explain this a bit more?

 

[perlfan]

Hi again - I didn't know that you can link queries like that! It works pretty well. Thanks a lot for working this out. Regarding the null values - they weren't meant to be in the absence table - I just wanted translators to be considered available that have no record in the absence table. That's all.
Anyway - I learnt a lot here. Thanks again!!

BTW:

strDatum = Format(date, "\#yyyy\-mm\-dd\#")

This is necessary before I can work with date in the SQL string because my default date format is German (dd.mm.yyyy).

Regards, Frank