query from 2 tables

[maggie]

Table 1 has a field called "father first name", one
called "father last name", and one called "father
address". Table 2 has a field called "grandfather first
name", one called "grandfather last name", and one
called "grandfather address". The two tables can be
joined together by a field called "student number", as
they are related to the same student. I want a query
that will take the father information (from table 1) and
put it into fields called "first name", "last name",
and "address". So far, not a problem. I can do this
part. But now I also want the grandfather information to
be to be put into these same fields (first name, last
name, address) as a separate record from the father. How
would I write the query so that I get the father's
information on one line of the query result, and the
grandfather information on another line of the query
result? But into the same field names? I hope this
makes sense. Your assistance is appreciated.

 

[Gerald Stanley]

A UNION query should be just what you need. E.g.

SELECT studentId, 1 AS recType, [father first name] AS
[first name], [father last name] AS [last name], [father
address] AS [address]
FROM [Table 1]
UNION
SELECT studentId, 2 , [grandfather first name],
[grandfather last name], [grandfather address]
FROM [Table 2]
ORDER BY studentId, recType

A couple of notes if you are unfamiliar with UNION queries.
There have to be the same number of columns returned by
each part of the union and the datatype has be consistent
for a column in all parts of the union.

I have included a constant column (recType) in case you
need to know which part of the union a particular row came
from. I have also added an ORDER BY clause so that you get
both lines for a student to appear consecutively in the
results.

Hope This Helps
Gerald Stanley MCSD

 

[maggie]

Excellent. That worked beautifully! Thank you.

-----Original Message-----
A UNION query should be just what you need. E.g.

SELECT studentId, 1 AS recType, [father first name] AS
[first name], [father last name] AS [last name], [father
address] AS [address]
FROM [Table 1]
UNION
SELECT studentId, 2 , [grandfather first name],
[grandfather last name], [grandfather address]
FROM [Table 2]
ORDER BY studentId, recType

A couple of notes if you are unfamiliar with UNION queries.
There have to be the same number of columns returned by
each part of the union and the datatype has be consistent
for a column in all parts of the union.

I have included a constant column (recType) in case you
need to know which part of the union a particular row came
from. I have also added an ORDER BY clause so that you get
both lines for a student to appear consecutively in the
results.

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
Table 1 has a field called "father first name", one
called "father last name", and one called "father
address". Table 2 has a field called "grandfather first
name", one called "grandfather last name", and one
called "grandfather address". The two tables can be
joined together by a field called "student number", as
they are related to the same student. I want a query
that will take the father information (from table 1) and
put it into fields called "first name", "last name",
and "address". So far, not a problem. I can do this
part. But now I also want the grandfather information to
be to be put into these same fields (first name, last
name, address) as a separate record from the father. How
would I write the query so that I get the father's
information on one line of the query result, and the
grandfather information on another line of the query
result? But into the same field names? I hope this
makes sense. Your assistance is appreciated.
.

.