CODEWITHSUNDEEP
ms-access
Tmyers
Tmyers

Reputation: 93

How to Design Query for Two Tables Not Directly Related

With my limited knowledge of Access, I have been struggling to figure this one out.

I have 4 tables. tblJobDetails, tblDrawings, tblDrawingFixtureType and tblFixtureType. They are related to each other in that order.

What I have been trying to do is a query based from tblFixtureType. I want my users to have a datasheet where they can input all the fixture types for the given job, but keep them under that particular JobID. I have not had much luck in that department. All queries I have made either show every single type entered in the DB, or nothing.

The JobID is the PK for tblJobDetails and is a FK in tblDrawings. tblDrawingFixtureType is intermediate/junction table that is meant to hold quantities, but has DrawingID and TypeID as its FK's.

So how would I correctly build a query for my users to input all fixture types (designations), but keep them assigned/filtered under that particular JobID?

I am sure more info will probably be needed, so please ask.

Upvotes: 0

Views: 40

Answers (1)

Albert D. Kallal
Albert D. Kallal

Reputation: 49329

You don't build a query to solve this problem. What you do is build a form based on each separate table.

In Access you model the parent to child relationships by using form + sub-form combos.

So, you might have a form that say lists out job (continues form).

You click on a row, and then launch a form with a nice display of the job details, and then in the sub form, you allow entry of tblDrawings.

The sub form will hook up the FK. And to edit/select the tblDrawingFixtureType, that drives a combo box in that sub form to select the fixture type, and it will save the PK of FixtureType in the one column.

All and any of each form (or even the sub form) is to be based on ONE table, and NOT a query. You don't edit data in tables, you build a form based on the table. If there is child records to add to that one record, then you build a sub form (and again, that sub form is based on ONE table).

Not only does this mean you don't have to create any queries, but you also don't have to write any code since when using a form + sub form, you get editing of related data that way.

Upvotes: 1

Related Questions