Re: Queries involving subforms

Ah, sorry I was too dense.

If you create your query, with the parent & child tables joined, and add the
fields from the child table that you wish to select on, you can then
un-check them so they won't show.

When you add "parent" fields, you will get as many as there are child rows
that match your criteria. This is a problem when you only want to see the
parent info one time, no matter how many child rows match.

Right-click in the open space (i.e., not on a table) and select properties.
Set the Unique Values to "Yes". You should get each unique combination of
parent row values only once.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Phil" <philde@xxxxxxxxxxxx> wrote in message
news:44eade03$0$4145$5a62ac22@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi Jeff,

Thanks again for the reply.

I don't want the query to return any child rows, only parent rows, but
based on criteria from the child rows.

I realise this can be done by using a field from the child row in the
query and choosing not to display it, but the problem with this method is
that if there are 2 matching results in the child row, it will list the
parent row twice in the query results.

Is there anyway to do this?


Thanks



"Jeff Boyce" <nonsense@xxxxxxxxxxxx> wrote in message
news:uRcZmAYxGHA.4484@xxxxxxxxxxxxxxxxxxxxxxx
Phil

I'll rephrase my question...

How do you let Access know WHICH child row's data to return if you
indicate a particular parent row.

Are you saying you want to see ALL child rows for a given parent row?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Phil" <philde@xxxxxxxxxxxx> wrote in message
news:44ea3fee$0$4146$5a62ac22@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Thanks for your reply.

To reword the question; is there anyway to base the criteria for a query
on data in a child table, which only returns the related parent row?

Thanks



"Jeff Boyce" <nonsense@xxxxxxxxxxxx> wrote in message
news:eNhWhbUxGHA.4256@xxxxxxxxxxxxxxxxxxxxxxx
Phil

Perhaps just a matter of semantics, but...

Queries don't work against forms. Queries are created to work against
tables of data, or against the data returned by (another) query.

If you join a main table and a related "child" table, you will get as
many rows as you have "children" for each "parent" row.

If you wish to see data from the child table, which one?!

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Phil" <philde@xxxxxxxxxxxx> wrote in message
news:44e9de5f$0$4196$5a62ac22@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hopeing someone can help me here..

I have a main form with 1 subform, I want to make a query that returns
data for the main form, but using criteria from the related subform.

The problem i'm having is, if i use a normal select query based on
both forms, each record is listed as many times as the number of
records in the subform that match the criteria.

And of course if I group data from the subform in the query to limit
the records, it doesnt allow me to edit the data in the query.

Is there anyway to use criteria from a subform, but not actually
include the subform records in the query results at all? Or is there
another way around this?

Thanks for your help!


- Phil











.

Relevant Pages

  • RE: Parent-Child relations in a dataset
    ... How do I keep pending parent and children in synch? ... that actually exist in the database. ... use those placeholder values for your pending child rows. ... "SELECT SCOPE_IDENTITY" query to retrieve the last identity ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Updating and Retrieving Data
    ... How do I keep pending parent and children in synch? ... that actually exist in the database. ... use those placeholder values for your pending child rows. ... "SELECT SCOPE_IDENTITY" query to retrieve the last identity ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Query vs form with child & parent: Deleting rows
    ... subordinate to that record, that is, all individuals (and sometimes child ... companies) whose Owner field contains the record key of the parent company. ... That second form is fed by this query: ... But I have a child table called "Channel"; ...
    (microsoft.public.access.forms)
  • Re: Binding sources question in Vs2005
    ... >happens is that the fill statements for the child tables get executed first ... Even if the child table is loaded after the parent it will still load all ... child rows that are potentially never seen are taking up memory. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Binding sources question in Vs2005
    ... >>dragging the parent table and then the child tables and grandchild tables, ... > Even if the child table is loaded after the parent it will still load all ... child rows that are potentially never seen are taking up memory. ...
    (microsoft.public.dotnet.languages.vb)