Query for all data from a Form with Subforms

[lyonder2000]

I have a database that I developed that will have forms with subforms. I also have an export feature that will export the data from the respective table. However, I didn't realize that the subforms throw a twist on a simple SELECT * query.

What is the best way to export out all the data from the main table and the subform tables for each record and include all the data, but avoid duplicates.

 

[Remou]

That would depend on what you mean by duplicates and how you want the data to appear. You can export a query or series of queries based on the main table and the subtable(s). For example, you could build a query along these lines:

[tt]SELECT tm.A, tm.B, ts.C, ts.D
FROM tm
INNER JOIN ts
ON tm.UniqueKeyFieldName=ts.ForeignKeyFieldName
WHERE tm.UniqueKeyFieldName=SuitableReferenceToForm[/tt]

 

[lyonder2000]

If you have more than one subform, but you have the uniquekey in each table, is it possible to create joins between all tables to retrieve all the data from the tables.

 

[dhookom]

If you have a main table with multiple related tables each with possibly multiple records linked to the main table, you can't join them all to create a single query. For instance, if you have these tables:
[tt][blue]
Customers
CustomerID
-- other fields --
Orders (multiple orders per customer)
OrderID
CustomerID
-- other fields --
Contacts (multiple contacts per customer)
ContactID
CustomerID
-- other fields --
[/blue][/tt]
There is no way to create a decent query from this that includes all customers with all orders with all contacts without getting duplicate field values. You would need to create at least two queries.

Duane
Hook'D on Access
MS Access MVP

 

[lyonder2000]

That's what I thought.
Thanks for the information.