Using Joins in Access

[garym238]

I have a database which is Oracle, with an Access front end. I'm trying to determine if I can join multiple tables into a single row. The tables are...

Table A       Link Table      Table B         Table C

A1               A1-B1           B1             C1

                 A1-B2           B2

                 A1-C1

 

Is there a way to join all of those tables into a single record so it would look like....

A1 B1 B2 C1

Thanks for the help!

Gary

 

[Remou]

I am not quite sure what you want to do, or whether the dash indicates a second field in the Link table.

It may be possible to start with a query:

SELECT A.Field1, U.Field1 AS UField
FROM (A 
INNER JOIN (SELECT Left([Field1],InStr([Field1],"-")-1) AS LinkA, 
                   Mid([Field1],InStr([Field1],"-")+1) AS LinkB
            FROM Link) AS L 
ON A.Field1 = L.LinkA) 
LEFT JOIN (SELECT Field1
           FROM B 
           UNION ALL 
           SELECT Field1
           FROM C) AS U 
ON L.LinkB = U.Field1

And follow with a crosstab:

TRANSFORM First(Linked.UField) AS FirstOfUField
SELECT Linked.Field1
FROM Linked
GROUP BY Linked.Field1
PIVOT Linked.UField

http://lessthandot.com

 

[garym238]

Thanks for the response Remou.

The dash indicates there are 2 columns in the link table. The first column is the primary key of Table A, the second column is the primary key of either Table B or Table C, depending on the row. As an example, Table A is a list of hosts, Table B is a list of cpu's for that host, and there a 2 cpu's. Table C is the amount of memory for that host. I would like a single row for each host that lists the hostname, cpu's, and memory size.

Thanks!

Gary

 

[PHV]

Have a look here:
faq701-4233

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886