D
Don
It might be beneficial to describe the problem before going into details of
the problem .......
The main report lists details about all positions within the company.
Included is a list of people who are assigned to that position. Normally,
only one person is assigned, but sometimes due to promotions, retirements,
transfers, etc two people might be assigned to the same position. Each
position in the position table has a unique ID number (in field PositionID),
and the record for each individual has a position field (PositionID) in
which the appropriate position number is placed.
Currently the report uses a SQL string to get the position data for the
report (Me.RecordSource = strSQL) from a table called tblPosition. On the
report is a sub-report that has information about the employees assigned to
the position which gets its data from a table called tblPersonnel. The
sub-form is linked to the main form using the PositionID field of the SQL in
the main form and PositionID field of tblPersonnel which is the data source
for the sub-form.
The folks that use this report have asked for some variations of this
report. For example, a report containing only cases where the position is
assigned to a particular department (in tblPosition) and the employee is
assigned to a different department (in tblPersonnel). To get this data I
have created an SQL query that provides all the results. However, the
sub-form is still getting its data directly from tblPersonnel, so if the
position has two people assigned to it, one in the same department as the
position and the other in a different department, both are listed!
What I am thinking, but don't know how to proceed is to have the sub-form
only use data flowed down to it from the main form. The problem being how
to link the sub-form to that data and not tblPersonnel.
Am I barking up the wrong tree here? Any ideas?
Thanks!
Don
the problem .......
The main report lists details about all positions within the company.
Included is a list of people who are assigned to that position. Normally,
only one person is assigned, but sometimes due to promotions, retirements,
transfers, etc two people might be assigned to the same position. Each
position in the position table has a unique ID number (in field PositionID),
and the record for each individual has a position field (PositionID) in
which the appropriate position number is placed.
Currently the report uses a SQL string to get the position data for the
report (Me.RecordSource = strSQL) from a table called tblPosition. On the
report is a sub-report that has information about the employees assigned to
the position which gets its data from a table called tblPersonnel. The
sub-form is linked to the main form using the PositionID field of the SQL in
the main form and PositionID field of tblPersonnel which is the data source
for the sub-form.
The folks that use this report have asked for some variations of this
report. For example, a report containing only cases where the position is
assigned to a particular department (in tblPosition) and the employee is
assigned to a different department (in tblPersonnel). To get this data I
have created an SQL query that provides all the results. However, the
sub-form is still getting its data directly from tblPersonnel, so if the
position has two people assigned to it, one in the same department as the
position and the other in a different department, both are listed!
What I am thinking, but don't know how to proceed is to have the sub-form
only use data flowed down to it from the main form. The problem being how
to link the sub-form to that data and not tblPersonnel.
Am I barking up the wrong tree here? Any ideas?
Thanks!
Don