CODEWITHSUNDEEP
ms-access
J. Andersen
J. Andersen

Reputation: 1

Query two separate fields from one field in a table

I have a ms access database with two tables I am concerned with.

Table 1
       ...[field 1] "Name" text
       ...[field 2] "IDBorn" number
       ...[field 3] "IDDied" number
Table 2
       ...[field 1] "IDDate"  number
       ...[field 2] "dtISO"   text of date

"IDBorn" and "IDDied" are related to "IDDate"

My question. Can I query (in one query) to get 

...[field 1] "Name"  text
...[field 2] "Born On" text of born on date
...[field 3] "Died On" text of died on date

I have not been able to get a successful UNION query to work. Is that the way to go?

Upvotes: 0

Views: 61

Answers (2)

ClintB
ClintB

Reputation: 499

While yes this is a bad example of where you want to have a lookup table for some data in your main table (You should just use the date field), Jorge Londoño's answer above is the correct way to query it.

You can also Open the table in design view. Select the field "IDBorn" Click on the Lookup tab in properties.

Display Control = "Combo Box".

Row Source Type = "Table/Query".

Row Source =

SELECT T1A.IDBorn,Name 
FROM [Table 2] T2 INNER JOIN [Table 1] T1A  on T2.IDDate = T1A.IDBorn

Bound column = "1"

Column Count = "2"

Column Width = "0;20"

This will show the actual value instead of the lookup value. Then depending on what you are doing with the table you may not have to do this query every time you want to get the data out of the table.

Upvotes: 0

Jorge Londoño
Jorge Londoño

Reputation: 608

your need create two inner join for each field

select Name, T1A.IDBorn, T1B.IDDied 
from [Table 2] T2
inner join [Table 1] T1A
    on T2.IDDate = T1A.IDBorn
inner join [Table 1] T1B
    on T2.IDDate = T1B.IDDied

Hope this can help you

Upvotes: 1

Related Questions