If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
ACCESS 2007: need help in database breakdown and table creation
Am new at Access. Been studying books on Access 2007 and tables I've created
are not efficient. Have feeling am going about it the wrong way. Currently have database in Excel 07 spreadsheet w/ these headings: Empl#, LastName, FirstName, Date Hired, Project Leader (PL), Jobsite, Apprentice Level, Previous Rate ($), Current Rate ($), Date of Raise, Apprentice School Start Date, End Date, Comments. This is a list of construction workers in diff jobsites. Each PL handles 2 to 3 jobsites. Some workers move from 1 site to another (therefore, at times from 1 PL to another). In Excel, have 1 worksheet as Master List (includes all workers) and broken down into separate worksheets for each PL. It was getting tedious in Excel moving some workers from one PL's list to another & also updating any changes of an employee file in Master List as well as the PL list for that employee. Reports are printed for each PL to study at their meeting each Monday. Need advice on how to breakdown this spreadsheet into smaller tables and need to create a 1 to 1 relationship so that any change in an employee file is automatically changed in other files where he/she is located. Empl# is unique. Thank you.... Bob76 |
#2
|
|||
|
|||
ACCESS 2007: need help in database breakdown and table creation
i would have something like
tblemployee Employeeid,autonum LastName,text FirstName,text DateHired,date/time ApprenticeLevel,text or number depending on format of field PreviousRate,currency CurrentRate,currency DateofRaise,date/time ApprenticeSchoolStartDate,date/time ApprenticeSchoolEndDate,date/time Comments,memo or text depending on length tbljobsite jobsiteid,autonum Jobsitename,text employeeid,number ProjectLeader,yes/no if you want to track the pay increases over time not juts the last pay increase you need another table tblpay payid,autonum employeeid,number PreviousRate,currency CurrentRate,currency DateofRaise,date/time so you would stick everyone in the employee table including project leaders i added the ProjectLeader,yes/no field to job site but this is only useful if an employee can be both a project leader and a normal employee on another site. if this is not the case then the projectleader,yes/no would be added to the employee table instead. i hope this makes sence Regards Kelvan |
#3
|
|||
|
|||
ACCESS 2007: need help in database breakdown and table creatio
Thanks for your reply.. I don't quite get the need for the Jobsite table
though. I also don't know how the pay table can track pay over time. Please explain. thanks "Lord Kelvan" wrote: i would have something like tblemployee Employeeid,autonum LastName,text FirstName,text DateHired,date/time ApprenticeLevel,text or number depending on format of field PreviousRate,currency CurrentRate,currency DateofRaise,date/time ApprenticeSchoolStartDate,date/time ApprenticeSchoolEndDate,date/time Comments,memo or text depending on length tbljobsite jobsiteid,autonum Jobsitename,text employeeid,number ProjectLeader,yes/no if you want to track the pay increases over time not juts the last pay increase you need another table tblpay payid,autonum employeeid,number PreviousRate,currency CurrentRate,currency DateofRaise,date/time so you would stick everyone in the employee table including project leaders i added the ProjectLeader,yes/no field to job site but this is only useful if an employee can be both a project leader and a normal employee on another site. if this is not the case then the projectleader,yes/no would be added to the employee table instead. i hope this makes sence Regards Kelvan |
#4
|
|||
|
|||
ACCESS 2007: need help in database breakdown and table creatio
The JobSite table will be a lookup table. It is incorrect as given.
The first two fields are correct. The second two fields don't belong in that table. I would add a couple of fields: JobSiteDescription, text and JobSiteNotes, memo. The pay table should only have fields for PayRate and PayEffectiveDate and PayNotes. That will allow you to track that person's rate of pay for every date from initial hire to the present or to final retirement or other termination. In tblEmployee there should be a field for DateSeparated and a field for ReasonForSeparation. There could be a lookup table: tblReasonForSeparation that might have values like: Lay off, Leave of Absence, Termination for cause, etc. Also, since the construction trades tend to be highly cyclical you might want to allow for the possibility of several hire and separation dates. For example; a person might be laid off due to lack of work. During the hiatus that person finds employment with another organization. When your organization might next desire his services he isn't available. At some later date that person might be available and hired to meet a need. tblEmployee records should have information only about the employee, nothing else. If you might track training events and skills and skill levels then other, related tables must be used for that purpose. For the next few years, don't worry about there being too much data in your application. You'll probably find that you won't fill the allowable space in an Access BackEnd (2 Gigabytes) in this application during your career unless you're storing images. With proper design you'll be able to retain old data for a long time. Believe it or not, as your application becomes more useful to your users they'll be asking you for historical information. Be patient with yourself while getting over the worst of the first major learning curves in learning Access. Post back with specific technical issues; one per thread. HTH -- -Larry- -- "Bob76" wrote in message ... Thanks for your reply.. I don't quite get the need for the Jobsite table though. I also don't know how the pay table can track pay over time. Please explain. thanks "Lord Kelvan" wrote: i would have something like tblemployee Employeeid,autonum LastName,text FirstName,text DateHired,date/time ApprenticeLevel,text or number depending on format of field PreviousRate,currency CurrentRate,currency DateofRaise,date/time ApprenticeSchoolStartDate,date/time ApprenticeSchoolEndDate,date/time Comments,memo or text depending on length tbljobsite jobsiteid,autonum Jobsitename,text employeeid,number ProjectLeader,yes/no if you want to track the pay increases over time not juts the last pay increase you need another table tblpay payid,autonum employeeid,number PreviousRate,currency CurrentRate,currency DateofRaise,date/time so you would stick everyone in the employee table including project leaders i added the ProjectLeader,yes/no field to job site but this is only useful if an employee can be both a project leader and a normal employee on another site. if this is not the case then the projectleader,yes/no would be added to the employee table instead. i hope this makes sence Regards Kelvan |
#5
|
|||
|
|||
ACCESS 2007: need help in database breakdown and table creatio
the job site table is designed to store what employees are in what job
site so you can do a quick seach so see this information the pay table allows you too see over time ie tblpay payid employeeid PreviousRate CurrentRate DateofRaise 1 1 50000 55000 1/1/2008 2 1 55000 58000 1/5/2008 does this make sence o right i seem to have forgotten a table you need a work table which will have the employeeid and the jobsiteid so you can see the list of employees on a site. tblemployee Employeeid LastName FirstName DateHired ApprenticeLevel ApprenticeSchoolStartDate ApprenticeSchoolEndDate Comments 1 bob jones 1/1/2007 1 1/5/2007 6/9/2007 he is fat 2 frank smith 1/8/2007 tbljobsite jobsiteid Jobsitename 1 that big job 2 that other job tblwork employeeid jobsiteid projectleader 1 1 2 1 yes 2 1 yes i hope that helps regards Kelvan |
#6
|
|||
|
|||
ACCESS 2007: need help in database breakdown and table creatio
Hi Larry & Kelvan,
Thanks so much for your input and suggestions.. Larry, you're right about the construction business being cyclical. I do have an Excel file w/ the terminated employees, moved from the main Empl file as soon as he/she is laid off/quit. I've had to move it back when re-hired. Also have an Excel file of Employee Evaluations (done 2x a yr by the PL's) w/c my boss falls back on when time comes to lay off or rehire people. Will have to start from scratch tables... What I've created for the past month are not working out..very frustrating, esp to do w/ creating relationships. Then I created this latest one: 1: TblEmployees w/ Empl#, LastName, FirstName, DateHired, ProjLeader, Jobsite. 2: Tbl PayRates w/ Empl#, PrevPay, CurrentPay, Date of Raise, Level, AppSchool Start, AppSchool End. 3: TblComments w/ Empl#, Comments. 4: TblEvaluations: Empl#, Date, Evaluation, Jobsite, Evaluated By: My boss wants subdatasheets so that when bring up an employee, can cascade to diff subdatasheets for the pay, comments, evaluations. But then he wants separate tables for each ProjLeader w/ his own employees/jobsites. So had 3 TBLEmployees for each PL,but the Tables Payrates, Comments and Eval include all employees. I created relationships and this is where I got stuck. Is there a way I can create a table from a query? I was thinking of creating queries for each PL as criteria, so that when a worker moves from 1 PL to another, it will be automatic move in the their respective tables Hope this makes sense. I will try out your suggestions and see where it takes me. Hope it's okay to bombard you guys w/ questions again when I encounter problems during this process. Thanks Totally confused, BOB76 "Larry Daugherty" wrote: The JobSite table will be a lookup table. It is incorrect as given. The first two fields are correct. The second two fields don't belong in that table. I would add a couple of fields: JobSiteDescription, text and JobSiteNotes, memo. The pay table should only have fields for PayRate and PayEffectiveDate and PayNotes. That will allow you to track that person's rate of pay for every date from initial hire to the present or to final retirement or other termination. In tblEmployee there should be a field for DateSeparated and a field for ReasonForSeparation. There could be a lookup table: tblReasonForSeparation that might have values like: Lay off, Leave of Absence, Termination for cause, etc. Also, since the construction trades tend to be highly cyclical you might want to allow for the possibility of several hire and separation dates. For example; a person might be laid off due to lack of work. During the hiatus that person finds employment with another organization. When your organization might next desire his services he isn't available. At some later date that person might be available and hired to meet a need. tblEmployee records should have information only about the employee, nothing else. If you might track training events and skills and skill levels then other, related tables must be used for that purpose. For the next few years, don't worry about there being too much data in your application. You'll probably find that you won't fill the allowable space in an Access BackEnd (2 Gigabytes) in this application during your career unless you're storing images. With proper design you'll be able to retain old data for a long time. Believe it or not, as your application becomes more useful to your users they'll be asking you for historical information. Be patient with yourself while getting over the worst of the first major learning curves in learning Access. Post back with specific technical issues; one per thread. HTH -- -Larry- -- "Bob76" wrote in message ... Thanks for your reply.. I don't quite get the need for the Jobsite table though. I also don't know how the pay table can track pay over time. Please explain. thanks "Lord Kelvan" wrote: i would have something like tblemployee Employeeid,autonum LastName,text FirstName,text DateHired,date/time ApprenticeLevel,text or number depending on format of field PreviousRate,currency CurrentRate,currency DateofRaise,date/time ApprenticeSchoolStartDate,date/time ApprenticeSchoolEndDate,date/time Comments,memo or text depending on length tbljobsite jobsiteid,autonum Jobsitename,text employeeid,number ProjectLeader,yes/no if you want to track the pay increases over time not juts the last pay increase you need another table tblpay payid,autonum employeeid,number PreviousRate,currency CurrentRate,currency DateofRaise,date/time so you would stick everyone in the employee table including project leaders i added the ProjectLeader,yes/no field to job site but this is only useful if an employee can be both a project leader and a normal employee on another site. if this is not the case then the projectleader,yes/no would be added to the employee table instead. i hope this makes sence Regards Kelvan |
#7
|
|||
|
|||
ACCESS 2007: need help in database breakdown and table creatio
Comments in line.
"Lord Kelvan" wrote in message ... the job site table is designed to store what employees are in what job site so you can do a quick seach so see this information the pay table allows you too see over time ie tblpay payid employeeid PreviousRate CurrentRate DateofRaise 1 1 50000 55000 1/1/2008 2 1 55000 58000 1/5/2008 does this make sence No. The below is better and is Relational. PreviousRate and CurrentRate cause someone to enter redundant data. tblPay PayID EmployeeID Rate EffectiveDate 1 1 55000 1/1/2008 2 1 58000 1/5/2008 Queries to extract required information are easy to create. The latest date is always the last rate. You can create a Report based on a query that will list every rate and effective date in date order for each employee. o right i seem to have forgotten a table you need a work table which will have the employeeid and the jobsiteid so you can see the list of employees on a site. tblemployee Employeeid LastName FirstName DateHired ApprenticeLevel ApprenticeSchoolStartDate ApprenticeSchoolEndDate Comments 1 bob jones 1/1/2007 1 1/5/2007 6/9/2007 he is fat 2 frank smith 1/8/2007 I would only include the apprentice School info in the tblEmployee record if every single employee must have that school and if there will never be any other training noted in your application. If there will be other training for some or all employees then I'd have a tblTraining. tblTraining would be in a many-to-many relationship with tblEmployee. tblTraining would list every course of training that an employee might take. The junction table, tblEmployeeTraining would list every instance of an employee and a course of training. tbljobsite jobsiteid Jobsitename 1 that big job 2 that other job tblwork employeeid jobsiteid projectleader 1 1 2 1 yes 2 1 yes I have trouble with the ProjectLeader issue. Is it a permanent Rank that once attained stays with that person? If so and thee are other Ranks to consider; President, Vice President, other officers, Clerk of the Works, etc. then there should be a tblRank that lists every possible rank. It would be a lookup table Is it simply situational: this person is the Project leader on this particular job but on other jobs s/he is not??? i hope that helps regards Kelvan HTH -- -Larry- -- |
#8
|
|||
|
|||
ACCESS 2007: need help in database breakdown and table creatio
Hi Bob,
There is a lot to learn about developing applications and beyond that there is a lot to learn about Relational Database Management Systems and still more to learn about Access. Not for the faint of heart but well worth the trip. The hardest thing for people making the transition from managing data with Excel to doing it with Access is that you must unlearn a lot of what works so well for you in Excel in order to learn how to do it with Access. Properly designed and implemented there will be no need to remove historical data. It will become useful to you in the future. Don't ask anyone's permission; just do it that way. When your design is complete and rolled out *no one should ever see the tables*. They should see and interact with the Forms you design that are based on Queries on those Tables. You will create Reports based on Queries on Tables. People will see the data organized as you have designed the report. Microsoft and the Access development team are working hard to push Access down to the level that unsophisticated users can do useful things with it. In the process of doing that they've created and implemented several dysfunctional traps. Those nifty things they do will allow a novice Access user to quickly create some applications that seem OK to the unsophisticated eye and judgement. The applications produced are expensive or impossible to maintain or enhance if you use those dysfunctional shortcuts. Try to hold yourself to a higher standard. Poke around at www.mvps.org/access for lots of useful insights and guidance. Your boss is wrong in what s/he thinks is the right way to design your schema (tables and relationships). What s/he said to do amounts to coding data in table names. That is not to be done. Tables are named for entities in play in your application. There will be one and only one Table for any given entity type. By implication, every entity of that type belongs in that one table. Do not accept micromanagement and technical direction from anyone who knows less about the technology in question that you do. A crude analogy to what you've been told to do follows: You will build an automobile tracking system for a car dealership. Your boss would have tblRedCars, tblBlueCars, tblWhiteCars, etc. From what's given just above you know that there should be a single tblCars and that every car would be in that table. Color would be a single field in the record for a car. You can then run a query to retrieve the records for cars of just one color if you choose. There should not be a tblComments unless comments are a really big issue to be tracked separately. Just about every table should have a notes field in it. If the notes for a single record can exceed 255 characters then the datatype should be Memo. You can use a Make Table Query to create a table. Post back with questions as they arise. HTH -- -Larry- -- "Bob76" wrote in message ... Hi Larry & Kelvan, Thanks so much for your input and suggestions.. Larry, you're right about the construction business being cyclical. I do have an Excel file w/ the terminated employees, moved from the main Empl file as soon as he/she is laid off/quit. I've had to move it back when re-hired. Also have an Excel file of Employee Evaluations (done 2x a yr by the PL's) w/c my boss falls back on when time comes to lay off or rehire people. Will have to start from scratch tables... What I've created for the past month are not working out..very frustrating, esp to do w/ creating relationships. Then I created this latest one: 1: TblEmployees w/ Empl#, LastName, FirstName, DateHired, ProjLeader, Jobsite. 2: Tbl PayRates w/ Empl#, PrevPay, CurrentPay, Date of Raise, Level, AppSchool Start, AppSchool End. 3: TblComments w/ Empl#, Comments. 4: TblEvaluations: Empl#, Date, Evaluation, Jobsite, Evaluated By: My boss wants subdatasheets so that when bring up an employee, can cascade to diff subdatasheets for the pay, comments, evaluations. But then he wants separate tables for each ProjLeader w/ his own employees/jobsites. So had 3 TBLEmployees for each PL,but the Tables Payrates, Comments and Eval include all employees. I created relationships and this is where I got stuck. Is there a way I can create a table from a query? I was thinking of creating queries for each PL as criteria, so that when a worker moves from 1 PL to another, it will be automatic move in the their respective tables Hope this makes sense. I will try out your suggestions and see where it takes me. Hope it's okay to bombard you guys w/ questions again when I encounter problems during this process. Thanks Totally confused, BOB76 "Larry Daugherty" wrote: The JobSite table will be a lookup table. It is incorrect as given. The first two fields are correct. The second two fields don't belong in that table. I would add a couple of fields: JobSiteDescription, text and JobSiteNotes, memo. The pay table should only have fields for PayRate and PayEffectiveDate and PayNotes. That will allow you to track that person's rate of pay for every date from initial hire to the present or to final retirement or other termination. In tblEmployee there should be a field for DateSeparated and a field for ReasonForSeparation. There could be a lookup table: tblReasonForSeparation that might have values like: Lay off, Leave of Absence, Termination for cause, etc. Also, since the construction trades tend to be highly cyclical you might want to allow for the possibility of several hire and separation dates. For example; a person might be laid off due to lack of work. During the hiatus that person finds employment with another organization. When your organization might next desire his services he isn't available. At some later date that person might be available and hired to meet a need. tblEmployee records should have information only about the employee, nothing else. If you might track training events and skills and skill levels then other, related tables must be used for that purpose. For the next few years, don't worry about there being too much data in your application. You'll probably find that you won't fill the allowable space in an Access BackEnd (2 Gigabytes) in this application during your career unless you're storing images. With proper design you'll be able to retain old data for a long time. Believe it or not, as your application becomes more useful to your users they'll be asking you for historical information. Be patient with yourself while getting over the worst of the first major learning curves in learning Access. Post back with specific technical issues; one per thread. HTH -- -Larry- -- "Bob76" wrote in message ... Thanks for your reply.. I don't quite get the need for the Jobsite table though. I also don't know how the pay table can track pay over time. Please explain. thanks "Lord Kelvan" wrote: i would have something like tblemployee Employeeid,autonum LastName,text FirstName,text DateHired,date/time ApprenticeLevel,text or number depending on format of field PreviousRate,currency CurrentRate,currency DateofRaise,date/time ApprenticeSchoolStartDate,date/time ApprenticeSchoolEndDate,date/time Comments,memo or text depending on length tbljobsite jobsiteid,autonum Jobsitename,text employeeid,number ProjectLeader,yes/no if you want to track the pay increases over time not juts the last pay increase you need another table tblpay payid,autonum employeeid,number PreviousRate,currency CurrentRate,currency DateofRaise,date/time so you would stick everyone in the employee table including project leaders i added the ProjectLeader,yes/no field to job site but this is only useful if an employee can be both a project leader and a normal employee on another site. if this is not the case then the projectleader,yes/no would be added to the employee table instead. i hope this makes sence Regards Kelvan |
#9
|
|||
|
|||
ACCESS 2007: need help in database breakdown and table creatio
Hi Larry,
Thanks a lot for your advice. It has cleared up a lot of misconceptions in my mind. I concentrated too much on designing my Access database based on what I have in Excel that I've totally lost focus on what Access can do w/ regards to providing meaningful infos in a structured format. I've also glanced thru the mvps site and it has loads of infos which I'll take the time to dissect these few weeks while I design my Access database again from scratch. Will get back to you for advice when I get stuck. Thanks again, Bob "Larry Daugherty" wrote: Hi Bob, There is a lot to learn about developing applications and beyond that there is a lot to learn about Relational Database Management Systems and still more to learn about Access. Not for the faint of heart but well worth the trip. The hardest thing for people making the transition from managing data with Excel to doing it with Access is that you must unlearn a lot of what works so well for you in Excel in order to learn how to do it with Access. Properly designed and implemented there will be no need to remove historical data. It will become useful to you in the future. Don't ask anyone's permission; just do it that way. When your design is complete and rolled out *no one should ever see the tables*. They should see and interact with the Forms you design that are based on Queries on those Tables. You will create Reports based on Queries on Tables. People will see the data organized as you have designed the report. Microsoft and the Access development team are working hard to push Access down to the level that unsophisticated users can do useful things with it. In the process of doing that they've created and implemented several dysfunctional traps. Those nifty things they do will allow a novice Access user to quickly create some applications that seem OK to the unsophisticated eye and judgement. The applications produced are expensive or impossible to maintain or enhance if you use those dysfunctional shortcuts. Try to hold yourself to a higher standard. Poke around at www.mvps.org/access for lots of useful insights and guidance. Your boss is wrong in what s/he thinks is the right way to design your schema (tables and relationships). What s/he said to do amounts to coding data in table names. That is not to be done. Tables are named for entities in play in your application. There will be one and only one Table for any given entity type. By implication, every entity of that type belongs in that one table. Do not accept micromanagement and technical direction from anyone who knows less about the technology in question that you do. A crude analogy to what you've been told to do follows: You will build an automobile tracking system for a car dealership. Your boss would have tblRedCars, tblBlueCars, tblWhiteCars, etc. From what's given just above you know that there should be a single tblCars and that every car would be in that table. Color would be a single field in the record for a car. You can then run a query to retrieve the records for cars of just one color if you choose. There should not be a tblComments unless comments are a really big issue to be tracked separately. Just about every table should have a notes field in it. If the notes for a single record can exceed 255 characters then the datatype should be Memo. You can use a Make Table Query to create a table. Post back with questions as they arise. HTH -- -Larry- -- "Bob76" wrote in message ... Hi Larry & Kelvan, Thanks so much for your input and suggestions.. Larry, you're right about the construction business being cyclical. I do have an Excel file w/ the terminated employees, moved from the main Empl file as soon as he/she is laid off/quit. I've had to move it back when re-hired. Also have an Excel file of Employee Evaluations (done 2x a yr by the PL's) w/c my boss falls back on when time comes to lay off or rehire people. Will have to start from scratch tables... What I've created for the past month are not working out..very frustrating, esp to do w/ creating relationships. Then I created this latest one: 1: TblEmployees w/ Empl#, LastName, FirstName, DateHired, ProjLeader, Jobsite. 2: Tbl PayRates w/ Empl#, PrevPay, CurrentPay, Date of Raise, Level, AppSchool Start, AppSchool End. 3: TblComments w/ Empl#, Comments. 4: TblEvaluations: Empl#, Date, Evaluation, Jobsite, Evaluated By: My boss wants subdatasheets so that when bring up an employee, can cascade to diff subdatasheets for the pay, comments, evaluations. But then he wants separate tables for each ProjLeader w/ his own employees/jobsites. So had 3 TBLEmployees for each PL,but the Tables Payrates, Comments and Eval include all employees. I created relationships and this is where I got stuck. Is there a way I can create a table from a query? I was thinking of creating queries for each PL as criteria, so that when a worker moves from 1 PL to another, it will be automatic move in the their respective tables Hope this makes sense. I will try out your suggestions and see where it takes me. Hope it's okay to bombard you guys w/ questions again when I encounter problems during this process. Thanks Totally confused, BOB76 "Larry Daugherty" wrote: The JobSite table will be a lookup table. It is incorrect as given. The first two fields are correct. The second two fields don't belong in that table. I would add a couple of fields: JobSiteDescription, text and JobSiteNotes, memo. The pay table should only have fields for PayRate and PayEffectiveDate and PayNotes. That will allow you to track that person's rate of pay for every date from initial hire to the present or to final retirement or other termination. In tblEmployee there should be a field for DateSeparated and a field for ReasonForSeparation. There could be a lookup table: tblReasonForSeparation that might have values like: Lay off, Leave of Absence, Termination for cause, etc. Also, since the construction trades tend to be highly cyclical you might want to allow for the possibility of several hire and separation dates. For example; a person might be laid off due to lack of work. During the hiatus that person finds employment with another organization. When your organization might next desire his services he isn't available. At some later date that person might be available and hired to meet a need. tblEmployee records should have information only about the employee, nothing else. If you might track training events and skills and skill levels then other, related tables must be used for that purpose. For the next few years, don't worry about there being too much data in your application. You'll probably find that you won't fill the allowable space in an Access BackEnd (2 Gigabytes) in this application during your career unless you're storing images. With proper design you'll be able to retain old data for a long time. Believe it or not, as your application becomes more useful to your users they'll be asking you for historical information. Be patient with yourself while getting over the worst of the first major learning curves in learning Access. Post back with specific technical issues; one per thread. HTH -- -Larry- -- "Bob76" wrote in message ... Thanks for your reply.. I don't quite get the need for the Jobsite table though. I also don't know how the pay table can track pay over time. Please explain. thanks "Lord Kelvan" wrote: i would have something like tblemployee Employeeid,autonum LastName,text FirstName,text DateHired,date/time ApprenticeLevel,text or number depending on format of field PreviousRate,currency CurrentRate,currency DateofRaise,date/time ApprenticeSchoolStartDate,date/time ApprenticeSchoolEndDate,date/time Comments,memo or text depending on length tbljobsite jobsiteid,autonum Jobsitename,text employeeid,number ProjectLeader,yes/no if you want to track the pay increases over time not juts the last pay increase you need another table tblpay payid,autonum employeeid,number PreviousRate,currency CurrentRate,currency DateofRaise,date/time so you would stick everyone in the employee table including project leaders i added the ProjectLeader,yes/no field to job site but this is only useful if an employee can be both a project leader and a normal employee on another site. if this is not the case then the projectleader,yes/no would be added to the employee table instead. i hope this makes sence Regards Kelvan |
Thread Tools | |
Display Modes | |
|
|