Help with table setup - based on Excel Import (1 Viewer)

[Jakboi]

Hello,

ACCOUNT_NUMBER
SHORT_ACCOUNT_TITLE
CONTACT_COMMENTS
CONTACT_TYPE_TEXT
ENTERED_BY
INITIAL_CONTACT_DATE
DATE_ENTERED

These are fields that are imported from Excel. I have to import 500-1500 rows each week which contain the data I need. Then I must, by using a form, go through these "Service Calls" and mark some that give the employee credit for the call and some that are unique calls. Meaning if they have serviced the same person 5x in the quarter I can only count that once.

There can be muliple people on a service call so I have these:

EMPLOYEE1
EMPLOYEE2
EMPLOYEE3

(Entered_By field contains the employee who recorded the call and they would have gone on the service call)

Then I have to record who they contacted regarding the account. The SHORT_ACCOUNT_TITLE will often differ from who they have contact regarding this account. So I have this field:

CONTACT

Then since muliple employees go on service calls together some may have already contacted this customer aleady. I have to keep track of employees unique customer contacts. So I have these fields:

CREDIT1 (checkbox for employee listed in the ENTERED_BY field)
CREDIT2 (checkbox for EMPLOYEE1)
CREDIT3 (checkbox for EMPLOYEE2)
CREDIT4 (checkbox for EMPLOYEE3)

Then I have to keep track of service calls regardless if they where unique ones or not. So I have this field:

ELIGIBLE

Then I put a notes section in so when I am filtering the data and finding what calls where good calls and need to make changes or whatever I can have a place to put that:

NOTES

Finally since some of these customer calls involve large amounts of customers (group meetings, etc) I have this field to type in a number of additional credit:

ADDITIONAL_CREDIT1
ADDITIONAL_CREDIT2
ADDITIONAL_CREDIT3
ADDITIONAL_CREDIT3

------------------------------------

So since I have to import Excel rows that contain the data I start with I am unsure of how to set this up and normalize it. I have attached my form I have used to find what fields I would need to show you more of what I am trying to do.

This form allow me to filter data that shows up in the subform. Then I can just double click it and bring up the record. Then mark the ones that count.

I am looking for any suggestions. Thanks.

 

[Jakboi]

Would this be ok, I am not sure if the columns for the imported excel sheet should be kept by itself in a able.

tblImport
ACCOUNT_NUMBER
SHORT_ACCOUNT_TITLE
CONTACT_COMMENTS
CONTACT_TYPE_TEXT
ENTERED_BY
INITIAL_CONTACT_DATE
DATE_ENTERED

tblEmployees
EMPLOYEE1
EMPLOYEE2
EMPLOYEE3

tblCredit
CREDIT1 (checkbox for employee listed in the ENTERED_BY field)
CREDIT2 (checkbox for EMPLOYEE1)
CREDIT3 (checkbox for EMPLOYEE2)
CREDIT4 (checkbox for EMPLOYEE3)
ELIGIBLE

tblAdditionalCredit
ADDITIONAL_CREDIT1
ADDITIONAL_CREDIT2
ADDITIONAL_CREDIT3
ADDITIONAL_CREDIT3

tblNotes
NOTES
CONTACT

Does anyone see any issues with this or a better way to do this?

 

[CraigDolphin]

Jakboi,

have a look at the attached relationship diagram. This is what, I think, your db calls for to get it properly normalized. You might need to add some fields to the customer table to store first and last names separately, add addresses etc.

However, since you have to start with a flatfile spreadsheet you're going to have to write some visual basic to take what's in your flatfile and programmatically populate the rest of the tables in your database. You're probably also going to have to deal with very messy data from excel where people enter something unexpected into a cell because something unanticipated happens. Like 5 employees go on a call, and someone decides to type two or three names into one cell because your spreadsheet doesn't allow for more than 4.

Your life would be considerably easier if you could bypass Excel altogether and just have people enter the data into your database using the forms you make. Form/subforms are a much easier way to go than trying to work with Excel since you can better enforce validation and data rules in Access.

