Hi everyone,
Attached is a sample file of data for what I am trying to do. I have 3 tables, one is a pivot table that contains projects and tasks and the other two standard tables with one column containing text (tasks and staff respectively) and the other column contain numbers that I am using as an index. What I am to do is generate a table that shows the text fields from the second two tables based on the corresponding indices. Several staff members will be assigned to each task and staff will be assigned to multiple tasks. Also, the entire pivot table must be included in the new table. It seems like a many to many relationships, but I cannot get it to work.
Any suggestions will be appreciated,
Chris R.
Hi Chris,
Please try uploading the file again as it's not attached. You need to click the yellow 'Start Upload' button after selecting your file and then wait until it has completed uploading before submitting.
Thanks,
Mynda
Here it is.
Thanks
Hi Chris,
Thanks for the file. First, you can't use a PivotTable as the source data for a table in a Power Pivot model. You need to use that PivotTable's source data instead.
I don't see the relevance of the second table having an index number assigned to a task. Please create some dummy data, or anonymise your own data, and upload the Excel file. Please also show a mock-up of how this final report/PivotTable should look.
Mynda
Hy Mynda,
Attached is the new worksheet. I am unsure of how the query/new table would look, but this is what I need:
Column F contains the number of staff required for the task, some only require a single staff person and others require multiple staff people. Additionally, any staff person can be assigned to any number of tasks.
I don't think adding an additional column for each assigned staff person would create a super-wide table. So I guess I need to assign all of the staff for each task first, then find a concise way to view or report the whole thing. I was initially using an index because that is how I have done things in Access to build relationships between different tables.
I hope that clarifies what I am trying to do.
Thanks in advance,
Chris
Thanks for the file, Chris. If you can't show me what you want the end result (report) to look like, then I can't do much to help, sorry. From what you've described, I can't build a picture in my head of what you're trying to do.
It might help if you just choose 10 tasks and then create a mock-up of how you want the end result to appear. Don't worry about the query or relationships at this stage. That's what I can help with. I just need to see what you're starting with, which is what you've provided in the file, and what you want in the end.
Mynda
Hi Mynda,
I am attaching a new file that shows an example of what I hope the report(s) will look like along with supporting data. I hope this helps.
Thanks,
Chris
