Report based on Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

duchessofireland - 26 Feb 2008 22:54 GMT

I have a report based on a query that I created. I cannot seem to get it to
give me all the records that I am showing in the query.....I'll try to
explain better

I have a query that shows all records for my "Construction jobs". I only
want the ones to show that don't have a completed date. In my query I have
"IS Null" in the criteria of my Completed date column. When I look at my
query in "Datasheet view" it is giving me the correct information.

Now - I have a report based on that particular query. It has worked thus
far. But, it is no longer giving me the same information as it shows in the
datasheet view of my query. example:

I've noticed that I have records that have exact duplicated information
except for maybe one column (such as service type). All the information
except for that column is exactly the same as it is in 4 other records. All
of those records show up in datasheet view of my query...but do not show up
on my report? I hope this makes some kind of sense. Thanks for any help you
can give me!

Reply to this Message

John W. Vinson - 27 Feb 2008 00:23 GMT

>I have a report based on a query that I created. I cannot seem to get it to
>give me all the records that I am showing in the query.....I'll try to
[quoted text clipped - 15 lines]
>on my report? I hope this makes some kind of sense. Thanks for any help you
>can give me!

What's in the Sorting and Grouping dialog of the report? Rightclick the little
block at the upper left intersection of the rulers in report design view and
be sure you're not grouping by jobs. Perhaps you could also post the SQL view
of the query.

Signature

John W. Vinson [MVP]

Reply to this Message

duchessofireland - 27 Feb 2008 15:53 GMT

> >I have a report based on a query that I created. I cannot seem to get it to
> >give me all the records that I am showing in the query.....I'll try to
[quoted text clipped - 20 lines]
> be sure you're not grouping by jobs. Perhaps you could also post the SQL view
> of the query.

Here is the SQL view of the query:

SELECT [tblNew Services].[Date Received], [tblNew Services].[House Number],
[tblNew Services].[Street Name], [tblNew Services].City, [tblNew
Services].[Service Type], [tblNew Services].[Single/Double], [tblNew
Services].Meters, [tblNew Services].[Request Date], [tblNew
Services].[Request Method], [tblNew Services].[Reference Number], [tblNew
Services].[Start Date], [tblNew Services].Completed, [tblNew Services].PSI,
[tblNew Services].[Remarks/Notes], tblEmployees.EmployeeName
FROM ([tblNew Services] LEFT JOIN tblNewServicesConnect ON [tblNew
Services].NewServicesID = tblNewServicesConnect.NewServicesID) LEFT JOIN
tblEmployees ON tblNewServicesConnect.EmployeeID = tblEmployees.EmployeeID
WHERE ((([tblNew Services].Completed) Is Null));

> Looking at the "Sorting and Grouping" dialog......It says....

Field - Date Received
Sort Order - Ascending

Group header - yes
Group footer - No
Group on - Each Value
Group Interval - 1
Keep together - No

Reply to this Message

John W. Vinson - 27 Feb 2008 22:42 GMT

>> Looking at the "Sorting and Grouping" dialog......It says....
>
[quoted text clipped - 6 lines]
>Group Interval - 1
>Keep together - No

And I suspect that all your textboxes are in the [Date Received] header, not
in the Detail section of the report. If so, that's why you're seeing only one
of each. You're grouping by the Date Received and displaying only the first
record on that date.

Signature

John W. Vinson [MVP]

Reply to this Message

duchessofireland - 28 Feb 2008 15:43 GMT

Absolutely!!! Problem fixed .....Thank you so much!!!

> >> Looking at the "Sorting and Grouping" dialog......It says....
> >
[quoted text clipped - 11 lines]
> of each. You're grouping by the Date Received and displaying only the first
> record on that date.

Reply to this Message