Subreports not printing in the right order

[LoboNetwork]

Hello,

I've done a lot of browsing on these forums and playing around with access
but cant seem to get this to work:

Description of my Report/Database:

I have three tables that are linked in from an Excel File. Each table has a
common column (TAG) (what i consider to be the primary key, even though
access wont let me set it as the primary key since its a linked table). I
have a query that takes all the data in these linked tables and displays
them. I have three "subreports" and they get their data from the query. I
have a master report that contains the three subreports above. Each
subreport prints on a separate page using page break controls.

The problem is that the subreports do not print correctly (well, at least
the way I want them to print).

Example:
Table 1
Tag1 Data1 Data2 Data3
Tag2 Data4 Data5 Data6
Tag3 Data7 Data8 Data9

Table 2
Tag1 Data10 Data11
Tag2 Data12 Data13
Tag3 Data14 Data15

Table 3
Tag1 Data16 Data17
Tag2 Data18 Data19
Tag3 Data20 Data21

When I print my Master Report, it will print as such:

Subreport1 (tag1) subreport1 (tag2), subreport1 (tag3), Subreport2 (tag1)
subreport2 (tag2), subreport2 (tag3), Subreport3 (tag1) subreport3 (tag2),
subreport3 (tag3).

Instead I want it to print as such:

Subreport1 (tag1) subreport2 (tag1), subreport3 (tag1), Subreport1 (tag2)
subreport2 (tag2), subreport3 (tag2), Subreport1 (tag3) subreport2 (tag3),
subreport3 (tag3).


Does anyone know what I am doing wrong? Or can provide any input towards me
fixing the way my subreports print? I figure it has something to do with
either relationships, primary keys, sorting/grouping, or such. I have played
around with various ideas but nothing seemed to help the order it came out in.

Any help would be greatly appreciated.

Thanks,
Lobo

 

[LoboNetwork]

Further Information

query1 combines data from linked tables: table1, table2, table3
Subreport1 datasource is query1
subreport2 datasource is query1
subreport3 datasource is query1
masterReport contains the three subreports
The link master fields for all subreports is left blank
The link child fields for all subreports is left blank
masterReport's datasource is left blank.

when i make masterReport's datasource to query1... it prints 27 pages
instead of the 9 detailed above. It looks something like this (still the
wrong order):

Subreport1 (tag1) subreport1 (tag2), subreport1 (tag3), Subreport2 (tag1)
subreport2 (tag2), subreport2 (tag3), Subreport3 (tag1) subreport3 (tag2),
subreport3 (tag3), Subreport1 (tag1) subreport1 (tag2), subreport1 (tag3),
Subreport2 (tag1), subreport2 (tag2), subreport2 (tag3), Subreport3 (tag1)
subreport3 (tag2), subreport3 (tag3). Subreport1 (tag1) subreport1 (tag2),
subreport1 (tag3), Subreport2 (tag1), subreport2 (tag2), subreport2 (tag3),
Subreport3 (tag1) subreport3 (tag2), subreport3 (tag3). Unnecessary pages?

When I make the tag column as the master/child link... I get several blank
pages for the report. All i see is the header/footer.

 

[GeoffG]

I've not looked at your post in detail. This is just a thought.

If I were doing this, I'd have separate queries for each sub-report and for
the main report (i.e. 4 queries in all). I'd add the queries to the
relationships window and create four relationships - between the one primary
key in the main query (the record source for the main report) and the three
foreign keys in the subqueries (the record sources of the subreports). When
dragging the subreports to the main report in design view, Access should set
up the master and child field properties of the subreports for you, so as
the the report knows which is your primary key and which are your foreign
keys (so the right records would be linked). I think this approach would
allow you to create the report to do exactly what you want.

Regards
Geoff

 

[GeoffG]

Correction:

3 relationships, not 4.


Geoff

I've not looked at your post in detail. This is just a thought.

If I were doing this, I'd have separate queries for each sub-report and
for the main report (i.e. 4 queries in all). I'd add the queries to the
relationships window and create four relationships - between the one
primary key in the main query (the record source for the main report) and
the three foreign keys in the subqueries (the record sources of the
subreports). When dragging the subreports to the main report in design
view, Access should set up the master and child field properties of the
subreports for you, so as the the report knows which is your primary key
and which are your foreign keys (so the right records would be linked). I
think this approach would allow you to create the report to do exactly
what you want.

Regards
Geoff

 

[LoboNetwork]

Hi Geoff (or anyone else),

Thank you for your response. I redid the database exactly as you stated
above. When I preview the report, I see 9 pages... And all I see is the
header/footer. The detail section for those 9 pages is blank. Any idea as
to what is going on here?

Lobo

 

[GeoffG]

Is your master report unbound (i.e. does it have a record source)?

I have a query that takes all the data in these
linked tables and displays them. I have three
"subreports" and they get their data from the query.

How did you construct this query? Was it a union query?
Can you post its SQL statement?

Geoff

 

[LoboNetwork]

Geoff,

Based on your replies above, I no longer have just one query running the
show. This is what I have now:

Type -> Record Source

Query1 -> LinkedTable1
Query2 -> LinkedTable2
Query3 -> LinkedTable3
Query4 -> LinkedTable4
MasterReport -> Query1
Subreport1 -> Query2
Subreport2 -> Query3
Subreport3 -> Query4

Relationship: Query1 contains the primary key which is linked to foreign
keys in the other Queries. The Master and Child Links for the subreports
came automatically from the relationship above.

I am not sure what you mean by Union Query... but these are the SQLs for the
Queries:

Query1:
SELECT [Table1].Tag
FROM [Table1]
ORDER BY [Table1].Tag;

Query2:
SELECT [Table2].*
FROM [Table2];

Query3:
SELECT [Table3].*
FROM [Table3];

