Missing data in Access reports

  • Thread starter Thread starter Preston Davis
  • Start date Start date
P

Preston Davis

I have several diffrent reports that I run, each off there own query. On a
few reports there will be a line or several lines of data that does not
appear on the report.

The database has records stored in multible tables. Records are based on a
property address from one table and linked to a 'Quantities' table that have
40 or so materials listed.

Trouble with one such report is that the query should pull all material
quatities for each proerty record. Trouble is that one specific quantity
field does not display on the report. But when I run a report for just the
one quantity it will show up.

I've examined the query closely and see no obvious
 
If there's a question, I missed it.

If you'll post the SQL of your query, and a description of the table
structure you're using, folks here might be able to offer ideas.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Post the query SQL by opening in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.
 
SELECT tblProperties.PropertyID, tblProperties.[Prop Cord],
tblProperties.Year, tblProperties.[Street Num], tblProperties.[Street Name],
tblDetails.DetailType, tblDetails.DetailNote, tblDetails.DetailValue,
tblDetails.[DetailY/N], [Street Num] & " " & [Street Name] AS Address,
tblTypes.Group
FROM tblTypes INNER JOIN (tblProperties INNER JOIN tblDetails ON
tblProperties.PropertyID = tblDetails.PropertyID) ON tblTypes.Type =
tblDetails.DetailType
WHERE (((tblProperties.[Prop Cord]) Like [Forms]![PropertyMain]![PickCoor] &
"*") AND ((tblProperties.Year)=[Forms]![PropertyMain]![PickYear]) AND
((tblDetails.DetailValue)>0) AND ((tblTypes.Group)="Quantities"));
 
Try this --
SELECT tblProperties.PropertyID, tblProperties.[Prop Cord],
tblProperties.Year, tblProperties.[Street Num], tblProperties.[Street Name],
tblDetails.DetailType, tblDetails.DetailNote, tblDetails.DetailValue,
tblDetails.[DetailY/N], [Street Num] & " " & [Street Name] AS Address,
tblTypes.Group
FROM tblTypes LEFT JOIN (tblProperties LEFT JOIN tblDetails ON
tblProperties.PropertyID = tblDetails.PropertyID) ON tblTypes.Type =
tblDetails.DetailType
WHERE (((tblProperties.[Prop Cord]) Like [Forms]![PropertyMain]![PickCoor] &
"*") AND ((tblProperties.Year)=[Forms]![PropertyMain]![PickYear]) AND
((tblDetails.DetailValue)>0) AND ((tblTypes.Group)="Quantities"));
--
Build a little, test a little.


Preston Davis said:
SELECT tblProperties.PropertyID, tblProperties.[Prop Cord],
tblProperties.Year, tblProperties.[Street Num], tblProperties.[Street Name],
tblDetails.DetailType, tblDetails.DetailNote, tblDetails.DetailValue,
tblDetails.[DetailY/N], [Street Num] & " " & [Street Name] AS Address,
tblTypes.Group
FROM tblTypes INNER JOIN (tblProperties INNER JOIN tblDetails ON
tblProperties.PropertyID = tblDetails.PropertyID) ON tblTypes.Type =
tblDetails.DetailType
WHERE (((tblProperties.[Prop Cord]) Like [Forms]![PropertyMain]![PickCoor] &
"*") AND ((tblProperties.Year)=[Forms]![PropertyMain]![PickYear]) AND
((tblDetails.DetailValue)>0) AND ((tblTypes.Group)="Quantities"));

--
Preston


KARL DEWEY said:
Post the query SQL by opening in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.
Click to expand...
Click to expand...
 
I discovered the error, it was in the database table.

Thank you for the help.
--
Preston


KARL DEWEY said:
Try this --
SELECT tblProperties.PropertyID, tblProperties.[Prop Cord],
tblProperties.Year, tblProperties.[Street Num], tblProperties.[Street Name],
tblDetails.DetailType, tblDetails.DetailNote, tblDetails.DetailValue,
tblDetails.[DetailY/N], [Street Num] & " " & [Street Name] AS Address,
tblTypes.Group
FROM tblTypes LEFT JOIN (tblProperties LEFT JOIN tblDetails ON
tblProperties.PropertyID = tblDetails.PropertyID) ON tblTypes.Type =
tblDetails.DetailType
WHERE (((tblProperties.[Prop Cord]) Like [Forms]![PropertyMain]![PickCoor] &
"*") AND ((tblProperties.Year)=[Forms]![PropertyMain]![PickYear]) AND
((tblDetails.DetailValue)>0) AND ((tblTypes.Group)="Quantities"));
--
Build a little, test a little.


