Please help me with an SQL query

[Guest]

the SQL below allows me to join 2 similar tables together by The Date field
and then show the results in date order, the problem I now have is that want
to query the USER_NAME from a NAME table using the NAME_CODE

SELECT Date, Dpt, "" AS NAME_CODE
FROM Reminder_Dates
UNION ALL SELECT Date, Dpt, NAME_CODE
FROM Reminder_Users
ORDER BY Date;

 

[Douglas J. Steele]

I don't understand why you'd need that Union if you want to search on a
specific Name, given that the first part of the Union doesn't provide a
name.

Can't you just do a normal query against Reminder_User?

 

[Guest]

The 2 tables are independant but have similar data fields but are NOT the
same, the only way I could list them in date order was to run the SQL and
merge them, it could not be done as a simple query. Now I need to convert the
NAME_CODE into the USER_NAME.

 

[Douglas J. Steele]

If you've saved that as a query, you can do lookups in it exactly the same
as if it were a table.

However, you still haven't explained why you can't just do your lookups
against Reminder_Users. I understand that Reminder_Dates contains
information not contained in Reminder_Users, but as soon as you try
searching on a User Name, you know it's not going to be in Reminder_Dates.

 

[Guest]

I have reduced the fields in the example to avoid confusion.
Table Reminder_Dates contains 2 fields, Date and Dpt
Table Reminder_Users contains 3 fields, Date, Dpt and Name_Code
Table Name contains 2 fields, Name_Code and User_Name
First I just wanted to merge the 2 reminder tables using the Date field, the
result would be a query in Date order ie:

12/Jan/04, Logistics, ID301
13/Jan/04, Finance,
14/Jan/04, IT,
15/Jan/04, Purchasing, ID302

Now what I want is, where there is a Name_Code also get the User_Name from
the Name table ie:

12/Jan/04, Logistics, ID301, John Smith
13/Jan/04, Finance,
14/Jan/04, IT,
15/Jan/04, Purchasing, ID302, Alan Smith

What I have done so far is to save the SQL query and then create another
simple query, but can’t this all be done in one query?