Reports not populating

[Guest]

I have a report that I built with information from about 12 different
queries. The report will work fine as long as each query brings back
information. The problem I'm having, is there are times that one of the
queries will have no information, if that happens the entire report is blank.
On my report I did add a new text field and tried to build an expression so
that if the field was blank to display something. It worked for that field,
the rest of the report was still blank. Below is the expression I used for
the text field. I'm very new at expression building so if it's real bad,
please be nice.
=IIf(IsNull([Block 1, 2_SumOfTime]),"Has not Taught ",[Block 1, 2_SumOfTime])

Any ideas? Thanks
Dave

 

[John Vinson]

I have a report that I built with information from about 12 different
queries. The report will work fine as long as each query brings back
information. The problem I'm having, is there are times that one of the
queries will have no information, if that happens the entire report is blank.

I strongly suspect that you're joining the queries using the default
Inner Join, which will indeed display nothing if any of the twelve has
a missing record. You probably need to change the join type to a Left
(or Right) Outer Join.

Could you post the SQL of the query? I know it'll be big but there's
likely a pattern we can spot (and fix).

John W. Vinson[MVP]

 

[Guest]

SELECT [Main Table].Names, [Main Table].[Block 1], Sum([Main Table].Time) AS
SumOfTime
FROM [Main Table]
GROUP BY [Main Table].Names, [Main Table].[Block 1]
HAVING ((([Main Table].Names)=[Who]) AND (([Main Table].[Block 1])=2));

I hope this is what you were looking for. This is the SQL from one of the
queries that is on the report. The other ones are the same except for the
last part will = different numbers. I've been told I'm having a problem
becuase my tables and relationships weren't built correctly. I'm starting
over from scratch but if you have any ideas on this one problem it might save
me some work. It's the only problem I'm having from making my initial
database useable. Thanks

 

[John Vinson]

SELECT [Main Table].Names, [Main Table].[Block 1], Sum([Main Table].Time) AS
SumOfTime
FROM [Main Table]
GROUP BY [Main Table].Names, [Main Table].[Block 1]
HAVING ((([Main Table].Names)=[Who]) AND (([Main Table].[Block 1])=2));

I hope this is what you were looking for. This is the SQL from one of the
queries that is on the report. The other ones are the same except for the
last part will = different numbers. I've been told I'm having a problem
becuase my tables and relationships weren't built correctly. I'm starting
over from scratch but if you have any ideas on this one problem it might save
me some work. It's the only problem I'm having from making my initial
database useable. Thanks

Well first off - if you have several queries which differ only by the
criterion (the block number) then I'd really suggest using a parameter
query instead. If you open the Report from a Form (frmCrit let's call
it), you could have two textboxes on the form named txtWho and
txtBlock; your query could be

SELECT [Main Table].Names, [Main Table].[Block 1],
Sum([Main Table].[Time]) AS SumOfTime
FROM [Main Table]
GROUP BY [Main Table].Names, [Main Table].[Block 1]
WHERE ((([Main Table].Names)=[Forms]![frmCrit]![txtWho])
AND (([Main Table].[Block 1])=[Forms]![frmCrit]![txtBlock]));

Note that I'm putting the fieldname Time in brackets - it may be best
to rename that field since it's a reserved word; and used WHERE rather
than HAVING, because the WHERE operator filters the records *before*
they're summed, and the HAVING operator does all the totals first and
then throws away any that don't match, wasting a lot of time.

As to your report being blank - if you open the Query directly,
independently of the Report, what do you see? I presume you're hoping
to see just a single row, with the selected value of [Names] and of
[Block 1]?

Just to clarify: you say "This is the SQL from one of the queries".
What is the actual Recordsource property of the Report? How are you
using multiple queries on the same report, given that a Report can
have only one recordsource at a time?

John W. Vinson[MVP]