Query4:
SELECT [Table4].*
FROM [Table4];

Any thoughts? Thanks in advance you for your assistance.

Lobo

 

[GeoffG]

Based on your replies above, I no longer have

just one query running the show.

Sorry, I was referring to your original post. Originally, you said you had
one query:

I have a query [singular] that takes all the data in
these linked tables and displays them.

This made me wonder whether you had created a union query (which would have
combined the three tables as if they were one table). That, in turn, made
me wonder whether you'd simply created a main report as a receptacle for the
three sub-reports (rather than as a means of linking a main report to three
subreports). As you say, the your situation as moved on from these
scenarios. I'm now clear that there is, indeed, a main report that's bound
to a recordsource.

I suggest the following:

1. Run queries 1, 2, 3 and 4. Do they return records?

2. Create a new query 5 based on queries 1 and 2. The relationship you
have already created between queries 1 and 2 should automatically appear in
the design of query 5. Drag fields in to the design grid from both queries
1 and 2. Run query 5. Does that return records?

3. Similarly, create new queries 6 and 7, based on queries 1 and 3 and 1
and 4 respectively. Do queries 6 and 7 return records?

4. If queries 5, 6 and 7 don't return records, then determine why not.
Are there no matching tags in queries 1 and 2, or between 1 and 3, or
between 1 and 4? This would identify the source of the problem.

5. If queries 5, 6 and 7 do return records, then examine those records
carefully. For example, when you run, say, query 5, for Tag 1 that's coming
from query 1, what's returning from query 2? Establish what you should be
seeing in your first subreport.

6. My guess at this stage is that you've probably created a report that
should work OK. The problem, I think, is with the data.

7. If all the above fails to lead anywhere, import the data into temporary
tables in Access, so that you're dealing with real tables, instead of linked
tables. I wouldn't have thought that'd make a difference, but, clearly,
something odd is going on.

Geoff.

Geoff,

Based on your replies above, I no longer have just one query running the
show. This is what I have now:

Type -> Record Source

Query1 -> LinkedTable1
Query2 -> LinkedTable2
Query3 -> LinkedTable3
Query4 -> LinkedTable4
MasterReport -> Query1
Subreport1 -> Query2
Subreport2 -> Query3
Subreport3 -> Query4

Relationship: Query1 contains the primary key which is linked to foreign
keys in the other Queries. The Master and Child Links for the subreports
came automatically from the relationship above.

I am not sure what you mean by Union Query... but these are the SQLs for
the
Queries:

Query1:
SELECT [Table1].Tag
FROM [Table1]
ORDER BY [Table1].Tag;

Query2:
SELECT [Table2].*
FROM [Table2];

Query3:
SELECT [Table3].*
FROM [Table3];

Query4:
SELECT [Table4].*
FROM [Table4];

Any thoughts? Thanks in advance you for your assistance.

Lobo

Is your master report unbound (i.e. does it have a record source)?


How did you construct this query? Was it a union query?
Can you post its SQL statement?

Geoff

 

[LoboNetwork]

Geoff,

Answers to your questions:

1) Yes Queries 1-4 return records
2) Yes Query 5 returns rercords
3) Yes, Query 6 and 7 return records
4) There are matching tags
5) Seems to be fine - i'm see the data needed for the reports and it matches.
6) Agreed as seen above - theorethically the report should be fine...
7) I imported the tables to form access tables but this did not make a
result - My subreports are still blank. I believe the report structure is
working as the pages/records are right and the order is right when I put some
"labels" to check for it. Its just seems to not be physically displaying the
subreports.

I am stumped here...

Lobo

p.s. thank you for all your assistance to date.

Based on your replies above, I no longer have
just one query running the show.

Sorry, I was referring to your original post. Originally, you said you had
one query:

I have a query [singular] that takes all the data in
these linked tables and displays them.

This made me wonder whether you had created a union query (which would have
combined the three tables as if they were one table). That, in turn, made
me wonder whether you'd simply created a main report as a receptacle for the
three sub-reports (rather than as a means of linking a main report to three
subreports). As you say, the your situation as moved on from these
scenarios. I'm now clear that there is, indeed, a main report that's bound
to a recordsource.

I suggest the following:

1. Run queries 1, 2, 3 and 4. Do they return records?

2. Create a new query 5 based on queries 1 and 2. The relationship you
have already created between queries 1 and 2 should automatically appear in
the design of query 5. Drag fields in to the design grid from both queries
1 and 2. Run query 5. Does that return records?

3. Similarly, create new queries 6 and 7, based on queries 1 and 3 and 1
and 4 respectively. Do queries 6 and 7 return records?

4. If queries 5, 6 and 7 don't return records, then determine why not.
Are there no matching tags in queries 1 and 2, or between 1 and 3, or
between 1 and 4? This would identify the source of the problem.

5. If queries 5, 6 and 7 do return records, then examine those records
carefully. For example, when you run, say, query 5, for Tag 1 that's coming
from query 1, what's returning from query 2? Establish what you should be
seeing in your first subreport.

6. My guess at this stage is that you've probably created a report that
should work OK. The problem, I think, is with the data.

7. If all the above fails to lead anywhere, import the data into temporary
tables in Access, so that you're dealing with real tables, instead of linked
tables. I wouldn't have thought that'd make a difference, but, clearly,
something odd is going on.

Geoff.

Geoff,

Based on your replies above, I no longer have just one query running the
show. This is what I have now:

Type -> Record Source

Query1 -> LinkedTable1
Query2 -> LinkedTable2
Query3 -> LinkedTable3
Query4 -> LinkedTable4
MasterReport -> Query1
Subreport1 -> Query2
Subreport2 -> Query3
Subreport3 -> Query4

