Many-to-many - Joint table and form (1 Viewer)

[Jimis]

ok found the solution
I was trying to use/change the PK of the table customer_JoinT linked to the subform but I should have used the custID in AssetsT and make this a combo box

So now I will need to make a new form for the user to change/add new customers in table customer_JoinT.

 

[mike60smart]

Hi Jim

Looks like we are back to where we started.

Nothing will work because your relationships are wrong.

You would never EVER use a Junction table to add records

 

[mike60smart]

Jim

Sorry to be rude but you are talking out the back of your head in regards to Relationships

If you look at the relationship between Assets and CustJoin_T - all you are able to do is look up a value from CustJoinT

Your Assets Table is the Main Form on which you can only create a Combobox Bound to CustID to add 1 value only

Even if you create a Form to add new Customers all you would be able to do is update a Customer or add a New Asset and add a Customer

 

[mike60smart]

Jim

It would be far far easier to just have a table for Assets and a table for Customers.

Then on your Form for Assets you create a Combobox to add a Customer.

Then use a Not in List event to add a new Customer when needed

 

[Pat Hartman]

One more time. Please look at the sample I posted. Look at how the subform works. look at how the popup form works. Those two forms are bound to the junction table. The main forms are bound to tblA and tblB respectively.

 

[mike60smart]

Pat's example is the perfect example of a Many to Many.

Do study it and amend your version to suit.

 

[Jimis]

What I'm trying to do is a little more complicated. I have transformed that example to show you what I'm trying to do.

So I made Table1 who will hold the "bookingsID" which should be a unique number pointing at tblBookings.

The idea is that through the frmTable1 the user can select a CustID , VenueID and BookingDt and the BookingsID is changed to reflect the new value taken from tblBookings

In my case instead of CustID , VenueID and BookingDt , I have custID and CustERP which should be shown in an Assets form (Table1) so when an Asset needs to change it's custID and/or custERP then the AssetsT field containing that relation should be updated (BookingsID)

 

[mike60smart]

Why are you needing to CHANGE a record using this method?
Forms are designed primarily as Data Input for new Records and also to Add New Records.

If you want at a later data to CHANGE a records properties then a different process is required.

 

[Pat Hartman]

If I understand your issue, BookingID should be the PK of the junction table. And you can add other intersection data in addition to Booking DT.

In most applications, junction tables have only two columns, the two FK's that form the m-m relationship. And that is how we tend to talk about them. But, they are not limited to the two FK columns. I have many that have intersection data. For example a junction table that connects a student to a test would contain a grade on the test and a date the test was taken and possibly some teacher notes. Intersection data is data that is an attribute of the two FK's that define the m-m. You can't put the grade in the student table and you can't put the grade in the test table. The test table which is one of the FK's in the junction has a FK to the class table.

I think your problem is that you are creating a separate table when you should be using the junction table to store intersection data or maybe you are trying to merge a separate relationship at the wrong level. We are 48 posts into this and I am totally confused regarding your actual requirements. Rather than asking how to implement what you think is the solution, perhaps, we need to understand what real world business problem you are trying to model.

 

[Jimis]

I agree with what you say Pat.
I was trying to keep it simple cause most might have a hard time following my case that's why I tried to be specific to the solution I want to apply so I could get an answer.But since you want to know I'm sending my db striped of most unnecessary stuff.

The main Table is AssetsT then we have CustomersT and CustomersERP. Forget about the rest of the tables. One Asset can have only one
CustomerID which is comprised of the combination of CustomersT and CustomersERP_T (unique combinations). I could just put in AssetsT the two fields instead of creating the 2 Customers tables but an Asset might not have a customer and that would be waste of space, isn't that what normalization is about?

I also want the user to be able to see "Device Customer" and "customerERP" fields from inside the Form Assets and change them via a subform or another way. I used a subform like this.

Let me make a note here. I see a lot of people talking about the "best practice" etc. If I have 10 tables and I want a user to update 1 or 2 fields in each of them do I need to open 10 forms to do that? This might be best practice for the db expert but not for a user(s) who has limited knowledge and time. That's why I want from my Assets form to be able to access customers easily and ofc other fields later the same way.
My understanding so far is that you can get away with tables connected directly, by using subforms to change data (or nested subforms which is not very nice in my opinion), but when it comes to join tables where you deal with IDs that's a problem (my case).

