Missing data in Access reports

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
 
J

Jeff Boyce

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.
 
K

KARL DEWEY

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

Preston Davis

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"));
 
K

KARL DEWEY

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...
 
P

Preston Davis

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...
 
J

Jeff Boyce

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple Reports – Same Query – Underlying Data Changes Frequently 1
Reports that sift records from different tables 0
Multiple Reports – Same Query – Underlying Data Changes Frequently 3
Dynamic crosstab reports 3
one query - many reports 6
Multiple reports from one query 3
Access 2000 reducing query parameter prompts in reports 1
filtering for reports 1

Top