Relationship: Query1 contains the primary key which is linked to foreign
keys in the other Queries. The Master and Child Links for the subreports
came automatically from the relationship above.

I am not sure what you mean by Union Query... but these are the SQLs for
the
Queries:

Query1:
SELECT [Table1].Tag
FROM [Table1]
ORDER BY [Table1].Tag;

Query2:
SELECT [Table2].*
FROM [Table2];

Query3:
SELECT [Table3].*
FROM [Table3];

Query4:
SELECT [Table4].*
FROM [Table4];

Any thoughts? Thanks in advance you for your assistance.

Lobo

Is your master report unbound (i.e. does it have a record source)?

I have a query that takes all the data in these
linked tables and displays them. I have three
"subreports" and they get their data from the query.

How did you construct this query? Was it a union query?
Can you post its SQL statement?

Geoff



Hi Geoff (or anyone else),

Thank you for your response. I redid the database exactly as you
stated
above. When I preview the report, I see 9 pages... And all I see is
the
header/footer. The detail section for those 9 pages is blank. Any
idea
as
to what is going on here?

Lobo

:

Correction:

3 relationships, not 4.


Geoff


I've not looked at your post in detail. This is just a thought.

If I were doing this, I'd have separate queries for each sub-report
and
for the main report (i.e. 4 queries in all). I'd add the queries to
the
relationships window and create four relationships - between the one
primary key in the main query (the record source for the main
report)
and
the three foreign keys in the subqueries (the record sources of the
subreports). When dragging the subreports to the main report in
design
view, Access should set up the master and child field properties of
the
subreports for you, so as the the report knows which is your primary
key
and which are your foreign keys (so the right records would be
linked).
I
think this approach would allow you to create the report to do
exactly
what you want.

Regards
Geoff



message
Further Information

query1 combines data from linked tables: table1, table2, table3
Subreport1 datasource is query1
subreport2 datasource is query1
subreport3 datasource is query1
masterReport contains the three subreports
The link master fields for all subreports is left blank
The link child fields for all subreports is left blank
masterReport's datasource is left blank.

when i make masterReport's datasource to query1... it prints 27
pages
instead of the 9 detailed above. It looks something like this
(still
the
wrong order):

Subreport1 (tag1) subreport1 (tag2), subreport1 (tag3), Subreport2
(tag1)
subreport2 (tag2), subreport2 (tag3), Subreport3 (tag1) subreport3
(tag2),
subreport3 (tag3), Subreport1 (tag1) subreport1 (tag2), subreport1
(tag3),
Subreport2 (tag1), subreport2 (tag2), subreport2 (tag3), Subreport3
(tag1)
subreport3 (tag2), subreport3 (tag3). Subreport1 (tag1) subreport1
(tag2),
subreport1 (tag3), Subreport2 (tag1), subreport2 (tag2), subreport2
(tag3),
Subreport3 (tag1) subreport3 (tag2), subreport3 (tag3).
Unnecessary
pages?

When I make the tag column as the master/child link... I get
several
blank
pages for the report. All i see is the header/footer.


:

Hello,

I've done a lot of browsing on these forums and playing around
with
access
but cant seem to get this to work:

Description of my Report/Database:

I have three tables that are linked in from an Excel File. Each
table
has a
common column (TAG) (what i consider to be the primary key, even
though
access wont let me set it as the primary key since its a linked
table).
I
have a query that takes all the data in these linked tables and
displays
them. I have three "subreports" and they get their data from the
query.
I
have a master report that contains the three subreports above.
Each
subreport prints on a separate page using page break controls.

The problem is that the subreports do not print correctly (well,
at
least
the way I want them to print).

Example:
Table 1
Tag1 Data1 Data2 Data3
Tag2 Data4 Data5 Data6
Tag3 Data7 Data8 Data9

Table 2
Tag1 Data10 Data11
Tag2 Data12 Data13
Tag3 Data14 Data15

Table 3
Tag1 Data16 Data17
Tag2 Data18 Data19
Tag3 Data20 Data21

When I print my Master Report, it will print as such:

Subreport1 (tag1) subreport1 (tag2), subreport1 (tag3), Subreport2
(tag1)
subreport2 (tag2), subreport2 (tag3), Subreport3 (tag1) subreport3
(tag2),
subreport3 (tag3).

Instead I want it to print as such:

Subreport1 (tag1) subreport2 (tag1), subreport3 (tag1), Subreport1
(tag2)
subreport2 (tag2), subreport3 (tag2), Subreport1 (tag3) subreport2
(tag3),
subreport3 (tag3).


Does anyone know what I am doing wrong? Or can provide any input
towards me
fixing the way my subreports print? I figure it has something to
do
with
either relationships, primary keys, sorting/grouping, or such. I
have
played
around with various ideas but nothing seemed to help the order it
came
out in.

Any help would be greatly appreciated.

Thanks,
Lobo

 

[LoboNetwork]

Problem Solved Geoff. Your steps 1-7 helped me realize this. It was a
problem with my Master Query (Query1) and the master/child links.

This was what I had previously:

Query1 from table1 (Only contains Tag)
Query2 from table2 (contains Tag1 and various other fields)
Query2 from table3 (contains Tag2 and various other fields)
Query2 from table4 (contains Tag3 and various other fields)

The relationship was as follows:
Query1 Tag was linked to Query2 Tag1
Query1 Tag was linked to Query3 Tag2
Query1 Tag was linked to Query4 Tag3

My master report was sourced from Query1

The subreports were as follows:

Subreport1:
Master Link: Tag
Child Link: Tag1

Subreport2:
Master Link: Tag
Child Link: Tag2

Subreport3:
Master Link: Tag
Child Link: Tag3


What I did to fix this problem was:
- Changed Query1 to get data from table1/2/3/4
- Update the master/child links in the subreports to be:

