Re: Queries involving subforms

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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: Queries involving subforms
    ... If you create your query, with the parent & child tables joined, and add ... you will get as many as there are child rows ... combination of parent row values only once. ... I have a main form with 1 subform, I want to make a query that ...
    (microsoft.public.access.queries)
  • Re: Query returning unwanted results
    ... introduce are eliminated by the WHERE clause. ... The problem i'm having is, if there are 2 child rows, and one matches the ... criteria, and the other doesn't, it will still return the parent row. ...
    (microsoft.public.access.queries)
  • Query returning unwanted results
    ... I have a select query which is using the distinctrow predicate to return ... The problem i'm having is, if there are 2 child rows, and one matches the ... criteria, and the other doesn't, it will still return the parent row. ...
    (microsoft.public.access.queries)
  • RE: Filtering a subform using many combo boxes
    ... SELECT AssignedPriority, AssignedPriorityDetail FROM tblAssignedPriority ... YES for all of the combo boxes.) ... I followed you instructions to the letter and every one of the criteria ... open the query "qryProjects" in design view. ...
    (microsoft.public.access.forms)
  • Re: using a form with combo box to input criteria
    ... Candia Computer Consulting - Candia NH ... When you removed the criteria, ... Then your criteria in the query would be... ... query behind your main report. ...
    (microsoft.public.access.forms)