Look up value

[accessvet]

Dear list,

I have 2 tables:
Table1 has SampleCollectionDate; and
Table2 has DateGaveBirth, DateStoppedLactating and DateGaveBirthNext.
Both tables linked together in a select query.

I want to return 1 if DateGaveBirth<SampleCollectionDate<DateStopedLactating,
or return 2 if DateStopedLactating<SampleCollectionDate<DateGaveBirthNext.

Using a IIF statements, I was able to do that for Table1 records that have 1
record only in Table 2 but when it came to cases with several lactation
records in Table 2 I got incorrect values, specifically all 1s although I
should be getting 2s.

The 1 and 2 are to identify when was a sample collected in a record.

I tried to use DLookup, Dfirst but was not able to apply them.

Any help is greatly appreciated.

 

[John W. Vinson]

Dear list,

I have 2 tables:
Table1 has SampleCollectionDate; and
Table2 has DateGaveBirth, DateStoppedLactating and DateGaveBirthNext.
Both tables linked together in a select query.

I want to return 1 if DateGaveBirth<SampleCollectionDate<DateStopedLactating,
or return 2 if DateStopedLactating<SampleCollectionDate<DateGaveBirthNext.

Using a IIF statements, I was able to do that for Table1 records that have 1
record only in Table 2 but when it came to cases with several lactation
records in Table 2 I got incorrect values, specifically all 1s although I
should be getting 2s.

The 1 and 2 are to identify when was a sample collected in a record.

I tried to use DLookup, Dfirst but was not able to apply them.

Any help is greatly appreciated.

Correct the error in your query.

If you would like help doing so, please open the query in SQL view and post it
here. Also indicate what results were incorrect, with an example.

We can't fix what we can't see!

 

[accessvet via AccessMonster.com]

I will try to paste the sql code below. Note there is Table3 with ID and BDAT
besides Table 1 and Table 2.

------------------------------------------------------------------------------
--------------------------------------------------------------

SELECT DISTINCT Table3.ID, Table3.BDAT, Table1.[SampleCollectionDate], IIf(
[DATEGAVEBIRTH]<[SampleCollectionDate]<[DateStoppedLactating],"sample
collected while milking",IIf([DateStoppedLactating]<[SampleCollectionDate]<
[DateGaveBirthNext],"sample collected while not milking")) AS TestPeriod

FROM (Table3 LEFT JOIN [Table2] ON (Table3.BDAT = [Table2].BDAT) AND (Table3.
ID = [Table2].ID)) LEFT JOIN Table1 ON Table3.ID = Table1.SampleID

------------------------------------------------------------------------------
---------------------------------------------------------------

The problem is there are cases where the IIF function is false and should
return "sample collected while not milking" but it is returning "sample
collected while milking" or null (the later is ok since it would just be the
data and I dont have a statement for the second false).

My guess is it is because there are more than 1 lactation record in Table2
for most IDs.

Thanks for any help you may have.

Dear list,

[quoted text clipped - 16 lines]

Any help is greatly appreciated.

Correct the error in your query.

If you would like help doing so, please open the query in SQL view and post it
here. Also indicate what results were incorrect, with an example.

We can't fix what we can't see!

 

[John Spencer]

I would rewrite the test. I think your statement will give you unexpected
results.

[DATEGAVEBIRTH]<[SampleCollectionDate] will return TRUE (-1) or False (0)
which will then be compared to [DateStoppedLactating] which means the
statement will almost always be true. The exception will be if DateGaveBirth
or SampleCollectionDate is ever null. In that case the expression will
evaluate to Null and will try then return the False part of the IIF. That
will probably return Null for the same reason.

Try

IIF([DATEGAVEBIRTH]<[SampleCollectionDate]
AND SampleCollectionDate <[DateStoppedLactating], ...
IIF([DateStoppedLactating]<[SampleCollectionDate]
AND [SampleCollectionDate]<[DateGaveBirthNext], ...))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I will try to paste the sql code below. Note there is Table3 with ID and BDAT
besides Table 1 and Table 2.

------------------------------------------------------------------------------
--------------------------------------------------------------

