Subreport with where clause in query

  • Thread starter Thread starter Max Yaffe
  • Start date Start date
M

Max Yaffe

Dear Group,

I'm having a problem with designing queries for subreports. I'm
selected a group of records for the subreport where I want to limit
the set accoring to a field specified in the main report. It looks
somewhat like the following:

SELECT Part, Revision, RevDate, Designer
FROM tblRevision
WHERE [Part] = [reports]![MAIN_REPORT].[Part]


The subreport uses this either as an SQL query or a stored query.
Works great.

I'd like to be able to use this subreport & query in other forms so I
need to do some sort of relative addressing like:

SELECT Part, Revision, RevDate, Designer
FROM tblRevision
WHERE [Part] = Parent.[Part]

But I can't get this to work. What is the correct syntax for
specifing the parent report of a sub? Is there any clean way to do
this?

Thanks,
Max
 
Max said:
Dear Group,

I'm having a problem with designing queries for subreports. I'm
selected a group of records for the subreport where I want to limit
the set accoring to a field specified in the main report. It looks
somewhat like the following:

SELECT Part, Revision, RevDate, Designer
FROM tblRevision
WHERE [Part] = [reports]![MAIN_REPORT].[Part]


The subreport uses this either as an SQL query or a stored query.
Works great.

I'd like to be able to use this subreport & query in other forms so I
need to do some sort of relative addressing like:

SELECT Part, Revision, RevDate, Designer
FROM tblRevision
WHERE [Part] = Parent.[Part]

But I can't get this to work. What is the correct syntax for
specifing the parent report of a sub? Is there any clean way to do
this?
Click to expand...

You should be able to set the MasterLink and ChildLink properties of the
subreport control to do this for you rather than using the WHERE clause in your
query.
 
Max said:
I'm having a problem with designing queries for subreports. I'm
selected a group of records for the subreport where I want to limit
the set accoring to a field specified in the main report. It looks
somewhat like the following:

SELECT Part, Revision, RevDate, Designer
FROM tblRevision
WHERE [Part] = [reports]![MAIN_REPORT].[Part]

The subreport uses this either as an SQL query or a stored query.
Works great.

I'd like to be able to use this subreport & query in other forms so I
need to do some sort of relative addressing like:

SELECT Part, Revision, RevDate, Designer
FROM tblRevision
WHERE [Part] = Parent.[Part]

But I can't get this to work. What is the correct syntax for
specifing the parent report of a sub? Is there any clean way to do
this?
Click to expand...


Sorry, but, since queries are executed outside the report,
they must use a full reference.

OTOH, you should be able to use the subreport control's Link
Master/Child properties to accomplish what you want. In
this case I think you want to set both properties to Part
 
I know about linking but that won't work in my case. Unfortunately, I
left out a critical detail - I want to get the TOP 5 records in the
subreport, i.e. the query really looks like:

SELECT TOP 5 Part, Revision, RevDate, Designer
FROM tblRevision
WHERE [Part] = [reports]![MAIN_REPORT].[Part]


If I were to use a linked sub-report, the query would return records
for all parts and then discard all but the last 5. I want it to
return the last 5 records for the part I'm interested in, hence the
offending where clause.

Any ideas?

Max


Max said:
I'm having a problem with designing queries for subreports. I'm
selected a group of records for the subreport where I want to limit
the set accoring to a field specified in the main report. It looks
somewhat like the following:

SELECT Part, Revision, RevDate, Designer
FROM tblRevision
WHERE [Part] = [reports]![MAIN_REPORT].[Part]

The subreport uses this either as an SQL query or a stored query.
Works great.

I'd like to be able to use this subreport & query in other forms so I
need to do some sort of relative addressing like:

SELECT Part, Revision, RevDate, Designer
FROM tblRevision
WHERE [Part] = Parent.[Part]

But I can't get this to work. What is the correct syntax for
specifing the parent report of a sub? Is there any clean way to do
this?
Click to expand...


Sorry, but, since queries are executed outside the report,
they must use a full reference.

OTOH, you should be able to use the subreport control's Link
Master/Child properties to accomplish what you want. In
this case I think you want to set both properties to Part
Click to expand...
 
Since you must use a full reference for this, the only idea
I have is to park the Part value in a central location the
the query can get to.

A hidden text box on an always open form (or a hidden form
that's used for this kind of thing) is a good place.

Another way is to put the Part value in a global variable
and create a Public function that the query can use to
retrieve the value.
--
Marsh
MVP [MS Access]



Max said:
I know about linking but that won't work in my case. Unfortunately, I
left out a critical detail - I want to get the TOP 5 records in the
subreport, i.e. the query really looks like:

SELECT TOP 5 Part, Revision, RevDate, Designer
FROM tblRevision
WHERE [Part] = [reports]![MAIN_REPORT].[Part]

If I were to use a linked sub-report, the query would return records
for all parts and then discard all but the last 5. I want it to
return the last 5 records for the part I'm interested in, hence the
offending where clause.

Max said:
I'm having a problem with designing queries for subreports. I'm
selected a group of records for the subreport where I want to limit
the set accoring to a field specified in the main report. It looks
somewhat like the following:

SELECT Part, Revision, RevDate, Designer
FROM tblRevision
WHERE [Part] = [reports]![MAIN_REPORT].[Part]

The subreport uses this either as an SQL query or a stored query.
Works great.

I'd like to be able to use this subreport & query in other forms so I
need to do some sort of relative addressing like:

SELECT Part, Revision, RevDate, Designer
FROM tblRevision
WHERE [Part] = Parent.[Part]

But I can't get this to work. What is the correct syntax for
specifing the parent report of a sub? Is there any clean way to do
this?
Click to expand...
Click to expand...
Marshall said:
Sorry, but, since queries are executed outside the report,
they must use a full reference.

OTOH, you should be able to use the subreport control's Link
Master/Child properties to accomplish what you want. In
this case I think you want to set both properties to Part
Click to expand...
Click to expand...
 
Back
Top