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.
Access 2000: How can I populate a two field table (1. Table Name 2. Field Name) with the name of ever field within every table in my database using VBA code?
Context I am a database novice currently in the process of building an access database to manage production in a manufacturing company.
Simplified Version of Problem Suppose I wanted to build a database to manage the baking process at a bakery.
Goal is to eventually generate a report from this database that tells the baker i) which raw ingredients to use and ii) gives instructions on baking his cake from the raw ingredients.
I believe I would need the following tables:
tblRawIngredients --> contains all raw ingredients that the bakery purchases (as well as respective properties of those ingredients)
tblCake --> all cakes sold at the bakery (and their respective properties)
tblCakeIngredients --> For each cake, this table stores which Raw Ingredients are needed and in what quantities. This table links tblCake with tblIngredients in some sort of many-to-many relationship
tblCakeInstructions --> For each cake must give me instructions as to how to bake
Specific Question
How do you recommend setting-up tblCakeIngredients? Given my inexperience, I am not sure which structure would be easiest to work with. I could think of two options:
Option 1) Each record couples 1 ingredient with 1 cake. The field list would be :
Cake (links to tblCake) Ingredient (links to tblRawIngredients) Quantity
In order to know the complete ingredient list for "Brownies" we'd have to pull all records where Cake = Brownies
Option 2) Each record gives the complete list of ingredients for a cake. The field list would therefore be
Cake(links to tblCake) Ingredient_1 (links to tblRawIngredients) Quantity_1 Ingredient_2 (links to tblRawIngredients) Quantity_2 Ingredient_3 (links to tblRawIngredients) Quantity_3 Ingredient_4 (links to tblRawIngredients) Quantity_4 ETC....
Note that Ingredient_1, Ingredient_2 etc. all link to the same field (primary key) of tblRawIngredients. In this structure, cake could be the primary key.
In order to know the complete ingredient list for "Brownies" we'd pull up the 1 record where Cake = Brownies.
Which structure do you recommend? Why?
I am a little bit stuck on this problem and want to choose the best structure so that I can easily build on this structure and expand/ refine the database.
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 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 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.
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 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'd like some initial help with how best to set-up my tables for a database i need to create. if anyone could give me a synopsis of what i need to do it would be great and then i can try and piece it together.
here are the requirements;
one student can attend many different courses.
each course runs for 10 weeks.
i'd like to be able to have forms to;
- add a student. - assign classes to a student. - record absence via a combo box for each student for each week
i'd also like to have a report mechanism that can record if a student is absent for 3 or more classes.
as i said, some pointers or examples would be great as i am new to this. i pressume i need;
a table for students studentid firstname lastname
a table for the courses courseid coursename
a table for attendance attendanceid courseid studentid present/absent (combobox) week
Am most stuck on the date part. the data may not be entered every week so i can't really use a date function. instead i need a combobox or something to be able to select the week.
Is there any way to keep track of a database structure in access? For instance, which query relates to which report? Sometimes I create queries that are no longer needed but if there are a lot (which there are!) it can be easy to delete one that's needed.
I created a database to track tardies and absences of my employees. I would like to be able to sum the number of absences and tardies for each person per month and graph it.
Per our attendance policy, 6 tardies = 1 absence. I need to take the total number of tardies that month/6 and add to the total absences that month to equal the total attendance for the month.
I need help. Can you please view my database and give me some ideas?
I am designing an application that tracks information on Choir membership and sheet music that we have on file. I am starting with a database of church members. There are four different choirs and choirs share some members. Some members of some of the choirs are also not members of our church so I will have to place non members in the member table. I am new to database design and would like the collective wisdom of this list to tell me of any problems I may encounter before I start doing any detail work.
What is the best way to deal with someone who is a member of more than one choir and may belong to a different section in this other choir (Tenor in one and Bass in another). An individual may also hold different offices in various choirs.
The table structure I have is as follows
CHURCH MEMBERSHIP DB: MemberId Autonumber (pk) FirstName, Text MiddleName, Text LastName, Text DateJoined, Date Phone, Text Address, Text City , Text Zip, Text EmailAddress BirthDate, Date Member, Boolean
CHOIR MEMBERSHIP DB (How do I efficiently track someone in > 1 choirs) MemberId, FK ChoirId, FK FolderNo RobeNo Section ChoirOfficeId, Fk
CHOIRS DB (This lists the various choirs in the Church) ChoirId, pk ChoirName, Text DirectorId, FK (Pointing to Member DB, Person may not be member of any Choir)
MUSIC DB CatalogId, PK Title Composer Arranger Type (Single Copy/octavo or book/collection) PublisherId, FK PublisherNumber VoicingId, FK (From table with possible voicing) NumCopies UsageId, FK (Where in the service is it appropriate Location, Text (Where in the filing system, or off site) ClassificationId, FK (List of classification/genre in table so can update)
PUBLISHER DB PublisherId, PK PublisherName PublisherAddress PublisherPhone PublisherWeb PublisherContact
MUSIC CLASSIFICATION DB ClassificationId, PK Classification, Text (Christmas, Easter, general anthem etc)
To be able to track performances and plan services and performances I have the following table.
PERFORMANCE DB (This is to keep track of and plan the regular service) PerformanceId, PK Pdate, date (Date of Past/Planned performance. Possibly more than one per day) ServiceTypeId, FK (From table of types of performances – morning service, evening, etc) Location Speaker Pianist Organist Introit Invocation Anthem Meditation Benediction (etc)
I would also like to be able to prepare mailing labels for the various choirs as well as the general membership from this DB. My primary focus will be on the music. I would like to have an efficient music DB that I may find out what music I do have and when I last performed them, what options for performance (usage and classification)
I am trying to figure out how to store data into a database to be use later in a web app. So I have a shirt and it can come in four different colors. What would be the best way to enter this into the database. I'll have shirts, jackets and accessories which I have give each their own table. The shirt one is giving me trouble though, I just don't know how to organize it.
I have a table called Attendance were information about the Student and the class they are attending is collected.
Another table called Student, this hold information about the student. I am using a Form called Attendance to input my information. What I am trying to achieve is:
BeforeUpdate on a text box:
When you input Student_ID it looks at the Student Table to see if the Student ID is valid before the information is updated to the Attendance Table.
I'm doing some changes to a database in work, but the person who created it doesnt work there anymore and so nobody knows anything about it. The database has been split into a front and back end, which is fine, but there appears to be 3 seperate databases linked to the main one, each with a .mde file for security. Depending on which department you're in, you access your database. Any information being added is updated in the main front end database. Does anybody know how that will be linked? I'm just probably going to have problems when encorporating my changes.
Hello, I am looking for someone's professional opinion to help me clear up some unresolved technical issues in my mind... =P Ok, I have a contact management database that would seem really straightforward to me except for the fact that I am dealing with 2 primary entities. In this I mean I have an Individual table and Organization table. These two tables have primarily the same field data, such as both having address data, phone (contact info) data. There are some unique fields to one entity that aren't contained in the other entity though, and visa versa. One important issue is that 0, 1, or more individuals can be related to an Organization. Therefore I now have linking tables for IndividualOrganization, as well as linking tables for Phone and Address data. My primary complication is that since both Individuals and Organizations have address and phone data, the respective linking tables for them contain: an addressID/phoneID key, for the appropriate linking table, with an OrganizationID key and an IndividualID key in each table. So, the way I am thinking is that if the record pertains to an Individual, the appropriate ID will be in IndividualID and the OrganizationID will just be 0, or empty. This brings up issues with primary keys being blank. I am wondering if there is a better way to do this that I am overlooking or if I am in fact on the right track? I have contemplated combining the Organization and Individual tables into one but that really doesn't seem like the best solution. If anybody has any ideas then I would GREATLY appreciate it. I had a working database the other day but now I am separating the phone and address info out of the primary tables and have really got a mess on my hands. =P If anybody would like to look at my database structure I'd be more than happy to post the back_end and the front_end for you to give me your honest opinion about any trouble areas. Thanks BIG TIME in advance to any nice souls willing to help out a struggling programmer who doesn't have any close support on this one as I am working alone, :rolleyes: Dana S.
Booking - BookingID Course - CourseID Company - CompanyID Materials Materials_on_course (had to have this in order to avoid many-to-many.) Employee - EmployeeID Employee_runs_course (had to have this in order to avoid many-to-many.)
I'm stuck with the Course and employee thing. Each employee has an expertise or two, and therefore each course has an expertise needed to run it (a specialist if you like).. so the PK in 'course table' is Course ID, you cannot have two PKs in one, so would it be right to say that the 'expertiseID' would have to go in 'Employee_runs_course' ?.. what would i do then? place 'expertise ID' as an FK in both employee and course tables?
Extra info:
Clients (tblCompany) can book one or many courses A course can have one or many bookings made for it A booking has one or many employees/courses An employee can work on many bookings (obv if available) Each employee has one or two expertise (usually only one) and therefore a course has on expertise attached to it.
__
If you have a better way of designing it, shoot! Attachment: ERD (kinda) of first draft.
I am building a database of in-car audio systems. Stored in this are simple things like vehicle information, audio brand and other general information. The main information I need to store includes details about speakers (position, quantity, material, range, size etc.) and amplifiers (power output, no. of channels etc.). Sounds simple I hope.
I have been thinking about this extensively and have come up with two solutions, each with its own pros and cons. My primary concern here is how the speaker and amplifier information is stored:
1) Tables specific to car regions (e.g. Rear Speakers, Front Speaker, Surround Speakers) have the fields 'Range', 'Material', Size, etc. Range and material can read their values from other look-up tables no probs. These would be linked to the main table via it's primary key in a 1-M relationship. -Pros: Ease of construction and general handling of data Flexibilty/expandability (in terms of speaker quantity) -Cons: When it comes to data entry the form would need to be continuous and continuous forms I have found to be a pain in terms of referring to individual records! I want some of these controls to conrol a diagram for my report (and input form) - such that they show/hide speaker pictures on a car diagram. Also, I have not yet figured out how to validate a continuous form in this situation or delete records from it either (should the user makes an error).
2) Again, tables specific to car regions but this time with a field for each speaker range (mid-range, tweeter, woofer, etc.) and it's associated data, tied to the main table via the main table ID in 1:M relationships. -Pros: Easy to manipulate form/report properties because every speaker has its own field Easier to remove erroneous data (I imagine) -Cons: More complex construction (and less efficient) - e.g. material occurs more than once for each record so to look up material form a central pool requires an intermediate table Many many more fields! Non-expandable in terms of speaker quantity
Similar principals can be applied to amplifiers so I haven't mentioned those.
Lets say u have a database that keeps track of all items sold and their respective prices. So u have a items table that keeps the product information and price. Then these items are displayed on invoice kept in a invoice table linked with a foreign key and a one to many relationship.
My question is lets say the price of an item should change....will the invoice table of old invoices display the new price or old price. Is this where the referential integrity comes to play ? Should u select it when doing the relationship ticking the cascade update related fields and cascade deleted records boxes respectively or not ?
