Queries involving subforms

P

Phil

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
 
J

Jeff Boyce

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
 
P

Phil

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
 
J

Jeff Boyce

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
 
P

Phil

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
 
J

Jeff Boyce

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
 
P

Phil

Thanks Jeff, this worked.

It still wouldn't let me edit the returned data, but when I changed it from
uniquevalues to uniquerecords it returned the same results and allowed me to
edit the data.

Thanks for all your help!


- Phil
 
Top