Another left join problem

[Brad]

Hi all,

I've read over all the previous left join queries in the ng but I
still can't solve this problem.

I have several different queries which perform various checks on some
data I have. Individually they work fine but when I join them together
via a separate query some of them return information when they
shouldn't.

I'll try to include some data but this gets quite complicated -

Source Table
ID Date
1000000000008600 28/04/2004
1000000000009100
1000000000009400 11/09/2003
1000000000010500
1000000000010800 27/04/2004

Now the first query works fine. Does the check and returns those that
are blank or more than 10 days old - SELECT source_table.id,
source_table.date, IIf(DateAdd("d",-10,Now())>CDate(nz([date],#1/1/1900#)),True,False)
AS checker, IIf(DateAdd("d",-10,Now())>CDate(nz([date],#1/1/1066#)),"date
is " & IIf(CDate(nz([date],#1/1/1066#))=#1/1/1066#,"blank",Format(CDate(nz([date],#1/1/1066#)),"dd-mmm-yy")),"")
AS data FROM source_table WHERE
((IIf(DateAdd("d",-10,Now())>CDate(nz([date],#1/1/1900#)),True,False))=True)
Results:
ID date checker data
1000000000009100 -1 date is blank
1000000000009400 11/09/2003 -1 date is 11-Sep-03
1000000000010500 -1 date is blank

But when I used a second query to combine the various results for all
IDs, I get erroneous results. SELECT source_table.id, query1.data AS
[date]
FROM source_table LEFT JOIN query1 ON source_table = query1.company_id
Results:
company_id date
1000000000008600 date is blank
1000000000009100 date is blank
1000000000009400 date is 11-Sep-03
1000000000010500 date is blank
1000000000010800 date is blank

As you can see the information for ids 8600 & 10800 is wrong.

Does anyone have any ideas? This is driving me barmy and I've been
pulling chunks of hair out for the last couple of days.

Any help is much appreciated

Regards,

Brad

 

[Steve Schapel]

Brad,

I haven't checked right through this, but there is something which is
"asking for trouble", and I would suggest fixing before going anywhere
else with this. I am referring to this part of your second query...
query1.data AS [date]
.... where [date] is the name of a field in the Source Table. Very
confusing. In fact, since 'date' is a reserved word in Access (i.e. has
a special meaning), it would be a good idea to change the name of this
field in the Source Table anyway, but my main point is that you have a
query based on a table and a query, and you are trying to use a field
from the query and alias it as the name of a field in the table. Change
this, and try again.

By the way, your first query is way more complicated than it needs to
be. Unless I'm missing something, why don't you just do it like this...
SELECT [id], [date], "date is " &
IIf(IsNull([date]),"blank",Format([date],"dd-mmm-yy")) AS data
FROM source_table
WHERE [date] Is Null Or [date]<Date()-10

--
Steve Schapel, Microsoft Access MVP

Hi all,

I've read over all the previous left join queries in the ng but I
still can't solve this problem.

I have several different queries which perform various checks on some
data I have. Individually they work fine but when I join them together
via a separate query some of them return information when they
shouldn't.

I'll try to include some data but this gets quite complicated -

Source Table
ID Date
1000000000008600 28/04/2004
1000000000009100
1000000000009400 11/09/2003
1000000000010500
1000000000010800 27/04/2004

Now the first query works fine. Does the check and returns those that
are blank or more than 10 days old - SELECT source_table.id,
source_table.date, IIf(DateAdd("d",-10,Now())>CDate(nz([date],#1/1/1900#)),True,False)
AS checker, IIf(DateAdd("d",-10,Now())>CDate(nz([date],#1/1/1066#)),"date
is " & IIf(CDate(nz([date],#1/1/1066#))=#1/1/1066#,"blank",Format(CDate(nz([date],#1/1/1066#)),"dd-mmm-yy")),"")
AS data FROM source_table WHERE
((IIf(DateAdd("d",-10,Now())>CDate(nz([date],#1/1/1900#)),True,False))=True)
Results:
ID date checker data
1000000000009100 -1 date is blank
1000000000009400 11/09/2003 -1 date is 11-Sep-03
1000000000010500 -1 date is blank

But when I used a second query to combine the various results for all
IDs, I get erroneous results. SELECT source_table.id, query1.data AS
[date]
FROM source_table LEFT JOIN query1 ON source_table = query1.company_id
Results:
company_id date
1000000000008600 date is blank
1000000000009100 date is blank
1000000000009400 date is 11-Sep-03
1000000000010500 date is blank
1000000000010800 date is blank

As you can see the information for ids 8600 & 10800 is wrong.

Does anyone have any ideas? This is driving me barmy and I've been
pulling chunks of hair out for the last couple of days.

Any help is much appreciated

Regards,

Brad