Anyway you can see that I have finally made a button (New) for the user to open a form which manipulates the data in CustomersT and CustomersERP_T and after he has input the new data (if required) then he can select them from the combo boxes above.
By selecting the 2 combos the custID should change (102 above) I tried using a query and update the (linked) custID in AssetsT. I couldn't make this work.

I would appreciate a specific answer to my problem.

Thanks in advance

 

[SHANEMAC51]

I would appreciate a specific answer to my problem.

Thanks in advance

I would do this
- there is a main ribbon form with dynamic search for the value of any field (conditions in green fields, the code is not written)
- - the yellow fields on the form should be protected from correction, they are only for selection
- pink fields for calling subordinate forms (there is a code)

replacing substitution with calling dialog forms to select/add/adjust the required element is a separate conversation

 

[Pat Hartman]

Asset might not have a customer and that would be waste of space, isn't that what normalization is about?

No. Normalization is about rational design and not duplicating data. You don't add a separate table because sometimes a field might be null. Tables model the real world and in the real world, sometimes data is optional. Would you create a separate table for middle name? Not everyone has a middle name? Would you create a separate table for CustomerID? Not every order needs a CustomerID if you take walk in customers.

If I have 10 tables and I want a user to update 1 or 2 fields in each of them do I need to open 10 forms to do that? This might be best practice for the db expert but not for a user(s) who has limited knowledge and time.

We already see that you've made an unnecessary table and complicated this m-m. Maybe there are other unnecessary tables.

You seem to know more than the experts so do it your way.

 

[Jimis]

Well this is not the specific answer to my problem that I was looking for.
You are not telling me which is the unnecessary table and why. What exactly are you proposing ?
If we for argument say accept that these are the tables is it not possible to do what I'm trying to do ?

 

[Pat Hartman]

You never showed us your tables. How can I tell you which are wrong? If you post the tables with data that makes sense, I will look at it. If the data is nonsense, I won't be able to figure out how the relationships should work. Sometimes we can tell the relationship is wrong by just looking at the column names and relationships but not always.

 

[Jimis]

Pat
I was clear about this "The main Table is AssetsT then we have CustomersT and CustomersERP. Forget about the rest of the tables."
If you read my message I explain exactly where my problem is. I can't be more precise than this. I have included my db so you can look at whatever you want.

 

[mike60smart]

Pat
I was clear about this "The main Table is AssetsT then we have CustomersT and CustomersERP. Forget about the rest of the tables."
If you read my message I explain exactly where my problem is. I can't be more precise than this. I have included my db so you can look at whatever you want.

Jim

See the attached.

You are trying to update a Main Form using a Subform. Not the way Access works

 

[Pat Hartman]

Well this is not the specific answer to my problem that I was looking for.

You didn't like my answer because it disagreed with your opinion on what the answer should be.

1. Data from "child" records is not stored in "parent" records. Why, because there are MANY child records and only ONE parent record.
2. Data that is optional should not be cause to create separate tables.

 

[Jimis]

Jim

See the attached.

You are trying to update a Main Form using a Subform. Not the way Access works

Mike
I figured that out and I was trying to find a way around it.
What I want to do is let the user select DevCustID and CustomerERP by opening a form or subform, then find the ID (PK) from the table customer_joinT based on his selection, pass the value to a global var for example and then change CustID in AssetsT in the mainForm(Frm_Assets).

I was trying to do it with a subform (with empty master/child links) but I still have a few issues. When I select DevCustID and CustomerERP it's like selecting these values from the table and I don't want to do that because it tries to add new values to the table (any way to avoid that?).

I tried running a query based on the user selections and update a text box/combo box with the ID to pass that to mainform but couldn't make it work

I also tried to populate the combo boxes with a query (with no control source) instead of using their table's field as their control source no luck either.

I could use some help with that. I have no problem using VBA if it's needed.

I attach the db striped of anything unnecessary (the query is not working as it should)

 

[mike60smart]

Hi Tim

Why not just leave the selection of a CustID to the usual Combobox and use that to change as necessary?

 

[Jimis]

I finally did it.