Subreport1:
Master Link: Tag1
Child Link: Tag1

Subreport2:
Master Link: Tag2
Child Link: Tag2

Subreport3:
Master Link: Tag2
Child Link: Tag3

The subreports did not show up blank, the data was as it should be, and the
order was right!

Thank you very much for all your help!

Lobo

Geoff,

Answers to your questions:

1) Yes Queries 1-4 return records
2) Yes Query 5 returns rercords
3) Yes, Query 6 and 7 return records
4) There are matching tags
5) Seems to be fine - i'm see the data needed for the reports and it matches.
6) Agreed as seen above - theorethically the report should be fine...
7) I imported the tables to form access tables but this did not make a
result - My subreports are still blank. I believe the report structure is
working as the pages/records are right and the order is right when I put some
"labels" to check for it. Its just seems to not be physically displaying the
subreports.

I am stumped here...

Lobo

p.s. thank you for all your assistance to date.

Based on your replies above, I no longer have
just one query running the show.

Sorry, I was referring to your original post. Originally, you said you had
one query:

I have a query [singular] that takes all the data in
these linked tables and displays them.

This made me wonder whether you had created a union query (which would have
combined the three tables as if they were one table). That, in turn, made
me wonder whether you'd simply created a main report as a receptacle for the
three sub-reports (rather than as a means of linking a main report to three
subreports). As you say, the your situation as moved on from these
scenarios. I'm now clear that there is, indeed, a main report that's bound
to a recordsource.

I suggest the following:

1. Run queries 1, 2, 3 and 4. Do they return records?

2. Create a new query 5 based on queries 1 and 2. The relationship you
have already created between queries 1 and 2 should automatically appear in
the design of query 5. Drag fields in to the design grid from both queries
1 and 2. Run query 5. Does that return records?

3. Similarly, create new queries 6 and 7, based on queries 1 and 3 and 1
and 4 respectively. Do queries 6 and 7 return records?

4. If queries 5, 6 and 7 don't return records, then determine why not.
Are there no matching tags in queries 1 and 2, or between 1 and 3, or
between 1 and 4? This would identify the source of the problem.

5. If queries 5, 6 and 7 do return records, then examine those records
carefully. For example, when you run, say, query 5, for Tag 1 that's coming
from query 1, what's returning from query 2? Establish what you should be
seeing in your first subreport.

6. My guess at this stage is that you've probably created a report that
should work OK. The problem, I think, is with the data.

7. If all the above fails to lead anywhere, import the data into temporary
tables in Access, so that you're dealing with real tables, instead of linked
tables. I wouldn't have thought that'd make a difference, but, clearly,
something odd is going on.

Geoff.

Geoff,

Based on your replies above, I no longer have just one query running the
show. This is what I have now:

Type -> Record Source

Query1 -> LinkedTable1
Query2 -> LinkedTable2
Query3 -> LinkedTable3
Query4 -> LinkedTable4
MasterReport -> Query1
Subreport1 -> Query2
Subreport2 -> Query3
Subreport3 -> Query4

Relationship: Query1 contains the primary key which is linked to foreign
keys in the other Queries. The Master and Child Links for the subreports
came automatically from the relationship above.

I am not sure what you mean by Union Query... but these are the SQLs for
the
Queries:

Query1:
SELECT [Table1].Tag
FROM [Table1]
ORDER BY [Table1].Tag;

Query2:
SELECT [Table2].*
FROM [Table2];

Query3:
SELECT [Table3].*
FROM [Table3];

Query4:
SELECT [Table4].*
FROM [Table4];

Any thoughts? Thanks in advance you for your assistance.

Lobo

:

Is your master report unbound (i.e. does it have a record source)?

I have a query that takes all the data in these
linked tables and displays them. I have three
"subreports" and they get their data from the query.

How did you construct this query? Was it a union query?
Can you post its SQL statement?

Geoff



Hi Geoff (or anyone else),

Thank you for your response. I redid the database exactly as you
stated
above. When I preview the report, I see 9 pages... And all I see is
the
header/footer. The detail section for those 9 pages is blank. Any
idea
as
to what is going on here?

Lobo

:

Correction:

3 relationships, not 4.


Geoff


I've not looked at your post in detail. This is just a thought.

If I were doing this, I'd have separate queries for each sub-report
and
for the main report (i.e. 4 queries in all). I'd add the queries to
the
relationships window and create four relationships - between the one
primary key in the main query (the record source for the main
report)
and
the three foreign keys in the subqueries (the record sources of the
subreports). When dragging the subreports to the main report in
design
view, Access should set up the master and child field properties of
the
subreports for you, so as the the report knows which is your primary
key
and which are your foreign keys (so the right records would be
linked).
I
think this approach would allow you to create the report to do
exactly
what you want.

Regards
Geoff



message
Further Information

query1 combines data from linked tables: table1, table2, table3
Subreport1 datasource is query1
subreport2 datasource is query1
subreport3 datasource is query1
masterReport contains the three subreports
The link master fields for all subreports is left blank
The link child fields for all subreports is left blank
masterReport's datasource is left blank.

when i make masterReport's datasource to query1... it prints 27
pages
instead of the 9 detailed above. It looks something like this
(still
the
wrong order):

Subreport1 (tag1) subreport1 (tag2), subreport1 (tag3), Subreport2
(tag1)
subreport2 (tag2), subreport2 (tag3), Subreport3 (tag1) subreport3
(tag2),
subreport3 (tag3), Subreport1 (tag1) subreport1 (tag2), subreport1
(tag3),
Subreport2 (tag1), subreport2 (tag2), subreport2 (tag3), Subreport3
(tag1)
subreport3 (tag2), subreport3 (tag3). Subreport1 (tag1) subreport1
(tag2),
subreport1 (tag3), Subreport2 (tag1), subreport2 (tag2), subreport2
(tag3),
Subreport3 (tag1) subreport3 (tag2), subreport3 (tag3).
Unnecessary
pages?

