Hot to intersect two tables

[Carlos]

Hello, i am a newbie to access, it is possible intersect this two tables in
a new table

The table T1

Data as DATE
EmpN1 as LONG
EmpN2 as LONG

an table T2

EmpN as LONG
Int as STRING
e.g

T1: | #2001-01-01# | 10 | 20 |
| #2001-01-02# | 11 | 21 |

T2: | 10 | A |
| 11 | B |
| 20 | C |
| 21 | D |

Result: | #2001-01-01# | A | C |
| #2001-01-02# | B | D | how i intersect this tables into new
table(Result)

thanks

carman

 

[Marshall Barton]

Hello, i am a newbie to access, it is possible intersect this two tables in
a new table

The table T1

Data as DATE
EmpN1 as LONG
EmpN2 as LONG

an table T2

EmpN as LONG
Int as STRING
e.g

T1: | #2001-01-01# | 10 | 20 |
| #2001-01-02# | 11 | 21 |

T2: | 10 | A |
| 11 | B |
| 20 | C |
| 21 | D |

Result: | #2001-01-01# | A | C |
| #2001-01-02# | B | D | how i intersect this tables into new
table(Result)

SELECT T1.Data, T2A.Int, T2B.Int
FROM (T1
INNER JOIN T2 As T2A
ON T1.EmpN1 = T2A.EmpN)
INNER JOIN T2 As T2B
ON T1.EmpN2 = T2A.EmpN

 

[Carlos]

Thank's Marshall Barton

It work with e.g but the original TABLE (T1) as 365 row with variant
columns.

Exist another way to conjugate the two tables .

Thanks

carlos

 

[Marshall Barton]

If there are only a few columns, you can add more Joins
following the same pattern.

In general, you can not do this kind of thing with an
unnormalized table. The query language was created based on
the rules of relational database theory and you are creating
a lot of trouble for yourself when you violate those rules.

Your desired result set is not normalized either so it would
only be appropriate fir use in a complex report. If you are
doing this to prepare data for a report, there are better
ways.

It is very important to keep in m,ind that a table in a
relational database is nothing like a spreadsheet and you
should never try to design a table as if it were a
spreadsheet.

 

[Jerry Whittle]

If you table T1 looked like so, it would be easy to do with a crosstab query.

T1: | #2001-01-01# | 10 |
| #2001-01-02# | 11 |
| #2001-01-01# | 20 |
| #2001-01-02# | 21 |

While it's possible to get the result with the data presented using some
rather convoluted SQL, it wouldn't take much to break it. At the very least
you need to redesign your T1 or even more likely need a third bridging or
joining table to get consistant results.