One-to-many query

[Guest]

I have two tables, TableA and tables, which have a 1-to-many relationship on
TableA.ID and TableB.TableA_ID. What I want is to get a query which has all
the values from TableB into a cloumn in a query.

SQL Statement:
SELECT TableA.ID, TableA.Field, (SELECT TableB.TableA_ID FROM TableB WHERE
TableA.ID = TableB.TableA_ID)
FROM TableA;

gives errors because subquery cannot return more than one row. Is there a
way to do it with an SQL query?

 

[Jason Lepack]

I think you mean that you want this:
TableA:
ID, FieldA
1, Bob

tableB:
TableA_ID, FieldB
1, Tom
1, Stu

Is the result you expect this?
Query1:
ID, FieldA, TableA_ID, FieldB
1, Bob, 1, Tom
1, Bob, 1, Stu

or this?
Query2:
ID, FieldA, ConcatFieldB
1, Bob, Tom Stu

Query1 is just using a join of the two tables:
SELECT tableA.ID,
tableA.FieldA,
tableB.tableA_ID,
tableB.FieldB
FROM tableA
INNER JOIN tableB
ON tableA.ID = tableB.tableA_ID

Query2 uses a method called concatenation. Look Here:
http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'

Cheers,
Jason Lepack

 

[Guest]

I think what you are looking for is:

SELECT TableA.ID, TableA.Field, TableB.[Field1], TableB.[Field2]
FROM TableB
LEFT JOIN TableA
ON TableB.TableA_ID = TableA.ID

HTH
Dale

 

[Amy Blankenship]

I don't think you can run a Shape query from within Access, since you have
to have a Shape connection to the database to be able to run one. I think
that the "connection" you have when inside Access to the open database is
just an ordinary ODBC/Jet connection. Plus the tabular way Access
represents query results cannot represent a heirarchical query set.

If you want to run this query from outside access, you might want to try
looking here
http://www.4guysfromrolla.com/webtech/092599-1.shtml

HTH;

Amy

 

[Guest]

Thanks Jason. Concatenate Function was everything I needed.

Cheers