When I make the tag column as the master/child link... I get
several
blank
pages for the report. All i see is the header/footer.


:

Hello,

I've done a lot of browsing on these forums and playing around
with
access
but cant seem to get this to work:

Description of my Report/Database:

I have three tables that are linked in from an Excel File. Each
table
has a
common column (TAG) (what i consider to be the primary key, even
though
access wont let me set it as the primary key since its a linked
table).
I
have a query that takes all the data in these linked tables and
displays
them. I have three "subreports" and they get their data from the
query.
I
have a master report that contains the three subreports above.
Each
subreport prints on a separate page using page break controls.

The problem is that the subreports do not print correctly (well,
at
least
the way I want them to print).

Example:
Table 1
Tag1 Data1 Data2 Data3
Tag2 Data4 Data5 Data6
Tag3 Data7 Data8 Data9

Table 2
Tag1 Data10 Data11
Tag2 Data12 Data13
Tag3 Data14 Data15

Table 3
Tag1 Data16 Data17
Tag2 Data18 Data19
Tag3 Data20 Data21

When I print my Master Report, it will print as such:

Subreport1 (tag1) subreport1 (tag2), subreport1 (tag3), Subreport2
(tag1)
subreport2 (tag2), subreport2 (tag3), Subreport3 (tag1) subreport3
(tag2),
subreport3 (tag3).

Instead I want it to print as such:

Subreport1 (tag1) subreport2 (tag1), subreport3 (tag1), Subreport1
(tag2)
subreport2 (tag2), subreport3 (tag2), Subreport1 (tag3) subreport2
(tag3),

 

[LoboNetwork]

Typo in my last post.

Subreport3:
Master Link: Tag2
Child Link: Tag3

should read

Subreport3:
Master Link: Tag3
Child Link: Tag3


Regards,
Lobo

Problem Solved Geoff. Your steps 1-7 helped me realize this. It was a
problem with my Master Query (Query1) and the master/child links.

This was what I had previously:

Query1 from table1 (Only contains Tag)
Query2 from table2 (contains Tag1 and various other fields)
Query2 from table3 (contains Tag2 and various other fields)
Query2 from table4 (contains Tag3 and various other fields)

The relationship was as follows:
Query1 Tag was linked to Query2 Tag1
Query1 Tag was linked to Query3 Tag2
Query1 Tag was linked to Query4 Tag3

My master report was sourced from Query1

The subreports were as follows:

Subreport1:
Master Link: Tag
Child Link: Tag1

Subreport2:
Master Link: Tag
Child Link: Tag2

Subreport3:
Master Link: Tag
Child Link: Tag3


What I did to fix this problem was:
- Changed Query1 to get data from table1/2/3/4
- Update the master/child links in the subreports to be:

Subreport1:
Master Link: Tag1
Child Link: Tag1

Subreport2:
Master Link: Tag2
Child Link: Tag2

Subreport3:
Master Link: Tag2
Child Link: Tag3

The subreports did not show up blank, the data was as it should be, and the
order was right!

Thank you very much for all your help!

Lobo

Geoff,

Answers to your questions:

1) Yes Queries 1-4 return records
2) Yes Query 5 returns rercords
3) Yes, Query 6 and 7 return records
4) There are matching tags
5) Seems to be fine - i'm see the data needed for the reports and it matches.
6) Agreed as seen above - theorethically the report should be fine...
7) I imported the tables to form access tables but this did not make a
result - My subreports are still blank. I believe the report structure is
working as the pages/records are right and the order is right when I put some
"labels" to check for it. Its just seems to not be physically displaying the
subreports.

I am stumped here...

Lobo

p.s. thank you for all your assistance to date.

Based on your replies above, I no longer have
just one query running the show.

Sorry, I was referring to your original post. Originally, you said you had
one query:

I have a query [singular] that takes all the data in
these linked tables and displays them.

This made me wonder whether you had created a union query (which would have
combined the three tables as if they were one table). That, in turn, made
me wonder whether you'd simply created a main report as a receptacle for the
three sub-reports (rather than as a means of linking a main report to three
subreports). As you say, the your situation as moved on from these
scenarios. I'm now clear that there is, indeed, a main report that's bound
to a recordsource.

I suggest the following:

1. Run queries 1, 2, 3 and 4. Do they return records?

2. Create a new query 5 based on queries 1 and 2. The relationship you
have already created between queries 1 and 2 should automatically appear in
the design of query 5. Drag fields in to the design grid from both queries
1 and 2. Run query 5. Does that return records?

3. Similarly, create new queries 6 and 7, based on queries 1 and 3 and 1
and 4 respectively. Do queries 6 and 7 return records?

4. If queries 5, 6 and 7 don't return records, then determine why not.
Are there no matching tags in queries 1 and 2, or between 1 and 3, or
between 1 and 4? This would identify the source of the problem.

5. If queries 5, 6 and 7 do return records, then examine those records
carefully. For example, when you run, say, query 5, for Tag 1 that's coming
from query 1, what's returning from query 2? Establish what you should be
seeing in your first subreport.

6. My guess at this stage is that you've probably created a report that
should work OK. The problem, I think, is with the data.

7. If all the above fails to lead anywhere, import the data into temporary
tables in Access, so that you're dealing with real tables, instead of linked
tables. I wouldn't have thought that'd make a difference, but, clearly,
something odd is going on.

Geoff.




Geoff,

Based on your replies above, I no longer have just one query running the
show. This is what I have now:

Type -> Record Source