SELECT DISTINCT Table3.ID, Table3.BDAT, Table1.[SampleCollectionDate], IIf(
[DATEGAVEBIRTH]<[SampleCollectionDate]<[DateStoppedLactating],"sample
collected while milking",IIf([DateStoppedLactating]<[SampleCollectionDate]<
[DateGaveBirthNext],"sample collected while not milking")) AS TestPeriod

FROM (Table3 LEFT JOIN [Table2] ON (Table3.BDAT = [Table2].BDAT) AND (Table3.
ID = [Table2].ID)) LEFT JOIN Table1 ON Table3.ID = Table1.SampleID

------------------------------------------------------------------------------
---------------------------------------------------------------

The problem is there are cases where the IIF function is false and should
return "sample collected while not milking" but it is returning "sample
collected while milking" or null (the later is ok since it would just be the
data and I dont have a statement for the second false).

My guess is it is because there are more than 1 lactation record in Table2
for most IDs.

Thanks for any help you may have.

Dear list,

[quoted text clipped - 16 lines]

Any help is greatly appreciated.

Correct the error in your query.

If you would like help doing so, please open the query in SQL view and post it
here. Also indicate what results were incorrect, with an example.

We can't fix what we can't see!

 

[accessvet via AccessMonster.com]

Hello again,
Thanks for your response, I did try your code, it did identify a couple of
tests correctly and the rest were null so at least there were no wrong
results. I added a "check" to the false of the second IIF to see where are
the nulls resulting from. I think the query is cross-checking if
SampleCollectionDate is between the two dates but using the wrong pairs of
dates, that is DateSGaveBirth from 1 lactation record and a
DateStoppedLactating in another record.
If this helps at all:

Table1
ID Test TestResult SampleCollectionDate
232 Test1 0 10/19/2007
232 Test2 0 10/5/2007
232 Test3 1 12/29/2007
232 Test4 1 10/24/2006
232 Test4 1 10/26/2006
232 Test5 0 10/5/2007
232 Test6 0 10/24/2006

Table2
ID BDAT Lactation DateGaveBirth DateStoppedLactating DateGaveBirthNext
232 1/14/2004 1 11/28/2005 10/25/2006 10/25/2006
232 1/14/2004 2 12/30/2006 10/18/2007 10/18/2007
232 1/14/2004 3 12/24/2007 10/20/2008

I would rewrite the test. I think your statement will give you unexpected
results.

[DATEGAVEBIRTH]<[SampleCollectionDate] will return TRUE (-1) or False (0)
which will then be compared to [DateStoppedLactating] which means the
statement will almost always be true. The exception will be if DateGaveBirth
or SampleCollectionDate is ever null. In that case the expression will
evaluate to Null and will try then return the False part of the IIF. That
will probably return Null for the same reason.

Try

IIF([DATEGAVEBIRTH]<[SampleCollectionDate]
AND SampleCollectionDate <[DateStoppedLactating], ...
IIF([DateStoppedLactating]<[SampleCollectionDate]
AND [SampleCollectionDate]<[DateGaveBirthNext], ...))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I will try to paste the sql code below. Note there is Table3 with ID and BDAT
besides Table 1 and Table 2.

[quoted text clipped - 33 lines]

 

[accessvet via AccessMonster.com]

Sorry for the incomplete post above, the example data I wanted to post is
below again, alternately I can email this example database or if there is
away to post it please let me know.Thanks for everyone's help in this.

Table1
ID Test TestResult SampleCollectionDate
232 Test1 0 10/19/2007
232 Test2 0 10/5/2007
232 Test3 1 12/29/2007
232 Test4 1 10/24/2006
232 Test4 1 10/26/2006
232 Test5 0 10/5/2007
232 Test6 0 10/24/2006

Table2
ID BDAT Lactation DateGaveBirth DateStoppedLactating DateGaveBirthNext
232 1/14/2004 1 11/28/2005 10/25/2006 10/25/2006
232 1/14/2004 2 12/30/2006 10/18/2007 10/18/2007
232 1/14/2004 3 12/24/2007
10/20/2008

