extraction query

[Guest]

Hi all,
I have a query in my greyhound racing database where i have a table that
lists say 10 runners for todays race and i extract each previous field(run)
the dog has had from another table where the date of that run was before
todays date using criteria in the select query.Problem is I would still like
to display the name of the dog that is running today even if i have no
previous data on that dog.
eg. Dog Number Dogs name Last run FinishPosition
1. Brett Lee 1/1/2005 1st
1. Brett Lee 8/1/2005 2nd
2. Boxer "blank" "blank"
and so on...
any help greatly appreciated.

 

[Michel Walsh]

Hi,

You need an outer join. In the query designer, click on the line, making the
graphical representation of the join, to edit it, and change from the
default type, an inner join, the appropriate join among the other 2 (out of
3) possibilities. That should change the line to an arrow, the arrow head
pointing toward the "incomplete" table (races), while the tail (table dogs)
is at the table that would include the data, even if none is mentioned in
the other joined table. In this case, we also say that table dogs is the
"preserved" table, while table "races" is the un-preserved table, but that
is more than a technical designation, once you know that the "preserved"
table has all its records in the output (while, with an inner join, only
matching records would appear, as you experienced).



Hoping it makes sense,
Vanderghast, Access MVP

 

[Guest]

Tks Michel that works good but when i try putting the below command in the
"daterun" criteria it somehow removes that unraced dog.....
Field: daterun
Criteria: In (select top 20 dateruns from greyhoundtable where
todaysdoglist.dogname = greyhoundtable.dogname order by w_date desc)

????

 

[Michel Walsh]

Hi,

You have to add, in a second line (to make a OR condition)

IS NULL


In SQL view, that should make something like:

..... WHERE .... ( dog IN( SELECT TOP 20 ... ) OR dog IS NULL ) ....




If dog is not in the races table, its contribution in the result is a NULL.
Not that in that case, the "top 20" dogs, plus those not in the races will
be listed. The dogs present in the races table, but no in the top 20, WON'T
be listed.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Genius

Hi,

You have to add, in a second line (to make a OR condition)

IS NULL


In SQL view, that should make something like:

..... WHERE .... ( dog IN( SELECT TOP 20 ... ) OR dog IS NULL ) ....




If dog is not in the races table, its contribution in the result is a NULL.
Not that in that case, the "top 20" dogs, plus those not in the races will
be listed. The dogs present in the races table, but no in the top 20, WON'T
be listed.



Hoping it may help,
Vanderghast, Access MVP