Query1 -> LinkedTable1
Query2 -> LinkedTable2
Query3 -> LinkedTable3
Query4 -> LinkedTable4
MasterReport -> Query1
Subreport1 -> Query2
Subreport2 -> Query3
Subreport3 -> Query4

Relationship: Query1 contains the primary key which is linked to foreign
keys in the other Queries. The Master and Child Links for the subreports
came automatically from the relationship above.

I am not sure what you mean by Union Query... but these are the SQLs for
the
Queries:

Query1:
SELECT [Table1].Tag
FROM [Table1]
ORDER BY [Table1].Tag;

Query2:
SELECT [Table2].*
FROM [Table2];

Query3:
SELECT [Table3].*
FROM [Table3];

Query4:
SELECT [Table4].*
FROM [Table4];

Any thoughts? Thanks in advance you for your assistance.

Lobo

:

Is your master report unbound (i.e. does it have a record source)?

I have a query that takes all the data in these
linked tables and displays them. I have three
"subreports" and they get their data from the query.

How did you construct this query? Was it a union query?
Can you post its SQL statement?

Geoff



Hi Geoff (or anyone else),

Thank you for your response. I redid the database exactly as you
stated
above. When I preview the report, I see 9 pages... And all I see is
the
header/footer. The detail section for those 9 pages is blank. Any
idea
as
to what is going on here?

Lobo

:

Correction:

3 relationships, not 4.


Geoff


I've not looked at your post in detail. This is just a thought.

If I were doing this, I'd have separate queries for each sub-report
and
for the main report (i.e. 4 queries in all). I'd add the queries to
the
relationships window and create four relationships - between the one
primary key in the main query (the record source for the main
report)
and
the three foreign keys in the subqueries (the record sources of the
subreports). When dragging the subreports to the main report in
design
view, Access should set up the master and child field properties of
the
subreports for you, so as the the report knows which is your primary
key
and which are your foreign keys (so the right records would be
linked).
I
think this approach would allow you to create the report to do
exactly
what you want.

Regards
Geoff



message
Further Information

query1 combines data from linked tables: table1, table2, table3
Subreport1 datasource is query1
subreport2 datasource is query1
subreport3 datasource is query1
masterReport contains the three subreports
The link master fields for all subreports is left blank
The link child fields for all subreports is left blank
masterReport's datasource is left blank.

when i make masterReport's datasource to query1... it prints 27
pages
instead of the 9 detailed above. It looks something like this
(still
the
wrong order):

Subreport1 (tag1) subreport1 (tag2), subreport1 (tag3), Subreport2
(tag1)
subreport2 (tag2), subreport2 (tag3), Subreport3 (tag1) subreport3
(tag2),
subreport3 (tag3), Subreport1 (tag1) subreport1 (tag2), subreport1
(tag3),
Subreport2 (tag1), subreport2 (tag2), subreport2 (tag3), Subreport3
(tag1)
subreport3 (tag2), subreport3 (tag3). Subreport1 (tag1) subreport1
(tag2),
subreport1 (tag3), Subreport2 (tag1), subreport2 (tag2), subreport2
(tag3),
Subreport3 (tag1) subreport3 (tag2), subreport3 (tag3).
Unnecessary
pages?

When I make the tag column as the master/child link... I get
several
blank
pages for the report. All i see is the header/footer.


:

Hello,

I've done a lot of browsing on these forums and playing around
with

 

[GeoffG]

I figured that was a typo.

I'm glad you were able to analyse the problem methodically and figure the
solution. Sometimes you just have to stand back and challenge everything
you've done - often a frustrating, but eventually satisfying learning
experience. I bet you'll not quickly forget the importance of master/child
links and the effect they can have if they're wrong.

I think it's probably more common to discover the challenges of master/child
links when designing forms with subforms (especially when you want a
sub-sub-form to display as a columnar table), but similar challenges apply
with reports and subreports, as you've discovered.

Good luck with your development.

Regards
Geoff

Typo in my last post.

Subreport3:
Master Link: Tag2
Child Link: Tag3

should read

Subreport3:
Master Link: Tag3
Child Link: Tag3


Regards,
Lobo

Problem Solved Geoff. Your steps 1-7 helped me realize this. It was a
problem with my Master Query (Query1) and the master/child links.

This was what I had previously:

Query1 from table1 (Only contains Tag)
Query2 from table2 (contains Tag1 and various other fields)
Query2 from table3 (contains Tag2 and various other fields)
Query2 from table4 (contains Tag3 and various other fields)

The relationship was as follows:
Query1 Tag was linked to Query2 Tag1
Query1 Tag was linked to Query3 Tag2
Query1 Tag was linked to Query4 Tag3

My master report was sourced from Query1

The subreports were as follows:

Subreport1:
Master Link: Tag
Child Link: Tag1

Subreport2:
Master Link: Tag
Child Link: Tag2

Subreport3:
Master Link: Tag
Child Link: Tag3


What I did to fix this problem was:
- Changed Query1 to get data from table1/2/3/4
- Update the master/child links in the subreports to be:

Subreport1:
Master Link: Tag1
Child Link: Tag1

Subreport2:
Master Link: Tag2
Child Link: Tag2

Subreport3:
Master Link: Tag2
Child Link: Tag3

The subreports did not show up blank, the data was as it should be, and
the
order was right!

Thank you very much for all your help!

Lobo

Geoff,

Answers to your questions:

1) Yes Queries 1-4 return records
2) Yes Query 5 returns rercords
3) Yes, Query 6 and 7 return records
4) There are matching tags
5) Seems to be fine - i'm see the data needed for the reports and it
matches.
6) Agreed as seen above - theorethically the report should be fine...
7) I imported the tables to form access tables but this did not make a
result - My subreports are still blank. I believe the report structure
is
working as the pages/records are right and the order is right when I
put some
"labels" to check for it. Its just seems to not be physically
displaying the
subreports.

