One form, five tables, new data...how?!

[ChrisM]

Perhaps I'm thinking of this in the wrong way, perhaps this is merely my inexperience showing, but I'm stuck.

I'm working on the most complex database I've ever designed, and while it's not a HUGE database, it certainly has the capability of growing to that point. The key problem at this moment is: entering new data. First, an overview...


I am putting this together for a home renovation company that generates their own customer leads with teams who go door to door. When we have a lead, several things happen at once:

• We have a first contact with a new customer
• We get the customer's contact information along with contact preferences (who to talk to, what the best time to call is, etc.)
• We get the customer's residential address
• (Internally) We have the start of a new "job" that will get more information added to the database at a future date.
• Certain automatic scheduled events (like the date and approx. time of the next phone call, when the lead goes dead, etc.) are generated

For the most part, the design of the database is pretty...not easy, but clear how it needs to happen:

1. Employees need their own table so their data can be separately tracked (who contacted the customer when, etc.)
2. Customers need their own table because duh
3. The property needs its own table because several different jobs can be done on a property and the property can be owned by several different people over the course of it's "life"
4. The jobs log needs its own table because the jobs are not tracked by contact, they're tracked by work completed
5. The contact log needs its own table because the customer can be contacted multiple times before a job is even started, multiple times during the course of a job, and multiple times after the job is done
6. Buncha supporting tables full of lists that don't change much but when they need to be changed it needs to be done easily in a way that allows the changes to be accessed throughout the database

At this point, I need to create a form that allows for entering data for the following:

• Contact Log Table
  
  • Which employee made the initial contact
  • What the date of that contact was
  • What office the employee works at (and therefor what office the job is done from)
  
  
• Customer Table
  
  • Primary contact first and last name
  • Secondary contact first and last name
  • Phone #
  • Contact preferences
  
  
