Megin
asked on
Repeating items in Access Report
I am working on an Access report and have something happening in it that I don't know how to fix, and I am not sure why it is happening.
The report lists subtasks, employees, and activities done under particular subtasks.
In the version I am attaching, you can look at rpt_Weekly. For the date, put in 4/6/12. Scroll down to Jacob Ladder, Aunt Jamima and Susan Summers. In their tables, they have the item "Went to Neverland" listed once. This thing was done by each of them one time. However, in the report, it lists the activity three times for each person. I don't know what to do.
Thank you for all the help! I am sure some of you looking at this have seen me post a lot of questions in the last couple of weeks. I am very grateful for all the assistance you all have been giving me.
DatabaseDemo.accdb
The report lists subtasks, employees, and activities done under particular subtasks.
In the version I am attaching, you can look at rpt_Weekly. For the date, put in 4/6/12. Scroll down to Jacob Ladder, Aunt Jamima and Susan Summers. In their tables, they have the item "Went to Neverland" listed once. This thing was done by each of them one time. However, in the report, it lists the activity three times for each person. I don't know what to do.
Thank you for all the help! I am sure some of you looking at this have seen me post a lot of questions in the last couple of weeks. I am very grateful for all the assistance you all have been giving me.
DatabaseDemo.accdb
Are you sure that the table relationships are correct?
How are you relating the employee to anything?
What about the stgroups table?
How are you relating the employee to anything?
What about the stgroups table?
ASKER
I thought it might be getting too complicated. Let me give you a rundown on what everything is.
tbl_Employees: Just a list of all employees who work for the company
tbl_Activities: List of different activities that anyone can do as part of their job. This is tasks and meetings. Tasks and meetings must be differentiated, however that is not done in this table.
tbl_actType: This table just contains the two different types of activites: tasks and meetings
tbl_TaskOrder: This is the way be do billing. There are task orders, and ever task order has a number of subtask orders. This table only has the task order no. and the title (along with the id, of course)
tbl_SubTaskOrders: I begin some combining in this table. This has the subtask orders with the id numbers from tbl_TaskOrder to connect each subtask order to its task order.
tbl_stgroups: This is the table that links employees to particular subtask orders. A subtask order can be just one person or a team. This table connects every employee with a subtask order. stgID is the number that ids that connection.
tbl_ActCmb1: This table is where all the action is. My reports are based on this and information from the forms mostly dump information here. It combines the task order ID, subtask order ID, actID, the actTypeID and the date.
The report I pull up is a weekly report that must have the Task Order, followed by the Subtask Order, with a list of those who work on that subtask order, followed by the activities performed by that subtask order, differentiated into task and meeting.
I have the user work with two different forms (this could change, I don't think it is the best way to do things). The frm_Weekly_Act_2 and frm_Weekly_mtg.
When I first started working on this, I couldn't get the subtask order employee names to list together correctly along with the tasks. I needed them to show up like they are now. Then I need the activities to show up in lists the way they are, without the duplicates. I tried different methods and most left me with either the employees names repeating per every activity or the activities showing up wrong. I was given the advice to add them as subforms, which worked just fine until I got to this weird problem with the new duplicates.
The query everything runs from is the Qry_Weekly_Report. It has all the information that I need for the report, with the exception of the employee names. Those are added with the subform. I suppose I probably don't need the actName in that query either.
I need to be able to pull up weekly reports and monthly reports. I also need to be able to pull up reports based on subtask orders and based on task orders alone.
I think that is it. My intent was to create tables that were normalized properly. Design is not my specialty, to say the least.
I can't thank you enough for all the help here.
tbl_Employees: Just a list of all employees who work for the company
tbl_Activities: List of different activities that anyone can do as part of their job. This is tasks and meetings. Tasks and meetings must be differentiated, however that is not done in this table.
tbl_actType: This table just contains the two different types of activites: tasks and meetings
tbl_TaskOrder: This is the way be do billing. There are task orders, and ever task order has a number of subtask orders. This table only has the task order no. and the title (along with the id, of course)
tbl_SubTaskOrders: I begin some combining in this table. This has the subtask orders with the id numbers from tbl_TaskOrder to connect each subtask order to its task order.
tbl_stgroups: This is the table that links employees to particular subtask orders. A subtask order can be just one person or a team. This table connects every employee with a subtask order. stgID is the number that ids that connection.
tbl_ActCmb1: This table is where all the action is. My reports are based on this and information from the forms mostly dump information here. It combines the task order ID, subtask order ID, actID, the actTypeID and the date.
The report I pull up is a weekly report that must have the Task Order, followed by the Subtask Order, with a list of those who work on that subtask order, followed by the activities performed by that subtask order, differentiated into task and meeting.
I have the user work with two different forms (this could change, I don't think it is the best way to do things). The frm_Weekly_Act_2 and frm_Weekly_mtg.
When I first started working on this, I couldn't get the subtask order employee names to list together correctly along with the tasks. I needed them to show up like they are now. Then I need the activities to show up in lists the way they are, without the duplicates. I tried different methods and most left me with either the employees names repeating per every activity or the activities showing up wrong. I was given the advice to add them as subforms, which worked just fine until I got to this weird problem with the new duplicates.
The query everything runs from is the Qry_Weekly_Report. It has all the information that I need for the report, with the exception of the employee names. Those are added with the subform. I suppose I probably don't need the actName in that query either.
I need to be able to pull up weekly reports and monthly reports. I also need to be able to pull up reports based on subtask orders and based on task orders alone.
I think that is it. My intent was to create tables that were normalized properly. Design is not my specialty, to say the least.
I can't thank you enough for all the help here.
You are never quite stating what this report is seeking to display, or it's purpose...?
Perhaps it would be easier if you posted a graphical representation of the *exact* output you are looking for based on some subset of the data in your db......
Perhaps it would be easier if you posted a graphical representation of the *exact* output you are looking for based on some subset of the data in your db......
ASKER
Okay.
I am attaching a sample of the report that this database needs to create. It is the excel spreadsheet that I am basing my work on. The final result can vary slightly from the one I am posting here, but only slightly.
The purpose is to submit an invoice to the company we consult for the work we do during the week. We also need to create a monthly report.
Please let me know if this helps.
Sample-of-Report.xlsx
I am attaching a sample of the report that this database needs to create. It is the excel spreadsheet that I am basing my work on. The final result can vary slightly from the one I am posting here, but only slightly.
The purpose is to submit an invoice to the company we consult for the work we do during the week. We also need to create a monthly report.
Please let me know if this helps.
Sample-of-Report.xlsx
Sorry, this is still not clear...
I'll *try* to work up a simple sample of how something like this might be done...
I'll *try* to work up a simple sample of how something like this might be done...
ASKER
I appretiate you helping me. I am not sure how to make this more clear. But, I will try again.
I work for a subconsultant. We bill to our client. Our contract is defined in terms of Task Orders and Sub Task Orders. Every Task Order is divided into Sub Task Orders. Every staff member is assigned to one or more Sub Task Order.
The report we send to the client every week and again once a month, is a summary of all activities that were done during that week. It has to list the task order, the sub task order, the staff and the activities.
Does this help?
I work for a subconsultant. We bill to our client. Our contract is defined in terms of Task Orders and Sub Task Orders. Every Task Order is divided into Sub Task Orders. Every staff member is assigned to one or more Sub Task Order.
The report we send to the client every week and again once a month, is a summary of all activities that were done during that week. It has to list the task order, the sub task order, the staff and the activities.
Does this help?
Again, I think, (and this is IMHO), this report seeks to show too much data...
Remember, I know very little about your business rules here:
Can one employee have many tasks?
Can one Task have many Employees?
Are employees assigned to a task, or a subtask?
What is the "310"
How does the "Permitting Manager" fit into all of this.
Is the underlying query even outputting the data correctly to give you what you need.
...etc
in other words, there is just too much here that needs to be considered...
Again, I have never seen a Grouped Report with subreports in the various Header sections...?)
(certain info in the group will not be available when the header displays...)
I have attached a basic sample of how I might do something like this.
You can comment on this if you like, or integrate bits of it in your design.
In the end you may have to step back from this and *first* do some research on Report design.
(Sorting, Grouping, Sub reports, Report sections and properties, ...etc)
JeffCoachman
Database110.mdb
Remember, I know very little about your business rules here:
Can one employee have many tasks?
Can one Task have many Employees?
Are employees assigned to a task, or a subtask?
What is the "310"
How does the "Permitting Manager" fit into all of this.
Is the underlying query even outputting the data correctly to give you what you need.
...etc
in other words, there is just too much here that needs to be considered...
Again, I have never seen a Grouped Report with subreports in the various Header sections...?)
(certain info in the group will not be available when the header displays...)
I have attached a basic sample of how I might do something like this.
You can comment on this if you like, or integrate bits of it in your design.
In the end you may have to step back from this and *first* do some research on Report design.
(Sorting, Grouping, Sub reports, Report sections and properties, ...etc)
JeffCoachman
Database110.mdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I found the answer elsewhere.
I honestly am confused as to why your report is a complex as it is...
(Sub reports inside Header sections of a grouped report???)
Are you quite sure it is designed in a way to give you what you want?
Are you quite sure the query running the report outputs the raw data in a way that the report can display this in the way you want...?
(You have a lot of left and right joins there...)
What is the ultimate goal of this report?