Many to Many Join

[ArthurJ]

I am new to Access so bear with me here.

I have a Job and a PO table. There is a many to many relationship between
job number and purchase order number. I have created a relational table
(JobPO) between the Job and PO tables, as per textbook.

Here is an abbreviated list of the tables/fields I am working with:
Job.JobNbr
JobPO.JobNbr
JobPO.POnbr
PO.POnbr
PO.Date

Then I created a query showing all these fields. In datasheet view I enter
my first Job.JobNbr. Then I enter the PO number into JobPO.POnbr. The
JobPO.JobNbr field immediate displays the same PO number - that's good, just
what I expected.

But I cannot get any futher by entering the date into PO.Date. I get en
error message saying something like there needs to be a matching number in
the PO.POnbr field. I understand that, but how do I get it there?

Jobs are "opened" after we receive a PO, and I want to design forms etc.
that have the Jobs table as primary with a subform allowing entry of PO
details. Some PO's are separated into two or more jobs, and some PO's from
the same customer are combined into one job.

Help please.

Art

 

[ArthurJ]

Correction: In my first post I wrote ...
The JobPO.JobNbr field immediate displays the same PO number.
I meant to say "same Job number". To be more precise I should have said ...

The JobPO.JobNbr field is immediately populated with the correct Job number.

Art

 

[BruceM]

A many-to-many relationship in your situation would be described something
like:
Each PO has many associated Job Numbers, and each Job Number is associated
with many POs. Is this your situation?
In a many-to-many situation one of the main tables is fairly static. For
instance, in a training records database there could be a table for employees
and another for training sessions. Since each employee can attend many
training session and each session can be attended by many employees, that is
a many-to-many relationship between employees and sessions, so a junction
table is needed to resolve the relationship. The junction table may be
something like Enrollment. A main form could be based on the Sessions table,
and a subform based on the Enrollment table. A combo box on the Enrollment
subform would be a good way of selecting names from the Employees table. The
effect would be that you could add employees to a training session, and see a
listing of those employees on the subform. If there is a new employee then
that person would need to be added to the Employees table before you could
select their name from the combo box on the Enrollment subform. Months could
go by wthout adding a record to the Employees table, but during all that time
you could continue to record training session information. The Employees
table in this case is fairly static.

 

[ArthurJ]

Thanks Bruce.

You asked "Is this your situation?" Yes, exactly.

Using your example, you say "If there is a new employee then
that person would need to be added to the Employees table before you could
select their name from the combo box on the Enrollment subform." This is a
helpful insight, because my desire was to be able to add the new employee
from the form (or subform). Going back to my example, the receipt of a PO
immediately triggers opening one or more jobs. PO's will never set for even a
day without giving rise to jobs, unlike employees who may not get assigned to
training right away.

I think perhaps what I wanted to do is impossible. I must populate the PO
table directly before attempting to associate a PO with a new job number
through a form based on the job table. Does this still sound right to you
(and unfortunate for me)?

Art

 

[John Vinson]

Then I created a query showing all these fields. In datasheet view I enter
my first Job.JobNbr. Then I enter the PO number into JobPO.POnbr. The
JobPO.JobNbr field immediate displays the same PO number - that's good, just
what I expected.

You're using the wrong tool.

Query datasheets are not appropriate for data entry or editing.
Instead use a Form. In this case, it would be appropriate to have a
Form based on Job, with a Subform based on JobPO. On this Subform you
could have a combo box allowing the selection of valid PO numbers from
the PO table.


John W. Vinson[MVP]

 

[ArthurJ]

John,

I did start out as you suggest, with a form based on Job, and a Subform
based on JobPO. But the subform would not work. That's when I switched to the
query, simply because I find it easier to mess with and experiment. Won't the
query reveal the same underlying relational principles (or problems) as the
form/subform?

Could it be that I need to alter the type of joins in some way?

Thanks for your help. Don't give up on this guys! I've got new hope now that
what I want CAN be done.

Art

 

[ArthurJ]

"On this Subform you could have a combo box allowing the selection of valid
PO numbers from the PO table".

John, I want to ENTER the PO numbers in the subform, not select them. Is
this possible?

Art

 

[BruceM]

Then each PO can have multiple jobs associated with it, but a any single job
will be associated with just one PO? In that case you have a one-to-many
relationship between POs and jobs. You need to enter something first, and
since it seems you start with a PO it makes sense to enter that information
first.
Let's say that PO_Number is the primary key in the PO table, and that its
data type is Number. In that case you need PO_Number as a field (data type
Number) in the Jobs table. It is called the foreign key once the
relationship is established (more in a moment). It doesn't need to have the
same name as the primary key field, but it makes things easier if it does,
IMHO. At Tools > Relationships you would drag one field onto the other and
click Enforce Referential Integrity. Select the PO table from the database
window (the window showing tables, queries, etc.) and click the Autoform icon
on the toolbar. If the result gives you something that approximates the
functionality you need, post back for more information. I won't go into a
lot of details here in case I am completely off track. I suggest autoform
not because it is the only or best way to go, but because it will give you a
functional form in a hurry so you can test your work.
Remember that each job can have any number of associated records as well.
Just as jobs is a subform of POs, a JobDetails table can be related to the
Jobs table, and a form based on JobsDetails can be a subform of the form
based on Jobs. This is sometimes referred to as nested subforms.

 

[KARL DEWEY]

Sounds to me like you need three tables --
Job - JobNbr, JobDescription, Active
PO - PoNbr, Company, OrderDate, PlanDate, StartDate, EnDate, Remarks
PoJob - PoNbr, JobNbr, PlanDate, StartDate, EnDate, Remarks

Your main form would be the PO.

Your subform would be a datasheet view of the PoJob with parent/child links

 

[John Vinson]

"On this Subform you could have a combo box allowing the selection of valid
PO numbers from the PO table".

John, I want to ENTER the PO numbers in the subform, not select them. Is
this possible?

I guess I'm not understanding the business model.

Does each Job allow only one PO, or multiple POs?
Does each PO apply to only one job, or might a PO cover multiple jobs?
If a PO covers multiple jobs, how do you apportion what part of the PO
applies to which job?
What information do you need to record for a PO?


John W. Vinson[MVP]