I am stumped here...

Lobo

p.s. thank you for all your assistance to date.


:

Based on your replies above, I no longer have
just one query running the show.

Sorry, I was referring to your original post. Originally, you said
you had
one query:

I have a query [singular] that takes all the data in
these linked tables and displays them.

This made me wonder whether you had created a union query (which
would have
combined the three tables as if they were one table). That, in
turn, made
me wonder whether you'd simply created a main report as a receptacle
for the
three sub-reports (rather than as a means of linking a main report to
three
subreports). As you say, the your situation as moved on from these
scenarios. I'm now clear that there is, indeed, a main report that's
bound
to a recordsource.

I suggest the following:

1. Run queries 1, 2, 3 and 4. Do they return records?

2. Create a new query 5 based on queries 1 and 2. The relationship
you
have already created between queries 1 and 2 should automatically
appear in
the design of query 5. Drag fields in to the design grid from both
queries
1 and 2. Run query 5. Does that return records?

3. Similarly, create new queries 6 and 7, based on queries 1 and 3
and 1
and 4 respectively. Do queries 6 and 7 return records?

4. If queries 5, 6 and 7 don't return records, then determine why
not.
Are there no matching tags in queries 1 and 2, or between 1 and 3, or
between 1 and 4? This would identify the source of the problem.

5. If queries 5, 6 and 7 do return records, then examine those
records
carefully. For example, when you run, say, query 5, for Tag 1 that's
coming
from query 1, what's returning from query 2? Establish what you
should be
seeing in your first subreport.

6. My guess at this stage is that you've probably created a report
that
should work OK. The problem, I think, is with the data.

7. If all the above fails to lead anywhere, import the data into
temporary
tables in Access, so that you're dealing with real tables, instead of
linked
tables. I wouldn't have thought that'd make a difference, but,
clearly,
something odd is going on.

Geoff.




message
Geoff,

Based on your replies above, I no longer have just one query
running the
show. This is what I have now:

Type -> Record Source

Query1 -> LinkedTable1
Query2 -> LinkedTable2
Query3 -> LinkedTable3
Query4 -> LinkedTable4
MasterReport -> Query1
Subreport1 -> Query2
Subreport2 -> Query3
Subreport3 -> Query4

Relationship: Query1 contains the primary key which is linked to
foreign
keys in the other Queries. The Master and Child Links for the
subreports
came automatically from the relationship above.

I am not sure what you mean by Union Query... but these are the
SQLs for
the
Queries:

Query1:
SELECT [Table1].Tag
FROM [Table1]
ORDER BY [Table1].Tag;

Query2:
SELECT [Table2].*
FROM [Table2];

Query3:
SELECT [Table3].*
FROM [Table3];

Query4:
SELECT [Table4].*
FROM [Table4];

Any thoughts? Thanks in advance you for your assistance.

Lobo

:

Is your master report unbound (i.e. does it have a record source)?

I have a query that takes all the data in these
linked tables and displays them. I have three
"subreports" and they get their data from the query.

How did you construct this query? Was it a union query?
Can you post its SQL statement?

Geoff



message
Hi Geoff (or anyone else),

Thank you for your response. I redid the database exactly as
you
stated
above. When I preview the report, I see 9 pages... And all I
see is
the
header/footer. The detail section for those 9 pages is blank.
Any
idea
as
to what is going on here?

Lobo

:

Correction:

3 relationships, not 4.


Geoff


I've not looked at your post in detail. This is just a
thought.

If I were doing this, I'd have separate queries for each
sub-report
and
for the main report (i.e. 4 queries in all). I'd add the
queries to
the
relationships window and create four relationships - between
the one
primary key in the main query (the record source for the main
report)
and
the three foreign keys in the subqueries (the record sources
of the
subreports). When dragging the subreports to the main report
in
design
view, Access should set up the master and child field
properties of
the
subreports for you, so as the the report knows which is your
primary
key
and which are your foreign keys (so the right records would
be
linked).
I
think this approach would allow you to create the report to
do
exactly
what you want.

Regards
Geoff



in
message
Further Information

query1 combines data from linked tables: table1, table2,
table3
Subreport1 datasource is query1
subreport2 datasource is query1
subreport3 datasource is query1
masterReport contains the three subreports
The link master fields for all subreports is left blank
The link child fields for all subreports is left blank
masterReport's datasource is left blank.

when i make masterReport's datasource to query1... it prints
27
pages
instead of the 9 detailed above. It looks something like
this
(still
the
wrong order):

Subreport1 (tag1) subreport1 (tag2), subreport1 (tag3),
Subreport2
(tag1)
subreport2 (tag2), subreport2 (tag3), Subreport3 (tag1)
subreport3
(tag2),
subreport3 (tag3), Subreport1 (tag1) subreport1 (tag2),
subreport1
(tag3),
Subreport2 (tag1), subreport2 (tag2), subreport2 (tag3),
Subreport3
(tag1)
subreport3 (tag2), subreport3 (tag3). Subreport1 (tag1)
subreport1
(tag2),
subreport1 (tag3), Subreport2 (tag1), subreport2 (tag2),
subreport2
(tag3),
Subreport3 (tag1) subreport3 (tag2), subreport3 (tag3).
Unnecessary
pages?

When I make the tag column as the master/child link... I get
several
blank
pages for the report. All i see is the header/footer.


:

Hello,

I've done a lot of browsing on these forums and playing
around
with

 

[LoboNetwork]

Definitely wont forget it. Learn something new everyday.

Thanks you,
Lobo

I figured that was a typo.

I'm glad you were able to analyse the problem methodically and figure the
solution. Sometimes you just have to stand back and challenge everything
you've done - often a frustrating, but eventually satisfying learning
experience. I bet you'll not quickly forget the importance of master/child
links and the effect they can have if they're wrong.

