Allowing Duplicates based on multiple fields

[AC5FF]

I've been playing around with a new database design and ran into a possible 'error' that I would like to avoid.

It's going to be a payroll database to store time codes for hours spent working on specific projects. I have been struggling on how to put this together to fit with what we've been doing for years and I think I hit a few breakthroughs this morning.

However I want to avoid this error of possible duplication of entry.

Simple table set up - primary key is just a running integer; Employee ID; and Week Ending Date.

I can have multiple week ending dates for a specific employee; but I want to avoid having the same employee with the same week ending date. I cannot set up either field as being unique.

Quick run of data that would be in this table:

1          ABC      11/21/2014
2          ABC      11/27/2014
3          ABC      12/07/2014
4          DEF      11/21/2014
5          DEF      11/27/2014
6          DEF      12/07/2014
7          ABC      11/27/2014

in this example, when the last row is entered I need to get a popup or some warning that this time has already been entered.

Possible?

Thanks

 

[Minty]

I'm not sure you have got the data structure right.
I'm guessing you have historically been storing this on a spreadsheet.

You really need to break down the data fields to be more useful. Without knowing a bit more about your requirements, I would probably look at storing a start and end time for each record, then sum these over a period, whether that is a day / week / month etc.

This will remove your problem with duplicate weeks as they won't be what you are storing. As I say this is a bit of a finger in the air guess.

 

[AC5FF]

Minty

Thanks for the reply here. This is only the start of my rework.

This is the main table, but the real data table is more of a sub table.
The sub table will have 8 fields; first being the accounting code, then the 7 days of the week. So in essence it will store data like:
6014.23, 0, 3, 2, 0, 4, 0, 0

Where 6014.23 is the accounting code, and the following 7 numbers are the number of hours worked on that day of the week (M,T,W,Th,F,S,Su).

The old way this has been done is on a spreadsheet each individual would fill out, but then it had to be entered into a database line by line. So, if I worked on 8 different accounting codes during the week, I could have 40 or so entries just for me. Right now each day would be stored on a separate row. Way to much work to re-enter everyone's time.

What I want to work towards is a form to fill out that looks just like the spreadsheet everyone is used to; but yet storing it directly into Access.

I've got a few more challenges to overcome - mainly on setting up the form and auto filling it with data based on the employee's historical work. But those questions are for later and in different forums as this project progresses.

 

[Galaxiom]

A normalized structure would have each day stored as a different record. Continuing with your current structure will run into trouble when you want to Sum all the hours worked.

However, to answer your question, you would put a composite unique index on EmployeeID and WeekEnding.

 

[AC5FF]

Galaxium

I understand what you are saying - as far as normalization of the database; to my knowledge that is what I am struggling to do.

As I mentioned before; employees fill out timesheets that list out there hours worked and these have to be manually entered. Well; I just entered 12 time sheets into the database and that required over 150 records entered.

If I understand what you are saying - then I would want these all entered into a table as:
ABC, 12/1/14, 1000, 8
ABC, 12/2/14, 1000, 5
ABC, 12/3/14, 1000, 2
ABC, 12/1/14, 2000, 1
ABC, 12/1/14, 2000, 3
ABC, 12/1/14, 2000, 7
etc...

That's essentially what I have now. It is also what will happen once data from this entry is submitted. I will run append queries on this table to add them into my official table and clear out the data from the entry tables.

I've racked my brain on how to accomplish this task and what I am working on has been the best I have been able to come up with.

HOWEVER.....

I'm open to ideas... I am attaching an excel file that everyone fills out so you can see an example. Anywhere that a time is entered during the week needs to be stored as a separate record.

Ideally when I am done I want my form to closely resemble this spreadsheet. When a user enters their name at the top the rows below will auto populate with their 5 or 6 most used account codes. the next rows will be blank to let them add whatever account that would be needed. Once filled out it will run a check on total hours entered to verify they are within a certain range; and no single day exceeds so many hours.

At this point a query or two will run to put each time entry into a final table and the data stored in this area will be deleted. I thought about running the form using a query or two and not storing info at all. But I need a user to be able to open the form today and fill out his/her information, then return to it tomorrow and update the information.

clear as mud right? HA! That's about my feelings on it right now...
:banghead:

 

[CJ_London]

@AC5FF - Don't know if you realise it but Galaxiom has provided the answer

However, to answer your question, you would put a composite unique index on EmployeeID and WeekEnding.

If you are not sure how to do this, see this link

http://www.access-programmers.co.uk/forums/showthread.php?t=272537

 

[AC5FF]

CJ

Yes I saw that. I just went on about the normalizing of my setup.
I don't know a thing about what Galaxium said so I appreciate the link. I will read up on it tomorrow. ..

 

[Minty]

Although there will be pain initially if you go down the record per person per day, in the long run it will make your life a great deal easier.

I think the easiest way to get to a good normalisation idea is to try and strip away everything except the actual information you need at its most basic level. Anything else is normally added fluff.

In your case to actually record the basic information you only need an employee ID a date, and either start and finish time, or hours or minutes depending on your business model.

TBH You could even get rid of the date if you recorded start finish times as date time fields.... (This could be a step too far for some people)

 

[AC5FF]

Minty

I am all for making this easier - even if it takes a pain to convert it over. I'm not sure I fully understand your view of this though. You say all I really need is the employee ID, date, and start/finish times or hours. Yet how do I code that over to a specific account code?

Did you look at the excel file I uploaded here? If there is a better way to store this information I'm all for it - I just can't see the forest through the trees yet.

Thanks!

 

[Minty]

Sorry, I am not on my PC any more, I'll have a look after the Christmas break for you though. Seasons greetings !

 

[AC5FF]

Thanks! Have a great holiday yourself!

 

[CodeMonkey]

AC5FF,

may I ask how the report on the stored and aggregated data will look like? Do you need to preserve the same structure Excel represents or transform it into something bigger/lesser?
Because if you will break the tables with manually-input data into small bits you may find yourself some hot time on aggregation queries after.

 

[AC5FF]

Essentially the data is taken from the excel sheets and manually entered - much like in post #5. Each block in the excel sheet that contains a number is a single line entered.

When I get back to work I will have to look up aggregation queries. Not sure I fully understand that.

 

[Minty]

I've had a look at the spreadsheet properly now, and I would suggest a fairly simple set up.

Initially you will need 3 tables; One with your employees details, one with your account codes/ work types, and finally the billing data - where you tie in the other tables with the hours worked.

Simple layout attached - I've made the assumption that the work types and billing/ account codes are fixed with relation to each other. If not you would need to break these down into another pair of related tables.