Combine tables to show date 2

[Dophia]

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

 

[SkipVought]

HI,

What "other dates?"

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

[pre]
select * from tblMain
Union all
Select * from tblOne
Union all
Select * from tbltwo
[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dhookom]

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 [highlight #CC0000] Preview[/highlight] doesn't line up in columns, please try again before posting.

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

Duane
Hook'D on Access
MS Access MVP

 

[Dophia]

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_twner
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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[randy700]

I would combine the tblAdopted and tblReturned_twner tables:
[pre]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[/pre]

Randy

 

[dhookom]

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:

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

 

[Dophia]

Thank you everyone for your help! It is very much appreciated.

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

 

[dhookom]

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
[pre]AnimalID
TransactionDate
TransactionStatusID (linked to a table with values like intake, out for adoption, back to owner, etc)
TransactionComments
StaffPersonID[/pre]


Duane
Hook'D on Access
MS Access MVP

 

[Dophia]

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

 

[dhookom]

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

 

[Andrzejek]

You may also consider combining Skip and randy700's ideas into:
[tt]
select ID, DateLeft, 'Adopted' As Reason
from TblAdopted
Union all
Select ID, DateLeft, 'Returned to Owner'
from tblReturned_twner
Union all
Select ID, DateLeft, 'Other'
from tblOther
Union all
Select ...
[/tt]

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.

 

[Dophia]

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


Sophia