need help with database design 3

[y2k1981]

hi all,

can somebody help me out here - I can't seem to figure out how I should store my data. I'll try to explain this as best I can. I'm desinging a db to record information about what claims an employee works on each day. I have roughly 20 employees, so I figure have an employees table. Then, at the end of each day, each employee logs (on paper form) how many of each type of claim they worked. They won't have worked some of each type of claim (so say there are 10 different types, they may have only worked one type that particular day). The db will then be updated by one person with the number of each type of claim worked by each employee. The problem is, I really need 3 headings, employee (say along the side), production date, and also a column for each type of claim. So, what would be the best way to go about this? Do I need to use subtables? The person that will be updating this only has Access 97, and subtables aren't a part of 97 are they? If I were to do it in subtables, how would I go about doing it?

The data would need to be stored in such a way that we could query how many claims in total were worked each day, and by each employee each day, as well as being able to query how many of each type of claim were worked on a particular day. And to also be able to extract this sort of info at the end of the year.

Any input would be appreciated. thanks inadvance

 

[dhookom]

You should NOT create fields for each type of claim. I would create a table of employees with one record for each employee and an Autonumber primary key.

Then create a table of claim types with one record for every different claim type. Again, I always use an Autonumber primary key.

Then create a table of logged work. This table will have an Autonumber primary key, WorkDate, EmployeeID, ClaimTypeID.

This will be the most versatile structure so you can easily add more Claim types.

Duane
MS Access MVP

 

[willir]

y2k1981

I will address some of your concerns towards the end.

The employee table will be straight forward.

tblEmployee
EmployeeID - primary key, autonumber will be fine for 20 employees
LastName - text
FirstName - text
etc


I agree with Duane's idea of logged work.

tblWork
WorkID - primary key, autonumber
EmployeeID - long number, foreign key to Employee table
ClaimType - see discussion
WorkDate - date field, default INT(NOW())
etc...

ClaimType can be accessed from a supporting table; OR it can be included in a combo or list box where you have manually entered the "pick list". If the ClaimType tables adds value -- description, costs, etc, then use the table. If not, an additional table is not really necessary. I would not use an autonumber as suggested by Duane; rather I would use the ClaimCode, provided it is short enough, as the primary key.

tblClaimType -- If requried
ClaimType - text, primary key
+ additional information for the claim type.



Back to the work log...

For the tblWork...
Do you want to include comments?
Do you want to indicate claim has been resolved?
Do you want to track claims??


I guess what is missing is if you want to enter the claims into the database. If you want a report on who worked on what claim, track when a claim was opened and closed, etc., you will need to create a tblClaims.


Okay, now for your questions...

A "table" is an object in Access. Tables are used for tracking details for a specific "thing". I like to look at these "things" as "object nouns" (my own terminaology, not from a book). A great example is the table for employees.

I think you are confusing "subtables" for supporting tables. With a relational database, a table may provide supporting information for another table. For example, a company table and an address table can supply information for the employee.

The idea of separating characteristics for each object noun and avoid redundancy has a technical name called "normilization". In a spreadsheet, you may have the same company name next to each employee. In a relational database, the two are separated into different tables -- characteristics for an employee are kept in the employee table; characteristics for the company are kept in the company table.

Each characteristic is usually tracked in a different field. Each field tracks the characteristic, and will have a field type -- numeric, text, date, etc.

JermeyNYC, a very bright flame in this forum, has an article written by Paul Litwin which is highly recommended by many. It will provide useful information that you should digest before proceeding...

http://www.abcdataworks.com/Developers/Index.html

This should give you a good start.
Richard

 

[y2k1981]

Thank you both for your responses, however, maybe I didn't explain properly the first time, or else I'm missing something. I need to record how many of each type of claim, each employee works on each day. as far as I can understand, the above will only tell me which types of claims each employee worked, but now how many of each. Basicly, this is a production log, so to answer your question Richard, no, I don't need to record any information about any particular claims or who worked on them. We just need to record the fact that I worked 10 Type A claims, 2 Type B Claims and 9 Type C claims while John Doe worked 16 Type B Claims and Joe Bloggs worked 10 Type D Claims on a particular date. So can you help me out at all?

Really do appreciate your help

 

[RickSpr]

tblEmployees
EmployeeID - autonumber, primary key
LastName - text
FirstName - text
etc.

tblClaimsWorked
ClaimsWorkedID - autonumber, primary key
EmployeeID - number (long), foreign key to tblEmployee
DateWorked - date/time, default Date()
ClaimType - see discussion
ClaimCount - number (long)

