Help...Many to Many Form/Subforms....I have a headache!

[akeller]

Here is a little back story.



I work for a manufacturing company in the quality control department. Our department did not really have a sound way of tracking incoming inspection jobs and non conformances so I took it upon myself to create log sheet in Excel. This has been working fine but I knew from the beginning that using excel was not the way to go but it was fast and temporary.

I began trying to learn Access while at work to hopefully create a sound database. Well, my supervisor noticed this and for some reason told his bosses about it and was trying to promote himself, me, and the department for contiuously improving. It pisses me off because I'm such a newbie and now that he told everyone, I feel they are expecting instant results.

So now I have to show off what I have on Monday morning to upper level Managers and could really use some help with my current issue.

We have groups of parts in tote pans that arrive in our department for inspection. Each tote can contain many of the same parts, all with unique work order numbers and serial numbers.

The goal is to use access to "log in" a "group" or "tote" of parts and have them in queue for inspection. I currently have it working but each part is listed separately which is not good. I need the parts to be associated with a "group" or "tote" so that an inspector can claim a job just one time. It would take forever to claim every part individually.

I finally realized that I need to create a many to many relationship by creating a form on based on the one side and link a subform to the many junction table.
I have tried this with the subform based on the table AND a query and neither one is working. I worked on this for over 12 hours yesterday with zero results.

Attached is a photo of my relationships and I also have a "stripped down" version I can upload if needed.

Please Help!

[orange]

Each tote can contain many of the same parts, all with unique work order numbers and serial numbers.

You mention tote and inspector in your description, but I don't see these in your model??? How are "totes" identified?
Workorder, LotNumber, PartNumber, SerialNumber
InspectionResult ---??Pass/Fail/ReWork???

I don't mean to slow you down. You know generally what you need to do for Monday and you know your environment.
Good luck.

[davegri]

For the many-to-many, try having a form/subform setup with tblIncomingJob on the main form and IncomingJobJoin as the subform.
You'll need a lot of comboboxes on the main form to select the FK fields, and one combobox on the subform to choose the workorder.

Edit: That's two comboboxes on the subform as I didn't originally notice the priority.

[akeller]

You mention tote and inspector in your description, but I don't see these in your model??? How are "totes" identified?
Workorder, LotNumber, PartNumber, SerialNumber
InspectionResult ---??Pass/Fail/ReWork???

I don't mean to slow you down. You know generally what you need to do for Monday and you know your environment.
Good luck.

Essentially the "totes" are the IncomingJob table. I'm trying to link one incoming job to multiple work orders.

As for the inspection reults, I was planning on tackling that once I'm able to group the IncomingJob to WorkOrders.
I do have the tables and relationships for those created already but I unrelated them until I get passed this issue.

I'm hoping to get at least this working for the "Show and Tell" and then I can then get back to learning and not have any more pressure.
I think I'm going to tell my supervisor to never mention another word about it until it is completed and actually working. Lol

[akeller]

For the many-to-many, try having a form/subform setup with tblIncomingJob on the main form and IncomingJobJoin as the subform.
You'll need a lot of comboboxes on the main form to select the FK fields, and one combobox on the subform to choose the workorder.

Edit: That's two comboboxes on the subform as I didn't originally notice the priority.

That is how I currently have it setup, with a lot of combos on the Incoming Job and it works. The problem is that when I try using the subform to join the data, nothing is being added.

Do you have any idea how I could have one main form (frmIncomingJobs) to create the first job, then a sub form (frmWorkOrders) to add the orders, and then finally the last linking subform (IncomingJobsJoin)? It would be ideal to be able to input all this data on one form instead of two or more.

[davegri]

Do you have any idea how I could have one main form (frmIncomingJobs) to create the first job, then a sub form (frmWorkOrders) to add the orders, and then finally the last linking subform (IncomingJobsJoin)? It would be ideal to be able to input all this data on one form instead of two or more.

That's the functionality of the scenario that I described. Post your DB and we can take a look.

[akeller]

That's the functionality of the scenario that I described. Post your DB and we can take a look.[/COLOR]

Attached is the "Stripped Down" version.

Don't mind all the errors that pop up, they are linked images and are stored on my work computer.

When it loads and opens the Log In screen, just right click and go into Design view to bypass.

There are multiple tables that I had originally been working with but then realized I needed to create different relationships for the many-to-many.

The tables and forms I have been messing with for the many to many are all in the "Unassigned" Area.

Thanks!

[akeller]

For the many-to-many, try having a form/subform setup with tblIncomingJob on the main form and IncomingJobJoin as the subform.
You'll need a lot of comboboxes on the main form to select the FK fields, and one combobox on the subform to choose the workorder.

Edit: That's two comboboxes on the subform as I didn't originally notice the priority.

I tried the two combo boxes on the subform. The Incoming Job one displays information, but does not auto populate. I'm unable to get the priority combobox to display anything. I will keep trying.

If my subform is to be for data entry and is based on a query, will the select query work for this?

Sorry for the trouble, like I said before, I'm a newbie.

[davegri]

NewStructure-davegri-v01.zip
Have a look at this.
Big problem was no duplicates allowed for the foreign keys in the join table. On the many side FKs HAVE to be duplicates allowed, no?
Added 2 forms and a subform. Had to duplicate a couple of tables. All the stuff I added has suffix _D.

[akeller]

NewStructure-davegri-v01.zip
Have a look at this.
Big problem was no duplicates allowed for the foreign keys in the join table. On the many side FKs HAVE to be duplicates allowed, no?
Added 2 forms and a subform. Had to duplicate a couple of tables. All the stuff I added has suffix _D.

Wow....You're a life safer, thank you so much!

I'm going to start reviewing everything now. I will post back in this thread if I have more questions.
This definitely makes me feel better about Monday and I will credit you to the managers.

Thanks again!

[akeller]

NewStructure-davegri-v01.zip
Have a look at this.
Big problem was no duplicates allowed for the foreign keys in the join table. On the many side FKs HAVE to be duplicates allowed, no?
Added 2 forms and a subform. Had to duplicate a couple of tables. All the stuff I added has suffix _D.

How would I go about structuring a query as a record source for another form to display the linked data within the IncomingJobJoin table?

I built one with all the data I needed and it works, however it is displaying every joined record.

I would like it to only display one record per "IncomingJob" but show a quantity or WorkOrders that are joined. Would that be done with "Group By" in the query?

As an example, I created a new "IncomingJob" that has 5 "Work Orders". The current continous form (shown in the picture) lists each Job 5 times. I want it to show only once.

[akeller]

I think I figured it out...Group By worked. For now at least.