Query Returning Multiple Rows - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Query Returning Multiple Rows

Nilsen Septon

12 months ago

I have a query that works properly except it pulls too much data.

I have an IndividualT and a PhoneT. In the PhoneT, I'm using a Yes/No as "IsPrimary" to indicate the primary phone number to use (NOT the 'primary key'). As a note, I'm currently not making the 'Is Primary' field as required...maybe that will help?) - anyway...I want my query to ONLY bring in the phone number that is marked as 'IsPrimary' regardless of how many phone numbers the individual has. Currently, my query is bringing in ALL numbers so that individual is being listed multiple times. However, if an individual does not have 'IsPrimary' marked for any of their phone numbers, I want to see that too (I tried setting 'IsPrimary' to 'True' in the query, but then that left out the 2nd scenario of those that did not use the 'IsPrimary' field.

	Sami Shamma @Reply 12 months ago
Show a screenshot with the query in design view. You need a second condition with an "OR" IsPrimary = True Or IsPrimary "" Or Isnull(IsPrimary)

	Kevin Robertson @Reply 12 months ago
I usually have the first entry of the Child Table (related to the current Parent) default to True. All others that are entered default to False.

Nilsen Septon OP @Reply

12 months ago

@Sami - I currently don't have any condition set on the 'IsPrimary' field - when I tried the "AND" condition to 'Yes', it didn't bring me any other number (as expected because no other individual had the 'IsPrimary' field set) - I'll try as you suggested.

@Kevin - I didn't know that could be done....having the first entry default to True while all other entries default to False - how do you do that

Update - In playing around with this a bit more, I actually kind of went the other way from what Kevin suggested. I set the default value of "IsPrimary" field = Yes, then added the same logic that I have with the "IsPrimary" field in the form to only allow any one number to be the primary number (when selecting any number as 'IsPrimary', the logic makes sure all other numbers 'IsPrimary' is set to False) - I applied that logic to the number field so when a new number is added and since the default is set to 'Yes', the logic will set all other numbers to False. This works, but it may not be the most elegant solution?

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View