Preston Davis said:
SELECT tblProperties.PropertyID, tblProperties.[Prop Cord],
tblProperties.Year, tblProperties.[Street Num], tblProperties.[Street Name],
tblDetails.DetailType, tblDetails.DetailNote, tblDetails.DetailValue,
tblDetails.[DetailY/N], [Street Num] & " " & [Street Name] AS Address,
tblTypes.Group
FROM tblTypes INNER JOIN (tblProperties INNER JOIN tblDetails ON
tblProperties.PropertyID = tblDetails.PropertyID) ON tblTypes.Type =
tblDetails.DetailType
WHERE (((tblProperties.[Prop Cord]) Like [Forms]![PropertyMain]![PickCoor] &
"*") AND ((tblProperties.Year)=[Forms]![PropertyMain]![PickYear]) AND
((tblDetails.DetailValue)>0) AND ((tblTypes.Group)="Quantities"));

--
Preston


KARL DEWEY said:
Post the query SQL by opening in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.
--
Build a little, test a little.


:

I have several diffrent reports that I run, each off there own query. On a
few reports there will be a line or several lines of data that does not
appear on the report.

The database has records stored in multible tables. Records are based on a
property address from one table and linked to a 'Quantities' table that have
40 or so materials listed.

Trouble with one such report is that the query should pull all material
quatities for each proerty record. Trouble is that one specific quantity
field does not display on the report. But when I run a report for just the
one quantity it will show up.

I've examined the query closely and see no obvious
Click to expand...
Click to expand...
Click to expand...
 
If you'll post back what you found, it could help others when they have a
similar issue.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Preston Davis said:
I discovered the error, it was in the database table.

Thank you for the help.
--
Preston


KARL DEWEY said:
Try this --
SELECT tblProperties.PropertyID, tblProperties.[Prop Cord],
tblProperties.Year, tblProperties.[Street Num], tblProperties.[Street
Name],
tblDetails.DetailType, tblDetails.DetailNote, tblDetails.DetailValue,
tblDetails.[DetailY/N], [Street Num] & " " & [Street Name] AS Address,
tblTypes.Group
FROM tblTypes LEFT JOIN (tblProperties LEFT JOIN tblDetails ON
tblProperties.PropertyID = tblDetails.PropertyID) ON tblTypes.Type =
tblDetails.DetailType
WHERE (((tblProperties.[Prop Cord]) Like
[Forms]![PropertyMain]![PickCoor] &
"*") AND ((tblProperties.Year)=[Forms]![PropertyMain]![PickYear]) AND
((tblDetails.DetailValue)>0) AND ((tblTypes.Group)="Quantities"));
--
Build a little, test a little.


Preston Davis said:
SELECT tblProperties.PropertyID, tblProperties.[Prop Cord],
tblProperties.Year, tblProperties.[Street Num], tblProperties.[Street
Name],
tblDetails.DetailType, tblDetails.DetailNote, tblDetails.DetailValue,
tblDetails.[DetailY/N], [Street Num] & " " & [Street Name] AS Address,
tblTypes.Group
FROM tblTypes INNER JOIN (tblProperties INNER JOIN tblDetails ON
tblProperties.PropertyID = tblDetails.PropertyID) ON tblTypes.Type =
tblDetails.DetailType
WHERE (((tblProperties.[Prop Cord]) Like
[Forms]![PropertyMain]![PickCoor] &
"*") AND ((tblProperties.Year)=[Forms]![PropertyMain]![PickYear]) AND
((tblDetails.DetailValue)>0) AND ((tblTypes.Group)="Quantities"));

--
Preston


:

Post the query SQL by opening in design view, click on VIEW - SQL
View,
highlight all, copy, and paste in a post.
--
Build a little, test a little.


:

I have several diffrent reports that I run, each off there own
query. On a
few reports there will be a line or several lines of data that does
not
appear on the report.

The database has records stored in multible tables. Records are
based on a
property address from one table and linked to a 'Quantities' table
that have
40 or so materials listed.

Trouble with one such report is that the query should pull all
material
quatities for each proerty record. Trouble is that one specific
quantity
field does not display on the report. But when I run a report for
just the
one quantity it will show up.

I've examined the query closely and see no obvious
Click to expand...
Click to expand...
Click to expand...
 
Back
Top