Report that pulls data from 6 tables

  • Thread starter Thread starter DStrong
  • Start date Start date
D

DStrong

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
 
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 here:

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];
 
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 said:
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 here:

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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 here:

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 said:
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

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
Click to expand...
Click to expand...
Click to expand...
 
Change all of your [RCA Records] INNER JOIN to [RCA Records] LEFT JOIN

--
KARL DEWEY
Build a little - Test a little


DStrong said:
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 said:
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 said:
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 here:

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];



:

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

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
Change all of your [RCA Records] INNER JOIN to [RCA Records] LEFT
JOIN

--
KARL DEWEY
Build a little - Test a little


DStrong said:
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!


:

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


:

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 here:

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];



:

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

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top