Access Access 2003 Report

[kareng]

Hi, I have an Access 2003 report that pulls from several tables. I am trying to figure out why the report is not returning all records. Here are the table properties:

SELECT tblTenant.[General Fund %], tblTenant.BUDGET, tblTenant.TENANT, tblTenant.ADDRESS AS tblTenant_ADDRESS, tblTenant.CSZ, tblTenant.EMPLOYEES, tblTenant.ADA, tblTenant.[PROPERTY SURVEY], [Current Rent Rates].RentalRate, [Current Rent Rates].[Sq Ft Rate], tblAgent.NAME AS tblAgent_NAME, tblAgent.ADDRESS AS tblAgent_ADDRESS, tblDept.DEPTABR, tblLease.REGION, tblLease.[SQ FT], tblLease.[START DATE], tblLease.[END DATE], tblLease.[TIME OF RENEWAL], tblLease.CUSTODIAL, tblLease.UTILITIES, tblLessor.NAME AS tblLessor_NAME, tblLessor.ADDRESS AS tblLessor_ADDRESS, tblLessor.[CITY STATE ZIP] FROM (((tblLessor INNER JOIN (tblAgent INNER JOIN tblLease ON tblAgent.AGENTID=tblLease.AgentID) ON tblLessor.LESSORID=tblLease.LessorID) INNER JOIN (tblDept INNER JOIN tblTenant ON tblDept.DEPTID=tblTenant.DeptID) ON tblLease.LeaseID=tblTenant.LeaseID) INNER JOIN tblRents ON tblTenant.LeaseID=tblRents.LeaseID) INNER JOIN [Current Rent Rates] ON tblRents.RentID=[Current Rent Rates].RentID;

I have been wracking my brain trying to figure out why it is not returning more than half of the records in [Current Rent Rates], and can't seem to figure it out. Please help!!

 

[alow]

Have you tried running the query itself to see if everything shows up in the query? If the query works and they aren't showing up in the report, it's probably a formatting issue. If the query has the same problem, you'll need help from someone who is better at SQL than I.

 

[kareng]

Have you tried running the query itself to see if everything shows up in the query? If the query works and they aren't showing up in the report, it's probably a formatting issue. If the query has the same problem, you'll need help from someone who is better at SQL than I.

Yes, the make-table query runs just fine and returns the records included in [Current Rent Rates]. I get about half of the records returned once I run the report.

 

[alow]

A couple of questions come from that, and a suggestion or two. Why are you using a make-table query if you just need to pull data for a report? You can use a select query just to pull the data and base the report on that. I've never tried using a make-table query as a data source for a report, so I'm not sure if there are any caveats to that. What I would try to do is create a very simple report based off of the query, designed as just a list, with 1 field, just to see if it pulls all the records in. Another thing to check with reports to see if it could be a formatting issue is to double check paper sizes, margins, report widths to make sure nothing is running out of your view.

 

[kareng]

A couple of questions come from that, and a suggestion or two. Why are you using a make-table query if you just need to pull data for a report? You can use a select query just to pull the data and base the report on that. I've never tried using a make-table query as a data source for a report, so I'm not sure if there are any caveats to that. What I would try to do is create a very simple report based off of the query, designed as just a list, with 1 field, just to see if it pulls all the records in. Another thing to check with reports to see if it could be a formatting issue is to double check paper sizes, margins, report widths to make sure nothing is running out of your view.

I am using the make-table query because I am using this table in several other reports/queries and need the information in a table. I have checked all of the formatting issues, and other reports I am running pull all of the records from this table. I have checked all of my relationships, as well. I will try your suggestion of a simple report and see how that works.

Ok, I tried the simple report, and it pulls all of the records, too. So, I figure there must be something conflicting when I put all of this information together....