Reports not showing all records

[Guest]

Access 2003

I have a database where data is entered using a form, which contains
subforms. The reports were created after I had about 45 records and work
fine for those records however any new entries added are not showing up in
any of the reports.

For example... my database records action requests, action and review. My
report is a summary of this data. The parameters are set to enter the
request number and the report should show the data for that record only.
With all records added since the development of the reports, the report shows
up but there is no data.

Can anyone help - thank you in advance
Sophie

 

[Dirk Goldgar]

Access 2003

I have a database where data is entered using a form, which contains
subforms. The reports were created after I had about 45 records and
work fine for those records however any new entries added are not
showing up in any of the reports.

For example... my database records action requests, action and
review. My report is a summary of this data. The parameters are set
to enter the request number and the report should show the data for
that record only. With all records added since the development of the
reports, the report shows up but there is no data.

Can anyone help - thank you in advance
Sophie

My best guess is that there is something in the report's recordsource
query that is excluding the new records. It could be a criterion
applied, or a table that is being joined on a field that is Null, or
something I can't guess at. Of course, if the report doesn't have a
query or SQL statement for its RecordSource property, this guess is
wrong. But if it does, please post the SQL of the query.

 

[Guest]

My best guess is that there is something in the report's recordsource

query that is excluding the new records. It could be a criterion
applied, or a table that is being joined on a field that is Null, or
something I can't guess at. Of course, if the report doesn't have a
query or SQL statement for its RecordSource property, this guess is
wrong. But if it does, please post the SQL of the query.

The report is linked to a query - SQL is below. I don't know SQL so it's
double-dutch to me.

SELECT CIRequests.CIRequestID, CIRequests.Date, CIRequests.[Action
Required], CIRequests.Reasons, CIActions.CIActionID, CIActions.[CI Action],
CIActions.[Outcome ID], CIActions.[Staff ID], CIActions.[Completion date],
ReviewofActions.[Actions Complete?]
FROM Staff INNER JOIN (CIRequests INNER JOIN (CIActions INNER JOIN
ReviewofActions ON CIActions.CIActionID = ReviewofActions.CIActionID) ON
CIRequests.CIRequestID = CIActions.CIRequestID) ON Staff.[Staff ID] =
CIActions.[Staff ID]
WHERE (((CIRequests.CIRequestID)=[enter requets ID number]));

 

[Guest]

I have made another discovery. I have three forms to enter data (for
different stages - Requests, Action and review). The action form includes
requests (they can be entered here too) and the Review form includes all
compoenents (and all data can be entered there too). It seems that if i
enter data directly into the final form (review), instead of going through
each step (as I would like others to do) that the records appear on the
report. If I enter each section into its relevant form, it doesn't. The
info still all shows in all of the forms.

If I have to I can remove the first two forms, but I would like to have a
sequence because some staff only need the requests section and management
require the others.

Any thoughts???

 

[John Vinson]

I have made another discovery. I have three forms to enter data (for
different stages - Requests, Action and review). The action form includes
requests (they can be entered here too) and the Review form includes all
compoenents (and all data can be entered there too). It seems that if i
enter data directly into the final form (review), instead of going through
each step (as I would like others to do) that the records appear on the
report. If I enter each section into its relevant form, it doesn't. The
info still all shows in all of the forms.

If I have to I can remove the first two forms, but I would like to have a
sequence because some staff only need the requests section and management
require the others.

The first thing to realize is:

Your Forms DON'T contain any data.

You're *not* entering data into the Form; you're entering data *into a
Table*, using the Form as a tool.

There's absolutely nothing wrong with using three different forms (for
different users or different purposes) to enter data into one table.
Of course, all three forms will display whatever data is now in that
table, regardless of how it got there - from the same form, a
different form, from an Append query, from directly entering data into
the table datasheet, or whatever.

John W. Vinson[MVP]

 

[Guest]

Yes, I know that - I used the wrong words. My concern is that the report is
not printing what is in the TABLE. I've even tried entering data directly
into the table to see if that works - it doesn't. As I said, data entered
into a table using the one form that links all tables is the only way that I
am able to get records to show up on the report (did I get the terminology
right there??) I'm still learning and value input into this problem.

 

[Dirk Goldgar]

My best guess is that there is something in the report's recordsource
query that is excluding the new records. It could be a criterion
applied, or a table that is being joined on a field that is Null, or
something I can't guess at. Of course, if the report doesn't have a
query or SQL statement for its RecordSource property, this guess is
wrong. But if it does, please post the SQL of the query.

The report is linked to a query - SQL is below. I don't know SQL so
it's double-dutch to me.

