INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Combine tables to show date

Combine tables to show date

(OP)
Hello:
I have created a query from a few tables and have the dates from each table listed. How can I put all the separate dates in one field? The tables all have a Primary key in common.

Eg.

tblMain -IDNo, date
tblOne - IDNo, date_one
tbltwo - IDNo, date_two

Qry
IDNo, date, date_one, date_two ( need one field with the result from dateone, date, or date_two. The other dates will be nil)

Thanks for any help
Sophia

RE: Combine tables to show date

HI,

What "other dates?"

tblMain -IDNo, date
tblOne - IDNo, date_one
tbltwo - IDNo, date_two

select * from tblMain
Union all
Select * from tblOne
Union all
Select * from tbltwo
 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Combine tables to show date

Dophia,
Can you provide a few sample records with the desired output? I assumed you wanted multiple date fields and Skip thinks you need a single date field.

Please use TGML tags (Pre works well) to format your data so it's easier to understand like the following. If the Preview doesn't line up in columns, please try again before posting.

tblMain
IDNo      Date
123       4/21/2015
234       5/3/2015
345       7/4/2015 

Duane
Hook'D on Access
MS Access MVP

RE: Combine tables to show date

(OP)
Thanks for your replies. The problem that I am trying to address is that when I set up the database, 10 years ago, I did not have a date field on the main table, which is causing me problems with new queries.

TblAnimals
ID
1000
1001
1002
1003
1004
1005

TblAdopted
ID-Date Left
1000 -Jan 1-15
1004 -Jan 3-15
1005 -Jan 5-15

tblReturned_to_Owner
ID-Date Left
1001- Jan 5-15
1002 -Jan 6-15

Query result wanted (which combines all three tables)
ID-Date Left
1000-Jan 1-15
1002-Jan 6-15
1003-NIl(still in shelter)
1004-Jan 3-15
1005-Jan 5-15

I want to be able to show the date that the animal left the shelter, whether it was by adoption, returned to owner, etc.

Sophia

RE: Combine tables to show date

Well this is a whole lot more than simply "How can I put all the separate dates in one field?"

Please specifically state your LOGIC requirements, so that we don't have to guess what you want from your meager example.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Combine tables to show date

I would combine the tblAdopted and tblReturned_to_Owner tables:
ID  DateLeft  Reason
1000  1/1/15  A
1001  1/5/15  R
1002  1/6/15  R
1004  1/3/15  A
1005  1/5/15  R 

Randy

RE: Combine tables to show date

Dophia,
Please learn how to use the text formatting available in this site.

Your "Query result wanted (which combines all three tables)" seems to me missing ID 1001.

Based on your data, you can use a query with SQL of:

CODE --> SQL

SELECT TblAnimals.ID, Nz([tblAdopted].[Date Left],[tblReturned_to_Owner].[Date Left]) AS DateLeft
FROM (TblAnimals LEFT JOIN TblAdopted ON TblAnimals.ID = TblAdopted.ID) 
LEFT JOIN TblReturned_to_Owner ON TblAnimals.ID = TblReturned_to_Owner.ID; 

Duane
Hook'D on Access
MS Access MVP

RE: Combine tables to show date

(OP)
Thank you everyone for your help! It is very much appreciated.smile

Duane, your suggestion worked, but I have more than three tables and I got an error message saying that there were too many conditions.

Skip, a Union query worked. For anyone with this problem, you should Google how to create a union query.

Randy, I don't know what you mean by combining the tables or how to do that.

Finally, Duane, I did try to align my last post. Can you tell where to look to find out how I can use Text formatting and TMGL tags.

Also, I tried to state my original question very basically, to not take up too much of your time, but I can see it was not complete. Sorry.

Sophia

RE: Combine tables to show date

Sophia,

Formatting text in replies is much the same as formatting in a word processor. Select the section of text you want to format and then click a button above the input window.

IMO, you shouldn't have more than one table for animal transactions. There needs to be the
tblAnimalTransactions
AnimalID 
TransactionDate
TransactionStatusID (linked to a table with values like intake, out for adoption, back to owner, etc)
TransactionComments
StaffPersonID 

Duane
Hook'D on Access
MS Access MVP

RE: Combine tables to show date

(OP)
Thank you Duane.
It is more involved then the basic example that I gave you. There is only one table for animals, but there are different tables for other associated events involving them, such as adoption.

Sophia

RE: Combine tables to show date

Sophia,
Has your issue been resolved? I expect Skip's union query of the "different tables for other associated events involving them" is the best starting place. The union query normaizes your data so you can more easily query it.

Duane
Hook'D on Access
MS Access MVP

RE: Combine tables to show date

You may also consider combining Skip and randy700's ideas into:

select ID, DateLeft, 'Adopted' As Reason
from TblAdopted
Union all
Select ID, DateLeft, 'Returned to Owner'
from tblReturned_to_Owner
Union all
Select ID, DateLeft, 'Other'
from tblOther
Union all
Select ...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Combine tables to show date

(OP)
Yes, it has been resolved. I indicated above that I used the Union query and it worked.
Thank you all for your help!


Sophia

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close