Not sure how best to display the information

[dibblermail]

Please can someone give me a nudge in the right direction on the best way to design this form?

I'm moving from a massive spreadsheet to Access & trying to upgrade how I do it as I go, but this is my first go at Access.

Several people have already helped with the normalisation of the data etc & I believe it's now about right.

I need a form which includes client info, showing multiple people in different categories.

the old form in Excel looks like this...

The bit I want to upgrade is the number of people who can be Site Contact, Director, etc.

Often its only 1 in each, but sometimes there aren't enough boxes, so it wants to be dynamic in how it works.

I'm storing the info about the contacts in this Table

I could create a subform for retrieving Site Contact, and another for Director, and so on. Showing how ever many in each group there are. But how would you make this work efficiently? Retrieving is easy. But how would you add new contacts including the groups they should be in; Or make an existing contact a member of another group after creation.

I could call another form used expressly for adding & editing contacts, which shows all the fields?

I feel like I'm overthinking this, so I'm looking to see what more experienced people would do.

 

[plog]

You need to put away forms for now and work on properly structuring your data. That process is called normalization:

Database normalization - Wikipedia

en.wikipedia.org

Give that link a read, google a few tutorials, then apply what you have learned to your data. The big thing I see with your data is that you are trying to accomodate data horizontally (with more fields) and not vertically (with more rows).

When you feel the need to make groups of fields (e.g. all those contact, invoice and director groups of fields), it is time for a new table to hold them. Instead of 4 groups of fields for your Site Contact data, you would add 4 records to a new table to hold that data. And if you only have 2 site contact records you just put 2 records in that table instead of leaving 2 groupings blank like you would now.

So, from the little you have provided us so far, I can see you need at least 3 tables and not one humongous one. You need to work on your tables and fields first before you work on forms.

 

[plog]

I apologize, I thought that form was your Access form. However, I'm not entirely wrong. I think you need a Roles table. All the data in those checkboxes in your Contact table would go there:

Roles
RoleID, autonumber, primary key
ID_Contact, number, foreign key to Contacts
RoleType, text, this will hold what is now the name of all those checkboxes (e.g. SiteContact, Director, etc.)

That table now holds all role information for contacts. For forms, I would make a main form based on Contacts and at the bottom I would have a subform based on roles where users could use a drop down to assign all the roles necessary for that contact.

 

[dibblermail]

I have realised I don't think there's a way to record which contacts are involved with which job.

I need a way to link the Job Back into Contacts. Or is my lack of knowledge making me miss a way to do it?

I can also split Contacts down as you suggested to make it easier to control.

 

[dibblermail]

I apologize, I thought that form was your Access form. However, I'm not entirely wrong. I think you need a Roles table. All the data in those checkboxes in your Contact table would go there:

Roles
RoleID, autonumber, primary key
ID_Contact, number, foreign key to Contacts
RoleType, text, this will hold what is now the name of all those checkboxes (e.g. SiteContact, Director, etc.)

That table now holds all role information for contacts. For forms, I would make a main form based on Contacts and at the bottom I would have a subform based on roles where users could use a drop down to assign all the roles necessary for that contact.

I think I could use your roles table to link in the jobID? That would close that issue as well?

 

[Pat Hartman]

If a Client may have many Contacts but only specific Contacts are related to Job, then you need a JobContacts junction table to hold this relationship.

 

[dibblermail]

If a Client may have many Contacts but only specific Contacts are related to Job, then you need a JobContacts junction table to hold this relationship.

OK... Ill go to YouTube University & have a look. Thanks for the steer in the right direction.

 

[Pat Hartman]

Junction tables are used to implement many-many relationships. A job has many contacts and a contact works many jobs. You use the ClientID from tblSite to control the RowSource of the combo you use in the JobContacts junction table to pick each Contact.

This example may be useful.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

 

[dibblermail]

Junction tables are used to implement many-many relationships. A job has many contacts and a contact works many jobs. You use the ClientID from tblSite to control the RowSource of the combo you use in the JobContacts junction table to pick each Contact.

This example may be useful.

thanks... I've watched a video & had a stab, I'll look at your example as well before I go too far.

 

[LarryE]

I have realised I don't think there's a way to record which contacts are involved with which job.

I need a way to link the Job Back into Contacts. Or is my lack of knowledge making me miss a way to do it?

I can also split Contacts down as you suggested to make it easier to control.

View attachment 118990

1. Create a ContactID foreign key in the tbl_Job table
2. Add that foreign key to your Job forms record source and add it to your form in a textbox like the JobID and SiteID

 

[dibblermail]

View attachment 118998

1. Create a ContactID foreign key in the tbl_Job table
2. Add that foreign key to your Job forms record source and add it to your form in a textbox like the JobID and SiteID

 

[dibblermail]

I've created that, but in my inexperience, I still can't see how that records which Jobs a contact is allocated to.

I think I need a table with ContactID and JobID over many rows (tbl_Job is 1 row per job).

 

[mike60smart]

On The form where you are recording the Job details you would create a Combobox to Lookup the Contact allocated for the specific Job

 

[dibblermail]

On The form where you are recording the Job details you would create a Combobox to Lookup the Contact allocated for the specific Job

I may be being a bit slow, but I can't see where that's being recorded.

 

[mike60smart]

Can you upload a copy of your db?

 

[dibblermail]

That's the backend. I haven't created a form to Add clients yet. I was trying to get my head around how to pull the info from the Tables to do that. I can upload the front end so far if that helps.

 

[mike60smart]

Need FE as well please

 

[dibblermail]

here you go

 

[dibblermail]

The intention is to create a table starting with a JobID that you can add contacts to

 

[mike60smart]

It would make more sense to say:

1 Site has a Specific Job and each Job has a Specific Contact