SELECT CIRequests.CIRequestID, CIRequests.Date, CIRequests.[Action
Required], CIRequests.Reasons, CIActions.CIActionID, CIActions.[CI
Action], CIActions.[Outcome ID], CIActions.[Staff ID],
CIActions.[Completion date], ReviewofActions.[Actions Complete?]
FROM Staff INNER JOIN (CIRequests INNER JOIN (CIActions INNER JOIN
ReviewofActions ON CIActions.CIActionID = ReviewofActions.CIActionID)
ON CIRequests.CIRequestID = CIActions.CIRequestID) ON Staff.[Staff
ID] = CIActions.[Staff ID]
WHERE (((CIRequests.CIRequestID)=[enter requets ID number]));

That query is joining four tables -- Staff, CIRequests, CIActions, and
ReviewofActions -- and it's using INNER JOINs to do it. That means that
it will only return records from any of the tables when those records
have corresponding records in all 4 of the tables. If, for example, you
have a record in CIActions that doesn't have at least one record in
ReviewofActions with the same value for CIActionID, you won't see that
record in the report. My guess is that the arrangements of your forms
and subforms is such that either (a) not all the records are being
created in the various tables, or (b) not all the linking key fields in
these records are being filled in.

If you look at the records that are actually in these various tables, do
you see linking fields that are Null (blank)? If so, check to make sure
that your subforms have their Link Master Fields and Link Child Fields
properties set properly so as to automatically fill in the linking keys.

 

[Guest]

Thanks Dirk

That's got it. I recognised that it needed data in 3 of the tables but
didn't think about the link to the fourth. The ones not showing up were
those where there was no staff member allocated to the action. Once I added
a staff name from the staff table it appeared!

Thanks again

Sophie

My best guess is that there is something in the report's recordsource
query that is excluding the new records. It could be a criterion
applied, or a table that is being joined on a field that is Null, or
something I can't guess at. Of course, if the report doesn't have a
query or SQL statement for its RecordSource property, this guess is
wrong. But if it does, please post the SQL of the query.

The report is linked to a query - SQL is below. I don't know SQL so
it's double-dutch to me.

SELECT CIRequests.CIRequestID, CIRequests.Date, CIRequests.[Action
Required], CIRequests.Reasons, CIActions.CIActionID, CIActions.[CI
Action], CIActions.[Outcome ID], CIActions.[Staff ID],
CIActions.[Completion date], ReviewofActions.[Actions Complete?]
FROM Staff INNER JOIN (CIRequests INNER JOIN (CIActions INNER JOIN
ReviewofActions ON CIActions.CIActionID = ReviewofActions.CIActionID)
ON CIRequests.CIRequestID = CIActions.CIRequestID) ON Staff.[Staff
ID] = CIActions.[Staff ID]
WHERE (((CIRequests.CIRequestID)=[enter requets ID number]));

That query is joining four tables -- Staff, CIRequests, CIActions, and
ReviewofActions -- and it's using INNER JOINs to do it. That means that
it will only return records from any of the tables when those records
have corresponding records in all 4 of the tables. If, for example, you
have a record in CIActions that doesn't have at least one record in
ReviewofActions with the same value for CIActionID, you won't see that
record in the report. My guess is that the arrangements of your forms
and subforms is such that either (a) not all the records are being
created in the various tables, or (b) not all the linking key fields in
these records are being filled in.

If you look at the records that are actually in these various tables, do
you see linking fields that are Null (blank)? If so, check to make sure
that your subforms have their Link Master Fields and Link Child Fields
properties set properly so as to automatically fill in the linking keys.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[John Vinson]

Yes, I know that - I used the wrong words. My concern is that the report is
not printing what is in the TABLE.

Sorry... I did misinterpret your post!

Open the Report in design view, and investigate its Recordsource
property. Is that a Query? If you open the query as a datasheet (try
clicking the ... icon by the property to see) and see if it returns
all the records you expect. If it does not, post the SQL view of the
query here - there may be an incorrect criterion or join.

John W. Vinson[MVP]

 

[John Vinson]

Once I added
a staff name from the staff table it appeared!

In addition (or instead) you can use a LEFT JOIN to that table (to
show all records in the parent table and matching records in the staff
table) rather than the default INNER JOIN.

John W. Vinson[MVP]

 

[Guest]

How would I do that?? and would it still allow me to have parameter (ie
[print query number])??

 

[John Vinson]

How would I do that??

Select the Join Line in query design view, and choose the option that
says "Show all records in <parent table> and matching records in

and would it still allow me to have parameter (ie
[print query number])??

Sure.

John W. Vinson[MVP]

 

[Guest]

Thanks John - that's got it so that I can print the report at any stage of
the process without having to have data entered into all the linking fields.

Thanks again
Sophie

How would I do that??

Select the Join Line in query design view, and choose the option that
says "Show all records in <parent table> and matching records in

and would it still allow me to have parameter (ie
[print query number])??

Sure.

John W. Vinson[MVP]