Help with Development of CRM Model

[mike60smart]

I cannot understand why you don't just use the standard Main Form / Subform as follows to
control which Persons belong to which Organisation:-

Main Form based on tblPerson and then a
Subform based on tblOrganisationPerson
with a Lookup to select Organisation and
a Lookup to selectJobClassification

 

[HillTJ]

@jdraw , my plan is to generate a unique autonumber PartyID in frmParty, then use this value as the PK in either tblPerson or tblOrganisation(i changed the name as you suggested). On each form i use the before_update event to validate data as usual. The problem is that if a record is not saved in either frmParty or frmOrganisation, then there has still been a record created in frmParty because its created first. It would be good to create hat record last & after the data has been validated but i don't know how. Thus, in such an instance i need to delete that entry in frmParty as its orphaned. I currently use the form_close event to check whether there is an entry in firstname, lastname, or dob, but i believe this is incorrect logic as the form_validate routine confirms required fields as well.

Also if you try to add another copy of a record that already exists, then the msgbox pops up but afterwoods an access system message pops up. I don't know why.

Maybe i've put the cart in front of the horse?, and i'd be happy to accept a better way.

Hope i made sense. Cheers

 

[HillTJ]

@mike60smart, could do it your way but not all people belong to an organisation. There may be a person who belongs to an organisation, but that person may have children (who may belong to an organisation at some point). My plan is to be fully inclusive of all family relationships. Thats my logic anyway!

 

[HillTJ]

@MajP , had a go with your recommendation as per post #7, but came up with an issue where PartyID is used as the Fk in other table relationships. How do you suggest I handle these? I've uploaded a copy of my work in progress so that you can see what I mean. I've generated 2 forms frmParty_Person & frmParty_Organisation along the lines you recommended. Appreciate the help.

 

[jdraw]

"I've uploaded a copy of my work in progress so that you can see what I mean."

Nothing attached??

 

[HillTJ]

Didn't Upload. See attached

 

[HillTJ]

@jdraw, I saw that too. See post #26. My Bad!

 

[HillTJ]

All fixed. Using Before_Update event to validate data & if validated successfully, using if me.newrecord to run the code to make the new record in frmParty, then using the dmax to find the max partyID PK which is then saved as either frmPerson or frmOrganisation PK. Thanks to those whom contributed. Cheers.

 

[TadesaeN]

I admire your determination! I remember when I was tackling a similar challenge, trying to link different tables in my database seamlessly. It can definitely be a head-scratcher at times!

Regarding your form not populating, have you checked your query or data source? Sometimes a small glitch there can throw everything off. Double-checking your logic and code step by step might help uncover any issues.

 

[HillTJ]

@TadesaeN , all fixed. Project is progressing well. Got login, user access rights, attachments & working on assigning tasks & reminders at the moment. Most comprehensive CRM i've seen. Able to "drill down" to related organisations & related personnel, assign personnel to job roles within organisations etc. Aim is to make it "universal" so that it works across different industries with their unique job roles.

Got to 'get outside your comfort zone' to learn new stuff.

Pretty happy with he progress at the moment (but can change without notice!).

See my original post for reference to an excellent treatise on the concept. I develped mine from this concept.

Cheers

 

[Pat Hartman]

I came late to this party but rather than have two separate tables, use a single table. That simplifies EVERYTHING else since you have only one table to link every other table to. You can have a flag to indicate whether the entity is an organization or an individual. You can use LastName to hold the organization name.

This entity table would be self referencing - assuming that an individual can belong to one and only one organization or family. If an individual might belong to multiple families or multiple organizations or one of each, then you would need a junction table instead.

If you have more than two entity types, you might end up with a lot of optional fields that exist for only one type of entity. In that case, you could create several specific tables that are related 1-1 with the entity table. You could than have a table for vendors, another for customers, another for individuals, etc. BUT, and this is important, NO OTHER table except for Entity ever relates to the specific entity type table. All tables such as addresses, orders, classes, whatever, always relate to the entity table.

 

[HillTJ]

@Pat Hartman , did you have a look at the table design developed in the reference on my original post. My database is derrived from a modified version of this. I have standardised lookup tables where possible and flag them as either 'organisation' or 'personel' related. Upon reflection, yes i probably should have used a single table for both 'organisational' & 'personnel' records. I might even have a go. Problem is i've invested quite a bit of time into this model, but the upside is quite a bit should be reusable. I wish you hadn't said anything., now you"ve got me thinking. Out of the comfort zone i go again....

 

[Pat Hartman]

No. I didn't look at the beginning. I looked at what you said you were doing and that is what I responded to. You have much more flexibility if you use the Entity model, with or without the specific child tables. Whether you go with the specific child tables depends on how much non-overlapping info you need for each entity type. But the point is - ALWAYS - every other relationship points to the Entity table. None will ever point to the specific type tables. The type tables only ever link to the Entity table.

If you go with the type tables, let them have their own uniqueID even though you could use the autonumber of the Entity table as the long integer PK for the type table. I find that using the EntityID as a FK rather than a PK allows me to handle RI better. You can add a unique index to the FK to ensure there is a 1-1 relationship with Entity and have the best of both worlds. You may be able to enforce RI between the Entity table and multiple 1-1 type tables in other RDBMS' but you can't do it with ACE so what I described is my solution.

 

[HillTJ]

@Pat Hartman , thankyou for your input. I'll have a good hard look at my design. The original design l adapted (link in post 1) made sense to me, but I understand what you are saying. I might make a copy of my database and modify it to a single entity table design. I just don't relish the work though...Outside my comfort zone I go again.