Table3
BDAT ID
1/14/2004 232


The query sql is:
TestPeriod: IIf([DateGaveBirth]<[SampleCollectionDate] And
[SampleCollectionDate]<[DateStoppedLactating],"sample collected while
milking",IIf([DateStoppedLactating]<[SampleCollectionDate] And
[SampleCollectionDate]<[DateGaveBirthNext],"sample collected while dry",
"Check"))

and it returned:
ID BDAT SampleCollectionDate TestPeriod
232 1/14/2004 10/24/2006 Check
232 1/14/2004 10/24/2006 sample collected while milking
232 1/14/2004 10/26/2006 Check
232 1/14/2004 10/5/2007 Check
232 1/14/2004 10/5/2007 sample collected while
milking
232 1/14/2004 10/19/2007 Check
232 1/14/2004 12/29/2007 Check

Hello again,
Thanks for your response, I did try your code, it did identify a couple of
tests correctly and the rest were null so at least there were no wrong
results. I added a "check" to the false of the second IIF to see where are
the nulls resulting from. I think the query is cross-checking if
SampleCollectionDate is between the two dates but using the wrong pairs of
dates, that is DateSGaveBirth from 1 lactation record and a
DateStoppedLactating in another record.
If this helps at all:

Table1
ID Test TestResult SampleCollectionDate
232 Test1 0 10/19/2007
232 Test2 0 10/5/2007
232 Test3 1 12/29/2007
232 Test4 1 10/24/2006
232 Test4 1 10/26/2006
232 Test5 0 10/5/2007
232 Test6 0 10/24/2006

Table2
ID BDAT Lactation DateGaveBirth DateStoppedLactating DateGaveBirthNext
232 1/14/2004 1 11/28/2005 10/25/2006 10/25/2006
232 1/14/2004 2 12/30/2006 10/18/2007 10/18/2007
232 1/14/2004 3 12/24/2007 10/20/2008

I would rewrite the test. I think your statement will give you unexpected
results.

[quoted text clipped - 23 lines]

 

[accessvet via AccessMonster.com]

Hello again,
Your code worked great and returned correct results, please ignore my earlier
posts sent today. I had a couple of dates mixed up in my example data and
didnt realize it till I saw my own post. Otherwise I think your code solved
the problem.
Thanks again for everyone's help and time.
Best regards.

Sorry for the incomplete post above, the example data I wanted to post is
below again, alternately I can email this example database or if there is
away to post it please let me know.Thanks for everyone's help in this.

Table1
ID Test TestResult SampleCollectionDate
232 Test1 0 10/19/2007
232 Test2 0 10/5/2007
232 Test3 1 12/29/2007
232 Test4 1 10/24/2006
232 Test4 1 10/26/2006
232 Test5 0 10/5/2007
232 Test6 0 10/24/2006

Table2
ID BDAT Lactation DateGaveBirth DateStoppedLactating DateGaveBirthNext
232 1/14/2004 1 11/28/2005 10/25/2006 10/25/2006
232 1/14/2004 2 12/30/2006 10/18/2007 10/18/2007
232 1/14/2004 3 12/24/2007
10/20/2008

Table3
BDAT ID
1/14/2004 232

The query sql is:
TestPeriod: IIf([DateGaveBirth]<[SampleCollectionDate] And
[SampleCollectionDate]<[DateStoppedLactating],"sample collected while
milking",IIf([DateStoppedLactating]<[SampleCollectionDate] And
[SampleCollectionDate]<[DateGaveBirthNext],"sample collected while dry",
"Check"))

and it returned:
ID BDAT SampleCollectionDate TestPeriod
232 1/14/2004 10/24/2006 Check
232 1/14/2004 10/24/2006 sample collected while milking
232 1/14/2004 10/26/2006 Check
232 1/14/2004 10/5/2007 Check
232 1/14/2004 10/5/2007 sample collected while
milking
232 1/14/2004 10/19/2007 Check
232 1/14/2004 12/29/2007 Check

Hello again,
Thanks for your response, I did try your code, it did identify a couple of

[quoted text clipped - 27 lines]