Report based on Query

[duchessofireland]

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!

 

[John W. Vinson]

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!

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.

 

[duchessofireland]

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.

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

 

[John W. Vinson]

Field - Date Received
Sort Order - Ascending

Group header - yes
Group footer - No
Group on - Each Value
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.

 

[duchessofireland]

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

Field - Date Received
Sort Order - Ascending

Group header - yes
Group footer - No
Group on - Each Value
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.