Not sure how best to display the information

[dibblermail]

The issue is that each site has a variable no of Contacts, a variable no of Directors & and Arch & Struc Eng.

The contacts are employed by the client (but come & go across the different jobs. Big jobs often have several client contacts)

Arch & Struc Eng can turn up on any job from any client

 

[dibblermail]

This was the old solution in excel... but it makes more sense to add the names dynamically so I can have x number of them
That form was built on a cascade from client down, so you could only pick contacts from that client. But that in itself causes issues as people move around between companies.

 

[mike60smart]

Because the Job can have multiple Contacts you need a table related to the Job where you can record Multiple Entries.

See the modified BE and the FE with the addition of a Main Form to record Site a Subform to Record Jobs with a related Subform to record Contacts.

 

[dibblermail]

Because the Job can have multiple Contacts you need a table related to the Job where you can record Multiple Entries.

See the modified BE and the FE with the addition of a Main Form to record Site a Subform to Record Jobs with a related Subform to record Contacts.

Thank you for taking the time to do that... I'll now sit down & unpick it all to understand exactly what you did.

 

[dibblermail]

Because the Job can have multiple Contacts you need a table related to the Job where you can record Multiple Entries.

See the modified BE and the FE with the addition of a Main Form to record Site a Subform to Record Jobs with a related Subform to record Contacts.

If you have time, please can you give me another nudge in the right direction?

I'm recreating what you uploaded in my original database. Partly to learn what you did & partly to expand on what you started.

I've made the JobContacts table & adjusted the relationships in the back end

I've got as far as creating the jobcontact subform in the front end

I'm Not sure why, but I can't get the ContactID to display the name, only the ID.

I've set the Row source to match yours & column count to 3 the same as you, but it just shows the number after I select it.

when I click on the cbox to select I get 3 columns Number | Name | Company

On yours you get 2 Name | Company

I can't see where I can be getting it wrong

I think it's a minor side issue, but don't get a datasheet view option for the form, which yours does. Again, no idea why.

I've attached the files just in case you think its quicker to look

 

[mike60smart]

Hi

I found in the version you uploaded you had a reference to 2 backend files.
I removed 1 of them and then relinked to the correct BE and all now appears to be fine

Remember to relink the FE to the BE

 

[Mike Krailo]

In your junction table, you probably want two more fields for the ContactName and CompanyName. Then in the after update of the combo on the subform, those two fields get populated depending on the contact you pick. So your new form looks like this. I updated the backend so you have the many to many jobs to contacts, contacts to jobs.

 

[dibblermail]

Hi

I found in the version you uploaded you had a reference to 2 backend files.
I removed 1 of them and then relinked to the correct BE and all now appears to be fine

Remember to relink the FE to the BE

where did you find the old links? I only have the one. The first thing I did was to clean it out

 

[mike60smart]

When I opened the last version you sent it was showing the 2.
As long as it is now sorted then that's great.

Mike has given you a good modification to the Contacts subform.

 

[dibblermail]

When I opened the last version you sent it was showing the 2.
As long as it is now sorted then that's great.

Mike has given you a good modification to the Contacts subform.

That's the same version I sent, I haven't made any changes & its still not working.

But by the sound of it its a stupid glitch, so I'll try compacting the database, remaking the table and reload again or even starting from scratch with the back end. It's my problem now anyway.

Thanks again for the time you've given me, its very much appreciated.

 

[dibblermail]

In your junction table, you probably want two more fields for the ContactName and CompanyName. Then in the after update of the combo on the subform, those two fields get populated depending on the contact you pick. So your new form looks like this. I updated the backend so you have the many to many jobs to contacts, contacts to jobs.

View attachment 119016

Thanks for taking the time to look at it as well, I'll have a look & try to unpick it.

 

[Pat Hartman]

Sorry @Mike Krailo but duplicating fields in the junction table is wrong. If you want to show multiple fields instead of just one in the combo, the simplest solution is to concatenate the fields so at rest, the combo shows Company - ContactName, ordered by company so the list makes sense. Otherwise, you solve the problem with a join to one of the related tables to pull in the test fields. However, when you do this, you MUST set the controls to locked to prevent accidental update since the values should NEVER be updated from this form.

 

