Report works ok but not subreport

[Guest]

Report reads from a query w/ a single parameter (user enters to search for
less than or equal to date). It produces company information. Report
includes a subreport that reads from another query; it includes project
number for each company and same date field.

The report filters the necessary information but the subreport doesn't. The
subreport includes all of the project numbers for each company that the main
report produces.

I want the subreport to include only those project numbers that match the
parameter entered in the main report. Please advise as to how i can I fix it
so that the subreport will read the parameter entered in the main report?

 

[Guest]

Hi Karen,

What you describe is a classic symptom of missing (or incorrect) Link Master
Field / Link Child Field properties. Open your main report in design view.
Press F4 to display the Properties dialog, if it is not already displayed.
Select the control that serves as the container for holding the subreport
(not the subreport itself). Click on the Data tab of the Properties dialog.
The Link Master Field is typically the primary key (or other uniquelly
indexed field) available in the recordsource for the main report. The Link
Child Field needs to be an associated foreign key field in the recordsource
for the subreport.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Guest]

Thanks Tom,
Both Link Master Field and Link Child Field are set to company name.
However, the company name is not a unique field in the record source. I set
the main query so that only unique company names are shown (i.e., it shows
once). But for each of these companies there may be many projects. So for
instance,

Main Report
Company A Subreport: projects 1, 2, 3, 4

Company B Subreport: projects 2, 3, 4

When I enter the date parameter, the main report gives only companies that
have projects before that date. But some of the the projects it brings up
are dated after the date parameter.

any suggestions?

 

[Guest]

Hi Karen,

First, do all of your tables include a primary key or unique index? Have you
created relationships between the tables (Tools > Relationships view)? If so,
then you likely related the primary key of the Customer's table to a similar
field in the Projects table that serves as the foreign key. If this
discussion *sounds* foreign to you, then you need to review some Database
Design documents. (I don't know your level of understanding, based on the
posts so far).

I set the main query so that only unique company names are shown...

Does this main query include a field that is set as the primary key? If yes,
then set this field as the Link Master field. If no, then you need to fix
this problem first.

Also, does the recordsource (table or query) for the subreport include the
foreign key field? If yes, set this as the Link Child field. If no, then you
need to fix this problem first.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Guest]

Hi Tom,
I think I understand all of this but maybe I've been unclear in my prior
explanation.

I have three tables: CON-16, Issued & Upcoming
CON-16 - Company is the primary key, also contains Formal Name as a field
Issued - seems to have no primary key, contains Company, contain Project
Number field
Upcoming - ID is the primary key, contains Project Number & Bid Opening Date
as fields

(When this database was set up, a formal name field and a company name field
were both created. the company name is a little more "informal" & my boss
doesn't want to change this. Also, the fact that there is no primary key in
Issued table was done before my time here.)

I've linked the tables for the main query. The connection is like this -
Company in Issued to Company in CON-16, Project Number in Issued & Upcoming.
It produces Formal names of all companies that have projects that fall before
a specified bid opening date. This query works perfectly.

For the secondary query I used the same three tables; it produces all Formal
names & their associated projects - no limitations.

I have a main report: It gives me the formal names of all companies that
have projects that fall before a specified date.
I have a subreport: It gives me all projects that are associated w/ the
formal names.
In that report I've set the Formal Name as the Master Link & the Child Link
fields on the subreport data sheet.

As I said before, the main report produces the correct formal companies but
the project numbers are not being limited to the right bid opening dates.

For instance: I have a company that has a bid opening date of 05/02/07. It
has no projects before this time. Rightfully so, it doesn't show up on the
report when I set the parameter to 04/30/07.

But I have a company that has projects w/ bid opening dates of 04/28/07 &
05/03/07. The report will not exclude the project dated 05/03/07 when I set
the parameter to 04/30/07. It always shows both projects.

 

[Guest]

Hi Karen,

I have three tables: CON-16, Issued & Upcoming

You would be best to not use an special characters in the names of anything
that a developer can assign a name to (fields, tables, queries, forms,
reports, etc., along with the names of controls on forms and reports). Here
is a Microsoft KB (Knowledge Base) article that discusses this in more detail:

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Otherwise, you will need to always remember to bracket the name of the
CON-16 table, as in [CON-16].

Issued - seems to have no primary key, contains Company, contain Project
Number field

This sounds like a join or linking table to me. This type of table is used
to define a many-to-many (M:N) relationship. It has the foreign key sides of
two one-to-many (1:M) relationships. In English, it sounds like the following
applies:

A Customer can have many projects (Upcoming records)
and
A Project (Upcoming record) can apply to many customers.

Do both of these sentences correctly describe the relationships between your
tables?

The fact that the join table has no primary key could be problematic,
especially if a multifield unique index has not been assigned on the Company
and Project Number fields. Without a combined field primary key, or a unique
index set on the two fields, there would be nothing to limit a person from
entering duplicate records into this table. In general, every table should
have a primary key (PK). A PK can include more than one field.

Based on what I've read so far, I believe you will want to have the Company
field from [CON-16] as the Link Master Field (so it must be in the recordset
for the report) and the Company field from the Issued table as the Link Child
field. This field must be available in the recordsource for the subreport.

Also, the fact that there is no primary key in Issued table was done
before my time here.)

