 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / January 2006How not to display similar records in a subform?
Hi All, I run a query based on a dynamically generated SQL select statement that draws from a parent table and two linked tables. I then make the subforms recordsource the query string. It works nicely to return and display the Candidates who match the desired criteria and all the permutations of the criteria combinations they've been matched against(example shown below). CandidateID Name Industry PositionType 543 Mary Health Secretary 284 Sarah Aged Care Receptionist 284 Sarah Aged Care Payroll 284 Sarah Construction Receptionist 284 Sarah Construction Administration 889 Bob Banking Payroll But I only need to show each Candidate only once to display to the user, the Industry and PositionType combinations are not needed for display purposes. Is there any way to get the form to show each candidate id only once? Any tips? Regards, Penny. Penny If Industry and Position Type are not germane, why show them? In fact, why include them in the query? If you want to see unique values of CandidateID, change that property in your query to show Unique Values.  SignatureRegards Jeff Boyce <Office/Access MVP> > Hi All, > [quoted text clipped - 11 lines] > 284 Sarah Construction Receptionist > 284 Sarah Construction Administration > 889 Bob Banking Payroll > [quoted text clipped - 6 lines] > > Penny. Hi Jeff, The point of the query is to return candidates who posses the Industry and of Position Types or any combination of either, so are a necessary criteria. The user only needs to see that candidate listed once however. I've tried various combinations of the 'Unique Values' property, DISTINCT and FIRST and none of them work. Any other ideas? Regards, Penny. > Penny > [quoted text clipped - 35 lines] >> >> Penny. Penny I understand that they are germane SELECTION criteria, but you said you didn't want/need to show them. While I do not have a clear picture of how your data is structured (and hence, how it needs to be queried), I suspect using those two as selection criteria (but unchecking the display-these checkboxes) would leave you with CandidateID and name, for which Unique Values should apply. SignatureRegards Jeff Boyce <Office/Access MVP> > Hi Jeff, > [quoted text clipped - 48 lines] > >> > >> Penny. Hi Jeff, Sorry, you're correct that the two criteria fields need not be shown. I only showed them in the example to illustrate why the same candidate was appearing more than once. I'm playing with versions of the query using your Unique Values idea and some other ones. I'm swaying between being very confused and then not so it'll take me a while. In the query design grid criteria for the joined positions table I've got [Forms]![frmMAINMENU]![Child0]![PositionTitle] to get the Position Title from the form and [Forms]![frmMAINMENU]![Child0]![Industry] from the form. If either one has no criteria selected no results at all are returned. How do I allow one of them to receive a null value but still allow the query to return candidates who the other criteria? Regards, Penny > Penny > [quoted text clipped - 69 lines] >> >> >> >> Penny. Penny You could try selecting on "Is Null Or ....(your forms reference)" SignatureRegards Jeff Boyce <Office/Access MVP> > Hi Jeff, > [quoted text clipped - 71 lines] > >> > Receptionist > >> >> 284 Sarah Aged Care Payroll > >> >> 284 Sarah Construction > >> > Receptionist [quoted text clipped - 15 lines] > >> >> > >> >> Penny. Thanks Jeff, Your suggestion to use 'Unique Values' with the pertinent fields not displayed and also using "Is Null Or ....(your forms reference)" where the tools I needed. Regards, Penny > Penny > [quoted text clipped - 105 lines] >> >> >> >> >> >> Penny. |
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.