I am setting up a database to help me prepare codes for employees timesheets in order to upload them into our payroll software. The table structure below is just my preliminary thoughts and current ideas and I guess I'm looking for ideas on how to work with my codes.
EMPLOYEE TABLE
EmpID (PK) - Employee ID # [Autonumber]
Surname - Employee's surname [Text]
Firstname - Employee's first name [Text]
ATTENDANCE TABLE
ShiftID (PK) - Shift ID# [Autonumber]
EmpID (SK) - Employee ID# [Foreign Key]
Date - Date of shift [Date/Time]
Start - Start time of shift [Time]
Finish - Finish time of shift [Time]
CostCtr - Cost centre being billed for shift. [Integer]
When employees work they are entitled to the following:
* Ordinary hours (code 001) for all hours worked.
* 10% penalty (code 006) for all hours worked when shift finishes after 18:00
* 12% penalty (code 007) for all hours when shift crosses midnight
* 50% penalty (code 008) for hours worked on a saturday
* 100% penalty (code 009) for hours worked on a sunday
The following shows data that in my Attendance table for an employee who worked shifts on the 16th (Mon), 17th (Tue), 20th (Fri), and 22nd (Sun).
ShiftIDEmpIDDateStartFinishCostCtr
18443416/10/0610:0019:00
28443417/10/0610:0019:003002
38443420/10/0622:0006:003001
48443422/10/0614:0022:00
From the above data I believe I will need to make another table that contains the entitlement codes generate from each shift.
For the first shift on Monday 16/10/06 I need to collect the following codes for the total calculation:
CodeHoursCostCtr
0019.00
0069.00
For the second shift on Tuesday 17/10/06 I need to collect the following codes for the total calculation:
CodeHoursCostCtr
0019.003002
0069.003002
For the third shift on Friday 20/10/06 I need to collect the following codes for the total calculation:
CodeHoursCostCtr
0018.003001 'Ordinary hours worked
0078.003001 '12% penalty as shift crossed midnight hour
0086.003001 'Only worked 6 actual hours on the Saturday as 2 hours were on Friday night.
For the fourth shift on Sunday 22/10/06 I need to collect the following codes for the total calculation:
CodeHoursCostCtr
0018.00'Ordinary hours worked
0098.00'Hours worked on the Sunday
From that information the only data I really need to store in a table would be the totals grouped by code and cost centre. Eg.,
CodeHoursCostCtr
00117.00
001 8.003001
001 9.003002
006 9.00
006 9.003002
007 8.003001
008 6.003001
009 8.00
Does anyone know the best way to go about this? Should I generate a new table that links these codes to an employee? Should I make a function to calculate the codes for each day and store them in a table or make the function only sum the code totals for the week and store them in a table?
I have an attendance database and I connect the time attendance machine db to my access db, what i am trying to do is to generate a report that shows the time in and time out for specific date. the type of attendance db is date/time.
Please see the attached screenshot db from attendance machine.
an also some time there is duplicate entry, I need to get the first and the last entry only for specific date.
Any sample database where its about Time In and Time Out Attendance. It will just calculate the difference of Time In Time Out with employees Card Number.
The Time In and Time Out attendance is provided (no data entry), it is generated from other tool. I will just load it from an excel sheet to the access database and it will calculate and generate report. Default should be 9 hours per day, if they wont render 9 hours it will show in Difference column. Form will be like this:
ID Num: | Name: | Time In: | Time Out: | Total |Difference (missing hours)
12345 | Al Smith | 8.00am | 5:00pm | 9 | 0 54321 | Joy Pitt | 8.00am | 4.30pm | 8.30 | -0.30
I am trying to create an attendance application for my group of 6people, does anyone have or know where i can find any to look at. I don't even know where to begin.
Daily in and out. Keeping tabs of our vacation days/sick days. I just need to know where to begin, or need a sample for ideas in creating this. I have looked high and low. Please show me/tell me if you have any or how you did it, if you have done it before. Thanks friends!
I posted this here but didn't get any response. http://access-programmers.co.uk/forums/showthread.php?t=114099
I had trouble figuring out how to 1) register a group of people for a class, 2) create a list of dates to meet for a class (which I call sessions), 3) track attendance per sessions. Having looked for templates at Microsoft, I found plenty of Excel templates, but no Access. The closest is Student Registration template, which doesn't track attendance at all. So I figured I'd make a prototype to help me understand how everything works. Since I got so much input from everyone, I figure I'd return the favor and perhap make it a sample database, especially for those who desire a spreadsheet-like data entry while maintaining a properly normalized data structure.But I would prefer that other has tested and given feedbacks on the prototype before I put this in sample database forum (if that's okay with you admins) as this is my first time and I don't want to give others bad template. So anyway, here's the prototype.Note: The database is 100% undocumented, 100% error-handling free, and 100% unsecured. Use it wisely. :)PS: The attachment will reference a extraneous library. If you are getting an error, clear the reference for MS Office 11 Web Components.PSS: I knew I forgot something: There is still unsolved problem of correcting sorting the columns in datasheet view. While the underlying query correctly sorts the recordset, it seems to be ignored entirely in datasheet. If anyone has a solution, I'm all eyes here.
I would like to build a database to keep track of tardies and absences in my dept (~70 employees).
I need Date, Name, tardy or absent,
My current table:
TblEmployees EmployeeID PK Last Name First Name Team
TblEvent EventID PK Event (Tardy or absent)
TblAttendance Date EmployeeID EventID
Is this a good structure? I need to be able to run a query that will sum the total number of tardies and divide by 6. That number will then be added to the total # of tardies. The query needs to only show the values over the last 6 months. Any help is appreciated.
Hello, I am in the process of creating my database and I was looking for some guidance. My goal is to manage and track clients attending our program and ultimately being able to print and individual report with that information. Example: john doe on 12/28 attended 3 groups X,Y and Z. I have created two tables one with the client's ID, name and starting date. The second with the groups offered, the instructors and days and times of the groups. The third I believe should be a dated table that would have groups and all the people who attended that day. This is where I am getting confused. I am not sure how to proceed. Any ideas or suggestions would be welcomed.
I am currently looking for coding on my current database's login screen. It functions correctly but i want to be able to add a user lock out feature to prevent people from constantly trying to hack users passwords...
I need to set up an attendance database, that has multiple statuses available for a single day.Example: employee can be present, he can have a sick leave, he can be away on training, or business trip, etc... And for some of those statuses, like business trip, i need to be able to freely enter a comment, stating where he is etc..
Anyway, the key is that this database should be able to offer a "headcount" option, and traceability for past statuses for at least a year, for every and all employees. Now i just need to set up the database tables and relationships.
I'm creating a Gym Database and need to be able to track attendance for specific classes. I need to have a system in place allowing me to do a register for attendance for each activity.
I also need to be able to calculate fees based on attendance. So if a person attended Gym 5 times in the month (£5 per session) and then Swimming 3 times in the month (£2 per session). My system should automatically calculate this based on the attendance tracking.
Also: In the booking stage, I need to have a field telling me how many spaces are left on each activity. Say for example I'm booking Person 99 in for Swimming and there is only 11 places left I need the field to display 11 places left. After that booking it should update saying 10 places left because Person 99 is booked in.
I'm in the process of constructing a student database for my school which would track (in addition to detailed student information) the daily attendance of over 270 students.
It is my understanding that there is a limit of 255 field names per table, so I can't use the student names as field names, nor can I really use dates as this would not quite cover even a year.
Student ID and AttendanceDate as field names is also not really a feasible strategy, as each week this would eat up 1300+ rows, so I would run out of space in that direction as well.
My question is ultimately am I better off building this in excel 2010, or is there a way of doing this in access that I have overlooked?
I run a soccer league where we track players attendance for each game. I currently do it on a spreadsheet where each game date is a column and each player is a row. We also track which team they play on at each game (they can play on different teams different weeks). I currently have a second tab in the spreadsheet to record which team a person plays on each week.
Setting up a table of fields for this is relatively easy. The problem comes to data entry. I want to be able to visually see the data like I can in a spreadsheet (names in rows, dates in columns, intersections containing either team name or whether attended) and whilst a cross-tab query gives me the layout, I cannot input data in a cross-tab query.
I am trying to create a database for a clinic, and am severely stuck on how to input appointment dates for individual patients.
I have been using the 'student' database from office.com as a template for how to save the dates (given that appointments and attendance are exactly the same!), however, even after following what has been set up in the 'student' template database, I can't seem to replicate it.
Every time I add multiple visit dates for a specific patient, these exact dates show up for every other patient in the database. I need to be able to add different dates for all the different patients.
I have a Form that currently contains a checkbox that has the following OnClick event... SELECT tblPlayerRegister.Surname,tblPlayerRegister.[Club],tblPlayerRegister.Age FROM tblPlayerRegister WHERE (((tblPlayerRegister.Age) < 11) And ((tblPlayerRegister.Club) = "Beaconsfield")) ORDER BY tblPlayerRegister.Surname;
The aim of this is to select the Surname, Club & Age from the Table PlayerRegister where the Age is less than 11 and the Club is 'Beaconsfield' and then Sort in Ascending Order by Surname. I get an error when I write this code and I don't know what I am doing wrong. I am not very knowledgable with Coding but I have given it a go. There will eventually be many checkboxes with different criteria and I don't want to do a query for every one. Could somebody please advise what I have done wrong? Thank-you for any assistance given.
Okay here is my code, I had thread open awhile back. In regards of exporting main form and subform to word. Here is the code, I am still having problem with it. I can't seen to take export nothing or if I switch around dbs.close and rs.close, I end up exporting whole subform (meaning all the selected text from all the records to on one word doc. I don't know what I am missing, and it has gone beyond the limits and I have turn my brain upside down, but still no answer :( It has to be something with coding. Because, I know my subform is working fine, if I print out a report on access. Help me out plz.
TIA
Private Sub Command4_Click()
'Declare the follwing Dim dbs As Database Dim objDocs As Object Dim objWord As Word.Application Dim prps As Object Dim rst As Recordset Dim blnSaveNameFail As Boolean Dim BorrowerID As String Dim InformationID As Long Dim intcount As Integer
'Set word as an application and make it invisible Set objWord = CreateObject("Word.Application") objWord.Visible = True 'True is visible
'path and name of the template your are using. objWord.Documents.Add ("C:Temp ermsheet3.dot")
'This is for the bookmark that you created in the template objWord.ActiveDocument.Bookmarks("bmCusadd").Select
'This is the field in access that containts the data that has to be entered at the 'bookmark objWord.Selection.Text = Forms![menu]![txtCusDetails]
If intcount < 1 Then MsgBox "No detail items for invoice; canceling" Exit Sub End If Set dbs = CurrentDb Set rst = dbs.OpenRecordset("tmakInvoice", dbOpenDynaset) With rst .MoveFirst Do While Not .EOF BorrowerID = Nz(![Borrower ID]) Debug.Print "[Borrower ID]:" & BorrowerID With objWord.Selection .TypeText Text:=BorrowerID .MoveDown Unit:=wdLine, Count:=2 '.MoveRight Unit:=wdCell End With .MoveNext Loop .Close End With
With objWord.Selection .GoTo what:=wdGoToTable, which:=wdGoToFirst, Count:=3, Name:="" .MoveDown Unit:=wdLine, Count:=1 End With dbs.Close objWord.ActiveDocument.Fields.Update
'Word (or the document that you created with the template, will now open) objWord.Visible = True
Set objWord = Nothing 'rst.Close Exit Sub 'End With
Hey, i need help with a code. It shouldn't be too hard but i don't know where to start as i am unskilled on this program.
I am looking for an input box saying 'enter password here' to pop-up. If it is the same as say 'customer' then i want it to let the user into another form.
I just created a database for work, its got two fields on the form, item and Price.
What i want it to do is when I enter the amount of item, I want it to automatically work out (price = £3.99)the price per item and display it on the Price text box on access.
I done VB but cant remember much, i Know u might have to do a IF statement.
trying to make it so that when you open the report, it'll pop a dialog box for users to choose the dates (from date to to date. ie. 04/15/05 to 07/17/05). When I click on the report, dialog shows up fine but it finds every record... can someone please help me with this.
here's the coding for the report
Option Compare Database Option Explicit
Public Sub cmdCancel_Click() ' Method in all forms to allow clean close DoCmd.Close acForm, Me.Name End Sub
Private Sub cmdPrint_Click() ' Validate the dates If Not IsDate(Me.txtFromDate) Then Me.txtFromDate.SetFocus MsgBox "You must enter a valid From date.", vbCritical, gstrAppTitle Exit Sub End If If Not IsDate(Me.txtToDate) Then Me.txtToDate.SetFocus MsgBox "You must enter a valid To date.", vbCritical, gstrAppTitle Exit Sub End If If Me.txtFromDate > Me.txtToDate Then Me.txtFromDate.SetFocus MsgBox "The From date must be less than or equal to the To date.", _ vbCritical, gstrAppTitle Exit Sub End If ' Hide me so the calling report can run Me.Visible = False
End Sub
Private Sub cmdToDateCal_Click() Dim varReturn As Variant ' Clicked the calendar icon asking for graphical help ' Put the focus on the control to be updated Me.txtToDate.SetFocus ' Call the get a date function - date only varReturn = GetDate(Me.txtToDate, True) End Sub
Private Sub Form_Open(Cancel As Integer) ' Set up the form caption Me.Caption = Me.OpenArgs ' Set up the label Me.lblTitle.Caption = "Select Dates for " & Me.OpenArgs End Sub
and coding for the form i designed.
Option Compare Database Option Explicit
Public Sub cmdCancel_Click() ' Method in all forms to allow clean close DoCmd.Close acForm, Me.Name End Sub
Private Sub cmdFromDateCal_Click() Dim varReturn As Variant ' Clicked the calendar icon asking for graphical help ' Put the focus on the control to be updated Me.txtFromDate.SetFocus ' Call the get a date function - date only varReturn = GetDate(Me.txtFromDate, True) End Sub
Private Sub cmdPrint_Click() ' Validate the dates If Not IsDate(Me.txtFromDate) Then Me.txtFromDate.SetFocus MsgBox "You must enter a valid From date.", vbCritical, gstrAppTitle Exit Sub End If If Not IsDate(Me.txtToDate) Then Me.txtToDate.SetFocus MsgBox "You must enter a valid To date.", vbCritical, gstrAppTitle Exit Sub End If If Me.txtFromDate > Me.txtToDate Then Me.txtFromDate.SetFocus MsgBox "The From date must be less than or equal to the To date.", _ vbCritical, gstrAppTitle Exit Sub End If ' Hide me so the calling report can run Me.Visible = False
End Sub
Private Sub Form_Open(Cancel As Integer) ' Set up the form caption Me.Caption = Me.OpenArgs ' Set up the label Me.lblTitle.Caption = "Select Dates for " & Me.OpenArgs End Sub
codings from Microsoft "Building Access Applications" viescas
Yesterday I was advised by 'Smart' on placing a certain code, as follows:
In the after update trigger of the combo box you can do the following
If YourCombobox = "-" Then yourtextfield.ForeColor = vbRed Else: yourtextfield.ForeColor = 0
End If __________________
Please forgive my ignorance but where do I put this code... on the line for 'after update' or in the code builder. If it is the latter, how do I begin the code because it has Private Sub and End Sub. I am obviously no expert but I'm just trying to plod along and learn as I go. Your help is gratefully accepted.
I want to track the shipping costs for my products (no commercial value) which I mail world wide. My fields are:
Product Name Product Weight Quantity Mailing Zone Unit mailing cost Total mailing cost
On my input form Product Name and Product Weight are input using a combo box. Quantity is entered manually.
I have a table listing my products and the unit cost (best price) to send them to various world mailing zones.
I want to create a combo box on the Mailing Zone field and code it so that when I select a Mail Zone, the Unit mailing cost field is updated with the correct value for the product.
I have a quick question. I have a main form with a label and a button on it. When the button is pressed a secondary form pops up. The second form has a button on it also. I am trying to code the button on the second from so that when it is pressed the label changes its value on the first form. In the past it was quite easy as there was no second form so in the Onclick VB coding of the button on the first form had something like:
With Me.mainHeading .caption = "Recontacts" End With
But, with the secondary form, the "Me." does not work. Is there a way i can replace the "Me." with some sort of link back to the first form?
I have a combo box (cmboType) and the options are (let's say) Cat1, Cat3, Dog4, Mouse5 (but there are about 30). If I select cat (any type)I then (manually) tick a Yes/No box.(chkYes)
How can I make the box picked ticked automatically whenever I select a cat in the combo box.
I am trying (on the afterupdate event of the combo box) something like
If cmboType is like "Ca*" then chkYes = true
Obviously this doen't work - can anyone help me with the correct code?
I have the following code that will import OLE objects into access, and it works quite well...but, how do I get it to check to see if a reocrd already exists and not to duplicate a reocrd? I am not a programmer, I have a couple of ideals but I don't know where to start. Any help would be appreciated.
If I were to put a new file into the folder, I would like the code to only import the new file and all files again. Which currently it will just create new reocrds for the existing document. I hope I have explained this... Any help would be appreciated. Thanks. vb Code: Original - vb Code Private Sub cmdLoadOLE_Click() Dim MyFolder As String Dim MyExt As String Dim MyPath As String Dim MyFile As String Dim strCriteria As String MyFolder = Me!SearchFolder ' Get the search path. MyPath = MyFolder & "" & "*." & [SearchExtension] ' Get the first file in the path containing the file extension. MyFile = Dir(MyPath, vbNormal) Do While Len(MyFile) <> 0 [OLEpath] = MyFolder & "" & MyFile [OLEFile].Class = [OLEClass] [OLEFile].OLETypeAllowed = acOLEEmbedded [OLEFile].SourceDoc = [OLEpath] [OLEFile].Action = acOLECreateEmbed ' Check for next OLE file in the folder. MyFile = Dir DoCmd.RunCommand acCmdRecordsGoToNew LoopEnd Sub Private Sub cmdLoadOLE_Click() Dim MyFolder As String Dim MyExt As String Dim MyPath As String Dim MyFile As String Dim strCriteria As String MyFolder = Me!SearchFolder ' Get the search path. MyPath = MyFolder & "" & "*." & [SearchExtension] ' Get the first file in the path containing the file extension. MyFile = Dir(MyPath, vbNormal) Do While Len(MyFile) <> 0 [OLEpath] = MyFolder & "" & MyFile [OLEFile].Class = [OLEClass] [OLEFile].OLETypeAllowed = acOLEEmbedded [OLEFile].SourceDoc = [OLEpath] [OLEFile].Action = acOLECreateEmbed ' Check for next OLE file in the folder. MyFile = Dir DoCmd.RunCommand acCmdRecordsGoToNew Loop End Sub
I have a task of creating a database to track committee members attendance at meetings. Members can be on more than one committee. I have created tables for committees, committee population and committee members.
The meetings would take place once a month and do not need an exact date. A Yes/No would suffice for record of attendance. My question is what would be the best way to set up the table to track this since members can be on more than one committee?
I'm thinking that having a separate meeting table for each committee would be a bad idea. But haven't come up with any other ideas. Any suggestions???