Refer to willir's discussion of ClaimType. Unless you have additional information (other than counts) to save about claim types, I would define the field to have a list box or combo box control with a Value List (see the table design Lookup tab). You can easily convert this to use a table if you want to add more info later.

Sample Queries (to show you can get the info you want):

Daily summary by employee:
SELECT LastName, FirstName, Sum(ClaimCount)
FROM tblEmployees INNER JOIN tblClaimsWorked
ON tblEmployees.EmployeeID = tblClaimsWorked.EmployeeID
GROUP BY LastName, FirstName
HAVING DateWorked = Date()
ORDER BY LastName, FirstName
Use a CrossTab query to get employees and claim types on separate axes.

Daily summary by claim type:
SELECT ClaimType, Sum(ClaimCount)
FROM tblClaimsWorked
GROUP BY ClaimType
HAVING DateWorked = Date()
ORDER BY ClaimType

For monthly or annual summaries, just change the HAVING clause
HAVING DateWorked BETWEEN #1/1/2003# AND #12/31/2003#

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[willir]

Thanks Rick for following up on this.

y2k1981
As you can see with Rick's SQL statement this type of design should meet your objectives.

To confirm this,
- create the tables per specifications
- add some dummy data
- run the select statements

Here is a sample...

tblWork / tblClaimsWorked

ID EmpID DateWorked ClaimType ClaimCount
1 1 10/18/03 THEFT 5
2 1 10/18/03 ACCIDENT 3
3 1 10/18/03 WEATHER 6
4 2 10/18/03 THEFT 2
5 2 10/18/03 WEATHER 4
6 3 10/18/03 ACCIDENT 2

The key to this design is cusing ClaimType to collolate your data.

Since you are just tracking work in a summary fashion, the design is much simplier than if you were to track claims.

Good luck
Richard

 

[y2k1981]

Hi All,

Yes willr, it's just after I had a read of some of the pages on that link you provided that I eventually figured out where you were coming from. But thank you all for your posts. I have two questions, is there any need to create an autonumber for the primary key for the ClaimsWorked table? Couldn't I just have the employeeID, DateWorked and ClaimType fields as a composite primary key? Or did you just want to avoid using too many fields?

Also, is there any way that I have type in the Employee's ID# and the First and Last Name fields would automatically be populated when you hit the <TAB> key?

Rick, your query didn't quiet work in Access (2000). When I tried to run the query, it says &quot;You tried to execute a query that does not include the specified expression 'DateWorked=Date()' as part of an aggreagate function&quot;. I went into the query in design view and remove the DateWorked field and then readded it and set the criteria to &quot;=Date()&quot; and it worked ... however, when I run the &quot;annual&quot; query, it's grouping the date and then by claim type, so instead of telling me that there were 10,000 Type A claims worked through out the year, it tells me that there were 15 worked on one day etc etc etc. I went into SQL view and noticed it had changed your SQl to the following:

