Relationships in a query

[RipperT]

I have tblHearings (one side) and tblCharges (many side) joined on
'HearingID'. tblHearings also has a 'HearingDate' field and a 'Select'
checkbox field. I have created a form that will allow a user to check the
'Select' checkbox for the records for which they want to set up a
HearingDate, then click a button and it plugs a date into the
'HearingDate' field. I want the form to only display the Hearing data from
tblHearings for each hearing and not any tblCharges data. The problem is
that if there are multiple charges for a hearing, the hearing data is listed
once for each charge and I want the hearing to only be listed once. If I
write the query SELECT DISTINCT... it will show the hearing only once, but
then nothing happens when the user clicks a checkbox. Is there a way around
this?
Many thanks,
Rip

 

[Marshall Barton]

I have tblHearings (one side) and tblCharges (many side) joined on
'HearingID'. tblHearings also has a 'HearingDate' field and a 'Select'
checkbox field. I have created a form that will allow a user to check the
'Select' checkbox for the records for which they want to set up a
HearingDate, then click a button and it plugs a date into the
'HearingDate' field. I want the form to only display the Hearing data from
tblHearings for each hearing and not any tblCharges data. The problem is
that if there are multiple charges for a hearing, the hearing data is listed
once for each charge and I want the hearing to only be listed once. If I
write the query SELECT DISTINCT... it will show the hearing only once, but
then nothing happens when the user clicks a checkbox. Is there a way around
this?

I suggest that you use a subform for the charges. The main
form would only be for the jearing records and the sub form
would be strictly for the charges. This way the main form's
query would not be confused by multiple charges for a
hearing. Setting subform control's Link Master/Child
properties to the hearing ID field will take care of the
relationship.

 

[RipperT]

Apologies. I've explained my problem very poorly. While I do only want
tblHearings data to be displayed in the main form (I've already added a
subform and both forms are in datasheet view), I would like the query to
return records based on tblCharges data. That is, the query contains both
tables and a WHERE clause that filters the records based on the value of a
tblCharges field. Have I designed this improperly?
Many thanks,

Rip

 

[Marshall Barton]

Apologies. I've explained my problem very poorly. While I do only want
tblHearings data to be displayed in the main form (I've already added a
subform and both forms are in datasheet view), I would like the query to
return records based on tblCharges data. That is, the query contains both
tables and a WHERE clause that filters the records based on the value of a
tblCharges field. Have I designed this improperly?

Can't tell without seeing the queries, but if no fields from
the charges table (i.e. the Show box is unchecked), then you
should be able to get rid of the duplicate records by
setting the query's UniqueValues property to Yes (i.e use
the Distinct predicate in SQL). However that will probably
make the query non updatable.

If you need the query to be updatable, then I think you have
to use subqueries in the criteria. If you are unfamiliar
with that, this site may get you started:
http://allenbrowne.com/subquery-01.html