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
|
|||
|
|||
Report that pulls data from 6 tables
Using WIN XP with Access 2003.
I have a table that lists the basics for an event/issue. Then I have 5 more tables that have a record ID to match up to the main table and each listing in these 5 sub tables can have multiple listings that are tied to the main record. I have created several queries trying to get this to work. I want the report to pull all of the secondary IDs (from the 5 sub tables) that are tied to the main Record ID fromt he main table. Everything I have tried is not working. I am at a loss as to what to try next. Anyone with some advise or direction would be very appreciated. David |
#2
|
|||
|
|||
Report that pulls data from 6 tables
David,
From what little you've told us, it's a bit difficult to pin-point where the problem might be. To start, carefully examine the "relationship" view to ensure that you have the correct relationships you desire between your "main" table and the records within the five secondary tables. If you don't find a problem there, you might want to post the SQL view of your query so we can see better what it is that you're attempting to display in your report. Another thought is to be sure you have bound controls for all the fields you want to report. For example, if you reference fields in the code sheet for the report that are not bound in the report design, you'll get errors when you try to preview or otherwise print the report. Bill "DStrong" wrote in message ... Using WIN XP with Access 2003. I have a table that lists the basics for an event/issue. Then I have 5 more tables that have a record ID to match up to the main table and each listing in these 5 sub tables can have multiple listings that are tied to the main record. I have created several queries trying to get this to work. I want the report to pull all of the secondary IDs (from the 5 sub tables) that are tied to the main Record ID fromt he main table. Everything I have tried is not working. I am at a loss as to what to try next. Anyone with some advise or direction would be very appreciated. David |
#3
|
|||
|
|||
Report that pulls data from 6 tables
Thanks Bill-
I have the relationships setup just fine, I am pretty sure since the few forms I have seem to work just fine. I have a form that allows a user to enter or edit records and they all pull from the 6 tables. I am not the best with reports so my first attempt was to pull the data from each table as I did with the form mentioned above. That did not produce what I am looking to accomplish. So a co-worker suggested that I use a query to pull the report data from. The query that I created pulls 306 rows of data and I have 2 main records. Here is my SQL View, this becomes all greek to me he SELECT [RCA Records].[Record ID], [Sequece of Events].[SE ID], [Sequece of Events].[Event Date], [Sequece of Events].[Event Time], [Sequece of Events].Event, [Corrective Action Plan].[CAP ID], [Corrective Action Plan].Action, [Corrective Action Plan].[Action Owner], [Corrective Action Plan].[Due Date], [Corrective Action Plan].[Completion Date], [Corrective Action Taken].[CA Id], [Corrective Action Taken].[Action Taken], [Corrective Action Taken].[Action Owner], [Corrective Action Taken].CompletedDate, [Corrective Action Taken].Comments, [Org Entites Impacted].[OET ID], [Org Entites Impacted].[Org Name], [Org Entites Impacted].[Extent of Impact], [Inital Possible Contributors].[IPB ID], [Inital Possible Contributors].Factors FROM (((([RCA Records] INNER JOIN [Sequece of Events] ON [RCA Records].[Record ID] = [Sequece of Events].[REC ID]) INNER JOIN [Corrective Action Plan] ON [RCA Records].[Record ID] = [Corrective Action Plan].[REC ID]) INNER JOIN [Corrective Action Taken] ON [RCA Records].[Record ID] = [Corrective Action Taken].[Rec ID]) INNER JOIN [Inital Possible Contributors] ON [RCA Records].[Record ID] = [Inital Possible Contributors].[REC ID]) INNER JOIN [Org Entites Impacted] ON [RCA Records].[Record ID] = [Org Entites Impacted].[REC ID]; "Bill" wrote: David, From what little you've told us, it's a bit difficult to pin-point where the problem might be. To start, carefully examine the "relationship" view to ensure that you have the correct relationships you desire between your "main" table and the records within the five secondary tables. If you don't find a problem there, you might want to post the SQL view of your query so we can see better what it is that you're attempting to display in your report. Another thought is to be sure you have bound controls for all the fields you want to report. For example, if you reference fields in the code sheet for the report that are not bound in the report design, you'll get errors when you try to preview or otherwise print the report. Bill "DStrong" wrote in message ... Using WIN XP with Access 2003. I have a table that lists the basics for an event/issue. Then I have 5 more tables that have a record ID to match up to the main table and each listing in these 5 sub tables can have multiple listings that are tied to the main record. I have created several queries trying to get this to work. I want the report to pull all of the secondary IDs (from the 5 sub tables) that are tied to the main Record ID fromt he main table. Everything I have tried is not working. I am at a loss as to what to try next. Anyone with some advise or direction would be very appreciated. David |
#4
|
|||
|
|||
Report that pulls data from 6 tables
You are using SE ID, CAP ID, CA Id , OET ID, IPB ID in the SELECT part but
using REC ID in the FROM part for all tables. What are the actual field names? -- KARL DEWEY Build a little - Test a little "DStrong" wrote: Thanks Bill- I have the relationships setup just fine, I am pretty sure since the few forms I have seem to work just fine. I have a form that allows a user to enter or edit records and they all pull from the 6 tables. I am not the best with reports so my first attempt was to pull the data from each table as I did with the form mentioned above. That did not produce what I am looking to accomplish. So a co-worker suggested that I use a query to pull the report data from. The query that I created pulls 306 rows of data and I have 2 main records. Here is my SQL View, this becomes all greek to me he SELECT [RCA Records].[Record ID], [Sequece of Events].[SE ID], [Sequece of Events].[Event Date], [Sequece of Events].[Event Time], [Sequece of Events].Event, [Corrective Action Plan].[CAP ID], [Corrective Action Plan].Action, [Corrective Action Plan].[Action Owner], [Corrective Action Plan].[Due Date], [Corrective Action Plan].[Completion Date], [Corrective Action Taken].[CA Id], [Corrective Action Taken].[Action Taken], [Corrective Action Taken].[Action Owner], [Corrective Action Taken].CompletedDate, [Corrective Action Taken].Comments, [Org Entites Impacted].[OET ID], [Org Entites Impacted].[Org Name], [Org Entites Impacted].[Extent of Impact], [Inital Possible Contributors].[IPB ID], [Inital Possible Contributors].Factors FROM (((([RCA Records] INNER JOIN [Sequece of Events] ON [RCA Records].[Record ID] = [Sequece of Events].[REC ID]) INNER JOIN [Corrective Action Plan] ON [RCA Records].[Record ID] = [Corrective Action Plan].[REC ID]) INNER JOIN [Corrective Action Taken] ON [RCA Records].[Record ID] = [Corrective Action Taken].[Rec ID]) INNER JOIN [Inital Possible Contributors] ON [RCA Records].[Record ID] = [Inital Possible Contributors].[REC ID]) INNER JOIN [Org Entites Impacted] ON [RCA Records].[Record ID] = [Org Entites Impacted].[REC ID]; "Bill" wrote: David, From what little you've told us, it's a bit difficult to pin-point where the problem might be. To start, carefully examine the "relationship" view to ensure that you have the correct relationships you desire between your "main" table and the records within the five secondary tables. If you don't find a problem there, you might want to post the SQL view of your query so we can see better what it is that you're attempting to display in your report. Another thought is to be sure you have bound controls for all the fields you want to report. For example, if you reference fields in the code sheet for the report that are not bound in the report design, you'll get errors when you try to preview or otherwise print the report. Bill "DStrong" wrote in message ... Using WIN XP with Access 2003. I have a table that lists the basics for an event/issue. Then I have 5 more tables that have a record ID to match up to the main table and each listing in these 5 sub tables can have multiple listings that are tied to the main record. I have created several queries trying to get this to work. I want the report to pull all of the secondary IDs (from the 5 sub tables) that are tied to the main Record ID fromt he main table. Everything I have tried is not working. I am at a loss as to what to try next. Anyone with some advise or direction would be very appreciated. David |
#5
|
|||
|
|||
Report that pulls data from 6 tables
All the "ID" on the row keys for each of the subtables. These are the field
names. Each row also contains a REC ID that is linked to the main table RECORD ID. -- Let me macronize that for you! "KARL DEWEY" wrote: You are using SE ID, CAP ID, CA Id , OET ID, IPB ID in the SELECT part but using REC ID in the FROM part for all tables. What are the actual field names? -- KARL DEWEY Build a little - Test a little "DStrong" wrote: Thanks Bill- I have the relationships setup just fine, I am pretty sure since the few forms I have seem to work just fine. I have a form that allows a user to enter or edit records and they all pull from the 6 tables. I am not the best with reports so my first attempt was to pull the data from each table as I did with the form mentioned above. That did not produce what I am looking to accomplish. So a co-worker suggested that I use a query to pull the report data from. The query that I created pulls 306 rows of data and I have 2 main records. Here is my SQL View, this becomes all greek to me he SELECT [RCA Records].[Record ID], [Sequece of Events].[SE ID], [Sequece of Events].[Event Date], [Sequece of Events].[Event Time], [Sequece of Events].Event, [Corrective Action Plan].[CAP ID], [Corrective Action Plan].Action, [Corrective Action Plan].[Action Owner], [Corrective Action Plan].[Due Date], [Corrective Action Plan].[Completion Date], [Corrective Action Taken].[CA Id], [Corrective Action Taken].[Action Taken], [Corrective Action Taken].[Action Owner], [Corrective Action Taken].CompletedDate, [Corrective Action Taken].Comments, [Org Entites Impacted].[OET ID], [Org Entites Impacted].[Org Name], [Org Entites Impacted].[Extent of Impact], [Inital Possible Contributors].[IPB ID], [Inital Possible Contributors].Factors FROM (((([RCA Records] INNER JOIN [Sequece of Events] ON [RCA Records].[Record ID] = [Sequece of Events].[REC ID]) INNER JOIN [Corrective Action Plan] ON [RCA Records].[Record ID] = [Corrective Action Plan].[REC ID]) INNER JOIN [Corrective Action Taken] ON [RCA Records].[Record ID] = [Corrective Action Taken].[Rec ID]) INNER JOIN [Inital Possible Contributors] ON [RCA Records].[Record ID] = [Inital Possible Contributors].[REC ID]) INNER JOIN [Org Entites Impacted] ON [RCA Records].[Record ID] = [Org Entites Impacted].[REC ID]; "Bill" wrote: David, From what little you've told us, it's a bit difficult to pin-point where the problem might be. To start, carefully examine the "relationship" view to ensure that you have the correct relationships you desire between your "main" table and the records within the five secondary tables. If you don't find a problem there, you might want to post the SQL view of your query so we can see better what it is that you're attempting to display in your report. Another thought is to be sure you have bound controls for all the fields you want to report. For example, if you reference fields in the code sheet for the report that are not bound in the report design, you'll get errors when you try to preview or otherwise print the report. Bill "DStrong" wrote in message ... Using WIN XP with Access 2003. I have a table that lists the basics for an event/issue. Then I have 5 more tables that have a record ID to match up to the main table and each listing in these 5 sub tables can have multiple listings that are tied to the main record. I have created several queries trying to get this to work. I want the report to pull all of the secondary IDs (from the 5 sub tables) that are tied to the main Record ID fromt he main table. Everything I have tried is not working. I am at a loss as to what to try next. Anyone with some advise or direction would be very appreciated. David |
#6
|
|||
|
|||
Report that pulls data from 6 tables
Change all of your [RCA Records] INNER JOIN to [RCA Records] LEFT JOIN
-- KARL DEWEY Build a little - Test a little "DStrong" wrote: All the "ID" on the row keys for each of the subtables. These are the field names. Each row also contains a REC ID that is linked to the main table RECORD ID. -- Let me macronize that for you! "KARL DEWEY" wrote: You are using SE ID, CAP ID, CA Id , OET ID, IPB ID in the SELECT part but using REC ID in the FROM part for all tables. What are the actual field names? -- KARL DEWEY Build a little - Test a little "DStrong" wrote: Thanks Bill- I have the relationships setup just fine, I am pretty sure since the few forms I have seem to work just fine. I have a form that allows a user to enter or edit records and they all pull from the 6 tables. I am not the best with reports so my first attempt was to pull the data from each table as I did with the form mentioned above. That did not produce what I am looking to accomplish. So a co-worker suggested that I use a query to pull the report data from. The query that I created pulls 306 rows of data and I have 2 main records. Here is my SQL View, this becomes all greek to me he SELECT [RCA Records].[Record ID], [Sequece of Events].[SE ID], [Sequece of Events].[Event Date], [Sequece of Events].[Event Time], [Sequece of Events].Event, [Corrective Action Plan].[CAP ID], [Corrective Action Plan].Action, [Corrective Action Plan].[Action Owner], [Corrective Action Plan].[Due Date], [Corrective Action Plan].[Completion Date], [Corrective Action Taken].[CA Id], [Corrective Action Taken].[Action Taken], [Corrective Action Taken].[Action Owner], [Corrective Action Taken].CompletedDate, [Corrective Action Taken].Comments, [Org Entites Impacted].[OET ID], [Org Entites Impacted].[Org Name], [Org Entites Impacted].[Extent of Impact], [Inital Possible Contributors].[IPB ID], [Inital Possible Contributors].Factors FROM (((([RCA Records] INNER JOIN [Sequece of Events] ON [RCA Records].[Record ID] = [Sequece of Events].[REC ID]) INNER JOIN [Corrective Action Plan] ON [RCA Records].[Record ID] = [Corrective Action Plan].[REC ID]) INNER JOIN [Corrective Action Taken] ON [RCA Records].[Record ID] = [Corrective Action Taken].[Rec ID]) INNER JOIN [Inital Possible Contributors] ON [RCA Records].[Record ID] = [Inital Possible Contributors].[REC ID]) INNER JOIN [Org Entites Impacted] ON [RCA Records].[Record ID] = [Org Entites Impacted].[REC ID]; "Bill" wrote: David, From what little you've told us, it's a bit difficult to pin-point where the problem might be. To start, carefully examine the "relationship" view to ensure that you have the correct relationships you desire between your "main" table and the records within the five secondary tables. If you don't find a problem there, you might want to post the SQL view of your query so we can see better what it is that you're attempting to display in your report. Another thought is to be sure you have bound controls for all the fields you want to report. For example, if you reference fields in the code sheet for the report that are not bound in the report design, you'll get errors when you try to preview or otherwise print the report. Bill "DStrong" wrote in message ... Using WIN XP with Access 2003. I have a table that lists the basics for an event/issue. Then I have 5 more tables that have a record ID to match up to the main table and each listing in these 5 sub tables can have multiple listings that are tied to the main record. I have created several queries trying to get this to work. I want the report to pull all of the secondary IDs (from the 5 sub tables) that are tied to the main Record ID fromt he main table. Everything I have tried is not working. I am at a loss as to what to try next. Anyone with some advise or direction would be very appreciated. David |
#7
|
|||
|
|||
Report that pulls data from 6 tables
David, did Karl's suggestion solve your problem? Also, when
you run the query in design mode, do you see the proper results? Bill "KARL DEWEY" wrote in message ... Change all of your [RCA Records] INNER JOIN to [RCA Records] LEFT JOIN -- KARL DEWEY Build a little - Test a little "DStrong" wrote: All the "ID" on the row keys for each of the subtables. These are the field names. Each row also contains a REC ID that is linked to the main table RECORD ID. -- Let me macronize that for you! "KARL DEWEY" wrote: You are using SE ID, CAP ID, CA Id , OET ID, IPB ID in the SELECT part but using REC ID in the FROM part for all tables. What are the actual field names? -- KARL DEWEY Build a little - Test a little "DStrong" wrote: Thanks Bill- I have the relationships setup just fine, I am pretty sure since the few forms I have seem to work just fine. I have a form that allows a user to enter or edit records and they all pull from the 6 tables. I am not the best with reports so my first attempt was to pull the data from each table as I did with the form mentioned above. That did not produce what I am looking to accomplish. So a co-worker suggested that I use a query to pull the report data from. The query that I created pulls 306 rows of data and I have 2 main records. Here is my SQL View, this becomes all greek to me he SELECT [RCA Records].[Record ID], [Sequece of Events].[SE ID], [Sequece of Events].[Event Date], [Sequece of Events].[Event Time], [Sequece of Events].Event, [Corrective Action Plan].[CAP ID], [Corrective Action Plan].Action, [Corrective Action Plan].[Action Owner], [Corrective Action Plan].[Due Date], [Corrective Action Plan].[Completion Date], [Corrective Action Taken].[CA Id], [Corrective Action Taken].[Action Taken], [Corrective Action Taken].[Action Owner], [Corrective Action Taken].CompletedDate, [Corrective Action Taken].Comments, [Org Entites Impacted].[OET ID], [Org Entites Impacted].[Org Name], [Org Entites Impacted].[Extent of Impact], [Inital Possible Contributors].[IPB ID], [Inital Possible Contributors].Factors FROM (((([RCA Records] INNER JOIN [Sequece of Events] ON [RCA Records].[Record ID] = [Sequece of Events].[REC ID]) INNER JOIN [Corrective Action Plan] ON [RCA Records].[Record ID] = [Corrective Action Plan].[REC ID]) INNER JOIN [Corrective Action Taken] ON [RCA Records].[Record ID] = [Corrective Action Taken].[Rec ID]) INNER JOIN [Inital Possible Contributors] ON [RCA Records].[Record ID] = [Inital Possible Contributors].[REC ID]) INNER JOIN [Org Entites Impacted] ON [RCA Records].[Record ID] = [Org Entites Impacted].[REC ID]; "Bill" wrote: David, From what little you've told us, it's a bit difficult to pin-point where the problem might be. To start, carefully examine the "relationship" view to ensure that you have the correct relationships you desire between your "main" table and the records within the five secondary tables. If you don't find a problem there, you might want to post the SQL view of your query so we can see better what it is that you're attempting to display in your report. Another thought is to be sure you have bound controls for all the fields you want to report. For example, if you reference fields in the code sheet for the report that are not bound in the report design, you'll get errors when you try to preview or otherwise print the report. Bill "DStrong" wrote in message ... Using WIN XP with Access 2003. I have a table that lists the basics for an event/issue. Then I have 5 more tables that have a record ID to match up to the main table and each listing in these 5 sub tables can have multiple listings that are tied to the main record. I have created several queries trying to get this to work. I want the report to pull all of the secondary IDs (from the 5 sub tables) that are tied to the main Record ID fromt he main table. Everything I have tried is not working. I am at a loss as to what to try next. Anyone with some advise or direction would be very appreciated. David |
#8
|
|||
|
|||
Report that pulls data from 6 tables
No and No. I am not the best with Access so this advise did not offer much
for me. I get the results many times over again. I should not get the 300+ rows of data that the query spits out. I am at a loss with this. -- Let me macronize that for you! "Bill" wrote: David, did Karl's suggestion solve your problem? Also, when you run the query in design mode, do you see the proper results? Bill "KARL DEWEY" wrote in message ... Change all of your [RCA Records] INNER JOIN to [RCA Records] LEFT JOIN -- KARL DEWEY Build a little - Test a little "DStrong" wrote: All the "ID" on the row keys for each of the subtables. These are the field names. Each row also contains a REC ID that is linked to the main table RECORD ID. -- Let me macronize that for you! "KARL DEWEY" wrote: You are using SE ID, CAP ID, CA Id , OET ID, IPB ID in the SELECT part but using REC ID in the FROM part for all tables. What are the actual field names? -- KARL DEWEY Build a little - Test a little "DStrong" wrote: Thanks Bill- I have the relationships setup just fine, I am pretty sure since the few forms I have seem to work just fine. I have a form that allows a user to enter or edit records and they all pull from the 6 tables. I am not the best with reports so my first attempt was to pull the data from each table as I did with the form mentioned above. That did not produce what I am looking to accomplish. So a co-worker suggested that I use a query to pull the report data from. The query that I created pulls 306 rows of data and I have 2 main records. Here is my SQL View, this becomes all greek to me he SELECT [RCA Records].[Record ID], [Sequece of Events].[SE ID], [Sequece of Events].[Event Date], [Sequece of Events].[Event Time], [Sequece of Events].Event, [Corrective Action Plan].[CAP ID], [Corrective Action Plan].Action, [Corrective Action Plan].[Action Owner], [Corrective Action Plan].[Due Date], [Corrective Action Plan].[Completion Date], [Corrective Action Taken].[CA Id], [Corrective Action Taken].[Action Taken], [Corrective Action Taken].[Action Owner], [Corrective Action Taken].CompletedDate, [Corrective Action Taken].Comments, [Org Entites Impacted].[OET ID], [Org Entites Impacted].[Org Name], [Org Entites Impacted].[Extent of Impact], [Inital Possible Contributors].[IPB ID], [Inital Possible Contributors].Factors FROM (((([RCA Records] INNER JOIN [Sequece of Events] ON [RCA Records].[Record ID] = [Sequece of Events].[REC ID]) INNER JOIN [Corrective Action Plan] ON [RCA Records].[Record ID] = [Corrective Action Plan].[REC ID]) INNER JOIN [Corrective Action Taken] ON [RCA Records].[Record ID] = [Corrective Action Taken].[Rec ID]) INNER JOIN [Inital Possible Contributors] ON [RCA Records].[Record ID] = [Inital Possible Contributors].[REC ID]) INNER JOIN [Org Entites Impacted] ON [RCA Records].[Record ID] = [Org Entites Impacted].[REC ID]; "Bill" wrote: David, From what little you've told us, it's a bit difficult to pin-point where the problem might be. To start, carefully examine the "relationship" view to ensure that you have the correct relationships you desire between your "main" table and the records within the five secondary tables. If you don't find a problem there, you might want to post the SQL view of your query so we can see better what it is that you're attempting to display in your report. Another thought is to be sure you have bound controls for all the fields you want to report. For example, if you reference fields in the code sheet for the report that are not bound in the report design, you'll get errors when you try to preview or otherwise print the report. Bill "DStrong" wrote in message ... Using WIN XP with Access 2003. I have a table that lists the basics for an event/issue. Then I have 5 more tables that have a record ID to match up to the main table and each listing in these 5 sub tables can have multiple listings that are tied to the main record. I have created several queries trying to get this to work. I want the report to pull all of the secondary IDs (from the 5 sub tables) that are tied to the main Record ID fromt he main table. Everything I have tried is not working. I am at a loss as to what to try next. Anyone with some advise or direction would be very appreciated. David |
Thread Tools | |
Display Modes | |
|
|