SELECT tblClaimsWorked.ClaimType, Sum(tblClaimsWorked.ClaimCount) AS SumOfClaimCount
FROM tblClaimsWorked
GROUP BY tblClaimsWorked.ClaimType, tblClaimsWorked.DateWorked
HAVING (((tblClaimsWorked.DateWorked) Between #1/1/2003# And #12/31/2003#))
ORDER BY tblClaimsWorked.ClaimType;

I tried removing tblClaimsWorked.DateWorked from the GROUP BY clause but then it won't run.

Can you help me out ?

Thanks again for all your help everybody

Regards
Martin


How can I stop it from grouping them like this?

 

[dhookom]

I always add an Autonumber primary key to every table. The only exception that I would make is when there is no table with many records related to one in the existing table. You can create a unique index on a group of fields without making it a primary key.

All data entry should be done in forms. This allows you to use a combo box to select an employee. Your combo box can display the employees' names rather than the PK of the table.

Try:
SELECT tblClaimsWorked.ClaimType, Sum(tblClaimsWorked.ClaimCount) AS SumOfClaimCount
FROM tblClaimsWorked
GROUP BY tblClaimsWorked.ClaimType
HAVING (((tblClaimsWorked.DateWorked) Between #1/1/2003# And #12/31/2003#))
ORDER BY tblClaimsWorked.ClaimType;


Duane
MS Access MVP

 

[RickSpr]

Martin,

Yes, you certainly can use the composite key rather than an AutoNumber. In fact, it's probably best if you do. Even if you did use an AutoNumber key, you should have a unique index on those three fields anyway, to prevent accidental duplicate data entry such as using the previous day's date, or entering the claim type twice.

I gave it an AutoNumber key from force of habit. I almost always give primary tables AutoNumber keys. But this is actually a many-to-many associative table between employees and claim types. In general, many-to-many tables should be keyed with their foreign key columns. I just wasn't thinking.

You can easily have the employee name pop up by basing your form on a query that joins tblClaimsWorked and tblEmployee. Make sure that your EmployeeID control is bound to tblClaimsWorked.EmployeeID. Simply drag the name fields to your form to create text boxes. (You will probably want to make these text boxes disabled and locked to keep them from being edited.) That's all you have to do--Access will automatically look up the employee name for you. This feature is called AutoLookup, and it applies to looking up data in the &quot;one&quot; side table when you update the foreign key in the &quot;many&quot; side table.

My mistake on the queries. Those HAVING clauses should be WHERE clauses, and should come before the GROUP BY clauses. When you removed and re-added the Date() criteria in the first query, you converted the HAVING to a WHERE, so it worked. To make the annual summary work, modify the WHERE clauses to include the date range.

To make up for my mistake here's a query that will give you the annual summary for the current year, so you don't have to plug in the beginning and ending dates:
SELECT LastName, FirstName, Sum(ClaimCount)
FROM tblEmployees INNER JOIN tblClaimsWorked
ON tblEmployees.EmployeeID = tblClaimsWorked.EmployeeID
WHERE Year(DateWorked) = Year(Date())
GROUP BY LastName, FirstName
ORDER BY LastName, FirstName

And here's one for the previous year:
SELECT LastName, FirstName, Sum(ClaimCount)
FROM tblEmployees INNER JOIN tblClaimsWorked
ON tblEmployees.EmployeeID = tblClaimsWorked.EmployeeID
WHERE Year(DateWorked) = Year(Date()) - 1
GROUP BY LastName, FirstName
ORDER BY LastName, FirstName



Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[y2k1981]

Thanks Rick, I was trying to get the Names to autofill in the table, that's why I couldn't get it to work. Two final questions, how do I prevent them from being updated and how do I ensure that they are required fields. That way if somebody enters an incorrect employeeID# (say 200 instead of 20) they'll get some sort of error message?

Also, I'd like to put this in a form rather than having the users enter data straight into the table, as Duane suggested. How could I do this so that the user would just have to enter the employeeID once and then select each type of claim from the drop down menu and enter the amount of corresponding claims. So that it looks like each employee really has only one record for each day. Basicly, like an invoice would appear. With the repeating data at the bottom. I tried doing this by putting the employee# etc in the form header and then repeating the ClaimType drop down menu in the detail but each time I change one of them to the ClaimType, they all change !! Is there any way I can do this, or am I trying to re-invent the wheel here?

Thanks again for all your help
Martin

 

[RickSpr]

You've lost me in your first paragraph. Are you talking about AutoLookup? You can't do that in a table. You need a form for that.

To prevent the name fields being updated, set their controls properties Enabled=No and Locked=Yes. You don't need to make the name fields required--that's for controls in which you want to allow editing. What you need is to ensure the EmployeeID control has a valid value. Since this is the foreign key, Access will automatically take care of this if you have defined a relationship between the tables and specified Enforce Referential Integrity on it.

For the form design you're thinking of, I'd suggest you'd need a main form/subform structure, with the main form based on tblEmployees and the subform on tblClaimsWorked. There will be some challenges making it look and function as you want it to, but that's the basic idea to get you started. If you run into difficulties with it, the Access Forms forum would be a good place to ask for help.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[willir]

Boy, I take a day off, and so much activity -- all of it good.

Both Duane's and Rick's comments on autonumber are valid.

In general, queries and data integrity are easier to write using a single field rather than mutiple fields. (And by all means make the the three fields unique to avoid duplicate data per Rick Sprague's comments.)

However, autonumber is apparently not perfect. Please read MichaelRed's FAQ to be better informed. I have never had a problem personally, but I understand where he and others are coming from -- basically, large multiuser applications are more prone to have problems.

http://www.tek-tips.com/faqs.cfm?spid=700&sfid=184

Per your comment on auto-fill. Use a combo box to select the user. By tweaking the query and formatting, this interface will provide a simple and effect tool. Take a look at my last post for the following thread on the specifics...
Thread702-676694

Richard