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
|
|||
|
|||
Table design problem?
I am building an employee production db at work. The problem I am having is
with the relationships and maybe my table design. I have one table for employee, Correspondence Received and Correspondence Completed. These employees receive and complete correspondence from taxpayers each day. There are 50 different audit types the department would like to track. With each audit type, there are 15 fields that may are may not apply. So, what I have currently design, the employee can have multiple entries into the database "in a day" if working on more than one audit type. What I need is for them to be able to logg any corresp rec'd or completed (that they did for that day) into one form. Putting it all together is confusing me. hope this makes sense. I have been trying to resolve this issue for months and cannot seem to come up with a solution. Any help would be truly appreciated. I have posted this question 10/20 but the solution hasn't helped. My tables are as follows: Table: Employees EmployeeName- Text Supervisor-Text EmployeeID-Autonumber= PrimaryKey Table: TaxType/AuditType TaxType/AuditType- Text Table: Correspondence Received/Assigned EmployeeID=AutoNumber PrimaryKey EmployeeName-Text DateReceived-Date/Time TaxAuditType- Text FilesReceived- Number PhoneCalls-Number FaxRecd-Number AdditionalCorrep-Number Correspfrom TP/Petitions- Number Checks-Number ServCtr/Email/Phone-Number OtherEmployees-Number DayEndingInventory-Number Table: Correspondence Completed EmployeeID-AutoNumber- Primary Key EmployeeName-Text DateCompleted-DateTime TaxAuditType-Text InventoryPending-Number PendingBill-Number Billing-Number PendingAsmt-Number Assessment-Number CertifiedAsmt-Number Worked/Not Completed-Number My Relationships are One to many= Employees(1) to CorrespondenceReceived/Assigned(many) Related fields a EmployeeName One to many= Employee(1) to CorrespondenceCompleted(many) TaxType/AuditType= one to many on both CorrespondenceRecd and Correspondence Completed (not sure this is necessary because I use TaxType/audit type for combo box on forms) Thank you |
#2
|
|||
|
|||
Table design problem?
It's all very confusing as you have presented it! The Correspondence
Received/Assigned table looks like a list of activities and you want to record how many times each activity was done. What does this have to do with correspondence? What are the 15 fields you want to record for each audit type? In the Correspondence Completed table it looks like you have correspondence that is either Billing or Assessment and your trying to record how many of each have been completed??? What is DateCompleted? Is it the date you completed a certain correspondence or the recording date for the count of correspondence you completed. The basis of your database appears to be pretty complex to where you need to write much more detail to get any help! -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com If you don't get the help you need in the newsgroup, I can help you for a very reasonable fee. Over 1000 Access users have come to me for help. Remember that a lone man built the Ark. A large group of professionals built the Titanic. "Melissa" wrote in message ... I am building an employee production db at work. The problem I am having is with the relationships and maybe my table design. I have one table for employee, Correspondence Received and Correspondence Completed. These employees receive and complete correspondence from taxpayers each day. There are 50 different audit types the department would like to track. With each audit type, there are 15 fields that may are may not apply. So, what I have currently design, the employee can have multiple entries into the database "in a day" if working on more than one audit type. What I need is for them to be able to logg any corresp rec'd or completed (that they did for that day) into one form. Putting it all together is confusing me. hope this makes sense. I have been trying to resolve this issue for months and cannot seem to come up with a solution. Any help would be truly appreciated. I have posted this question 10/20 but the solution hasn't helped. My tables are as follows: Table: Employees EmployeeName- Text Supervisor-Text EmployeeID-Autonumber= PrimaryKey Table: TaxType/AuditType TaxType/AuditType- Text Table: Correspondence Received/Assigned EmployeeID=AutoNumber PrimaryKey EmployeeName-Text DateReceived-Date/Time TaxAuditType- Text FilesReceived- Number PhoneCalls-Number FaxRecd-Number AdditionalCorrep-Number Correspfrom TP/Petitions- Number Checks-Number ServCtr/Email/Phone-Number OtherEmployees-Number DayEndingInventory-Number Table: Correspondence Completed EmployeeID-AutoNumber- Primary Key EmployeeName-Text DateCompleted-DateTime TaxAuditType-Text InventoryPending-Number PendingBill-Number Billing-Number PendingAsmt-Number Assessment-Number CertifiedAsmt-Number Worked/Not Completed-Number My Relationships are One to many= Employees(1) to CorrespondenceReceived/Assigned(many) Related fields a EmployeeName One to many= Employee(1) to CorrespondenceCompleted(many) TaxType/AuditType= one to many on both CorrespondenceRecd and Correspondence Completed (not sure this is necessary because I use TaxType/audit type for combo box on forms) Thank you |
#3
|
|||
|
|||
Table design problem?
I am creating this database for work. This is to record each piece of
correspondence a employee works (resolves) in a day. To try to simplify this, when you receive a bill and do not agree or have a different explaination, and respond to this bill, this is correspondence received. The correspondence we are working is just that. The department would like to track how many are received and completed by an employee each day. We are currently recording this information into a excel spreadsheet, but with 47 employees and the many types of audit types, the workbooks are becoming huge. I hope this simplifies things. "PC Datasheet" wrote: It's all very confusing as you have presented it! The Correspondence Received/Assigned table looks like a list of activities and you want to record how many times each activity was done. What does this have to do with correspondence? What are the 15 fields you want to record for each audit type? In the Correspondence Completed table it looks like you have correspondence that is either Billing or Assessment and your trying to record how many of each have been completed??? What is DateCompleted? Is it the date you completed a certain correspondence or the recording date for the count of correspondence you completed. The basis of your database appears to be pretty complex to where you need to write much more detail to get any help! -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com If you don't get the help you need in the newsgroup, I can help you for a very reasonable fee. Over 1000 Access users have come to me for help. Remember that a lone man built the Ark. A large group of professionals built the Titanic. "Melissa" wrote in message ... I am building an employee production db at work. The problem I am having is with the relationships and maybe my table design. I have one table for employee, Correspondence Received and Correspondence Completed. These employees receive and complete correspondence from taxpayers each day. There are 50 different audit types the department would like to track. With each audit type, there are 15 fields that may are may not apply. So, what I have currently design, the employee can have multiple entries into the database "in a day" if working on more than one audit type. What I need is for them to be able to logg any corresp rec'd or completed (that they did for that day) into one form. Putting it all together is confusing me. hope this makes sense. I have been trying to resolve this issue for months and cannot seem to come up with a solution. Any help would be truly appreciated. I have posted this question 10/20 but the solution hasn't helped. My tables are as follows: Table: Employees EmployeeName- Text Supervisor-Text EmployeeID-Autonumber= PrimaryKey Table: TaxType/AuditType TaxType/AuditType- Text Table: Correspondence Received/Assigned EmployeeID=AutoNumber PrimaryKey EmployeeName-Text DateReceived-Date/Time TaxAuditType- Text FilesReceived- Number PhoneCalls-Number FaxRecd-Number AdditionalCorrep-Number Correspfrom TP/Petitions- Number Checks-Number ServCtr/Email/Phone-Number OtherEmployees-Number DayEndingInventory-Number Table: Correspondence Completed EmployeeID-AutoNumber- Primary Key EmployeeName-Text DateCompleted-DateTime TaxAuditType-Text InventoryPending-Number PendingBill-Number Billing-Number PendingAsmt-Number Assessment-Number CertifiedAsmt-Number Worked/Not Completed-Number My Relationships are One to many= Employees(1) to CorrespondenceReceived/Assigned(many) Related fields a EmployeeName One to many= Employee(1) to CorrespondenceCompleted(many) TaxType/AuditType= one to many on both CorrespondenceRecd and Correspondence Completed (not sure this is necessary because I use TaxType/audit type for combo box on forms) Thank you |
#4
|
|||
|
|||
Table design problem?
Melissa -
It appears that you want the employee to enter each correspondence into a form, and to let the employee select the type of "audit" that is associated with each corresondence that is done? Is this essentially what you seek to do? Or is the "audit" type already associated with each correspondence type, and you just want to let the employee select the correspondence type, entering each one separately that the employee did? It would be helpful if you could show one or two rows of data from the EXCEL spreadsheet so that we can see the types of data you're capturing per row. What data will the employee specifically have "in hand" when he/she is ready to enter a correspondence that is done, and what will the employee need to select or enter additionally while entering that "in hand" data? -- Ken Snell MS ACCESS MVP "Melissa" wrote in message ... I am creating this database for work. This is to record each piece of correspondence a employee works (resolves) in a day. To try to simplify this, when you receive a bill and do not agree or have a different explaination, and respond to this bill, this is correspondence received. The correspondence we are working is just that. The department would like to track how many are received and completed by an employee each day. We are currently recording this information into a excel spreadsheet, but with 47 employees and the many types of audit types, the workbooks are becoming huge. I hope this simplifies things. "Melissa" wrote in message ... I am building an employee production db at work. The problem I am having is with the relationships and maybe my table design. I have one table for employee, Correspondence Received and Correspondence Completed. These employees receive and complete correspondence from taxpayers each day. There are 50 different audit types the department would like to track. With each audit type, there are 15 fields that may are may not apply. So, what I have currently design, the employee can have multiple entries into the database "in a day" if working on more than one audit type. What I need is for them to be able to logg any corresp rec'd or completed (that they did for that day) into one form. Putting it all together is confusing me. hope this makes sense. I have been trying to resolve this issue for months and cannot seem to come up with a solution. Any help would be truly appreciated. I have posted this question 10/20 but the solution hasn't helped. My tables are as follows: Table: Employees EmployeeName- Text Supervisor-Text EmployeeID-Autonumber= PrimaryKey Table: TaxType/AuditType TaxType/AuditType- Text Table: Correspondence Received/Assigned EmployeeID=AutoNumber PrimaryKey EmployeeName-Text DateReceived-Date/Time TaxAuditType- Text FilesReceived- Number PhoneCalls-Number FaxRecd-Number AdditionalCorrep-Number Correspfrom TP/Petitions- Number Checks-Number ServCtr/Email/Phone-Number OtherEmployees-Number DayEndingInventory-Number Table: Correspondence Completed EmployeeID-AutoNumber- Primary Key EmployeeName-Text DateCompleted-DateTime TaxAuditType-Text InventoryPending-Number PendingBill-Number Billing-Number PendingAsmt-Number Assessment-Number CertifiedAsmt-Number Worked/Not Completed-Number My Relationships are One to many= Employees(1) to CorrespondenceReceived/Assigned(many) Related fields a EmployeeName One to many= Employee(1) to CorrespondenceCompleted(many) TaxType/AuditType= one to many on both CorrespondenceRecd and Correspondence Completed (not sure this is necessary because I use TaxType/audit type for combo box on forms) Thank you |
#5
|
|||
|
|||
Table design problem?
Ken, you get what I am saying and I so appreciate that. The employee would
select the type of Audit he/she is receiving/ and worked for each day. They would complete their production each day. The Audit type can be received by 8 different sources; Files Rec'd, a phone call; email etc. The department has over 50 types of audit that the division currently works. (This is a billing/assessment dept). Here is one row of from the datasheet. It did not paste well but, it reads, AgentID 1 on 10/13/2005, (AuditType) Corp DQ Edit (number of files) 25 (same audit type) 5 (additional files recd via phone) then an addition 5 (referrals). We also want to capture their ending inventory by audit type. Same example would also apply to the Corresp completed table. AgentID DateReceived TaxAuditType FilesReceived Phone Calls FaxRecd AdditionalCorresp CorresfromTP/Petitions Checks ServCtr/Email/Phone AG Email Other Agents Activity Notices Bankruptcy Email TaxAppeal TPSReferrals DayEndingInventory 1 10/13/2005 CORP DQ EDITS 25 5 0 0 0 0 0 5 0 I am thinking in essense, that one employee can have multiple sales in one day and those sales can be different products. (This is just for a comparision) Thanks for any help you can give me. I am pretty good at Access and have built many databases for the dept, but this one has me stumped. "Ken Snell [MVP]" wrote: Melissa - It appears that you want the employee to enter each correspondence into a form, and to let the employee select the type of "audit" that is associated with each corresondence that is done? Is this essentially what you seek to do? Or is the "audit" type already associated with each correspondence type, and you just want to let the employee select the correspondence type, entering each one separately that the employee did? It would be helpful if you could show one or two rows of data from the EXCEL spreadsheet so that we can see the types of data you're capturing per row. What data will the employee specifically have "in hand" when he/she is ready to enter a correspondence that is done, and what will the employee need to select or enter additionally while entering that "in hand" data? -- Ken Snell MS ACCESS MVP "Melissa" wrote in message ... I am creating this database for work. This is to record each piece of correspondence a employee works (resolves) in a day. To try to simplify this, when you receive a bill and do not agree or have a different explaination, and respond to this bill, this is correspondence received. The correspondence we are working is just that. The department would like to track how many are received and completed by an employee each day. We are currently recording this information into a excel spreadsheet, but with 47 employees and the many types of audit types, the workbooks are becoming huge. I hope this simplifies things. "Melissa" wrote in message ... I am building an employee production db at work. The problem I am having is with the relationships and maybe my table design. I have one table for employee, Correspondence Received and Correspondence Completed. These employees receive and complete correspondence from taxpayers each day. There are 50 different audit types the department would like to track. With each audit type, there are 15 fields that may are may not apply. So, what I have currently design, the employee can have multiple entries into the database "in a day" if working on more than one audit type. What I need is for them to be able to logg any corresp rec'd or completed (that they did for that day) into one form. Putting it all together is confusing me. hope this makes sense. I have been trying to resolve this issue for months and cannot seem to come up with a solution. Any help would be truly appreciated. I have posted this question 10/20 but the solution hasn't helped. My tables are as follows: Table: Employees EmployeeName- Text Supervisor-Text EmployeeID-Autonumber= PrimaryKey Table: TaxType/AuditType TaxType/AuditType- Text Table: Correspondence Received/Assigned EmployeeID=AutoNumber PrimaryKey EmployeeName-Text DateReceived-Date/Time TaxAuditType- Text FilesReceived- Number PhoneCalls-Number FaxRecd-Number AdditionalCorrep-Number Correspfrom TP/Petitions- Number Checks-Number ServCtr/Email/Phone-Number OtherEmployees-Number DayEndingInventory-Number Table: Correspondence Completed EmployeeID-AutoNumber- Primary Key EmployeeName-Text DateCompleted-DateTime TaxAuditType-Text InventoryPending-Number PendingBill-Number Billing-Number PendingAsmt-Number Assessment-Number CertifiedAsmt-Number Worked/Not Completed-Number My Relationships are One to many= Employees(1) to CorrespondenceReceived/Assigned(many) Related fields a EmployeeName One to many= Employee(1) to CorrespondenceCompleted(many) TaxType/AuditType= one to many on both CorrespondenceRecd and Correspondence Completed (not sure this is necessary because I use TaxType/audit type for combo box on forms) Thank you |
#6
|
|||
|
|||
Table design problem?
I can think of a few ways to do this... each with its own complications and
validation needs. But, let's try something "easy" in terms of setup and design at the moment. You'll want three tables: tblEmployees EmpID ( Primary Key ) EmpName (etc.) tblCorrespondenceTypes CorrID ( Primary Key ) CorrName tblEmpCorrWork EmpID ( composite primary key with CorrID and WorkDate ) CorrID ( composite primary key with EmpID and WorkDate ) WorkDate ( composite primary key with EmpID and CorrID ) WorkQuantity In the third table, EmpID and CorrID would be foreign keys back to the "parent" tables. Then, for data entry, I'd create a form. Set the form up as "Continuous Forms" view. Also set its Data Entry property to Yes (we'll use this form just for data entry right now). Make the form's Record Source be a query that is based on tblEmpCorrWork table. All four fields from that table should be in the query. In the form's FormHeader section, I'd put a combo box and textbox. The combo box would display all the employees so that the employee can select his/her name; the combo box would be bound to the EmpID field in the form's RecordSource. The textbox is where the work date goes -- can be automatically entered or can be typed in by employee, or both; the textbox would be bound to the WorkDate field in the form's RecordSource. In the form's Detail section, I'd put a combo box that displays all the correspondence types; this combo box would be bound to the CorrID field in the form's RecordSource. I'd then put a textbox to allow entry of the number of items for that specific correspondence type; this textbox would be bound to the WorkQuantity field in the form's RecordSource. This setup will not prohibit an employee from selecting the same correspondence type twice for the same date, but the composite primary key in the table will prevent the saving of any data where that is done for that record. But this should get you started..... -- Ken Snell MS ACCESS MVP "Melissa" wrote in message ... Ken, you get what I am saying and I so appreciate that. The employee would select the type of Audit he/she is receiving/ and worked for each day. They would complete their production each day. The Audit type can be received by 8 different sources; Files Rec'd, a phone call; email etc. The department has over 50 types of audit that the division currently works. (This is a billing/assessment dept). Here is one row of from the datasheet. It did not paste well but, it reads, AgentID 1 on 10/13/2005, (AuditType) Corp DQ Edit (number of files) 25 (same audit type) 5 (additional files recd via phone) then an addition 5 (referrals). We also want to capture their ending inventory by audit type. Same example would also apply to the Corresp completed table. AgentID DateReceived TaxAuditType FilesReceived Phone Calls FaxRecd AdditionalCorresp CorresfromTP/Petitions Checks ServCtr/Email/Phone AG Email Other Agents Activity Notices Bankruptcy Email TaxAppeal TPSReferrals DayEndingInventory 1 10/13/2005 CORP DQ EDITS 25 5 0 0 0 0 0 5 0 I am thinking in essense, that one employee can have multiple sales in one day and those sales can be different products. (This is just for a comparision) Thanks for any help you can give me. I am pretty good at Access and have built many databases for the dept, but this one has me stumped. "Ken Snell [MVP]" wrote: Melissa - It appears that you want the employee to enter each correspondence into a form, and to let the employee select the type of "audit" that is associated with each corresondence that is done? Is this essentially what you seek to do? Or is the "audit" type already associated with each correspondence type, and you just want to let the employee select the correspondence type, entering each one separately that the employee did? It would be helpful if you could show one or two rows of data from the EXCEL spreadsheet so that we can see the types of data you're capturing per row. What data will the employee specifically have "in hand" when he/she is ready to enter a correspondence that is done, and what will the employee need to select or enter additionally while entering that "in hand" data? -- Ken Snell MS ACCESS MVP "Melissa" wrote in message ... I am creating this database for work. This is to record each piece of correspondence a employee works (resolves) in a day. To try to simplify this, when you receive a bill and do not agree or have a different explaination, and respond to this bill, this is correspondence received. The correspondence we are working is just that. The department would like to track how many are received and completed by an employee each day. We are currently recording this information into a excel spreadsheet, but with 47 employees and the many types of audit types, the workbooks are becoming huge. I hope this simplifies things. "Melissa" wrote in message ... I am building an employee production db at work. The problem I am having is with the relationships and maybe my table design. I have one table for employee, Correspondence Received and Correspondence Completed. These employees receive and complete correspondence from taxpayers each day. There are 50 different audit types the department would like to track. With each audit type, there are 15 fields that may are may not apply. So, what I have currently design, the employee can have multiple entries into the database "in a day" if working on more than one audit type. What I need is for them to be able to logg any corresp rec'd or completed (that they did for that day) into one form. Putting it all together is confusing me. hope this makes sense. I have been trying to resolve this issue for months and cannot seem to come up with a solution. Any help would be truly appreciated. I have posted this question 10/20 but the solution hasn't helped. My tables are as follows: Table: Employees EmployeeName- Text Supervisor-Text EmployeeID-Autonumber= PrimaryKey Table: TaxType/AuditType TaxType/AuditType- Text Table: Correspondence Received/Assigned EmployeeID=AutoNumber PrimaryKey EmployeeName-Text DateReceived-Date/Time TaxAuditType- Text FilesReceived- Number PhoneCalls-Number FaxRecd-Number AdditionalCorrep-Number Correspfrom TP/Petitions- Number Checks-Number ServCtr/Email/Phone-Number OtherEmployees-Number DayEndingInventory-Number Table: Correspondence Completed EmployeeID-AutoNumber- Primary Key EmployeeName-Text DateCompleted-DateTime TaxAuditType-Text InventoryPending-Number PendingBill-Number Billing-Number PendingAsmt-Number Assessment-Number CertifiedAsmt-Number Worked/Not Completed-Number My Relationships are One to many= Employees(1) to CorrespondenceReceived/Assigned(many) Related fields a EmployeeName One to many= Employee(1) to CorrespondenceCompleted(many) TaxType/AuditType= one to many on both CorrespondenceRecd and Correspondence Completed (not sure this is necessary because I use TaxType/audit type for combo box on forms) Thank you |
#7
|
|||
|
|||
Table design problem?
Thanks for your help Ken. I have completed doing the suggestions you have
offered. But have a couple questions on the tblEmpCorrWork. On the third table you suggested only to put the 4 fields into the table, then to build the form based on a qry of those fields. In this table, there would be more than 4 fields because each audit type can have up to 9 different sort fields. Would these fields go into a different table? I put these fields into the table 3 with the EmpID and CorrID being the foreign keys back to the parent. What is happening is it is associating each agent with a particular audit code when each agent may work many different types of audits. Thanks again for your help "Ken Snell [MVP]" wrote: I can think of a few ways to do this... each with its own complications and validation needs. But, let's try something "easy" in terms of setup and design at the moment. You'll want three tables: tblEmployees EmpID ( Primary Key ) EmpName (etc.) tblCorrespondenceTypes CorrID ( Primary Key ) CorrName tblEmpCorrWork EmpID ( composite primary key with CorrID and WorkDate ) CorrID ( composite primary key with EmpID and WorkDate ) WorkDate ( composite primary key with EmpID and CorrID ) WorkQuantity In the third table, EmpID and CorrID would be foreign keys back to the "parent" tables. Then, for data entry, I'd create a form. Set the form up as "Continuous Forms" view. Also set its Data Entry property to Yes (we'll use this form just for data entry right now). Make the form's Record Source be a query that is based on tblEmpCorrWork table. All four fields from that table should be in the query. In the form's FormHeader section, I'd put a combo box and textbox. The combo box would display all the employees so that the employee can select his/her name; the combo box would be bound to the EmpID field in the form's RecordSource. The textbox is where the work date goes -- can be automatically entered or can be typed in by employee, or both; the textbox would be bound to the WorkDate field in the form's RecordSource. In the form's Detail section, I'd put a combo box that displays all the correspondence types; this combo box would be bound to the CorrID field in the form's RecordSource. I'd then put a textbox to allow entry of the number of items for that specific correspondence type; this textbox would be bound to the WorkQuantity field in the form's RecordSource. This setup will not prohibit an employee from selecting the same correspondence type twice for the same date, but the composite primary key in the table will prevent the saving of any data where that is done for that record. But this should get you started..... -- Ken Snell MS ACCESS MVP "Melissa" wrote in message ... Ken, you get what I am saying and I so appreciate that. The employee would select the type of Audit he/she is receiving/ and worked for each day. They would complete their production each day. The Audit type can be received by 8 different sources; Files Rec'd, a phone call; email etc. The department has over 50 types of audit that the division currently works. (This is a billing/assessment dept). Here is one row of from the datasheet. It did not paste well but, it reads, AgentID 1 on 10/13/2005, (AuditType) Corp DQ Edit (number of files) 25 (same audit type) 5 (additional files recd via phone) then an addition 5 (referrals). We also want to capture their ending inventory by audit type. Same example would also apply to the Corresp completed table. AgentID DateReceived TaxAuditType FilesReceived Phone Calls FaxRecd AdditionalCorresp CorresfromTP/Petitions Checks ServCtr/Email/Phone AG Email Other Agents Activity Notices Bankruptcy Email TaxAppeal TPSReferrals DayEndingInventory 1 10/13/2005 CORP DQ EDITS 25 5 0 0 0 0 0 5 0 I am thinking in essense, that one employee can have multiple sales in one day and those sales can be different products. (This is just for a comparision) Thanks for any help you can give me. I am pretty good at Access and have built many databases for the dept, but this one has me stumped. "Ken Snell [MVP]" wrote: Melissa - It appears that you want the employee to enter each correspondence into a form, and to let the employee select the type of "audit" that is associated with each corresondence that is done? Is this essentially what you seek to do? Or is the "audit" type already associated with each correspondence type, and you just want to let the employee select the correspondence type, entering each one separately that the employee did? It would be helpful if you could show one or two rows of data from the EXCEL spreadsheet so that we can see the types of data you're capturing per row. What data will the employee specifically have "in hand" when he/she is ready to enter a correspondence that is done, and what will the employee need to select or enter additionally while entering that "in hand" data? -- Ken Snell MS ACCESS MVP "Melissa" wrote in message ... I am creating this database for work. This is to record each piece of correspondence a employee works (resolves) in a day. To try to simplify this, when you receive a bill and do not agree or have a different explaination, and respond to this bill, this is correspondence received. The correspondence we are working is just that. The department would like to track how many are received and completed by an employee each day. We are currently recording this information into a excel spreadsheet, but with 47 employees and the many types of audit types, the workbooks are becoming huge. I hope this simplifies things. "Melissa" wrote in message ... I am building an employee production db at work. The problem I am having is with the relationships and maybe my table design. I have one table for employee, Correspondence Received and Correspondence Completed. These employees receive and complete correspondence from taxpayers each day. There are 50 different audit types the department would like to track. With each audit type, there are 15 fields that may are may not apply. So, what I have currently design, the employee can have multiple entries into the database "in a day" if working on more than one audit type. What I need is for them to be able to logg any corresp rec'd or completed (that they did for that day) into one form. Putting it all together is confusing me. hope this makes sense. I have been trying to resolve this issue for months and cannot seem to come up with a solution. Any help would be truly appreciated. I have posted this question 10/20 but the solution hasn't helped. My tables are as follows: Table: Employees EmployeeName- Text Supervisor-Text EmployeeID-Autonumber= PrimaryKey Table: TaxType/AuditType TaxType/AuditType- Text Table: Correspondence Received/Assigned EmployeeID=AutoNumber PrimaryKey EmployeeName-Text DateReceived-Date/Time TaxAuditType- Text FilesReceived- Number PhoneCalls-Number FaxRecd-Number AdditionalCorrep-Number Correspfrom TP/Petitions- Number Checks-Number ServCtr/Email/Phone-Number OtherEmployees-Number DayEndingInventory-Number Table: Correspondence Completed EmployeeID-AutoNumber- Primary Key EmployeeName-Text DateCompleted-DateTime TaxAuditType-Text InventoryPending-Number PendingBill-Number Billing-Number PendingAsmt-Number Assessment-Number CertifiedAsmt-Number Worked/Not Completed-Number My Relationships are One to many= Employees(1) to CorrespondenceReceived/Assigned(many) Related fields a EmployeeName One to many= Employee(1) to CorrespondenceCompleted(many) TaxType/AuditType= one to many on both CorrespondenceRecd and Correspondence Completed (not sure this is necessary because I use TaxType/audit type for combo box on forms) Thank you |
#8
|
|||
|
|||
Table design problem?
I need to better understand the relationship of the "sort" fields to the
CorrID entity (which I am using as the identity of a type of correspondence from your earlier list). What are these fields, how are they used, what type of data entry is needed for them, etc.? -- Ken Snell MS ACCESS MVP "Melissa" wrote in message ... Thanks for your help Ken. I have completed doing the suggestions you have offered. But have a couple questions on the tblEmpCorrWork. On the third table you suggested only to put the 4 fields into the table, then to build the form based on a qry of those fields. In this table, there would be more than 4 fields because each audit type can have up to 9 different sort fields. Would these fields go into a different table? I put these fields into the table 3 with the EmpID and CorrID being the foreign keys back to the parent. What is happening is it is associating each agent with a particular audit code when each agent may work many different types of audits. Thanks again for your help "Ken Snell [MVP]" wrote: I can think of a few ways to do this... each with its own complications and validation needs. But, let's try something "easy" in terms of setup and design at the moment. You'll want three tables: tblEmployees EmpID ( Primary Key ) EmpName (etc.) tblCorrespondenceTypes CorrID ( Primary Key ) CorrName tblEmpCorrWork EmpID ( composite primary key with CorrID and WorkDate ) CorrID ( composite primary key with EmpID and WorkDate ) WorkDate ( composite primary key with EmpID and CorrID ) WorkQuantity In the third table, EmpID and CorrID would be foreign keys back to the "parent" tables. Then, for data entry, I'd create a form. Set the form up as "Continuous Forms" view. Also set its Data Entry property to Yes (we'll use this form just for data entry right now). Make the form's Record Source be a query that is based on tblEmpCorrWork table. All four fields from that table should be in the query. In the form's FormHeader section, I'd put a combo box and textbox. The combo box would display all the employees so that the employee can select his/her name; the combo box would be bound to the EmpID field in the form's RecordSource. The textbox is where the work date goes -- can be automatically entered or can be typed in by employee, or both; the textbox would be bound to the WorkDate field in the form's RecordSource. In the form's Detail section, I'd put a combo box that displays all the correspondence types; this combo box would be bound to the CorrID field in the form's RecordSource. I'd then put a textbox to allow entry of the number of items for that specific correspondence type; this textbox would be bound to the WorkQuantity field in the form's RecordSource. This setup will not prohibit an employee from selecting the same correspondence type twice for the same date, but the composite primary key in the table will prevent the saving of any data where that is done for that record. But this should get you started..... -- Ken Snell MS ACCESS MVP "Melissa" wrote in message ... Ken, you get what I am saying and I so appreciate that. The employee would select the type of Audit he/she is receiving/ and worked for each day. They would complete their production each day. The Audit type can be received by 8 different sources; Files Rec'd, a phone call; email etc. The department has over 50 types of audit that the division currently works. (This is a billing/assessment dept). Here is one row of from the datasheet. It did not paste well but, it reads, AgentID 1 on 10/13/2005, (AuditType) Corp DQ Edit (number of files) 25 (same audit type) 5 (additional files recd via phone) then an addition 5 (referrals). We also want to capture their ending inventory by audit type. Same example would also apply to the Corresp completed table. AgentID DateReceived TaxAuditType FilesReceived Phone Calls FaxRecd AdditionalCorresp CorresfromTP/Petitions Checks ServCtr/Email/Phone AG Email Other Agents Activity Notices Bankruptcy Email TaxAppeal TPSReferrals DayEndingInventory 1 10/13/2005 CORP DQ EDITS 25 5 0 0 0 0 0 5 0 I am thinking in essense, that one employee can have multiple sales in one day and those sales can be different products. (This is just for a comparision) Thanks for any help you can give me. I am pretty good at Access and have built many databases for the dept, but this one has me stumped. "Ken Snell [MVP]" wrote: Melissa - It appears that you want the employee to enter each correspondence into a form, and to let the employee select the type of "audit" that is associated with each corresondence that is done? Is this essentially what you seek to do? Or is the "audit" type already associated with each correspondence type, and you just want to let the employee select the correspondence type, entering each one separately that the employee did? It would be helpful if you could show one or two rows of data from the EXCEL spreadsheet so that we can see the types of data you're capturing per row. What data will the employee specifically have "in hand" when he/she is ready to enter a correspondence that is done, and what will the employee need to select or enter additionally while entering that "in hand" data? -- Ken Snell MS ACCESS MVP "Melissa" wrote in message ... I am creating this database for work. This is to record each piece of correspondence a employee works (resolves) in a day. To try to simplify this, when you receive a bill and do not agree or have a different explaination, and respond to this bill, this is correspondence received. The correspondence we are working is just that. The department would like to track how many are received and completed by an employee each day. We are currently recording this information into a excel spreadsheet, but with 47 employees and the many types of audit types, the workbooks are becoming huge. I hope this simplifies things. "Melissa" wrote in message ... I am building an employee production db at work. The problem I am having is with the relationships and maybe my table design. I have one table for employee, Correspondence Received and Correspondence Completed. These employees receive and complete correspondence from taxpayers each day. There are 50 different audit types the department would like to track. With each audit type, there are 15 fields that may are may not apply. So, what I have currently design, the employee can have multiple entries into the database "in a day" if working on more than one audit type. What I need is for them to be able to logg any corresp rec'd or completed (that they did for that day) into one form. Putting it all together is confusing me. hope this makes sense. I have been trying to resolve this issue for months and cannot seem to come up with a solution. Any help would be truly appreciated. I have posted this question 10/20 but the solution hasn't helped. My tables are as follows: Table: Employees EmployeeName- Text Supervisor-Text EmployeeID-Autonumber= PrimaryKey Table: TaxType/AuditType TaxType/AuditType- Text Table: Correspondence Received/Assigned EmployeeID=AutoNumber PrimaryKey EmployeeName-Text DateReceived-Date/Time TaxAuditType- Text FilesReceived- Number PhoneCalls-Number FaxRecd-Number AdditionalCorrep-Number Correspfrom TP/Petitions- Number Checks-Number ServCtr/Email/Phone-Number OtherEmployees-Number DayEndingInventory-Number Table: Correspondence Completed EmployeeID-AutoNumber- Primary Key EmployeeName-Text DateCompleted-DateTime TaxAuditType-Text InventoryPending-Number PendingBill-Number Billing-Number PendingAsmt-Number Assessment-Number CertifiedAsmt-Number Worked/Not Completed-Number My Relationships are One to many= Employees(1) to CorrespondenceReceived/Assigned(many) Related fields a EmployeeName One to many= Employee(1) to CorrespondenceCompleted(many) TaxType/AuditType= one to many on both CorrespondenceRecd and Correspondence Completed (not sure this is necessary because I use TaxType/audit type for combo box on forms) Thank you |
#9
|
|||
|
|||
Table design problem?
I related the EmpID and CorrID to the parent tables. I then made a combo box
on the form for both of these fields. The Correspondence Name consists of all 50 different types of correspondence(audit types) received which will be chosen from a drop down list. Now grant it, no one employee will do 50 different audit types in a day (or work the correp from all 50) but, they will at the very minimun do 3 different types. And each of those types usually comes in through different avenues. A audit type would be for example; ST NR (sales tax no remit) or Corporate DQ (delinquency) etc. Each tax type can have different types of bills. That is why there is so many. In the third table EmpCorrWork I did not include the EmpName or CorrName since the EmpID and CorrId is already linked to these tables. In my form I placed the EmpName and Date in the FormHeader and the detail section I put the different sorts of how they would receive the files. Phone, FileRm etc. The form would not allow me to tab down to any of my fields within the detail section after I enter the date. Should I use a subform for the detail section? Hope I answered your question and thanks again for all your help. "Ken Snell [MVP]" wrote: I need to better understand the relationship of the "sort" fields to the CorrID entity (which I am using as the identity of a type of correspondence from your earlier list). What are these fields, how are they used, what type of data entry is needed for them, etc.? -- Ken Snell MS ACCESS MVP "Melissa" wrote in message ... Thanks for your help Ken. I have completed doing the suggestions you have offered. But have a couple questions on the tblEmpCorrWork. On the third table you suggested only to put the 4 fields into the table, then to build the form based on a qry of those fields. In this table, there would be more than 4 fields because each audit type can have up to 9 different sort fields. Would these fields go into a different table? I put these fields into the table 3 with the EmpID and CorrID being the foreign keys back to the parent. What is happening is it is associating each agent with a particular audit code when each agent may work many different types of audits. Thanks again for your help "Ken Snell [MVP]" wrote: I can think of a few ways to do this... each with its own complications and validation needs. But, let's try something "easy" in terms of setup and design at the moment. You'll want three tables: tblEmployees EmpID ( Primary Key ) EmpName (etc.) tblCorrespondenceTypes CorrID ( Primary Key ) CorrName tblEmpCorrWork EmpID ( composite primary key with CorrID and WorkDate ) CorrID ( composite primary key with EmpID and WorkDate ) WorkDate ( composite primary key with EmpID and CorrID ) WorkQuantity In the third table, EmpID and CorrID would be foreign keys back to the "parent" tables. Then, for data entry, I'd create a form. Set the form up as "Continuous Forms" view. Also set its Data Entry property to Yes (we'll use this form just for data entry right now). Make the form's Record Source be a query that is based on tblEmpCorrWork table. All four fields from that table should be in the query. In the form's FormHeader section, I'd put a combo box and textbox. The combo box would display all the employees so that the employee can select his/her name; the combo box would be bound to the EmpID field in the form's RecordSource. The textbox is where the work date goes -- can be automatically entered or can be typed in by employee, or both; the textbox would be bound to the WorkDate field in the form's RecordSource. In the form's Detail section, I'd put a combo box that displays all the correspondence types; this combo box would be bound to the CorrID field in the form's RecordSource. I'd then put a textbox to allow entry of the number of items for that specific correspondence type; this textbox would be bound to the WorkQuantity field in the form's RecordSource. This setup will not prohibit an employee from selecting the same correspondence type twice for the same date, but the composite primary key in the table will prevent the saving of any data where that is done for that record. But this should get you started..... -- Ken Snell MS ACCESS MVP "Melissa" wrote in message ... Ken, you get what I am saying and I so appreciate that. The employee would select the type of Audit he/she is receiving/ and worked for each day. They would complete their production each day. The Audit type can be received by 8 different sources; Files Rec'd, a phone call; email etc. The department has over 50 types of audit that the division currently works. (This is a billing/assessment dept). Here is one row of from the datasheet. It did not paste well but, it reads, AgentID 1 on 10/13/2005, (AuditType) Corp DQ Edit (number of files) 25 (same audit type) 5 (additional files recd via phone) then an addition 5 (referrals). We also want to capture their ending inventory by audit type. Same example would also apply to the Corresp completed table. AgentID DateReceived TaxAuditType FilesReceived Phone Calls FaxRecd AdditionalCorresp CorresfromTP/Petitions Checks ServCtr/Email/Phone AG Email Other Agents Activity Notices Bankruptcy Email TaxAppeal TPSReferrals DayEndingInventory 1 10/13/2005 CORP DQ EDITS 25 5 0 0 0 0 0 5 0 I am thinking in essense, that one employee can have multiple sales in one day and those sales can be different products. (This is just for a comparision) Thanks for any help you can give me. I am pretty good at Access and have built many databases for the dept, but this one has me stumped. "Ken Snell [MVP]" wrote: Melissa - It appears that you want the employee to enter each correspondence into a form, and to let the employee select the type of "audit" that is associated with each corresondence that is done? Is this essentially what you seek to do? Or is the "audit" type already associated with each correspondence type, and you just want to let the employee select the correspondence type, entering each one separately that the employee did? It would be helpful if you could show one or two rows of data from the EXCEL spreadsheet so that we can see the types of data you're capturing per row. What data will the employee specifically have "in hand" when he/she is ready to enter a correspondence that is done, and what will the employee need to select or enter additionally while entering that "in hand" data? -- Ken Snell MS ACCESS MVP "Melissa" wrote in message ... I am creating this database for work. This is to record each piece of correspondence a employee works (resolves) in a day. To try to simplify this, when you receive a bill and do not agree or have a different explaination, and respond to this bill, this is correspondence received. The correspondence we are working is just that. The department would like to track how many are received and completed by an employee each day. We are currently recording this information into a excel spreadsheet, but with 47 employees and the many types of audit types, the workbooks are becoming huge. I hope this simplifies things. "Melissa" wrote in message ... I am building an employee production db at work. The problem I am having is with the relationships and maybe my table design. I have one table for employee, Correspondence Received and Correspondence Completed. These employees receive and complete correspondence from taxpayers each day. There are 50 different audit types the department would like to track. With each audit type, there are 15 fields that may are may not apply. So, what I have currently design, the employee can have multiple entries into the database "in a day" if working on more than one audit type. What I need is for them to be able to logg any corresp rec'd or completed (that they did for that day) into one form. Putting it all together is confusing me. hope this makes sense. I have been trying to resolve this issue for months and cannot seem to come up with a solution. Any help would be truly appreciated. I have posted this question 10/20 but the solution hasn't helped. My tables are as follows: Table: Employees EmployeeName- Text Supervisor-Text EmployeeID-Autonumber= PrimaryKey Table: TaxType/AuditType TaxType/AuditType- Text Table: Correspondence Received/Assigned EmployeeID=AutoNumber PrimaryKey EmployeeName-Text DateReceived-Date/Time TaxAuditType- Text FilesReceived- Number PhoneCalls-Number FaxRecd-Number AdditionalCorrep-Number Correspfrom TP/Petitions- Number Checks-Number ServCtr/Email/Phone-Number OtherEmployees-Number DayEndingInventory-Number Table: Correspondence Completed EmployeeID-AutoNumber- Primary Key EmployeeName-Text DateCompleted-DateTime TaxAuditType-Text InventoryPending-Number PendingBill-Number Billing-Number PendingAsmt-Number Assessment-Number CertifiedAsmt-Number Worked/Not Completed-Number My Relationships are One to many= Employees(1) to CorrespondenceReceived/Assigned(many) Related fields a EmployeeName One to many= Employee(1) to CorrespondenceCompleted(many) TaxType/AuditType= one to many on both CorrespondenceRecd and Correspondence Completed (not sure this is necessary because I use TaxType/audit type for combo box on forms) Thank you |
#10
|
|||
|
|||
Table design problem?
My error -- I was designing this form in my head, and overlooked some
things. Yes, you'll want to use a subform instead of the Detail section for entering the CorrID and WorkQuantity values. This subform's RecordSource will be the EmpCorrWork table. The subform also should have textboxes bound to the EmpID and WorkDate fields, but set the Visibility property of these textboxes to No. Keep the combo box for EmpID selection and the textbox for WorkDate entry in the main form's ReportHeader section. The main form should have no RecordSource at all. The linking fields from main form to the subform will be these (use real names): LinkChildFields: EmpID,WorkDate LinkMasterFields: EmpIDComboBox, WorkDateTextBox This should fix the data entry problem you were having. I am not understanding how CorrID (which you're using as the type of audit) is related to the various sort fields. Are the sort fields the "type of bills" for each audit type? I need you to fully spell out the entities involved in your data model. It appears that you may need an additional field in the EmpCorrWork table, or that you may need another table. I just cannot tell yet without a better picture of your data model. -- Ken Snell MS ACCESS MVP "Melissa" wrote in message ... I related the EmpID and CorrID to the parent tables. I then made a combo box on the form for both of these fields. The Correspondence Name consists of all 50 different types of correspondence(audit types) received which will be chosen from a drop down list. Now grant it, no one employee will do 50 different audit types in a day (or work the correp from all 50) but, they will at the very minimun do 3 different types. And each of those types usually comes in through different avenues. A audit type would be for example; ST NR (sales tax no remit) or Corporate DQ (delinquency) etc. Each tax type can have different types of bills. That is why there is so many. In the third table EmpCorrWork I did not include the EmpName or CorrName since the EmpID and CorrId is already linked to these tables. In my form I placed the EmpName and Date in the FormHeader and the detail section I put the different sorts of how they would receive the files. Phone, FileRm etc. The form would not allow me to tab down to any of my fields within the detail section after I enter the date. Should I use a subform for the detail section? Hope I answered your question and thanks again for all your help. "Ken Snell [MVP]" wrote: I need to better understand the relationship of the "sort" fields to the CorrID entity (which I am using as the identity of a type of correspondence from your earlier list). What are these fields, how are they used, what type of data entry is needed for them, etc.? -- Ken Snell MS ACCESS MVP "Melissa" wrote in message ... Thanks for your help Ken. I have completed doing the suggestions you have offered. But have a couple questions on the tblEmpCorrWork. On the third table you suggested only to put the 4 fields into the table, then to build the form based on a qry of those fields. In this table, there would be more than 4 fields because each audit type can have up to 9 different sort fields. Would these fields go into a different table? I put these fields into the table 3 with the EmpID and CorrID being the foreign keys back to the parent. What is happening is it is associating each agent with a particular audit code when each agent may work many different types of audits. Thanks again for your help snipped |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Access combo box-show name, not ID, in table? | write on | New Users | 30 | April 30th, 2005 09:11 PM |
Table Design | A. Williams | Database Design | 3 | April 29th, 2005 07:02 PM |
Seeking some expert advice. | HD87glide | Using Forms | 14 | March 23rd, 2005 10:11 PM |
Manual line break spaces on TOC or Table of tables | Eric | Page Layout | 9 | October 29th, 2004 04:42 PM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |