ACCESS FEED: RE: [MS_AccessPros] Searching on 2 tables

Rabu, 20 Juli 2011

RE: [MS_AccessPros] Searching on 2 tables

If there are multiple subject records related to one record in your main report, I still think this could be handled with a single report. You can set up group sections for Media and possibly other levels of detail. There would be no waste of space that I am aware of with a single report.

If there are multiple types of "child" records such as Families with Children and Families with Trips, you would need a subreport for either Children or Trips. Another time a subreport is necessary is when you want multicolumn subreports.

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
From: wgriffin48@mchsi.com
Date: Wed, 20 Jul 2011 23:49:44 +0000
Subject: Re: [MS_AccessPros] Searching on 2 tables

Duane; The subreport is populated from the second table containing all subjects for all media entries with each subject linked by a field containing the ID field in tblMedia to associate the subject entry to the correct media item.
Since the number of subjects is unknown on each media entry this was the easiest way to create a "dynamic" report that expanded or contracted to fit the data onto the report and I didn't need to set up fields for "subject1, subject2, adnauseum" on the form/report and in the Media table and waste a lot of space on both the form/report/table involved. The data entry form is built the same way with a sub form for subject data.
The parent reports data source is a query built from several tables, but I don't know haw to associate, what could be several subjects, that are records, in the tblMediaSubjects to a single record in tblMedia.
It should be noted that the report works well otherwise and searches sorts on the other parameters (Or no parameters) and displays the data properly.

Walter

--- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@...> wrote:
>
>
> What is the justification for using a subreport. If there are only 2 tables involved, you should be able to use a record source from the 2 tables.
>
> Duane Hookom
> MS Access MVP
>
>
>
>
> To: MS_Access_Professionals@yahoogroups.com
> From: wgriffin48@...
> Date: Wed, 20 Jul 2011 23:10:34 +0000
> Subject: [MS_AccessPros] Searching on 2 tables
>
>
>
>
>
>
> Hello Group;
> I've been missing a while due to surgery, but I'm up and banging away again.
> I am trying to search on two tables at the same time, The search is to build a report. I have one table "tblMedia" in a library/collections application that contains fairly static information (IDMedia, title, producer, cost, value, etc) and a separate table "tblMediaSubjects" with (IDMediaSubject, idMediaLink(to IDMedia in tblMedia)and StrMediaSubject).
>
> The report is constructed with a report containing base information about the media item and a subreport containing subjects because the subjects may be numerous and the number unpredictable.
>
> One of the search parameters is a "like" search on strMediaSubject. I don't want the search to stop on the first hit as I want to display a report with all media with the target subject string.
>
> Is there a graceful way to accomplish this?
>
> John: You Can't retire! I'm still treading water here.. :-)
>
> Walter Griffin
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>