limiting records in report based on value of data found in sub-report 2

[uscitizen]

i have a form and correlated sub-form in my database. IRB# is the linking parent/child field. my parent table has pk IRB# and my child table has IRB# and PRMS_Review.

i am trying to create a report which lets the user see only those records which have values of 'PRC_Meeting_Date' occuring before some date which the user would presumably input. 'PRC_Meeting_Date' is a field on my child table.

it can be assumed that each record in the parent can have more than one record in the child. each record in the child is a reflection of something that happened to the parent (in this case it's an contract which can go through several iterations and reviews and each child record chronicles when these reviews may have occurred).

the consumer of the report would have to have a report that displayed only those IRB# records which had 'PRC_Meeting_Dates' occurring up to some value the user would input.



“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[dhookom]

Include the PRC_Meeting_Dates in your main report's record source. Place a criteria under this column and change the query into a totals query. Set the Totals property of the PRC_Meeting_Dates to "Where".

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[uscitizen]

hi dhookum,

that's the gotcha in this isn't it, PRC_Meeting_Dates isn't on the parent data source/table, it's in the child's table.

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[dhookom]

Correct. But you can add the table with the field so it becomes available in the query.

Is your issue resolved or are you still looking for assistance?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[uscitizen]

well, i've been cogitating on this for a while, and i think i may have resolved it; while i await for a review of the first draft of this product from one of its consumers, here was the approach i took..

a) using a query with between this and that date on 'PRC_Meeting_Date' from the child record source,i generate a table which i am going to use as the record source for my child report (the one nested w/in the main one).

b) developed another query which uses the table created in 'a' to select records from the parent table to create a sub-set of the parent table (iiner join) selecting DISTINCT IRB#s. actually, the query is a make query which results in a separate table

c) the table from 'b' is used as the parent table for the parent/main portion of the report.

d) the IRB# fields are used to link parent/child records sources in the report.

i can't claim to understand what you were aiming at in your first reponse. a little too elliptical for the newbie in yours truly. perhaps it's too elegant and will be understood down the road, but for the time being, i'll try to use what i wrote.

thank you for trying to explain it, though.

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[dhookom]

a) I don't think you need to generate a table when a query will do.
b) Again, you could combine the query from a) with your parent table and select distinct/unique IRB#s.

This is basically the approach I first mentioned.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[uscitizen]

a) unless i'm mistaken i tried your 'a' approach and kept repeatedly getting prompted for the begin/end limits of PRC meeting date

regarding your belief that your two approaches had something in common, i.e. when you say

"Include the PRC_Meeting_Dates in your main report's record source. Place a criteria under this column and change the query into a totals query. Set the Totals property of the PRC_Meeting_Dates to "Where". "

what do you mean that is the same as

"a) I don't think you need to generate a table when a query will do.
b) Again, you could combine the query from a) with your parent table and select distinct/unique IRB#s."

at this time it may be a moot point though.

thanks anyway dhookum.


“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[cheerio]

I think this can be done by making the row source of the main form use a subquery.

I would try something like

SELECT fields FROM MainTable M
WHERE EXISTS (SELECT * FROM SubTable WHERE subID= M.MainID AND PRC_Meeting_Date BETWEEN date1 AND DAte2)

Using an ordinary join is fine for a report but tends to make forms non-updateable.

 

[dhookom]

Use a form to enter your query parameters. If you want to limit the parent table output based on the dates then use a query like:
Select Parent.[IRB#], Parent.AnyOtherField
FROM Parent INNER JOIN Child on Parent.[IRB#] = Child.[IRB#]
WHERE Child.PRC_Meeting_Dates Between Forms!frmA!txtStart and Forms!frmA!txtEnd
GROUP BY Parent.[IRB#], Parent.AnyOtherField;

Duane
MS Access MVP
Find out how to get great answers faq219-2884.