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
|
|||
|
|||
How do I design tables that collect information to generate vario.
I have worked with access for a few years and even designed a few databases
but none as large as this one and would love to benefit from others expertise before I begin the design. This data base will be used for tracking assignments for several employees - many assignments (in thousands) and several employees. I will email the assignments to each employee (could be 25-50 assignments to each employee). The employee then returns a draft of each assignment which I will then forward to an editor. There could be several levels of correspondence between editors & the employee on each assignment. When the assignment is approved by the editor I will then send the assignment to our client for approval. The assignment could then involve several levels of revision (clientmeeditoremployee) before final acceptance by the client. Once approved by the client a payment code is assigned and this payment code is then applied to the employee’s record and the accumulation of such assignment payments becomes the basis for the employee’s pay. This data base must track the assignments received from the client, showing progress on each assignment; must keep track of each employee’s completed assignments and the payments received; and must be able to generate reports for various units within our organization such as payroll, billing and accounting; and possibly keep personnel type records for these employees some of who are also under another payroll code on an hourly basis. Since these assignments have a completion time factor I need to be able to track employee’s leave time so that assignments are not left in someone’s email and we miss deadlines. I welcome any suggestions on building tables and establishing relationships for this project. I also would like opinions on whether it would be easier to use the assignment code given by the client; establish a new simpler one for internal use and link it to the client’s code; or add an internal company code in front of the client’s code. It is conceivable that the employee originally assigned the project does not complete it and it would be reassigned to another employee; otherwise, I would without hesitation use the client’s code. -- Jo Anna |
#2
|
|||
|
|||
Jo Anna
I am not clear, from your description, what the relationships are for your situation. But I'd say you've posted in the correct newsgroup (tablesdbdesign) for ideas on table structure and relationship. I guess I'm a "picture" person. Could you repost a description along the lines of: We have many Employees We have many Assignments Employess work on one or more Assignments We want to track the different things Employees do on their Assignments We have Editors do things on Assignments (does that mean you could count an Editor as just another type of Employee?) We want to track the routing of the different things done on Assignments (suggests knowing where each assignment is, at which stage, with which Employee - ?Colonel Mustard, in the Conservatory, with the candlestick?!) We have Clients do things on Assignments (whoops! Clients aren't Employees, right?) We get paid by Clients for Assignments We want to distribute payments to Employees There may need to be interfaces to other systems in the business. Whew! This is rather ambitious! Feel free to make any/all corrections to my first stab at it. I'm wondering, since you mentioned deadlines, if you'll have enough time to work all the details out? Have you looked into existing applications that might be able to do some/most/all of what you need. I'm not sure, but the Customer Management or Enterprise Resource management fields offer commercial applications (pricey to be sure!). Good luck! Jeff Boyce Access MVP "Jo Anna" wrote in message ... I have worked with access for a few years and even designed a few databases but none as large as this one and would love to benefit from others expertise before I begin the design. This data base will be used for tracking assignments for several employees - many assignments (in thousands) and several employees. I will email the assignments to each employee (could be 25-50 assignments to each employee). The employee then returns a draft of each assignment which I will then forward to an editor. There could be several levels of correspondence between editors & the employee on each assignment. When the assignment is approved by the editor I will then send the assignment to our client for approval. The assignment could then involve several levels of revision (clientmeeditoremployee) before final acceptance by the client. Once approved by the client a payment code is assigned and this payment code is then applied to the employee’s record and the accumulation of such assignment payments becomes the basis for the employee’s pay. This data base must track the assignments received from the client, showing progress on each assignment; must keep track of each employee’s completed assignments and the payments received; and must be able to generate reports for various units within our organization such as payroll, billing and accounting; and possibly keep personnel type records for these employees some of who are also under another payroll code on an hourly basis. Since these assignments have a completion time factor I need to be able to track employee’s leave time so that assignments are not left in someone’s email and we miss deadlines. I welcome any suggestions on building tables and establishing relationships for this project. I also would like opinions on whether it would be easier to use the assignment code given by the client; establish a new simpler one for internal use and link it to the client’s code; or add an internal company code in front of the client’s code. It is conceivable that the employee originally assigned the project does not complete it and it would be reassigned to another employee; otherwise, I would without hesitation use the client’s code. -- Jo Anna |
#3
|
|||
|
|||
Jeff,
Thanks for jumping in the deep water to try to help me. I hope I have included enough information in my responses so that we both can get to land. Jo Anna "Jeff Boyce" wrote: Jo Anna I am not clear, from your description, what the relationships are for your situation. But I'd say you've posted in the correct newsgroup (tablesdbdesign) for ideas on table structure and relationship. I guess I'm a "picture" person. Could you repost a description along the lines of: We have many Employees We have many Assignments Employess work on one or more Assignments We want to track the different things Employees do on their Assignments We need to know where the assignment is the the process. How close to completion, make sure it doesn't get lost as it is routed from client-employee-editor-client. We have Editors do things on Assignments (does that mean you could count an Editor as just another type of Employee?) yes editors for the most part are FTE hourly paid employees and these "assignment employees" are part time employees paid on the basis of completed & client approved assignments We want to track the routing of the different things done on Assignments (suggests knowing where each assignment is, at which stage, with which Employee - ?Colonel Mustard, in the Conservatory, with the candlestick?!) yes where each assignment is, at which stage, with which Employee We have Clients do things on Assignments (whoops! Clients aren't Employees, right?) Right, the clients are not our employees. They will either approve what we send or send it back with request for additional work on the assignment We get paid by Clients for Assignments we get paid only for client approved/accepted assignment We want to distribute payments to Employees employees get paid for assignments accepted by the client (editors are hourly paid) There may need to be interfaces to other systems in the business. " Maybe interface is too strong a link. All I need to do is generate reports for payroll and billing. I will keep personnel type records for the part time employees for this project especially, contact information, leave time, etc. Whew! This is rather ambitious! Feel free to make any/all corrections to my first stab at it. I'm wondering, since you mentioned deadlines, if you'll have enough time to work all the details out? Have you looked into existing applications that might be able to do some/most/all of what you need. I'm not sure, but the Customer Management or Enterprise Resource management fields offer commercial applications (pricey to be sure!). As you can imagine with all the routing required for each assignemnt the profit margin is very thin for this project and funds for additional software was not included in the original planning. Good luck! Jeff Boyce Access MVP "Jo Anna" wrote in message ... I have worked with access for a few years and even designed a few databases but none as large as this one and would love to benefit from others expertise before I begin the design. This data base will be used for tracking assignments for several employees - many assignments (in thousands) and several employees. I will email the assignments to each employee (could be 25-50 assignments to each employee). The employee then returns a draft of each assignment which I will then forward to an editor. There could be several levels of correspondence between editors & the employee on each assignment. When the assignment is approved by the editor I will then send the assignment to our client for approval. The assignment could then involve several levels of revision (clientmeeditoremployee) before final acceptance by the client. Once approved by the client a payment code is assigned and this payment code is then applied to the employee’s record and the accumulation of such assignment payments becomes the basis for the employee’s pay. This data base must track the assignments received from the client, showing progress on each assignment; must keep track of each employee’s completed assignments and the payments received; and must be able to generate reports for various units within our organization such as payroll, billing and accounting; and possibly keep personnel type records for these employees some of who are also under another payroll code on an hourly basis. Since these assignments have a completion time factor I need to be able to track employee’s leave time so that assignments are not left in someone’s email and we miss deadlines. I welcome any suggestions on building tables and establishing relationships for this project. I also would like opinions on whether it would be easier to use the assignment code given by the client; establish a new simpler one for internal use and link it to the client’s code; or add an internal company code in front of the client’s code. It is conceivable that the employee originally assigned the project does not complete it and it would be reassigned to another employee; otherwise, I would without hesitation use the client’s code. -- Jo Anna |
#4
|
|||
|
|||
Jo Anna
I'm even more convinced now... if there's no budget for acquiring off-the-shelf software that might meet your needs, how much of your time is your company willing to commit to developing what sounds like a fairly complex application. If someone asked me to "size" what you've described, I would put it in the "bigger than a breadbox", and perhaps "bigger than a boxcar" category. I didn't see anything in your description that triggered alarms, but I can imagine that what you've described (and left out) could be a year-long project. Best of luck! Jeff Boyce Access MVP "Jo Anna" wrote in message ... Jeff, Thanks for jumping in the deep water to try to help me. I hope I have included enough information in my responses so that we both can get to land. Jo Anna "Jeff Boyce" wrote: Jo Anna I am not clear, from your description, what the relationships are for your situation. But I'd say you've posted in the correct newsgroup (tablesdbdesign) for ideas on table structure and relationship. I guess I'm a "picture" person. Could you repost a description along the lines of: We have many Employees We have many Assignments Employess work on one or more Assignments We want to track the different things Employees do on their Assignments We need to know where the assignment is the the process. How close to completion, make sure it doesn't get lost as it is routed from client-employee-editor-client. We have Editors do things on Assignments (does that mean you could count an Editor as just another type of Employee?) yes editors for the most part are FTE hourly paid employees and these "assignment employees" are part time employees paid on the basis of completed & client approved assignments We want to track the routing of the different things done on Assignments (suggests knowing where each assignment is, at which stage, with which Employee - ?Colonel Mustard, in the Conservatory, with the candlestick?!) yes where each assignment is, at which stage, with which Employee We have Clients do things on Assignments (whoops! Clients aren't Employees, right?) Right, the clients are not our employees. They will either approve what we send or send it back with request for additional work on the assignment We get paid by Clients for Assignments we get paid only for client approved/accepted assignment We want to distribute payments to Employees employees get paid for assignments accepted by the client (editors are hourly paid) There may need to be interfaces to other systems in the business. " Maybe interface is too strong a link. All I need to do is generate reports for payroll and billing. I will keep personnel type records for the part time employees for this project especially, contact information, leave time, etc. Whew! This is rather ambitious! Feel free to make any/all corrections to my first stab at it. I'm wondering, since you mentioned deadlines, if you'll have enough time to work all the details out? Have you looked into existing applications that might be able to do some/most/all of what you need. I'm not sure, but the Customer Management or Enterprise Resource management fields offer commercial applications (pricey to be sure!). As you can imagine with all the routing required for each assignemnt the profit margin is very thin for this project and funds for additional software was not included in the original planning. Good luck! Jeff Boyce Access MVP "Jo Anna" wrote in message ... I have worked with access for a few years and even designed a few databases but none as large as this one and would love to benefit from others expertise before I begin the design. This data base will be used for tracking assignments for several employees - many assignments (in thousands) and several employees. I will email the assignments to each employee (could be 25-50 assignments to each employee). The employee then returns a draft of each assignment which I will then forward to an editor. There could be several levels of correspondence between editors & the employee on each assignment. When the assignment is approved by the editor I will then send the assignment to our client for approval. The assignment could then involve several levels of revision (clientmeeditoremployee) before final acceptance by the client. Once approved by the client a payment code is assigned and this payment code is then applied to the employee’s record and the accumulation of such assignment payments becomes the basis for the employee’s pay. This data base must track the assignments received from the client, showing progress on each assignment; must keep track of each employee’s completed assignments and the payments received; and must be able to generate reports for various units within our organization such as payroll, billing and accounting; and possibly keep personnel type records for these employees some of who are also under another payroll code on an hourly basis. Since these assignments have a completion time factor I need to be able to track employee’s leave time so that assignments are not left in someone’s email and we miss deadlines. I welcome any suggestions on building tables and establishing relationships for this project. I also would like opinions on whether it would be easier to use the assignment code given by the client; establish a new simpler one for internal use and link it to the client’s code; or add an internal company code in front of the client’s code. It is conceivable that the employee originally assigned the project does not complete it and it would be reassigned to another employee; otherwise, I would without hesitation use the client’s code. -- Jo Anna |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to make Access stop adding records to my tables when I switch from Form view to Design view | Mister John Doe | Using Forms | 4 | January 4th, 2005 04:31 AM |
Help with Junction Tables and Subforms | Maureen Smith | New Users | 11 | September 23rd, 2004 02:39 PM |
What is MDE | Charlie | General Discussion | 4 | August 24th, 2004 04:15 PM |
Need help with Tables Design and Relationships | Tom | Database Design | 24 | May 19th, 2004 06:51 PM |
Multiple Many-To-Many Tables | Tom | Database Design | 7 | May 15th, 2004 03:47 AM |