 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / August 2006Queries involving subforms
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 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 > Hopeing someone can help me here.. > [quoted text clipped - 15 lines] > > - 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 > Phil > [quoted text clipped - 32 lines] >> >> - Phil 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 > Thanks for your reply. > [quoted text clipped - 39 lines] >>> >>> - 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 > Phil > [quoted text clipped - 53 lines] >>>> >>>> - Phil 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 > Hi Jeff, > [quoted text clipped - 69 lines] >>>>> >>>>> - 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 > Ah, sorry I was too dense. > [quoted text clipped - 90 lines] >>>>>> >>>>>> - Phil |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.