I think it's probably more common to discover the challenges of master/child
links when designing forms with subforms (especially when you want a
sub-sub-form to display as a columnar table), but similar challenges apply
with reports and subreports, as you've discovered.

Good luck with your development.

Regards
Geoff

Typo in my last post.

Subreport3:
Master Link: Tag2
Child Link: Tag3

should read

Subreport3:
Master Link: Tag3
Child Link: Tag3


Regards,
Lobo

Problem Solved Geoff. Your steps 1-7 helped me realize this. It was a
problem with my Master Query (Query1) and the master/child links.

This was what I had previously:

Query1 from table1 (Only contains Tag)
Query2 from table2 (contains Tag1 and various other fields)
Query2 from table3 (contains Tag2 and various other fields)
Query2 from table4 (contains Tag3 and various other fields)

The relationship was as follows:
Query1 Tag was linked to Query2 Tag1
Query1 Tag was linked to Query3 Tag2
Query1 Tag was linked to Query4 Tag3

My master report was sourced from Query1

The subreports were as follows:

Subreport1:
Master Link: Tag
Child Link: Tag1

Subreport2:
Master Link: Tag
Child Link: Tag2

Subreport3:
Master Link: Tag
Child Link: Tag3


What I did to fix this problem was:
- Changed Query1 to get data from table1/2/3/4
- Update the master/child links in the subreports to be:

Subreport1:
Master Link: Tag1
Child Link: Tag1

Subreport2:
Master Link: Tag2
Child Link: Tag2

Subreport3:
Master Link: Tag2
Child Link: Tag3

The subreports did not show up blank, the data was as it should be, and
the
order was right!

Thank you very much for all your help!

Lobo

:

Geoff,

Answers to your questions:

1) Yes Queries 1-4 return records
2) Yes Query 5 returns rercords
3) Yes, Query 6 and 7 return records
4) There are matching tags
5) Seems to be fine - i'm see the data needed for the reports and it
matches.
6) Agreed as seen above - theorethically the report should be fine...
7) I imported the tables to form access tables but this did not make a
result - My subreports are still blank. I believe the report structure
is
working as the pages/records are right and the order is right when I
put some
"labels" to check for it. Its just seems to not be physically
displaying the
subreports.

I am stumped here...

Lobo

p.s. thank you for all your assistance to date.


:

Based on your replies above, I no longer have
just one query running the show.

Sorry, I was referring to your original post. Originally, you said
you had
one query:

I have a query [singular] that takes all the data in
these linked tables and displays them.

This made me wonder whether you had created a union query (which
would have
combined the three tables as if they were one table). That, in
turn, made
me wonder whether you'd simply created a main report as a receptacle
for the
three sub-reports (rather than as a means of linking a main report to
three
subreports). As you say, the your situation as moved on from these
scenarios. I'm now clear that there is, indeed, a main report that's
bound
to a recordsource.

I suggest the following:

1. Run queries 1, 2, 3 and 4. Do they return records?

2. Create a new query 5 based on queries 1 and 2. The relationship
you
have already created between queries 1 and 2 should automatically
appear in
the design of query 5. Drag fields in to the design grid from both
queries
1 and 2. Run query 5. Does that return records?

3. Similarly, create new queries 6 and 7, based on queries 1 and 3
and 1
and 4 respectively. Do queries 6 and 7 return records?

4. If queries 5, 6 and 7 don't return records, then determine why
not.
Are there no matching tags in queries 1 and 2, or between 1 and 3, or
between 1 and 4? This would identify the source of the problem.

5. If queries 5, 6 and 7 do return records, then examine those
records
carefully. For example, when you run, say, query 5, for Tag 1 that's
coming
from query 1, what's returning from query 2? Establish what you
should be
seeing in your first subreport.

6. My guess at this stage is that you've probably created a report
that
should work OK. The problem, I think, is with the data.

7. If all the above fails to lead anywhere, import the data into
temporary
tables in Access, so that you're dealing with real tables, instead of
linked
tables. I wouldn't have thought that'd make a difference, but,
clearly,
something odd is going on.

Geoff.




message
Geoff,

Based on your replies above, I no longer have just one query
running the
show. This is what I have now:

Type -> Record Source

Query1 -> LinkedTable1
Query2 -> LinkedTable2
Query3 -> LinkedTable3
Query4 -> LinkedTable4
MasterReport -> Query1
Subreport1 -> Query2
Subreport2 -> Query3
Subreport3 -> Query4

Relationship: Query1 contains the primary key which is linked to
foreign
keys in the other Queries. The Master and Child Links for the
subreports
came automatically from the relationship above.

I am not sure what you mean by Union Query... but these are the
SQLs for
the
Queries:

Query1:
SELECT [Table1].Tag
FROM [Table1]
ORDER BY [Table1].Tag;

Query2:
SELECT [Table2].*
FROM [Table2];

Query3:
SELECT [Table3].*
FROM [Table3];

Query4:
SELECT [Table4].*
FROM [Table4];

Any thoughts? Thanks in advance you for your assistance.

Lobo

:

Is your master report unbound (i.e. does it have a record source)?

I have a query that takes all the data in these
linked tables and displays them. I have three
"subreports" and they get their data from the query.

How did you construct this query? Was it a union query?
Can you post its SQL statement?

Geoff



message
Hi Geoff (or anyone else),

Thank you for your response. I redid the database exactly as
you
stated
above. When I preview the report, I see 9 pages... And all I
see is
the
header/footer. The detail section for those 9 pages is blank.
Any
idea
as
to what is going on here?

Lobo

:

Correction:

3 relationships, not 4.


Geoff


I've not looked at your post in detail. This is just a
thought.

If I were doing this, I'd have separate queries for each
sub-report
and