Is this query possible?

  • Thread starter Thread starter newboy21
  • Start date Start date
N

newboy21

Please help me with my problem. I would like to join 2
tables together using a date field.

Table1 has 3 fields:
KeyName1, Name1, Date1.
1, fred, 1-Jan-04
2, adam, 4-Apr-04

Table 2 has 3 fields:
KeyName2, Name2, Date2.
1, Mary, 2-Feb-04
2, Anne, 3-Mar-04

What I want is a query that will end up with the 2 tables
joined together and sorted buy date:
Name1, Date, Name2
fred, 1-Jan-04, blank
blank, 2-Feb-04, Mary
blank, 3-Mar-04, Anne
adam, 4-Apr-04, blank

Is this possible in access, I dont realy want to use VBA,
if so how?
 
Dear Boy:

The term "join" has a very important meaning in queries which is
almost certainly what you want to do. The term for what you want to
do is based on the fact that what we do in queries is largely based on
set theory. The term, and the actual functional programming word
used, is UNION.

For what you want, the query could be written:

SELECT Name1, Date1 AS [Date], "blank" AS Name2
FROM Table1
UNION ALL
SELECT "blank" AS Name1, Date2 AS [Date], Name2
FROM Table2
ORDER BY [Date]

By the way, using "Date" as a column name is a bad idea because it is
a reserved word, the name of a function. It would be better to choose
something else. Otherwise, it must always be in square brackets to
avoid ambiguity in certain contexts, where it may BYITA (bite you . .
..)

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Thanks, this is just what I wanted but do I have to
insert it as SQL or could I do the same in Access, I am
still a beginner?
 
Dear Boy:

I assume by "Access" you mean the Query Design Grid. There is an
alternative view (within Access) for queries you can select, called
the SQL View. Use the selection in the list toward the upper left.
Pull down this list and select SQL, than paste in what I typed.

UNION queries cannot be represented in the Design Grid. You would
have to do this in SQL View.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top