Hi there guys,

I am trying to devise a query and its just not coming to me. I'm hoping
someone out there can help.
I am trying to create a query that will give me the distinct value from two
columns in the same table. Here is what its like:

Table1: Schools
Columns: school_id, school_abbrev, school_name

Table2: Travel_Log
Columns: log_to, log_from (these are numerics that tie to school_id above)

So I want to create a list of all the schools visited in the travel log, so
I want the distince values of log_to and log_from and then list them out with
their abbreviations and full names in the list. If it were just a distinct
value of one of those columns it would be easy, but I can't figure out how to
get both to work.

I hope that made sense. I would really appreciate any suggesstions.

Thanks in advance,

Josh

Hello,

Your post is somewhat confusing...

....so I want the distince values of log_to and log_from...

Did you mean distinct?

I would run a few different steps here. First would be to query your school
ID by log_to grouping on both and then the same query with log_from (no
log_to). Then I would create another query that finds the schools in common
(a log_to and log_from) entry and combine those. Lastly run an unmatched
query and append any unmatched records.

I am not sure you can do what you are trying to do with just one query, but
that should get the job done.

HTH
Thanks,
Roger

"Josh C." wrote:

> Hi there guys,
>
> I am trying to devise a query and its just not coming to me. I'm hoping
> someone out there can help.
> I am trying to create a query that will give me the distinct value from two
> columns in the same table. Here is what its like:
>
> Table1: Schools
> Columns: school_id, school_abbrev, school_name
>
> Table2: Travel_Log
> Columns: log_to, log_from (these are numerics that tie to school_id above)
>
> So I want to create a list of all the schools visited in the travel log, so
> I want the distince values of log_to and log_from and then list them out with
> their abbreviations and full names in the list. If it were just a distinct
> value of one of those columns it would be easy, but I can't figure out how to
> get both to work.
>
> I hope that made sense. I would really appreciate any suggesstions.
>
> Thanks in advance,
>
> Josh
>
>

On Wed, 9 Jan 2008 05:16:02 -0800, Josh C. <(E-Mail Removed)>
wrote:

>Hi there guys,
>
>I am trying to devise a query and its just not coming to me. I'm hoping
>someone out there can help.
>I am trying to create a query that will give me the distinct value from two
>columns in the same table. Here is what its like:
>
>Table1: Schools
>Columns: school_id, school_abbrev, school_name
>
>Table2: Travel_Log
>Columns: log_to, log_from (these are numerics that tie to school_id above)
>
>So I want to create a list of all the schools visited in the travel log, so
>I want the distince values of log_to and log_from and then list them out with
>their abbreviations and full names in the list. If it were just a distinct
>value of one of those columns it would be easy, but I can't figure out how to
>get both to work.
>
>I hope that made sense. I would really appreciate any suggesstions.
>
>Thanks in advance,
>
>Josh
>

I think you need to add the Schools table to the query *twice* - once linked
to log_from and once linked to log_to. The SQL might be

SELECT Table2.<whatever you want to see>, SchoolFrom.School_abbrev AS FromA,
SchoolFrom.School_Name As FromN, SchoolTo.School_abbrev AS ToA,
SchoolTo.School_Name AS ToN
FROM (Table2 INNER JOIN Table1 AS SchoolFrom ON Table1.log_from =
SchoolFrom.school_id) INNER JOIN Table1 AS SchoolTo ON table1.log_2 =
SchoolTo.school_id);

John W. Vinson [MVP]