Simple Database - Giving this newbie trouble

[broomulack]

Hi all. I've tried to create a database to keep track of the submissions in my insurance agency. We've been using an Excel spreadsheet as a log and it's gotten too big and too slow. After at least 8-10 iterations of the database, I THINK I've gotten it normalized correctly. I've got forms working properly, and it seems everything updates where it's supposed to update. My problem is when I create a query for the submissions, I can't update any of the data. Submission status, contact name, it's basically ready only. I've looked this up and it seems that having more than three tables linked to a query is an issue, but I don't see any way to not have the tables linked.

I've attached a copy of the relationships I've created. Basically, an insured is an account name. Insureds have submissions, and submissions link all of the other tables. We're a wholesaler dealing with other agents, so agents are "customers" for the most part. Insureds would be their customers. Agents can have many submissions, but a submission can have only one agent. Contacts are linked to agents only, I've had to use cascading combo boxes to set this up, and it works fine. Underwriters are the account managers, and for convenience I am not linking them to agents because we've switched account underwriters so much that enforcing integrity would be impossible. So, underwriters are standalone on a submission by submission basis, and can have many submission - while a submission can have only one underwriter.

I don't see any need for junction tables, but maybe being a complete novice has me missing something.

Any help, no matter how small, would be greatly appreciated.


UPDATED: The "agent_aimcode" is linked to the "contact_agent" on the "contacts" table. I had deleted this link inadvertently.

[June7]

A form can do data entry/edit for only one table. You have one main data table (tbl_submission), the other tables are 'lookup' info. Those lookup tables will require their own forms for entry/edit of records. There is really no requirement to include the lookup tables in the form's RecordSource for purpose of data entry to tbl_submission, although it can be done (do not use INNER JOIN). Limit for number of tables/queries in a query is 50.

Why would tbl_agent link to the contact_name field in tbl_contact?

[nick404]

You are having trouble updating records through a form? You can only change data in a query if the tables are 1-1 relationship between them, I believe.
Set form property Data Entry to 'yes', it is defaulted as no.

[broomulack]

Thank you for the response. I have separate forms to edit each table, that's not my concern. What I'm trying to do is recreate the log we use which was previously done on an Excel spreadsheet. For instance, when an underwriter works on an account, the status starts as "Cleared", then it might go from cleared to "Indicated" then to "Quoted" and ultimately to either "Bound" or "Closed". We update the status fields as we go - same for premium fields, comments, etc.

When I make a query to show a submission log, it's perfect - except it's not editable....haha

The agent ID is linked to the contact_agent, not the contact name. Contacts are part of an agency, and linked directly to that agency.

[June7]

Post the query or provide database. Follow instructions at bottom of my post.

Sorry, don't know how I misread contact_name for contact_agent. Are you saying tbl_agent is linked to two tables?

I see only 1 status field. Is there a new record each time the status changes? If yes, possibly need a related 'child' table for the status logging. What data would be the same? These fields would be in the parent table.

[nick404]

Could you post sql for that specific query? Are you using a macro to run this query? Check that it is set to be able to be edited.

[broomulack]

Post the query or provide database. Follow instructions at bottom of my post.

Sorry, don't know how I misread contact_name for contact_agent. Are you saying tbl_agent is linked to two tables?

I see only 1 status field. Is there a new record each time the status changes? If yes, possibly need a related 'child' table for the status logging. What data would be the same? These fields would be in the parent table.

The agent field is linked to the contact field and to the submissions field. The fact that you're asking about it has me even more confused - the relationships have given me a lot of trouble. I evidently don't understand them well enough.

Status field is simply a lookup menu, I created a table for the codes rather than have it as a pick list. A new record is not created, the status simply changes.

[broomulack]

Could you post sql for that specific query? Are you using a macro to run this query? Check that it is set to be able to be edited.

I've since deleted the query in trying to troubleshoot what I'm doing wrong. However, it was done with the query wizard, and pulled data from the various tables. I attempted to pull it from the submissions table since that is the one place it's all centralized, but I can't view anything but the ID numbers. I edited the query to include the names and hide the ID numbers, but that's when it got uneditable.

I realize how much of an idiot I sound like. I'm sorry everyone, thank you for your patience.

[June7]

I am confused. You say "got forms working properly, and it seems everything updates where it's supposed to update" then you say "when I create a query for the submissions, I can't update any of the data".

Exactly what is the issue?

Where did nick get your db from?

[broomulack]

I am confused. You say "got forms working properly, and it seems everything updates where it's supposed to update" then you say "when I create a query for the submissions, I can't update any of the data".

Exactly what is the issue?

Where did nick get your db from?

The entry forms when I want to create new data - they work properly. For instance, entering a new insured, new submission, etc. When I want to view the submissions and update various fields on a de facto log (i.e. like we used on Excel), I am unable to do so.

Nick didn't get my database, I believe he responded in error.

[nick404]

Where did nick get your db from?

That's my bad, got this post confused with another

[June7]

My comments in post 2 still apply.

I would use the same form for tbl_Submission new record entry as well as to edit existing. I would build search tools into the form to find existing record and a 'Add New Record' button to go to new record row. Bind form to tbl_submission.

[nick404]

So create a query that you need (strongly recommend against the Wizard), use the design view and drag the tables you need into it, and drag the fields you want to the bottom portion of the screen to build your query.

To my understanding you want to edit the data that appears right in the query's table and not in a form- I do not like that idea generally.

I agree with June, have a form bound to a query with search criteria and set form to allow data entry. Will solve whole issue.

[broomulack]

My comments in post 2 still apply.

I would use the same form for tbl_Submission new record entry as well as to edit existing. I would build search tools into the form to find existing record and a 'Add New Record' button to go to new record row. Bind form to tbl_submission.

So create a query that you need (strongly recommend against the Wizard), use the design view and drag the tables you need into it, and drag the fields you want to the bottom portion of the screen to build your query.

To my understanding you want to edit the data that appears right in the query's table and not in a form- I do not like that idea generally.

I agree with June, have a form bound to a query with search criteria and set form to allow data entry. Will solve whole issue.

This is good stuff. Thank you both, I will give this a try!

[nick404]

Any further troubles just post back as they arise.