Member Avatar for army88m2

Can anyone steer me in the right direction here as to how I go about creating a form in access based on more than one table.

  • 3 Contributors
  • 2 Replies
  • 236 Views
  • 3 Weeks Discussion Span
  • Latest Post Latest Post by slurpee55

Dani AI

Generated

Brief summary and practical next steps based on the replies from , and .

Two common, reliable patterns:

  • For a single record per form where fields come from multiple tables, build a saved Select query that joins the tables on their key fields and use that query as the form’s RecordSource.
  • For one‑to‑many data (for example: Customers → Orders), use a main form bound to the “one” table and a subform (continuous or datasheet) bound to the “many” table; set LinkMasterFields and LinkChildFields to the primary/foreign key pair.

Practical checklist (applies to either approach):

  1. Define the primary key / foreign key relationship in Database Tools → Relationships. Enforce referential integrity if appropriate.
  2. Create a Select query that pulls only the fields needed and test it in Datasheet view to verify results and avoid accidental Cartesian products.
  3. Save the query and set the form’s RecordSource to that saved query (or paste the SQL into the form’s RecordSource property).
  4. For master/subform, place the subform control on the main form and set LinkMasterFields/LinkChildFields (typically the PK/FK names).

Sample SQL (one-to-many view):

SELECT C.CustomerID, C.Name, O.OrderID, O.OrderDate
FROM Customers AS C
LEFT JOIN Orders AS O
  ON C.CustomerID = O.CustomerID;

Troubleshooting notes and cautions:

  • If the form is not editable, the underlying query may be non-updateable (GROUP BY, DISTINCT, UNION, aggregates or certain joins can cause this). Use subforms for editable one‑to‑many scenarios.
  • Duplicate rows usually mean the join condition is missing or incorrect.
  • Index the join fields and keep the query narrow for performance.
  • Prefer integer PK/FK keys and use combo boxes (bound to the ID) for lookups rather than storing text lookups in the main table.

Recommended Answers

All 2 Replies

Member Avatar for Kegtapper

if the db is relational try creating an SQL statement, then have the form linked to the new statement.

Member Avatar for slurpee55

You need to have some matching item between the two tables - a unique ID is best, but you can use other things, as long as they are unique. Then make a query based on the two tables linked by the ID or whatever. In the query, have all the fields you need from the two tables - if that means everything, that is fine.
Then make the query the record source for your form.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.