I'd like to allow employees to create new customer records from the project record, including adding multiple contact names and emails.
And what tables and relationships exist in your database to facilitate this. To me, this sounds like a many to many relationship where on project can link to many contacts and one contact might be linked to many projects.
Is that the case?
Do you have the needed tables and relationships in place for such a relationship?
That's what I'm struggling to grasp.
I have a "Project" table containing Customer/Contact/Email fields. I also have a "Customer" table containing the same fields. I am struggling to understand how to relate, and whether I should be creating additional tables for the customer info.
I'd need the fields in the project layout to be pop-up menus with related customers from the "Customer" table. I would also need to be able to add new customers through this field. Choosing an existing customer from that pop-up menu would filter the Contact/Email fields accordingly so that the user is given the correct list of customer contacts.
Actual situational usage: Sales rep creates a new project, and can create a customer name, multiple contact names, and email addresses. Additional future projects will be created, and will now allow the sales rep to choose the existing customer, and then choose a specific contact name and email address based on the list of multiple names.
I apologize for the length....I am a true novice.
It sounds like you need these tables/relationships:
Start with these relationships:
Contacts::__pkContactID = Contact_Project::_fkContactID
Projects::__pkProjectID = Contact_Project::_fkProjectID
You can place a portal to Contact_Project on the Contacts layout to list and select a Projects record for each given Contacts record. Fields from Projects can be included in the Portal to show additional info about each selected Projects record and the _fkProjectID field can be set up with a value list for selecting Projects records by their ID field.
Keep in mind that this is just a "beginner level" approach. There are a number of enhancements that are possible that can make the resulting layouts much more user friendly.
You also appear to have multiple "contacts" for a given "customer". That suggests this relationship:
Customer::__pkCustomerID = Contacts::_fkCustomerID
Though in some cases, this can also require a many to many relationship if there is any chance that a given contact might work for/represent more than one customer.
And there is more than one way that you can set up what happens when someone selects a Customer for a given project. Selecting a customer could automatically link all contacts for that customer to the project or it could serve to limit the list of possible contacts from which the user would then make specific selections for the current contact. Which is the better approach, (and they aren't mutually exclusive) will determine the precise relationships needed to support it.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained