Access 2000 query help (please!?)

[Josh C.]

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

 

[Roger Converse]

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

 

[John W. Vinson]

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]