having trouble implementing many to many

[poppet]

hi,
i have been working for a long time trying to develop a database for the university that i work for. i have managed to get quite far but keep tripping up on one or two issues. the first issue is as follows:



i have been using the following youtube video as a basis for implementing my many to many relationship but my when i try to implement a copy of what he is working on there is a subtle difference. this may of course be because we are using different versions of access. i am using access 365.

here is the video:
https://www.youtube.com/watch?v=Ou1z...Ysl3DCX&t=201s

the problem that i have is that at 4:56 he removes customer id from a sub form he is creating. then at 5:23 customer id does not appear in the sub form (good). then at 6:14 while inserting the subform in his form and creating the link the customer id is present!

on my version the customer id is missing because of the previous removal (and i cannot complete the linking).

this has led me to not remove customer id but then i get customer id appearing in my subform which i don't really want.

are you please able to tell me what action i should take in access 365 to get past this issue?

david.

i have just updated my settings; i am access 365 and windows 10.

[Bob Fitz]

Can you post a copy of your db with a few records to show the problem

[davegri]

The subform recordsource is qrySubOrders. The fields in the query are available to use even though they may not be included in the subform display fields.
You CAN include the fields if you wish, and simply set the visible property to NO in the subform. Either way.

[poppet]

hi bob,
you asked for it...

essentially there is a one to one mapping between performance criterion and assessments.

the main form is: 2 mapping. if you bring up this form it looks like what i want it to do; link a performance criteria id to one or more assessments. if you enter an assessment, such as basic skills, it works and the assessment type appears (great so far).

but you then get an error relating to the junction table which says: you must enter a value in the 'assessments pc.PerformanceCriteriaId' field that i just don't understand.
thank you!

https://1drv.ms/f/s!Atb7fd9Ztrl8gsMrphF0YdOsisBjjg
d.

[poppet]

hi davegri,
i just saw your msg. thanks for replying.
i will have a look tomorrow.
d.

[davegri]

unit to assessment mapping-davegri-v01.zip
Have a look at this. Replaced the subform. In a Many-to-many setup, the main form supplies one side of the junction table keys and the subform supplies the other.
You had both linking keys in the junction table set as Primary Keys. Removed that property and added a dedicated primary autonumber key.
Regarding autonumber primary keys, the one I added is the only one in the entire database. I recommend that you study your database design and add autonumber primary keys to EVERY table and use those in the relationships.

I don't know what else you want to display in the subform, but hope you can work it out.

[orange]

poppet,
Can you step back a little and describe to readers what you are assessing in a few lines of plain English?
Just looking for simple overview of what you are trying to automate.

I often reference or recommend the videos by Dr. Verschuuren. His tutorials are quite good, but I have found that he sometimes overviews things and skips some details --probably intending for readers/viewers to buy his CD/DVD.

As davegri suggested, you can have fields on the subform and just not display them.

Good luck with your project.

[poppet]

hi davegri,
that looks great.

if your solution is the preferred way to implement many to many i am happy to work with it. whatever works.

i had no idea how to implement many to many and i was using that video as a basis. he actually shows the 2 keys in the junction table. i suppose i am a bit confused that there is no 'industry standard' on how to implement m2m.

also, i thought i was doing a good thing trying to reduce the autonumber PKs. Surely if you have a unique set of entries (as mine are) then it is ok to use for them for the PK?

if you don't mind me saying, the subform xxxsubfm does not work.
(and yes i have not specified a brief here because my original question related to the video)
but in xxxsubfrm if you try to specify an assessment type it comes up with only BMI which is actually an assessment title. the assessment types are: exam, observation, practical, quiz.

lastly, when i tried to create a subform like yours, when i then tried to embed it into my main form i still had trouble with the linking. there are no common fields to make the link as i said before. please see picture.
(maybe i have an issue with my ms access app).


anyway, sorry for writing so much.
thank you for your help.

[davegri]

also, i thought i was doing a good thing trying to reduce the autonumber PKs. Surely if you have a unique set of entries (as mine are) then it is ok to use for them for the PK?

If you can make that work for you, it's fine. Just not my cup of tea to use the data fields as keys.

if you don't mind me saying, the subform xxxsubfm does not work.

That's your original subform. I replaced it and changed its name to xxxx to say "NO GOOD!"

there are no common fields to make the link as i said before.

You have to make it so:

[poppet]

hi davegri & all,
thank you for your comments i am beginning to see how it will all fit together now that i have the new PK structure.
believe it or not now i have had some guidance i returned to my version and got that to work as well.
that video is very hard to follow! it was asking the developer to create a query before creating the subform not sure why.
d.