One To Many to Many

[Lance via AccessMonster.com]

One:
I have a master list of companies with unique companyid (primary Key) for
each. This table is not to be updated. Looks like this:

1; abcCompany

One to many:
Another table has companyid (many), with requirements for the many. For
instance, company# 1 can be listed 8 times because it has 8 requirements,
whereas company#2 can be listed 5 times with 5 requirements. This table is
not to be updated. Looks like this:

1; abcCompany; exhibit form
1; abcCompany; annex form
1; abcCompany; confer form

Many to many:
I need to tie these two tables to an event table that has companyid, list all
requirements for that ID and then JO# for them. This is the table that I
wish to be updated and write the events to. Looks like this:

1; abcCompany; confer form, JO#123
1; abcCompany; annex form, JO#123
1; abcCompany; employment form, JO#123
1; abcCompany; confer form, JO#456
1; abcCompany; annex form, JO#456
1; abcCompany; employment form, JO#456

My problem is that the query is not updatable.

Does anyone have a solution?

 

[Jeff Boyce]

Lance

What query?

Without some idea of what you're using, it will be tough to guess what might
not be working about it.

And where -- where are you trying to use a query to update your table? In a
form?

More info, please...

Jeff Boyce
<Access MVP>

 

[Lance via AccessMonster.com]

Here is my query. I'm trying to use it in a subform that is on my main form.

SELECT tblVerificationCkList.Companyid, tblEventJOVerification.CustPkg,
tblEventJOVerification.[Recd /Completed?], tblEventJOVerification.Date,
tblEventJOVerification.[JO#], tblEventJOVerification.SSN
FROM tblEventJOVerification INNER JOIN tblVerificationCkList ON
tblEventJOVerification.Companyid = tblVerificationCkList.Companyid
WHERE (((tblVerificationCkList.Companyid)=[Forms]![frmDataEntry]!
[txtcompanyid]));


The subform will bring up a CKlist of requirements. This is a list that will
come up every time for each company with different requirements. I need to
join it to the Event table and from this list the User need to be able to
check off if the requirements are complete. This is why I need the query to
be updatable. The tblVerificationCkList table is the list and the
tblEventJOVerification table is updatable.

I'm building the tblEventJOVerification event table each time a data entry is
made. Each time the data entry companyname drop-down is clicked the
tblVerificationCkList of requirements, which are specific to that companyid,
populates in the subform. In that subform I need to write to the event table
the requirements from the tblVerificationCkList, the JO# and yes/no if
completed from the tblEventJOVerification.

I appreciate any help / advice with this....

Lance