Normalizating a giant table

[umair434]

Hi guys,

I have this huge table with the following columns:

Id
Date
Employee Initials
Time of Schedule
For Report 1 (Total Available Products)
For Report 1 (Total Actioned Products)
........
...

..
For Report 15 (Total Available Products)
For Report 15 (Total Actioned Products)


Now I know this table is huge, but this is true import from an excel file. Different employees can work on the same date. Each employee fills in the data for some of the Reports (1-15) not all, while other employee may record data for different report numbers. These reports are basically recording the activities of an employee during a shift (hope i'm not confusing you

How should I normalize this table and break it into small tables?? Any ideas?

I'm really new and bad at this, so please help if any of you can

thanks!!!

 

[jzwp22]

Each employee fills in the data for some of the Reports (1-15) not all, while other employee may record data for different report numbers. These reports are basically recording the activities of an employee during a shift (hope i'm not confusing you

It sounds like the reports are generated based on activity data. Can you explain more about the activities and what specific information is recorded about each you record. Do these activities relate to something such as a project, a customer etc.?

Some example data might be helpful

 

[umair434]

Thanks for your help mate!!

so there are different activities an employee might do in their shift. for example, they might "slot" the products from an area of lower demand to an area of high demand (Im working for a warehouse distribution center). Other activities might be to see how many products were available in 1 part of the warehouse, and how many of them were actioned (slotted, replenished, removed etc)

so these 15 reports name are assigned for different things that an employee might do.

This data is solely entered by "slotters" - and is currently being entered in an excel. An ideal situation would be to creat a front end interface (forms) in Access where they enter information and that's it. The higher management can print out reports to see what they have been doing (which areas are being focused more)

I hope I'm making sense.. I am a new intern for this company, and even new to access :/

thanks again

 

[jzwp22]

From the sound of it, you need three basic tables to start.

First a table to hold the information about the employees/people

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName

Then a table to hold all possible activities

tblActivities
-pkActID primary key, autonumber
-txtActivityName

Now a table to hold the person and the activity they performed

tblPeopleActivities
-pkPeopleActID primary key, autonumber
-fkPeopleID foreign key to tblPeople
-fkActID foreign key to tblActivities
-dteAct (date field to hold when the activity was performed by the person)

 

[umair434]

thanks!!

but I need to enter "numbers" for these activities. For example, John found out that there were 12 Caseflow available products, out of which he actioned 8 of them:

Caseflow Total Available: 12
Caseflow Total Actioned: 8

now these are the two fields names.. how should i incorporate that in the activities table?

 

[jzwp22]

For the actions you mentioned, the following shows the records in each table

tblPeople
pkPeopleID|txtFName|txtLName
1|John|?

tblActivities
pkActID|txtActivityName
1|Caseflow available products
2|Caseflow action products


Add a new field to hold the amount value on which the action is applied
tblPeopleActivities
-pkPeopleActID primary key, autonumber
-fkPeopleID foreign key to tblPeople
-fkActID foreign key to tblActivities
-dteAct (date field to hold when the activity was performed by the person)
-ActAmt

-pkPeopleActID|fkPeopleID|fkActID|dteAct|ActAmt
1|1|1|07/19/2011|12
2|1|2|07/19/2011|8

 

[umair434]

i am just going to use your approach to see what I come up with.

thanks for your time

 

[jzwp22]

What I proposed is just a basic core, you will have to expand on it. I assume that you have other fields and will need other tables, so please post back with questions.

 

[umair434]

Hey!

so i used the tables you suggessted:

TblEmployees (Employee_Id, First Name, Last Name)
TblActivities (Activities_Id, Activities Name)
tblActivities_Act (Employee_Id, Activities_Id, date, time, Amount)

Now I made the relationships and a query which seems to work fine. I am trying to create a form where the user enters information (First name, last name, date, time, activity they are doing and the amount) and this information is saved in a new table.


Now the problem is that everytime I enter new information, it overwrites previous record in the table I made from the query. How do I prevent overwritting?

Thank you for your time!!

 

[umair434]

ahhh figured it out.. was easy lol

anyways, I would really appreciate some more insight into this matter. So I have my tables working... I can add data to the form and new records are being displayed in the tblQuery.

one thing I wanted to ask is that I have heard that there is a function/code that allows the reads the user's information as they log in to the computer. this would mean that if John Flemmings logs in the computer, the form will automatically show

First name: John
Last name: Flemmings

so they would just have to enter the following:

Time
Date
Activity
Amount

hope I'm making some sense. And thanks for your support

 

[umair434]

"that allows access to read the user's information*"

 

[jzwp22]

One question, are you using a form/subform design for your data entry form with the main form being bound to the employee table and the subform bound to tblActivities_Act?

one thing I wanted to ask is that I have heard that there is a function/code that allows the reads the user's information as they log in to the computer. this would mean that if John Flemmings logs in the computer, the form will automatically show

I have heard of that & found this site that has the code.


I generally have the user to log-in specifically to the database (that is just my preference) via a form I provide.

BTW, this sounds like it will be a multi-user application. Do the users each have their own computers from which they will be loggin in & connect to a server? If so, you will want to split the database into front and back ends. Each user gets a copy of the front end that they put on their local hard drive while the back end goes on the server. There is a utility in Access to do the splitting.

 

[umair434]

I created a form which was bound to the tblQuery (which had all the necessary fields as I made this table using a query). Does that make sense?

and yes, different users will be using this application. Could you please point me a bit more towards the splitting function?

again, I appreciate your help and quick reply!! thanks!

 

[jzwp22]

...and yes, different users will be using this application.

Over a network?

If over a network then it is best to split. In Access 2007/2010, go to the Database Tools tab-->Move Data and select Access Database

In Access 2000/2003, I think it is under tools-->database utilities (but I may be mistaken)..

 

[umair434]

Yes it is over a network!! But when I split the database, the form doesn't work - It says that the table it is connected to is not available :S

Also, I created a module and used the code from the website you provided. How do I activate the module? I'm sorry for asking so many questions (some of them might be really stupid :/)


thanks again for your help!!

 

[jzwp22]

Yes it is over a network!! But when I split the database, the form doesn't work - It says that the table it is connected to is not available :S

When you did the split, Access put the back end file somewhere. Did you move it from that initial location? If so, you will have to relink the front end to the back end. (In Access 2007/2010, External Data-->Linked Table Manager).

Also, I created a module and used the code from the website you provided. How do I activate the module? I'm sorry for asking so many questions (some of them might be really stupid :/)

I can call a function without a problem when doing something in an already open database, but I was unsure of how to call the function as the database opened. So, I was playing around with it and decided to open up a form that copied the username (using the function) to a textbox on a form. Also on the form is a button that when clicked opens up the form to enter the activity data filtered to the specific user. I could not figure out how to open the activity data form directly to the user when opening the database. Perhaps others on the forum may have a way of doing it. I have attached a database that illustrates the technique I used.


By the way it is best not to have spaces or special characters in your table and field names (the _ is OK). Also, time and date are reserved words in Access, so it is best not to use those as field or table names. You can check out this site for more reserved words.

 

[umair434]

wow!! thanks for putting your thoughts into it.

one thing - when I open your database, the windows ID gets updated, but when I "click to continue" to gives an error saying Invalid use of Null :S

thanks buddy!

 

[jzwp22]

You have to enter a user (probably yourself) in the employee table. Here where I work, our network user name is firstname.lastname so I set it up like that. I don't know how your network usernames are configured. You may have to adjust the query I included in the database. You should be able to see the user name in the textbox on the initial form below the button to see how your network has it configured.

 

[umair434]

got it! my network uses the same format (firstname.lastname)

thanks

 

[jzwp22]

You're welcome. Good luck with your project.