• Property table
  
  
  
  • Full address (incl. house #, street name, etc.)

I'm basing this all on the Contact Log, since this is the first contact the company has with the customer, and each entry on this form also needs to generate a new Job ID

While the form certainly could be broken up into discrete forms that all create records in their respective tables, the data all still needs to be tied together as appropriate.

• Contact log needs
  
  • The employee who made the contact
  • The date of the contact
  • The associated job ID
  • The name of the customer
  
  
• The customer table needs
  
  • Customer name(s)
  • Customer phone #
  • Customer contact prefs
  
  
• The property table needs
  
  • The property address
  • Customer that lives there (Stored here in case we do a property search and find that the property is not owned by the resident, yes, this is important)
  
  
• The job log needs
  
  • The customer ID (based on the primary key of the Customer table)
  • The property ID (based on the primary key of the Property table)
  • The "class" of job (windows, siding, kitchen, etc.)

I can create a form off the Contact log and then add subforms, but then the data either isn't recorded or throws out multiple errors when the data entry happens. I'd do a query, but I need to enter data, not create it. :/

Like I said, I may simply be showing my ignorance, but I need help.

[June7]

Ugh! What a spiderweb! I may be wrong, but looks like circular relationships. http://www.codeproject.com/Articles/...atabase-Design

I don't understand why contacts and customers are separate tables. Aren't contacts also the customers?

[ChrisM]

I don't understand why contacts and customers are separate tables. Aren't contacts also the customers?

Contact log, not Contacts. The contact log pulls the customer reference from the Customers table.

[June7]

What is purpose of tblContactLog? Is this to document communications with established customers? I don't think it should have both JobID and PropertyID fields. Isn't a job for a single property and a single customer?

[ChrisM]

What is purpose of tblContactLog? Is this to document communications with established customers? I don't think it should have both JobID and PropertyID fields. Isn't a job for a single property and a single customer?

I think you're right, and that's part of me asking here; I'm the most knowledgeable and experienced person with databases in general and Access in specific that's working at this office.


Basically, any feedback given on this would be great, as I'm hitting wall after wall trying to get this done.

I did some thinking yesterday on this and broke it down for the other main guy on the team (a kid 2/3rds my age with no dev or database experience) as follows:

There are three main tables that form the "load bearing beams" for this project: tblContactLog, tblJobLog, and tblAppointmentLog. Everything else acts as a support in one fashion or another to those three. Without any one of those three, the database doesn't serve it's primary purpose for being. (to track customer contact from generating the lead to closing out the final sales appointment) Even tblCustomers is a supporting table to the other three and could (theoretically, though I'm loath to even suggest such out-loud) be replaced with just a text box where we enter the customer's name & phone number.

That said, there is some tracking we'd like to do that is only really possible with any reliability when using tblCustomers, tblEmployees, etc. Plus, I'm trying to leave some room for growth for when the owner of the company (inevitably) asks, "Can we do <x> with the database?" (He's already asked for about four features I hadn't planned for when he gave me this task, thus I know the requests are inevitable)

The main problem comes down to creating the forms for user interaction. The very first form is based on what we call a Lead Sheet, and on the lead sheet we have tons of information that touches on nearly every table of the database, thus my problem with how to even get the form started.

Again, any feedback is appreciated.

[June7]

Normally a form can enter/edit data for only one table. Use form/subform arrangement for entry/edit of multiple (master/child) related tables. Multiple subforms can be arranged on a Tab control or give Navigation Form a try (I don't like them).

Records must already exist in 'lookup' tables for them to be available for selection in comboboxes. If a needed record does not yet exist, use combobox NotInList event to add new record 'on the fly' during data entry. This involves code that opens a form to add record to 'lookup' table and then requery the combobox to make the new record available. Gets a little complicated.

[John_G]

You are certainly on the right track.

Your tables and relationships seem to be OK, except for those I have noted below. Properties and Customers are many-to-many, with tblJobLog as the join table, as you have it.

While a one-size-fits-all form would be nice, it can become difficult or impossible to use where many tables have to be updated, as in your case.

You could use one form for the initial data entry though, but I would suggest you update the tables with a command button and VBA, rather than having the form linked to any particular table. This approach would also allow you to properly verify the data before adding it. You could use a form with a tab control on it to separate the different sections of data.

It would be a relatively simple task to put buttons on the Customers and Properties tabs to pop up forms to add new properties or customers, or use June7’s suggestion to use the NotinList events of the combo boxes.

One problem I have is that you want to add job data at the same time you add Contact, Customer and/or Property data. I’m not sure this is the best approach, since many contacts won’t result in a job at all, especially in door-to-door work. (Unless of course you only add contact data if it does result in a job).

Can one contact result in more than one job? If so, then you need to take JobID out of tblContactlog, and put ContactID in to tblJobLog; you should then have another form to add job data. This might be the best arrangement anyway, putting the ContactID in to the JobLog. If you do that, take CustomerID and PropertyID (and officeID) out of tblJobLog, and replace them with ContactID – those three fields are redundant in tblJobLog.

Do employees change offices? If not, then you don’t need OfficeID in tblContactLog, since the OfficeID can be derived from the Employees table.

HTH a bit

John

[ChrisM]

You are certainly on the right track.

Your tables and relationships seem to be OK, except for those I have noted below. Properties and Customers are many-to-many, with tblJobLog as the join table, as you have it.

While a one-size-fits-all form would be nice, it can become difficult or impossible to use where many tables have to be updated, as in your case.

Well, I did a nuke-n-pave and started a new version of the database after getting the feedback from June7. A bit extreme, but I kept finding tons of problems and decided I'd just start fresh with what I'd learned and rebuild rather than trying to patch a ton of problems that came from a bad foundation.

Fortunately, I've cleaned things up quite a bit.

You could use one form for the initial data entry though, but I would suggest you update the tables with a command button and VBA, rather than having the form linked to any particular table. This approach would also allow you to properly verify the data before adding it. You could use a form with a tab control on it to separate the different sections of data.

That's actually what I wanted to do in the first place, but I haven't the foggiest idea how to do that. Is there a website that you could point out that would show at least the basics?

It would be a relatively simple task to put buttons on the Customers and Properties tabs to pop up forms to add new properties or customers, or use June7’s suggestion to use the NotinList events of the combo boxes.

I've actually started in that direction:


There's a couple of problems, though...

1. The scanned PDF is in Grainy-vision(tm). Not sure if anything can be done about that, but if you have a possible solution off the top of your head I'll take it.
2. When tabbing through the form(s), when finishing with the "First Contact" subform, I'd like for the cursor to jump to the "Last Name (Primary..." field, instead of jumping to the next record in tblContactLog
3. The form is based on tblJobLog, with tblContactLog, tblCustomers, and tblProperty as the source tables for the subforms. (The subform for Property isn't on the form yet, still trying to get past this problem before that...) tblContactLog gets written to without a problem, as does tblJobLog, but tblCustomers throws up an error.

Actually...thought! I may have a...start of a solution to my problem, looking for feedback:

I think my problem is that I'm trying to write the CustomerID and PropertyID to tblJobLog while the form is open:

Is that the source of the error? If so, suggestions on a full solution...? I'd just shove references to tblJobLog into tblCustomers and tblProperty so I can get the data into the database, but then I'll need to have that information connected to the job going forward...

One problem I have is that you want to add job data at the same time you add Contact, Customer and/or Property data. I’m not sure this is the best approach, since many contacts won’t result in a job at all, especially in door-to-door work. (Unless of course you only add contact data if it does result in a job).

Thought about that, but there's two reasons to do it this way:

1. I decided to treat each lead as both a first contact (tblContactLog) and a "job seed," or the first step in a job. (tblJobLog) Mostly I chose this because the Contact Log and the Job Log are for tracking separate information that should be kept separate.
2. That's how the boss wants it.

Can one contact result in more than one job? If so, then you need to take JobID out of tblContactlog, and put ContactID in to tblJobLog; you should then have another form to add job data. This might be the best arrangement anyway, putting the ContactID in to the JobLog. If you do that, take CustomerID and PropertyID (and officeID) out of tblJobLog, and replace them with ContactID – those three fields are redundant in tblJobLog.

OK, I think I understand where you're going with this, but no, each "Contact" is a separate time we, as a company, contact the customer. (Canvassing lead, phone call, meetings, warranty mailings, etc.) Each chain of contacts does, however, need to reference the job log, as each job will have numerous contacts over the course of the job and without that connection to the job, it's just random data. (Each customer can have multiple jobs, but a contact will never happen without being attached to a job)

Do employees change offices? If not, then you don’t need OfficeID in tblContactLog, since the OfficeID can be derived from the Employees table.

Thought of that, too. However, only about half the employees are locked down to one office, the rest work in multiple offices and in multiple capacities.

HTH a bit

John

...HTH?

Anyway, sorry for not replying sooner, for some odd reason I didn't get a subscription notification for this thread after the first reply... :/

Thanks for your help in the meantime!

[ssanfu]

I would recommend against using Look Up FIELDS..
Look up tables are good.
Look up FIELDS are bad.

See http://access.mvps.org/access/lookupfields.htm

[ChrisM]

I would recommend against using Look Up FIELDS..
Look up tables are good.
Look up FIELDS are bad.

See http://access.mvps.org/access/lookupfields.htm

...I have no idea what, specifically, you're referring to, nor how to use the knowledge you just gave me. Perhaps some clarification...?

[ssanfu]

I was looking at the picture in your post #8. The field "EmpRoll" in the table "tblEmployees " looks like it is a look up field.
In design view of the table "tblEmployees", click on the field "EmpRoll". At the bottom of the window , there are two tabs: "General" and "Lookup".
Under the "Lookup" tab, the top line will probably be
"Display Control: Combo box". that is a look up FIELD.
Below that would be SQL in the Row source line.

Most experienced programmers avoid the use of look up fields. See the link as to why....

Hard to tell in the picture, but I didn't see any spaces or punctuation in the field names. That is good

[ChrisM]

I was looking at the picture in your post #8. The field "EmpRoll" in the table "tblEmployees "

<snip>

Most experienced programmers avoid the use of look up fields. See the link as to why....

Ah, I see your point. That was an early design thing that I threw in and didn't think about changing even though I've moved away from that sort of things as much as possible. So the question is now: how to fix it so it's right without having to nuke the two tables. I tried just changing the lookup, but then Access locked the field as read only (Thank heaven for backups!).

Hard to tell in the picture, but I didn't see any spaces or punctuation in the field names. That is good

Of course! This isn't my first database, just my first with more than two tables.

[ssanfu]

Just change the setting from a combo box to a text box. The field type should be a Long integer ( because it is/will be a FK).
So tblEmployees.EmpRoll is the FK and tblEmployeeRoll.RollID is the PK. Changing the field type shouldn't lock the field....

[ChrisM]

Just change the setting from a combo box to a text box. The field type should be a Long integer ( because it is/will be a FK).
So tblEmployees.EmpRoll is the FK and tblEmployeeRoll.RollID is the PK. Changing the field type shouldn't lock the field....

No dice, any attempt to change it as you outlined results in the following:

[June7]

Odd, I don't get that warning.

If you want to provide db for analysis, follow instructions at bottom of my post.