Payroll Database- Advice Needed On Relationships/normalisation
Aug 22, 2006
Hi,
I would like to get your advice on my table setup and relationships for this payroll project. The company is an engineering company with Projects (or construction sites) around the world.
The 'Candidates' are current or potential employees and contractors. There are three main pay categories:
1.Shift-workers
All shift workers doing a particular job on a particular project are paid the same rates e.g. all welders on a particular project or site in England are paid the same as each other. For that reason I want to link the pay rates with the job description for these workers. This avoides creating 50 records for 50 welders on the site in England to say that they make £10 an hour normal time (or whatever it is) etc.
2. Contract
Contract workers usually get paid a flat rate per hour. As these are negociated on an individual basis I would need to have this information linked to each individuals job (M_CandidateJobDetails).
3. Salary
Again this information needs to be input for each individuals job.
For the contract and salary people the pay frequency can vary (weekly, bi-weekly or monthly). So can the currency they are paid in. I haven't got as far as the currency issue yet.
The reason for the one-to-many relationship between M_JobClassifaction and M_CandidateJobDetails is that many candidates can have the same type of job e.g. there can be many employees that in the job classifaction of 'Electrician'. For many of the jobs at managerial level e.g. 'site manager' there will only be one.
I will have a table with the hours worked by each person per week. I can use this for those on shift work or contract to calculate what they will be paid.
One of the main reasons for this database is so that the company can print reports to see what is paid out in payroll for each site and in total (in euros). These will be gross figures and I don't need to take expenses, vacations, bonuses or taxes into account. They other thing we will need to be able to do is assign candidates to vacant positions and change them from one position to another - possibly between different projects.
So basically does anyone have any comments on the relationships, normalisation or anything else. Is this the best way to do it?
Hi, I am new to this post. I am a physician with interest in database designing. I have been trying to design a database for my clinic for few months but am unable to make one. I have been searching/ reading alot of info and came across this thread. Maybe someone can help me. Actually, I want to make a database regarding ultrsound scan examinations of patients. I have five tables. 1. Patients. (patinetid*, patientname, age, sex, address, contact no) 2. Physicians. (physicianid*, physicianname, speciality, address, contact no) 3. Scans. (Scanid*, scanname, charges) 4. Scan orders.(scanorderid*, patientid*, physicianid, scanordernumber, scandiscount, totalcharges) 5. Scanorder details.(scanorderdetailid*, scanorderid, scanid, charges, discount)
I want to have primary key for scanordernumber which wil be the patient number and should this be placed in patient table?? All the ids have been linked with one to many reltionships. Actually I am unable to set proper relationship. So when the patient arrives he is registered with a unique number, a physian name with date added and scan ordered is entered. Sum calculated. I have done the later part with the form all designed but the relationships and primary key are all messed up.
I can post an image of relationships or blank database.
This is my first post here and was just hoping for a bit of advice, what it is my dad wants me to create a small database for him and was hoping for some advice on how to tackle it. What he wants is listed below. I have some ideas but the most confusing aspect I found was how to update a persons age from the computers date.Heres what he wants and hope you can give me some advice. Thanks in advance John
Fields (free text unless otherwise stated) for people;
First name Surname Age Further Education level - drop down menu for "GCSE/A level ,HNC/HND, Degree, MSc, PhD" Degree held? - drop down menu "yes" or "no" Chartered engineer - drop down "yes" or "no" Professional Quals - free field for these Skill base 1 - drop down for " Manager - Operations, Production, Maintenance, Engineering; Engineer - Mechanical, Instrument, E&I, Electrical, Rotating Equip, Process, Asset Integrity, Inspection, QA/QC; Superintendant/Supervisor - as above; and any others (think of all KPO Ops parented people) Skill base 2 - as above Skill base 3 - as above Professional ladder job title Grade - drop down menu BG3,BG4. BG5 Hay points Date of birth Age - can this be generated from entry above and current date?
Current Assignment job title Current Assignment start date Current Assignment Asset Current Assignmnet end date Current Assignment location (eg Cairo, Idku etc) Currnet Assignment type - drop down menu rotation 14/14, rotation 28/28, expat
Next assignment options Comments
Then set of data fields for posts
Post job title Post type - drop down rotation menu as above Likely Start date Location Asset Sponsor in Asset
People Reports
People - All data People - Listed by current job end date People - Listed by skills (if Mechanical in any of three fields above, list him), will be repeats of names in list (as have more than 1 skill but thats OK) People - List of those with degrees People - List of those chartered People - Listed by current job end date People - Listed by current job end date People - Listed by current job end date
I built a DB for work (Access2000) which is on the network and is for inputting maintenance request. Nothing complicated about it and at any time there would probably only be 3-4 users logged on at a time, and then probably only for a few minutes each. This DB got corrupted yesterday and I had to recover from teh backup. I compact and repair manually on a weekly basis(usually). My question is would it be less likely to get corrupted if I split the DB? Would it help to set up an automated complact & repair to run overnight? Thanks RussG
Im currently building a database which holds customer delivery options, quantities, unit and delivery price and so on.
It has all been going fine until now...!
I am trying to setup the billing aspect. My main headache is that each customer could have a delivery every day, of different quantities. Now there will be times when this customer goes on holiday and of course doesnt need to have anything delivered - my only problem is getting the billing to adjust accordingly.
I have my SubForm which contains:
UnitName UnitPrice Mon Tues Wed Thursday Fri Sat Sun DeliveryCost TotalCost
at the moment in the main form the holiday period is put in and i convert this then into a number of days value - this is then subtracted from the total of units (count of Mon to Sun) x UnitPrice + Delivery.
My problem is that if a customer has 2 days off which are Mon and Tues, how do i get it to only remove the mon and tues values from the unit amount - instead of just removing a figure assuming that they have only 1 per day - wihch in most cases they don't.
Can i somehow assign the day values to each day field in the subform and get the correct days from the holiday or something!
I have a database that has one aspect that is highly dependent on certain payroll information. For example:
When a pilot begins entering his flight for the day at the bottom of the entry form it tells him the payroll dates are from x to x and you have worked X number of days in this pay period.
This part I have gotten down quite nicely using a payperiod table. Problem is that I must enter that information manually and it is time consuming to do that for the whole year. Point of fact I only put the first day of the pay period in this table. Our pay periods are every two weeks.
I also generate a report for payroll based on these pay periods.
My question/advice is a new way of going about this pay period deal without having to enter it in manually every year.
We have these wonderful computers but I feel I am doing too much work but just don't see a different method around it.
A bit wordy but I wanted to be clear. Any advice in a new direction?
I'm looking for some advice: the problem is that I've got 13 sections in which people are working, and administrative assistants that are taking care of 1 or more of these same sections.
In addition to the usual security, there is also a requirement to make the AA's only output their own sections reports.
The way I see it there are two ways to ensure this:
1) put in a field in the users table that would indicate which sections the AA was responsible for ie
userID 1 respFor 56,34,45
userID 2 respFor 41,3
userID 3 respFor 42
... etc respFor would have to be a text field with a delimiter between each sectionID
sectID 41 sectName AAA
sectID 42 sectName BBB
2) have a separate table that will form a "union" between user and section tables ie User&Section
userID 1 sectID 56
userID 1 sectID 34
userID 1 sectID 45
userID 2 sectID 41
userID 2 sectID 3
userID 3 sectID 42
Now for my question: Can you advise me on which way is best for this circumstance? or perhaps you've got another way to go? Have you got an example ?
Greetings! I am setting up a database in Access 2003 for a Seniors Program. Through this db I need to set up forms where they can add information on individual members, denote which programs they participate in/pay dues for, etc.
Any advice you all could give would be greatly appreciated.
EDITED TO ADD: I'm basically a newb when it comes to Access...I can do the basics, but need to go beyond what I know to do this project...
Hello, I am working on a database of translators. Each of these translators can have multiple Source Languages, and multiple Target Languages. I understand that I must use a many-to-many relationship, but as the Language fields for both Source and Target Languages comes from one table (tblLanguages), I am having difficulty conceptualizing how I can get the table relationship to produce the desired effect; i.e. having a datasubsheet for each translator with fields for each their Source and Target languages.
My current table structure:
tblTrans TransID (name and contact data)
tblLINK_Language_Trans TransID LanguageID
tblLanguages LanguageID
I thought that maybe if I create a separate LINK table for each Source and Target Language, I might get the desired effect, but this was futile, as only one datasubsheet was shown.
Any advice for a humble Access beginner would be GREATLY appreciated!!!
I am new to access and despite reading what seems to be a mountain of stuff I'm a bit stuck. Can somebody point me in the right direction to achieve the following.
This is a simplified version of my problem, but it is the principle I'm after.
I have a query, the results of which look like
FIRSTNAME SECONDNAME ITEM ITEMSIZE
There are multiple results with the same firstname and secondname but different item info. e.g.
Bill Smith Shoes 10 Bill Smith Shirt 16
I need to be able to produce some output - table/report/query (to be export to excel) where there is only one record per (FIRSTNAME SECONDNAME) of the form
I wonder if someone could help me, i have started to create a database which will be uses for a gardening service company.
I have created the customer database table and form which him quite impressed with the say ive never done it before.
I also followed a tutorial from the net which was based around a video rental shop, ive put in in the transdetail and masterquery forms also as said in the tutorials as it will basically need a databse for the customers, then i will need to create invoices from services we do for the customers i:e lawn cut etc.
this is where im stuck really, i have tried variuos things but it doesnt seem to be tying into what i want it to do, i think ive tried to overcomplicate it. If anyone could give me any advise on what i need to do to create this very simple database i would really be grateful.
i'm trying to build something that knows when to notify someone that they can call a trade, given 1) a callable date 2) a notice period and 3) the relevant cities
'--------------------------------------------------------------------------------------- ' Procedure : NoficationDate ' Date : 28/03/08 ' Purpose : to calculate the date of notification for an EMTN, given the call date & notice period & cities '--------------------------------------------------------------------------------------- ' Public Function NotificationDate(dtCall As Date, intPeriod As Integer, strSixDigitCities As String) As Date
Dim intWorkingDaysBefore As Integer Dim strCities(2) As String Dim dtLoop As Date
Do dtLoop = dtLoop - 1 If Left(Format(dtLoop, "ddd"), 1) <> "s" And IsBankHoliday(dtLoop, strCities(0)) = False _ And IsBankHoliday(dtLoop, strCities(1)) = False And IsBankHoliday(dtLoop, strCities(0)) = False Then intWorkingDaysBefore = intWorkingDaysBefore + 1 End If
Loop Until intWorkingDaysBefore = intPeriod
NotificationDate = dtLoop
End Function
'--------------------------------------------------------------------------------------- ' Procedure : IsBankHoliday ' Date : 28/03/08 ' Purpose : to see if it's a bank holiday '--------------------------------------------------------------------------------------- ' Public Function IsBankHoliday(dtInput As Date, strCity As String) As Boolean Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM qry_Tass_All_Hols WHERE CITY = '" & strCity & "' AND HDATE=#" & Format(dtInput, "mm/dd/yyyy") & "#", dbReadOnly) If rs.RecordCount > 0 Then IsBankHoliday = True Else IsBankHoliday = False End If rs.Close Set rs = Nothing
End Function
BUT it runs like arthritic toad, it makes a minute per execution and i was hoping to scale it up to 4000 records => 2 days of run time :eek:
any ideas on how to attack this problem...even guesses appreciated, i can try things out and see if they work
I have a list of events and what the out come was on a particular day or date.
To give you some idea, the event is basically number given to a day and the outcome is all possible things that “could happen” and that did happen (so not all 160 possible Outcomes are used all the time).
So it is quite possible to have:
Event 1 (This could be “Monday”) Outcome 12 (This could be “Rain”)
Therefore as you collect your data over time the “Outcome Diff” would = 12
The problem comes when I try to create a report to automatically calculate the “Outcome Diff” this in a Query as the “counter” that should have been added at the time this was created was not done at the time. I also do not mind if this has to involve creating to a table, as long as I get the “outcome diff” calculated in MS Access, I do not mind how this is done. I also cannot use date diff as event mean more than just an index for the day.
Event, Outcome, Date, Outcome Diff 12, 1, 14-Feb-95, 12 (0 to 12 = 12) i.e this is the 1st event outcome of 1 22, 1, 19-Apr-95, 10 (12 to 22 = 10) i.e this is the 2nd event outcome of 1 29, 4, 07-Jun-95, 29 (0 to 29 = 29) i.e this is the 1st event outcome of 29 34, Ect... *This is just a representation with test data.
Just for further clarification: the reason this is need this is to find out difference in "event" days as opposed to just an average.
Hi. I am developing a db for juvenile salmon-focussed fishery survey data and have encountered something of a conundrum which I could use some advice on. Apologies in advance for the length of the post.
Background Juvenile salmon move from freshwater to saltwater. During this transition they require time to adapt physiologically and are thought to seek out nearshore areas with intermediate salinities, or with freshwater overlaying the saltwater. They also experience problems with elevated temperatures.
We are interested in tracking salinity and temperature information at each site where we sample for fish to aid in interpreting our catch results.
Data Collection Our convention is to collect temp/salinity at the surface and at 3-feet below the surface wherever we beach seine (or just at the surface if the site is shallower than 3-feet). However, we use a depth-temp-salinity data-logger attached to the lead-line of a lampera net for openwater sets. The logger provides measurements of depth/temp/salinity every 5 seconds during the set, down to depths of 20-30 feet.
So, for some 'sets' we have one or two measurements of depth/temp/salinity, and for other sets we might have over one hundred measurements.
Problem
1.How best to get that data entered into the db? 2.I'm just starting to get my toes wet with VBA
Ideally, I could directly enter the values into a subform for sets with only one or two measurements, but could instead 'import' the extensive data for those sets where the logger was used. Entering the logger data manually would be ridiculously time-consuming.
Existing DB Setup Records for temp/salinity subform/table linked to other set information by a unique Set_ID field.
Subform for depth-temp-salinity information bound to a dedicated depth-temp-salinity table. The subform is currently viewed as a continuous form.There would be one excel file for each set where a data logger was used, but no excel files for sets where no data logger was used..
My thoughts so far.
Somehow create a subform with the ability to enter up to two records manually or else click a button that imports the data from an excel file. One thought is to pop open a window to navigate to the excel file that contains the data for that set. However, I'm thinking that if I place all such excel files into a particular directory and name them using the appropriate Set_ID number convention, that maybe clicking the button with be able to find the file directly, without navigation required, and bring in the records automatically.
Is this possible? How would I go about creating a subform that provides both an 'import data' button and allows for manual data entry of up to two records?
Can anyone show me a similar example for both the data entry (form) and for how to automate the importing of data from excel files to append to an existing database table?
Aim: The eventual goal of this is to have a command button that could be clicked on the form/subform that would produce a popup window containg a scatterplot graph of salinty versus depth. another button to produce a scatterplot of temperature versus depth. A third button to open a line graph with time on the x-axis, and temperature/salinity series on the y-axis. Before I can get there, however, I need to get the data into the table somehow.
I would appreciate any input/advice on this matter, (especially custom code! ;) ) As, I mentioned, I'm just starting out in VBA and I have a lot to learn. I know how to open a MsgBox, but have no clue on what the command is to open an explorer 'window'.
I hope the problem is sufficiently interesting to generate some response.
I'm looking for advice on the best method to accomplish the following from the esteemed members of this Forum (You all have provided excellent advice in the past to this Access Dummy, with my thanks), (I've also searched the forums without result):
I would like to make several fields "required" fields on my form, easy enough, in that I set the Required property on the table to "Yes".
What I would like to happen on the form is that when a user tabs out of a required field, a message box pops up that says "This is a required field" and/or when they click any of the following command buttons I've created, "Save Record", "New Record" or "Close Form", that a message box pop up and list the required fields that they missed.
Any ideas, with code, macros, or other solutions would be greatly appreciated, keeping in mind that I'm just not that swift to start with.
I have an order database Access 2000 for an inspection company, tblInspectors, tblOrders, tblCustomers, I want to be able to calculate inspector pay within order database. Each inspector is an independent contractor, so it just needs to calculate percentage, for each inspection performed and total for pay period. Each inspector is paid a different percentage, which is stored in tblInspectors. Payroll is paid every two weeks . . . I've been trying to figure out the best way to do this ... :confused: Any advise be greatly appreciated.:confused:
Access Query. I am creating a time sheet / pay roll database and I want to be able to get a total of the daily hours in a query.
For example I have 'Mon Start' and 'Mon Finish' for Mondays in/out times and I have a 'Mon Total' which gives me the total hours worked for Monday.
The problem I have is that Mon Total only works if the hours are say between 07:00 and 17:00, anything after midnight (00:00) like 21:00 to 07:00 and 'Mon Total' goes crazy !!
At the moment 'Mon Total' is the result of CDate 'Mon Finish' - 'Mon Start' (bit rough I know).
I hope someone can help me, I have a database compiled from different sources which means that information in fields that need to be linked are written differently, meaning that I can't just simply make a relationship between them.
The two tables I would like to link are, Table 1 has the fields OCCUPATION and AMOUNT and contains over 740,000 records. Table 2 has the fields COMPANY_NAME and TICKER and has 500 records.
I need to find a way for all COMPANY_NAME fields in table 2 to be cross-referenced with the OCCUPATION field, so if COMPANY_NAME is part of the string in the OCCUPATION field then the TICKER (of that company) can be attached to the record in table 1 (specifically to AMOUNT).
The problem is that the OCCUPATION field is not written in a standard form and can include either only the occupation, only the company name, or both in either order.
I can make a seperate query for each company by using as criteria "like "*[COMPANY_NAME]*", but then I would have to do this 500 times!!! Is there a way to automate this?
The final purpose is to link the AMOUNT to TICKER so as to find the sum of all the amount associated with a company.
I really hope you can help, I have little programming knowledge and it will save me the time of making 500 seperate queries. The final use is for my thesis studying private contributions in the american elections.
I have to create reports from an Access database used in a commercial application. The backend uses Access but the front end does not.
The vendor does not document the data or provide any support for that. The database has over one hundred tables and thousands of fields.
I'm thinking I'll create small transactions in the application and then study the database to see what has changed. But this seems overwhelming considering how many tables and fields there are. So I'm looking for advice on how to approach this.
Hi, im currently working on a database which is for someone else. Whe it is handed over to them, they will no doubt want some changes done every so oftern eg new reports, changes to forms etc.
What is the best way to do this? The database holds a lot of data, so I dont think its feasible for them to send it to me via email everytime they need something changed!
Ive noticed a few posts here about splitting the database into a front/back end. If this was done, would they just have to send me the front end; this would probably be a smaller file ye?
If this is going to be the best way round the problem; how easy is it to split a database which is basically already made?
Hi, im currently working on a database which is for someone else. Whe it is handed over to them, they will no doubt want some changes done every so oftern eg new reports, changes to forms etc.
What is the best way to do this? The database holds a lot of data, so I dont think its feasible for them to send it to me via email everytime they need something changed!
Ive noticed a few posts here about splitting the database into a front/back end. If this was done, would they just have to send me the front end; this would probably be a smaller file ye?
If this is going to be the best way round the problem; how easy is it to split a database which is basically already made?
I want to know if anyone has taken a large database (e.g 400,000 records) and had to take a subsection of this so to reduce size. (E.g you randomly would like to take 20,000 records). I need to reduce a database size for a test situation but would like to know what I should consider when reducing the size in this way.
Do I take the base database structure – table organisation with fields linkages etc and load data in?
I have some help in this task but would like to know of any advice that could be provided.
I was going to say before I screw it up, but that would have made the title too long.
I've read some forum history and done some searches and can't see that a similar query has come up previously - but if anyone can point me at previous relevant threads then that would be much appreciated. I've looked at some of the design theory threads, but am not really sure whether I've applied some of the good stuff in there effectively - and have put this query here as it's specific to my database rather than a 'general principles' thing.
I am hoping that some kind person/people might be able to pick holes in my planned database revamp as I may not have seen some of the pitfalls - thank you.
Background: I am the first and sole employee of a grant-making trust and set up our (still relatively small) database early on - wish I could have waited, but before my database everything was paper-based and impossible to manage. The data is all on the database, but an exponential growth in number of enquiries/applications over the last year means that it can't be easily manipulated in a way that meets our needs because of my short-sighted (OK, poor owing to inexperience in job) initial design in some areas.
Database: What it's supposed to do: In brief, the way our organisation works is that we get a phone call or a letter or sometimes an entire application from a charity or community group, decide what to do with it and have to manage the funding contract (regular reviews, grant instalments) if the application is successful.
I think existing data is reasonably normalised (certainly at least equivalent to normalisation in the structure below) so migration shouldn't be a problem, but if anyone spots any normalisation or naming convention issues then please do point them out - I'm an Access amateur, but would like to get it as right as I can (this time). Small letters indicate linked tables, numbers indicate 'footnotes'.
(a) tblProjEvent PEvID (primary key) PEvDate ProjName ProjEvent (g) ProjEvDesc - additional detail - e.g. who involved, topic if not obvious from event field. ProjEvActWho - who is responsible if future action ProjEvActDue - due date ProjEvActComp - checkbox ProjEvActCompDate ProjEvNote (NB not to duplicate description - more for 'additional notes arising from completed action' or sensitivities that may need to be considered and kept separate from regular reporting)
(b) tblOrganisation Details not listed for brevity, but split one field per line of address/detail, org name as primary key - existing table can be migrated as is (2). Includes suppliers, networking orgs. (f)
(c) tblProjSourceLup How they heard about us - list of sources for speed/consistency of data entry.
(d) tblProjStatusLup What it says on the tin - where we're at right now with a project. History/status changes recorded in tblProjAction.
(e) tblProjReasonDecline List of common reasons for speed/consistency of data entry
(f) tblOrgContacts OrgContact ID (primary key) Names/personal details of organisational contacts, split one field per item (firstname, lastname, jobtitle, etc).
(g) tblProjEventLup Letter, Funding contract, review meeting, report, telecon, Board review, etc. May be a future action.
Accounts functions: Payments in/out linked to organisation with a lookup table for account types (in, out, grant, donation, expenses etc etc). Works fine and can be migrated as is (2)
The current arrangement of relationships has the organisation as the focus - I'm looking to make the project the focus, which seems to make sense as each project is unique - whereas we may get several project applications from the same organisation - and the existing structure makes it impossible to sort these out :o.
Footnotes
1) Vast number of applications that fall into the "arrive, get read, get binned" category make this duplication worthwhile - most applications won't make it into the Project Event table. Apart from the "Greetings in the name of the Lord"/419 e-mails, they do all need recording somehow so we can spot any trends and correct misinformation - save hard-working fundraisers wasting their time*.
2) Of course data validation will be necessary, but in most tables we're talking hundreds or thousands of records only, not tens of thousands so should not be that onerous. Especially as I've been the only person using the database, so there are hopefully not too many inconsistencies!
This post is too long already, but if I've been uselessly unclear in explaining anything or anyone wants any more before they want to comment on efficacy of structure please let me know. All feedback appreciated (as I said, I work alone, so really looking for idea-bouncing to check I can see the wood as well as the trees as much as anything - thanks).
*As charity is always a sensitive area and some of you may be involved with voluntary work, an OT comment: 'No response' might seem rude - but if an organisation is -clearly- excluded by the criteria on our website, or that very same person (or a large charity who should be able to manage its information effectively) has previously been told that they are ineligible by reason of their location, size or type then they have had their response already. I could spend all day every day writing 'No' letters - I'd rather be doing something more positive with my time (like checking out/meeting with organisations that are potentially eligible so they get their answer - and in some cases money ASAP). Not R-ing TFM (aka grant guidelines) apparently applies to fundraisers as much as any other profession :rolleyes:
I am trying to develop a simple payroll. I have already developed three tables named tblEbmployee, tblPayment, tblDate. These are related with one to many between tblEmployees and tblPayment and one to many between tblPayment and tblDate. I dont need much calculations in the system. I will add salaries in tblPayments of the employees manually (as there is no set formula relating to the salary an employee). What i need is: To make a form which uses tblemployee for each month salary To carry the previous month salary data to next month without adding date for each employee indiividually. (is there a way to change month once that applies to all employees, keeping the record of previous month ) Do i need to add some other table to keep record of previous month payments and a table keeping record of rate of salary yours Arshad Slatch