[Mike Krailo]

It's not wrong if you want to do it that way and it suits your requirements. I do it all time and there is no issue with it at all. I understand your point of course, and that is another way of doing it. Set it up however you desire, but there is nothing inherently wrong with it.

 

[MajP]

It's not wrong if you want to do it that way and it suits your requirements. I do it all time and there is no issue with it at all. I understand your point of course, and that is another way of doing it. Set it up however you desire, but there is nothing inherently wrong with it.

No, it is not really debatable. Pat is correct. The fact that it may work "kind of" for you is not a good reason to suggest a novice should adopt this.
Sure you can make a non-normal database design work "sort of ". But you open up problems with data integrity. Maybe not that big of a case here, but it is in other cases. I would not suggest this to a novice as a viable solution.

I know you would not do the same thing in a regular child table and duplicate data from the parent. So why do it here? There are times when it makes sense to denormalize your data, but no need or benefit in this case. There are no benefits because it is not really easier, but there are drawbacks

Your design should simply be this.

You can do the exact same thing with out violating normalization.

Maybe not a big deal in this case but here is where data integrity gets violated.
Ben's Company get renamed to ACME Company. Now John Smith should show up in ACME but instead on this form and in the table he shows in the old Ben's Company.

Pam Brown gets Married and now is Pam Smith but on this form and table has Pam Brown. Or worse, Mark can now a days decide he wants to be Jessica. You have now created a hostile work environment because on this form "she" is still shown as Mark.

If those fields where instead things like cost values, item quantities, or contact emails/phones then you have created big problems.

 

[MajP]

Fere is an example where a normalized table makes the form design more complicated. Here is a case where I might argue that violating normalization is worth it, and most people I see do violate normalization in this case.

The issue is there are Resource Types (categories)
ResourceTypes
-Resource Type ID
-Resource Type Name

Resources
-Resource ID
-Resource Name
-Resource Type ID

In the data table, the user needs to select a resource, but this list is long so they need to cascade somehow. The OP then stored the ResourceType ID in the datatable only to help make a cascading combobox in a continuous form. It should not be stored because it is related through the Resource table foreign key. If you did this with an unbound combo it would be very complicated.

My solution to "cascade" and not have to store the ID is a much more complicated (although more functional) pop up UI. Because of the complexity of building the UI doing it non-normal and storing the ResourcetypeID may make the ease in creating the UI outweigh data integrity concerns.

Solved - Normalization - Struggles In Access

New to db design & really struggling with it. Watched countless tutorials; implementing seems far harder. Been told to avoid composite keys in Access as it makes hard work, which seems to cause violations but I really don't understand normalization. ResTypesT - Labour/ Plant/ Material/...

www.access-programmers.co.uk

 

[Mike Krailo]

I know you would not do the same thing in a regular child table and duplicate data from the parent. So why do it here? There are times when it makes sense to denormalize your data, but no need or benefit in this case. There are no benefits because it is not really easier, but there are drawbacks

I'm not pushing any final design on anyone. That's up to the OP's own business rules. If they need something else, that's perfectly fine. Use the shopping cart method and take what you can use and leave the rest. If there is no need to retain the contact names or company names at the time of the job, then don't do it that way. What I showed retains the names as they were for that particular job. I assumed that would be desirable, as it is in some of my own projects, maybe it isn't.

My own company changed names seven times in 35 years and I like to see the company name as it was at the time of the job. Is there something wrong with that requirement?

 

[MajP]

If your intent is to make a snapshot in time that could make sense. If you are trying to maintain data integrity and permeate changes across the db then not. As long as the user understands these implications, I guess its OK.

 

[Mike Krailo]

If your intent is to make a snapshot in time that could make sense. If you are trying to maintain data integrity and permeate changes across the db then not. As long as the user understands these implications, I guess its OK.

I just made the assumption that was contact info based on the time of the job. If not, by all means do not do it the way I suggested. Anyways, no worries. Everyone have a good weekend.