Employee Attendance Database

[ddecker3]

Ok, so I have an attendance database
with 3 tables right now

TABLE NAMES AND FIELDS
Main - Fields (ID, Rank, Name, Shop, Status, DX/NX, Comment)
Muster - Fields (MusterID, ID, Musterdate, Atwork, DX,NX, Comment)
Shop - Fields (120, 220, 050, ect)

I am using and AppendQuery from the main, to the Muster table. But I was using subforms to open up all the differant shops via a drop down and IF/THEN.

Subforms are based off of queries for that shop.

I have 148 users, and everytime I try and update a subform it updates all 148 users.

1. How would I go about only updating the users from the shop for that day, without updating all users every time someone clicks Update.

2. Once everyone is updated for the day how would I got about creating a report stating something possible like this

SHOP Mustered OTHER

120 XX (Number Present) Number that did not muster (and if possible reason why (under comments)


Thanks for any tips or idea's

Decker

 

[Remou]

Please post your SQL. Where is information on the user held.

 

[ddecker3]

How do I post the SQL? I am kind of new at using Access/

Thanks

David

 

[Remou]

You say I have 148 users, and everytime I try and update a subform it updates all 148 users.

This suggests that you have a query. You should be able to look at the query in SQL View and cut and paste the SQL.

 

[ddecker3]

Here is the append query that I run:

INSERT INTO Muster ( ID, AtWork, Comment, [DX/NX], MusterDate )
SELECT MAIN.ID, MAIN.STATUS, MAIN.COMMENT, MAIN.[DX/NX], Forms![ALL]!MusterDate AS TodaysDate
FROM MAIN;

 

[lespaul]

I don't understand what is being updated on the 148 records? This just adds a NEW record to the table Muster. Now it will add the records returned by this query:

SELECT MAIN.ID, MAIN.STATUS, MAIN.COMMENT, MAIN.[DX/NX], Forms![ALL]!MusterDate AS TodaysDate
FROM MAIN;

and it looks like that will return ALL the records in MAIN because there is no WHERE clause


Leslie

Have you met Hardy Heron?

 

[ddecker3]

I thought it was supposed to add a new entry for each person for each day.

I needed a way to keep an accurate attendance everday with some extra info (DX/NX), comments.

 

[ddecker3]

What I wanted was a way to keep an attendance report for everyday. After reading around the above way is the way I found to work.

But it was kind of hard to checkbox everyone here (148users) everyday, so I tried breaking it down to each shop/class. From there, there is also DX/NX (Days/Nights)

For DX/NX I used a text field, and will require input of DX/NX, i could have gone with a combo box, but figured this would be easier.

Then there is the comments box. this is were someone can put things like LEAVE, TAD, UA.

All of this will be updated on the Muster table on a daily basis.

But it seems the way that i put in the subforms, and using the append, every time i run the query (even for a shop that has 7 users) it will do it for all 148 users.

Any help would be appreciated, or if you need further information.

thanks

 

[Remou]

You need to add something about the shop, for example:

INSERT INTO Muster ( ID, AtWork, Comment, [DX/NX], MusterDate )
SELECT MAIN.ID, MAIN.STATUS, MAIN.COMMENT, MAIN.[DX/NX], Forms![ALL]!MusterDate AS TodaysDate
FROM MAIN
WHERE Main.[Name of unique shop id in table main here]=Forms![Name of open form here]![Name of control with shop id here];

 

[ddecker3]

Would I need mutiple queries then?
I just created mutiple queries and call them?
or can you build/create them on the fly? I dont know which is easier/better

Decker

 

[ddecker3]

Ok this is what I have:

For one Shop now

INSERT INTO Muster ( ID, AtWork, Comment, [DX/NX], MusterDate )
SELECT MAIN.ID, MAIN.STATUS, MAIN.COMMENT, MAIN.[DX/NX], Forms![ALL]!MusterDate AS TodaysDate
FROM MAIN
WHERE Main.SHOP=Forms![120SUB]!SHOP;

But now it pops a small window when I run the query asking for:

Forms!ALL!MusterDate
If i put the date I want it will append the records, but its not pulling it from the main form anymore for some reason with the Where.

Forms!120SUB!SHOP
If i put the correct "120" in the box it will append 14 records,

thanks for all the assistance in getting me this far

X

 

[fneily]

Have you read:
Fundamentals of Relational Database Design

http://r937.com/relational.html

Seems to me your tables are not normalized.
Maybe:
tblEmployee
EmployeeID Primary Key
Firstname
Lastname
etc.

CrewWorkTable
CWTID Primary Key
ShopID (120,220, etc.)
EmployeeID Foreign Key
Rank
Shift (DX/NX)
Date
AtWork (yes/no)
Notes (Leave,TAD,UA) (if you can use just abbreviations like this, it would help in producing reports on each category)

Now you can pick off any shop and produce the report with the shop and its crew and how many showed up for work or not and why not.

 

[lespaul]

Maybe even:

tblEmployees
(all info about the Employee)
ID
Name
Rank

tblShops
(all info about the Shop)
ShopID
Name(?)

tblShopEmployees
(all info about the shop Employees)
ID
EmployeeID
ShopID
Shift

tblAttendance
(all info about Attendance)
ID
EmployeeID
Date
Notes

then you would create a form where you selected the shop, and it would display a list of the employees that worked at that shop and insert a record into the Attendance table and make any notes/leave information.

That seems even more normalized.

HTH

Leslie

Have you met Hardy Heron?

 

[fneily]

The question I had with rank is it a designation that spans crews or does it change when a crew changes. In other words, does the "leader" of a crew rotate between crews.

Also, if he has no shop info except name, then a shop table is not needed. By theory, you can over normalize. Like East, West, South, North - you wouldn't have a Direction table.

 

[ddecker3]

SHOP info is just

120
220
130
13A


Each shop as anywhere from 3-12 people in it.

Rank is like
IT1
IT2
AM2

Rank can be the same for anyone (multiple AM2's, ect)

I have not put into the work on who is the Leader of each shop, to much "not needed" work.

I have tried to do what lespaul said with normalzing, and well it looks fine, now I guess I will have to go back and recreate queries, and forms.

David

 

[ddecker3]

Also shifts switch on a regular basis, so shouldnt it be mainly in the attendance table?

 

[lespaul]

why don't you post the current structure you're thinking about going with so we can review it and give us a narrative of what you're trying to model. Details like the shifts switching and rank are good, try to include as many details as you can about what happens. Think about what you want to extract from the system...reports....it's hard to report on something if you don't capture it or can't calculate it.

HTH

Leslie

Have you met Hardy Heron?

 

[ddecker3]

Ok current table structures now:

MAIN (not a good name for a table probally)
id
rate
name
muster
comments

MUSTER
musterid
employeeid
musterdate
atwork
shift
comment

SHOPS
shopid
shop

TBLSHOPEMPLOYEE
id
employeeid
shopid

CURRENT RELATIONSHIP SETUP
MAIN-ID to tblShopEmployee-EmployeeID (1tomany)
MAIN-ID to Muster-EmployeeID (1tomany)

tblshopemployee-ID to Muster-EmployeeID (1toMany)
tblshopemployee-ShopID to SHOPS-ShopID (1toMany)

The information that I would like to capture on a daily basis

RATE/NAME SHOP MUSTER(ATWORK) SHIFT COMMENTS

There are only two shifts (days/nights), but they switch so fast, people also switch shops. About the only thing that will stay pretty current is the NAME. RATE can change (not to often though).

What I want to be able to do is

Have each shop Leader use the program to open up the muster program and it would show them

RATE NAME MUSTER(atwork) shift comments

For everyone in thier shop. Or at min, a drop down to select thier shop.

Muster will be a YES/NO, Shift would be either a drop down or just DX/NX, and comments are for things like LEAVE,TAD,UA.

Once they fill out the information for the day, it will save it for that dates so it can be pulled at a later time.

After I have all this stuff in line. I want to make a overall form to pull up a daily muster for all shops. Listing like this

SHOP Muster COMMENTS DX NX
X X out of X XXX NAMES NAMES
X X out of X
X X out of X

I think thats it for now.

I know its alot. If you want me to try and post a blank copy of what I have let me know also.

Dave