Now would be a good time to fix this problem.

For the secondary query I used the same three tables; it produces all
Formal names & their associated projects - no limitations.

You can likely eliminate the [CON-16] table from this query, if it is the
one that serves as the recordsource for your subreport.

In that report I've set the Formal Name as the Master Link & the
Child Link fields on the subreport data sheet.

Use the primary key and foreign key, which in this case, appears to be the
Company field from [CON-16] as the Link Master Field and the Company field
from the Issued table as the Link Child field.

If you still cannot get it to work, then I invite you to send me a compacted
and zipped copy of your database. You can use some fake company names if
needed. If you are interested, send me a private e-mail message with a valid
reply-to address. My e-mail address is available at the bottom of the
contributor's page indicated below. Please do not post your e-mail address
(or mine) to a newsgroup reply. Doing so will only attract the unwanted
attention of spammers.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Hi Tom,
I think I understand all of this but maybe I've been unclear in my prior
explanation.

I have three tables: CON-16, Issued & Upcoming

CON-16 - Company is the primary key, also contains Formal Name as a field

 

[Guest]

Thanks a lot for all the trouble Tom,
I decided to just get rid of the parameter in the query. I added a new form
w/ an unbound dialogue box for the date to search through. Then, I put the
form parameter on both the main and the secondary queries. I was able to
limit the data in both the report and the subreport by doing this. The
report now produces the correct information.

--
Thanks, Karen

Hi Karen,

I have three tables: CON-16, Issued & Upcoming

You would be best to not use an special characters in the names of anything
that a developer can assign a name to (fields, tables, queries, forms,
reports, etc., along with the names of controls on forms and reports). Here
is a Microsoft KB (Knowledge Base) article that discusses this in more detail:

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Otherwise, you will need to always remember to bracket the name of the
CON-16 table, as in [CON-16].

Issued - seems to have no primary key, contains Company, contain Project
Number field

This sounds like a join or linking table to me. This type of table is used
to define a many-to-many (M:N) relationship. It has the foreign key sides of
two one-to-many (1:M) relationships. In English, it sounds like the following
applies:

A Customer can have many projects (Upcoming records)
and
A Project (Upcoming record) can apply to many customers.

Do both of these sentences correctly describe the relationships between your
tables?

The fact that the join table has no primary key could be problematic,
especially if a multifield unique index has not been assigned on the Company
and Project Number fields. Without a combined field primary key, or a unique
index set on the two fields, there would be nothing to limit a person from
entering duplicate records into this table. In general, every table should
have a primary key (PK). A PK can include more than one field.

Based on what I've read so far, I believe you will want to have the Company
field from [CON-16] as the Link Master Field (so it must be in the recordset
for the report) and the Company field from the Issued table as the Link Child
field. This field must be available in the recordsource for the subreport.

Also, the fact that there is no primary key in Issued table was done
before my time here.)

Now would be a good time to fix this problem.

For the secondary query I used the same three tables; it produces all
Formal names & their associated projects - no limitations.

You can likely eliminate the [CON-16] table from this query, if it is the
one that serves as the recordsource for your subreport.

In that report I've set the Formal Name as the Master Link & the
Child Link fields on the subreport data sheet.

Use the primary key and foreign key, which in this case, appears to be the
Company field from [CON-16] as the Link Master Field and the Company field
from the Issued table as the Link Child field.

If you still cannot get it to work, then I invite you to send me a compacted
and zipped copy of your database. You can use some fake company names if
needed. If you are interested, send me a private e-mail message with a valid
reply-to address. My e-mail address is available at the bottom of the
contributor's page indicated below. Please do not post your e-mail address
(or mine) to a newsgroup reply. Doing so will only attract the unwanted
attention of spammers.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Hi Tom,
I think I understand all of this but maybe I've been unclear in my prior
explanation.

I have three tables: CON-16, Issued & Upcoming

CON-16 - Company is the primary key, also contains Formal Name as a field

Issued - seems to have no primary key, contains Company, contain Project
Number field

Upcoming - ID is the primary key, contains Project Number & Bid Opening Date
as fields

(When this database was set up, a formal name field and a company name field
were both created. the company name is a little more "informal" & my boss
doesn't want to change this. Also, the fact that there is no primary key in
Issued table was done before my time here.)

I've linked the tables for the main query. The connection is like this -
Company in Issued to Company in CON-16, Project Number in Issued & Upcoming.
It produces Formal names of all companies that have projects that fall before
a specified bid opening date. This query works perfectly.

For the secondary query I used the same three tables; it produces all Formal
names & their associated projects - no limitations.

I have a main report: It gives me the formal names of all companies that
have projects that fall before a specified date.
I have a subreport: It gives me all projects that are associated w/ the
formal names.
In that report I've set the Formal Name as the Master Link & the Child Link
fields on the subreport data sheet.

As I said before, the main report produces the correct formal companies but
the project numbers are not being limited to the right bid opening dates.

For instance: I have a company that has a bid opening date of 05/02/07. It
has no projects before this time. Rightfully so, it doesn't show up on the
report when I set the parameter to 04/30/07.

But I have a company that has projects w/ bid opening dates of 04/28/07 &
05/03/07. The report will not exclude the project dated 05/03/07 when I set
the parameter to 04/30/07. It always shows both projects.