A subreport won't display if there are no related records. There are times
when it can be "fooled". Start a new thread if you have questions.

--
Duane Hookom
Microsoft Access MVP


"Perplexed" wrote:

DUANE! You are the greatest!! It works!!!!!
I'm not sure I fully understand what a "totals query" is nor do I totally
understand why adding the GROUP BY line made the difference in the sql code
for the query but none the less I got exactly what I wanted. I had to tweak
the query a little bit and add the appt date and criteria and then also make
the same change to my appointments query but I resolved the issue by making
them both the same reference (=now()-90) so that I get a rolling 90 day
period and don't have to manually change the date reference each month to
drop off the previous 30 day period. It will work for what I need to use it
for.

Again, thanks to everyone who has given me help with this report. You are
all wonderful and I've learned way more than I ever knew Access could do.
This goes way deeper than the Microsoft Certification course.

My next hurtle is to determine if it's possible to have the third table
subreport print out blank fields even if there is no corresponding data
related to the main table. If this is too complicated, I think I can live
with out it but I do love a challenge.

Thanks again Duane! I can see from other posts that you are a great
resource to the group. Take care.
================
"Duane Hookom" wrote:

Yes, base your report on a query like:
SELECT ... Customers column list ...
FROM Customers AS C INNER JOIN Appointments As A ON C.customer_id =
A.customer_id
WHERE A.cancelled = True
AND A.reason 1

But as I stated, make this into a totals query and don't include any field
in the output from the appointments table. This might be like:

SELECT CustomerID, CustomerName, Address, Phone, ...
FROM Customers AS C INNER JOIN Appointments As A ON C.customer_id =
A.customer_id
WHERE A.cancelled = True
AND A.reason 1
GROUP BY CustomerID, CustomerName, Address, Phone, ...;


--
Duane Hookom
Microsoft Access MVP


"Perplexed" wrote:

If I am understanding your statement --
My main report is based on a query that is based on the main table only. It
is pulling selected fields (name, address & phone number) only. This results
in my report listing the customer only once. This is great except that I get
all customers and I only want to see the ones that have appts in table 2 that
have reasons greater than '1'. The suggestion from another person to make a
query with the following code provided the correct results; however, once
again when I created a report from this query, the customers appear multiple
times based on the number of appts in table 2 that meet the criteria.

SELECT ... Customers column list ...
FROM Customers AS C INNER JOIN Appointments As A ON C.customer_id =
A.customer_id
WHERE A.cancelled = True
AND A.reason 1

I seem to be going in circles.

"Duane Hookom" wrote:

Apparently your main report's record source query should be a totals query
that outputs only the main tables records.
--
Duane Hookom
Microsoft Access MVP


"Perplexed" wrote:

This scenario is pretty close to what I have been struggling with. I posted
in both the reports and queries categories and have received several helpful
suggestions; however, none resolved my problem. I did learn a lot about sql
code though. In the meantime, I have based my report on the main table
selecting just the fields that I need so that I have only one instance of
each record. Then I added subreports for the two other tables which are
joined by the CustomerID field in each table. The report LOOKS great;
however, I only want to see the data for records in the main table based on
criteria in the second table; AND, I want to see the third subreport for
those same records even though there is no data in the third table to
correspond. Some of the suggestions received previously didn't work. I
followed the suggestion for making a query to select the records from the
main table based on the second table criteria which would return only the
records I wanted and got a query with exactly what I wanted; however, when I
made a report from the query, the records repeat based on the number of
records in the second table that apply to the record in the main table. Is
there any way to send my file for analyzing the codes to Duane? Any
suggestions will be greatly appreciated!!!!

Regards!

"Xfree" wrote:

I have a report that contains three sub reports everything is working
correctly as far as the output except the one page report repeats its self on
a second page with all the same info except the header. Is this a setting I
have wrong or a format issue?

Let me know if more info is needed for this issue.

Thanks