UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Wrong Result Query With First, Access 2010    
 
   
Blacksmith
post Feb 28 2018, 04:25 AM
Post#1



Posts: 90
Joined: 28-November 06



Hi

I have a member database, each member may have several phone number, stored in second table. The phone numbers have additional sort information that allows to bring them in the correct order. I have a query 'qryMemberPhones' that generates a correct list of phone numbers, starting with the most important one.

Now I need to draw a report, with only the most important phone number. I tried the following query:
CODE
SELECT qryMemberPhones.memberId, First([memberPhones]) AS expMostImportantPhone FROM qryMemberPhones Group by memberId;


This results in one phone number per member, but in the case of at least one member the second number from qryMemberPhones is displayed instead of the first one. What is wrong with my SELECT statement?

Thank you
Go to the top of the page
 
Larry Larsen
post Feb 28 2018, 04:48 AM
Post#2


UA Editor + Utterly Certified
Posts: 24,239
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
It would seem that the data (phone numbers) need to be validated to ensure that there are no corrupt data/characters in there..

What data type is [First([memberPhones])]..??

Text/numeric..??
thumbup.gif
Go to the top of the page
 
HairyBob
post Feb 28 2018, 04:52 AM
Post#3



Posts: 992
Joined: 26-March 08
From: London, UK


Personally, with something like this, I would have a boolean field in the underlying phone numbers table which indicates which number is preferred for each member (e.g. named PrefNo). In your query, you can then simply filter where PrefNo = True. You can then either let the user indicate which the preferred number is using a check box, or if the 1st phone number entered for a member is always the preferred number and will never change, simply set the PrefNo field to True only when entering the first phone number record for a member.

HTH,

Hairy.
Go to the top of the page
 
projecttoday
post Feb 28 2018, 05:11 AM
Post#4


UtterAccess VIP
Posts: 10,350
Joined: 10-February 04
From: South Charleston, WV


It seems to me that that would work but I and most developers don't use SELECT FIRST much. The question is does it preserve the sort sequence which you have defined in qryMemberPhones? Can you post the code for qryMemberPhones?

You might have to go with SELECT TOP 1.
Go to the top of the page
 
LPurvis
post Feb 28 2018, 05:46 AM
Post#5


UtterAccess Editor
Posts: 16,295
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

I'd have to question two things.
Most importantly, exactly what your definition of "most important" phone number is.
Based on the description, it would appear to be that you prefix it with a character which makes it appear alphabetically first?
Other than that, what are you wanting the First function to return?

And,with that in mind, your use of First is almost certainly not going to be fruitful for you.
It returns the first row value fund in the order in which the engine accesses the data pages. This is likely going to be your primary key order. (Which is almost certainly not going to be your phone number field, correct?)

Are you not using Min for a reason?
SELECT memberId, Min([memberPhones]) AS expMostImportantPhone FROM qryMemberPhones Group by memberId

That said, I have to agree with the sentiment that you could be said to have non-atomic data if you have a single field representing more than one attribute (both pone number and which is the most important phone number).
A separate means of determining the most important might be a better option for you.

Cheers
Go to the top of the page
 
Blacksmith
post Feb 28 2018, 09:35 AM
Post#6



Posts: 90
Joined: 28-November 06



Thank you for all this input, that was very informative!

Long time ago I learned, that it is never a good idea to use something like the calculated and stored value of PrefNo. That is why I tried to do it with queries!

@Larry Larsen
The data type is text. qryMemberPhones is uses somewhere else (a report with all phonenumbers) and it seems to work there...

@HairyBob
I think your proposal will be the 'save' way. As I need to make sure, that only one 'PrefNo' is set, my way would be to clear all 'PrefNo' und use a Recordset with qryMemberPhones to update the first phone record of a member. qryMemberPhones seems to work as intened - if it does not, I have some other problems as well :-)

@projecttoday
I always supposed, that access preserves the order of the underlying table/query if there is a defined order for this query. There is such a definde order for my qryMemberPhones and it seems to work.
SELECT TOP 1 by the way can not be used here: that would only return 1 record. I, however, need all the most important phone for *every member.

@LPurvis
The "most important" phone number is defined by additional fields: a numeric field AND phone type (mobile, home, business). If the numeric field contains a number the lowest number (typically 1) will make this record the most important phone number. Further records can be sortet by entering 2, 3, ... If this filed contains NULL, the phone number is sorted accoring to the above metintioned phone type. All this is done by qryMemberPhones. And - I mentioned it above - qryMemberPhones is used somewhere else (a report with all phonenumbers) and it seems to work correctly there...