Generally, the process will involve importing the flatfile of new records to a table. Then, you'd need to parse out what's in those new records and add them to the relevant tables using append queries.

First off you'd have to check for new account numbers, employees etc and add them to the Accounts table or Employee table respectively.

Then, you'd need to add new 'contacts/calls' to the Calls table. For each record you add you would need to define the CallDate (value from the INITIAL_CONTACT_DATE field?), and CallTime (if applicable), and the relevant AccountID.

Then, you'd add records to the CallStaffDetails table for that call. The first record you add would be the value from your Entered_by field, and you'd lookup the EmployeeID for that person and store that in the EmployeeId field, along with an CallRoleTypeID of 1 (=entered by)

If there's a value in the EMPLOYEE1 field you'd add another record to the CallStaffDetails table with the appropriate EmployeeID and a CallRoleType of 2 (means they did not enter the record but did attend the call).

Repeat this for the EMPLOYEE2 and EMPLOYEE3 fields in the spreadsheet.

Once you've populated your db, delete any records in your import table to clean it up for the next batch import.

Then you can simply go through the calls and add credits or not (if a call has no associated record in CallCredits then you know no credits were given to that call. Credits currently come in two flavors (more could be added easily with this structure): "Normal" and "Additional". You can add as many credits as you want. Simply use a select query to group by callID and sum up the CreditAmount field in the CallCredits field to determine how many credits a call gets.

 

[Jakboi]

Thanks for the assistance. See the excel sheet is actually a database query from a purchased database that strores a lot of data from our division. I have tried serveral times to either be allowed to connect via odbc or some other way in order to automatically get the data.

The data in the import will bascially be the same except for CONTACT_COMMMENTS. Everything else is either dropdown boxes, etc.

I will take a look at your suggestions and see what I get. Thanks for help.

--------

Currently I have to take the spreadsheet and do this manually. I have to read the CONTACT_COMMENTS and find out who else went on the call, who they contacted on the account, what quarter this fell into, was it first time they were contacted...etc. Then each month and quarter add up all the calls that employees have and the unique ones...which is quite cumbersome.

I am trying to find a easy way of searching for the calls, marking them and producing the reports I need.

 

[Jakboi]

I think this might be right along what your saying or in general what I should do. I attached the image.

I will then just import the Excel sheets on a weekly basis and will do the append query to populate the data.

Is there anything wrong with this setup? I attempted to to follow your advice as closely as possible.

Thanks for the help and any feedback.

 

[CraigDolphin]

Um, not there yet.

Date is a piece of information about a contact, not an entity that you track, therefore to have a table of 'dates' in this context is not logical. CallDate should be a field in table that stores information about specific calls.

Also, you missed the point about the regular and additional credits. You keep placing additonal credit as extra fields in a table. This is completely the wrong approach. Stop thinking as if this were excel. Each additional credit should be a new line, or record, in a table. The number of fields stays the same, the number of rows in the table increases.

Go back to the diagram I showed you and have another look. All this is already accounted for in that design.

You can always piece together the information in the various tables using queries.

 

[Jakboi]

Ok. So I will take a look at yours again, I tried to understand what you were saying. This normalization thing can be tricky.

So I can use something very similar to what you setup in your picture then add a seperate table for the imported Excel sheet. Then I would be able to use some append queries to add the data from the imported worksheet to the correct tables right? Think that sounds right.

Let me take a closer look at what you did.

Thanks for the continued assitance on this.

 

[Jakboi]

Ok I think I understand what you are saying now. I have attached the image which is bascially what you had before. So this is all I would need, correct?

The employee role would be say like this:

1 = Entered_by
2 = Went on Call

The credit type would be as follows:

1 = Normal Call Credit
2 = Unique Call Credit
3 = Additional Call Credit

The date table I took your advice. Similiar data should be grouped together in tables so I assumed since date is a different type of data then it should be by itself, but that is more associated with the calls.

My only question is about the tblEmployees. Now since normally no more than 4 employees will go on a call how should that be setup. How would I add additional people on the call? Should I have Employee_1, Employee_2, etc in that table?

I guess I dont know what I would bound to the text fields in my form? I have a table setup seperately that simply lists all the employees and would use this to choose additional employees that went on the call.

If I have on my form 5 dropdown boxes to add addtional employees who went on the call, how would I show this in my table or tables?

I think I am getting closer. Thanks. I just need a bit more clarification on the employees part of it.

 

[CraigDolphin]

tblEmplyees is a table to hold information specific to a person: like their name, SSN etc.

Each employee who goes on a call is a new record in tblCallDetails. So, if you have a form based on tblCalls with fields for date entered, account id, etc you would have a subform bound to tblCalldetails in continuous form view. That subform would have a field for the employee (probably a cvombo box), a field to show the emplyees role in the call etc. To add more employees to the call, you just add a new row of data in the subform. Simple as that

And if you need to add a new employee to the db, just add them to the tblEmployees. Incidentally, don't you want to include details like the Employees name in the Employees table so that you can recognise which id belongs to which person?

The point of normalization is not to put similar data types into their own tables but rather to put all data that depends solely on the primary key into the same table. For example, a person's name and birthdate is information solely about a person and therefore belongs in a table about people. The date of a call depends entirely on which call you're talking about, therefore it belongs in a table about calls.

However, when you start getting into one-to-many situations you sometimes need other tables to handle that. For example, many people might attend a call, and one person might attend many calls. That's where junction tables like tblCallDetails come into their own.

Now, as I understand your earlier post, the person who entered the call information also attends the call? So, when you add person X to the tblCallDetails for a particular call, you give them the roletype Entered_By. Other attendees get some other roletype. If you want to know who entered data about a particlar call you simply query or lookup tblCallDetails to find employees with roletype = Entered_By associated with that call. There's no need for a separate Entered_By field in tblCalls as it is only duplicating the information that's allready available to you.

 

[Jakboi]

I appeciate all the help and explanations. It seems like normalizing is one of the most difficult and most crucial areas of Access since its the foundation. Yes the "ENTERED_BY" employee definately goes on the call. I just need to input other employees who attended the call.

Ok so for the most part would you consider this to be "Normalized". If you thought this was bad you should a database I put together about 10 months ago. I have to redo that one eventually as well.

I think now I will redo my form if this is set and redo my queries. Thanks for all the help with getting my tables setup correctly and with the explanation.

+rep <-- says i cant till I spread some more...

 

[CraigDolphin]

Normalization is an artform, and you're quite right: it is the most critical thing to get right in your database design.

I like to say that code, forms, reports, and queries are the wrapper around the core of your database: your tables, relationships and data. No matter how shiny the wrapper, if your tables are turds, then all you have are nicely wrapped turds! Proper normalization makes that core a gem and it's worth keeping even if the wrapper is ugly.

I think your db is now normalized for the most part. As I was trying to allude to earlier, your decision on how to handle the entered_by employee might denormalize your db to some degree if you continue to use a field in tblCalls for Entered_by. Up to you though.

And thanks for using the rep thing even if the system won't let you this time.

 

[Jakboi]

Hmmm in regards to my tblCalls I have the following:

CALLID
ACCTID
INTIAL_CONTACT_DATE
DATE_ENTERED
CALL_NOTES

I have changed the following under tblEmployees to the following:

EMPLOYEEID
EMPLOYEE (aka Entered_By)

Your stating if I used "Entered_By" in both tables right? Currently I am just using it in the one table. Sorry if I misunderstood you.

 

[CraigDolphin]

No. tblEmployees should be like
EmployeedID (auto, pk)
EmployeeFirstName
EmployeeLastName
EmployeeSSN
etc

tblCalls looks fine.

tblCallDetails has all that you need to know who entered the call (this would be the record where callID = whatever the call in question is, and EmployeeRoleID = whatever the right number is that matches employee role type 'Entered_by'). (and, incidentally, on closer inspection you appear to have an unnecessary field [CallCreditID] in this table)

There is no need to have an additional 'Entered by' field in ANY table.