> It returns the first row value fund in the order in which the engine accesses the data pages. This is likely going to be your primary key order. (Which is almost certainly not going to be your phone number field, correct?)
I supposed the order to be defined by the sort order I defined in qryMemberPhones. Oh, and by the way: the wrongly displayed phone number has - by coincidence - the higher primary key than correct phone number... - so the primary key does not seem to induce this.


So, in short, HairyBob's solution might be the simplest way to sove this... I will continue to follow this thread - just in case someone has a killer idea....
Go to the top of the page
 
projecttoday
post Feb 28 2018, 09:45 AM
Post#7


UtterAccess VIP
Posts: 10,350
Joined: 10-February 04
From: South Charleston, WV


It might help to see qryMemberPhones.
Go to the top of the page
 
zaxbat
post Feb 28 2018, 09:50 AM
Post#8



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Right....need to look at that first query (querymembersphones???)...that is most likely where the problem is. Look at its results and make sure that it is getting the phone numbers in the right priority....do that first....then if it is working....we can dig further.
This post has been edited by zaxbat: Feb 28 2018, 09:51 AM
Go to the top of the page
 
projecttoday
post Feb 28 2018, 10:01 AM
Post#9


UtterAccess VIP
Posts: 10,350
Joined: 10-February 04
From: South Charleston, WV


Post the database itself if it you can remove confidential info.
Go to the top of the page
 
HairyBob
post Feb 28 2018, 10:18 AM
Post#10



Posts: 992
Joined: 26-March 08
From: London, UK


If you do go with a boolean field to flag which is the preferred phone number for each member and you let the user delete phone records and/or change which number is preferred, you have to handle that; If the user can select another phone number entered for a member, you need to automatically set the preferred flag for the current record selected as preferred to False. If you let the user delete phone record(s) and one of them is the one flagged as preferred, you need to select a phone number which will not be deleted as the preferred one, then inform the user that if they proceed, that number will be flagged as preferred - if the user then proceeds with the delete, he/she can then select another phone record as preferred if that is not the right one.

Hairy.
This post has been edited by HairyBob: Feb 28 2018, 10:23 AM
Go to the top of the page
 
zaxbat
post Feb 28 2018, 11:17 AM
Post#11



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Opens a can of worms when you consider it that way. Takes a lot of code to make sure that one of the numbers is always designated as the most important regardless of what the user add, selects, delete or whatever. Believe it would benefit to store the id of the important phone number into the header record and not have to rely on the phone number table to have the number already tagged in some way.
Go to the top of the page
 
projecttoday
post Feb 28 2018, 11:40 AM
Post#12


UtterAccess VIP
Posts: 10,350
Joined: 10-February 04
From: South Charleston, WV


I agree with HairyBob's idea and I think it's doable but Blacksmith says he/she has that all figured out, that it's in the query. So we are waiting form more information from Blacksmith.
Go to the top of the page
 
zaxbat
post Feb 28 2018, 12:04 PM
Post#13



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


I had a situation like this before. I had a company table and a contact table. The contact table held contact names with phone numbers and since one company might have multiple contacts i put a priority field in the contact table (just an integer) so that among any companies list of contacts they could be assigned a priority for which to call first, second, etc. assuming the first one(s) were away (on vacation, lunch whatever). It worked flawlessly and did not break too many normalization rules (i think). Only thing was....that in the UI where the user would do the prioritization...man it was tricky...amazing how many different possibilities you have to allow for when the user is there poking keys.
Go to the top of the page
 
Blacksmith
post Mar 1 2018, 06:28 AM
Post#14



Posts: 90
Joined: 28-November 06



This topic seems to be of interest for many. I'll try to remove all unneeded stuff and anonymize the data, Then I'll publish the database here.

This will be quite a bit of work. Please allow me some time to do so.
Go to the top of the page
 
projecttoday
post Mar 1 2018, 07:21 AM
Post#15


UtterAccess VIP
Posts: 10,350
Joined: 10-February 04
From: South Charleston, WV


wink.gif
Go to the top of the page
 
John Vinson
post Mar 1 2018, 07:13 PM
Post#16


UtterAccess VIP
Posts: 4,274
Joined: 6-January 07
From: Parma, Idaho, US


What's important is the structure - you don't need to anonymize hundreds of records, just enough to demonstrate the problem.

"First" is misleading. It means the "first record the program comes to", and that is NOT necessarily the record you think of as "first". But we'll see when we get an example!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2018 - 02:43 AM