473,322 Members | 1,540 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Prioritization of values retured by queries

markmcgookin
648 Expert 512MB
Hi Folks,

Not really looking for code or anything here, just a point in the right direction to research.

I am going to be designing a system that will take a bunch of user inputs related to values in SQL Server CE tables, and if there are 10 options, and they select 3 of them, I want to return a list of all the things in the table that have any of those values, but prioritise the ones that match the most.

I was thinking of adding all records that match to a recordset, and possibly creating some form of temp array which could be ordered by say an incremented value (that increases for each value that matches up)? Anyone know about any techniques for this sort of thing? or similar posts? I had a look but couldn't find anything.

Cheers!

PS - I think i sound clear, but if not ask me and I'll clarify stuff. I havent started designing the system yet, so i cant provide table info etc.
Jan 29 '07 #1
10 1555
Killer42
8,435 Expert 8TB
Perhaps you could create a function which calculates the closeness of the match for a record, then use that in a calculated column of your query and sort by it.
Jan 30 '07 #2
markmcgookin
648 Expert 512MB
Perhaps you could create a function which calculates the closeness of the match for a record, then use that in a calculated column of your query and sort by it.
Hmm... I dont think that would work with this, as the inputs will hopefully all be pre-defined, i.e. list boxes, check boxes etc. It is going to be a PDA app with SQLServerCE and I had ideally wanted to produce something with function/interface similar to this website for flower identification

Flower Identification Site

Which just produces a list of things that match the feilds you put in. And obviously some of those results will feature in more fields than the others.

i.e.

object a - matches characteristcs a,b,c
object b - matches b,c
object c - matches a

so if you put in, characteristics a and b

objects c and a will be results (the function of the website, and somewhere i would happily be with my project) , however object a matches them both, thus should be higher up the results (somewhere I would IDEALLY be with my project)

My prog would be running on the PDA. Currently my MASSIVE (panic attack enducing) issue is connecting to a SLQ Server CE database and passing an SQL query to it to have some form of output. Unfortunatly no one seems to be responding to any of my pleas for help here, I would love to hear from someone who has designed a system running with a database on a PDA in VB and who would be willing to spare some of their time to answer a few questions I have.
Jan 30 '07 #3
Killer42
8,435 Expert 8TB
I suppose at the simplest (and possibly slowest) levem you could go with your original idea. Copy the found records to a temp table, adding in a "match percentage" field calculated by your code. Then return that, sorted by the percentage.

But I still think that the function idea might be able to work. I'll have a play with it in the query builder when I have some time (maybe at lunch time, in a few hours).
Jan 30 '07 #4
Killer42
8,435 Expert 8TB
But I still think that the function idea might be able to work. I'll have a play with it in the query builder when I have some time (maybe at lunch time, in a few hours).
Ok, I've just tried this function technique, and it appears to work satisfactorily. (I have no idea what performance will be like.)

I created a simple table with three text fields called Field1, Field2 and Field3. Created some test records. Created the following simple function in a VBA module:
Expand|Select|Wrap|Line Numbers
  1. Public Function CountMatches(Fld1 As String, Fld2 As String, _
  2.     Fld3 As String, Val1 As String, Val2 As String, _
  3.     Val3 As String) As Long
  4.   Dim M As Long
  5.   If Fld1 = Val1 Then
  6.     M = M + 1
  7.   End If
  8.   If Fld2 = Val2 Then
  9.     M = M + 1
  10.   End If
  11.   If Fld3 = Val3 Then
  12.     M = M + 1
  13.   End If
  14.   CountMatches = M
  15. End Function
This should return a number between 0 and 3, indicating how many of the fields match their corresponding values.
Then created this query (threw it together in the GUI query designer)...
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.*
  2. FROM Table1
  3. ORDER BY CountMatches([Field1],[Field2],[Field3],"a","G","Q") DESC , Table1.ID;
Since I was just testing the concept, I simply hard-coded my search values. Obviously it needs some work, but I believe this should illustrate the idea sufficiently.
Jan 31 '07 #5
NeoPa
32,556 Expert Mod 16PB
i.e.

object a - matches characteristcs a,b,c
object b - matches b,c
object c - matches a

so if you put in, characteristics a and b

objects c and a will be results (the function of the website, and somewhere i would happily be with my project) , however object a matches them both, thus should be higher up the results (somewhere I would IDEALLY be with my project)
Surely object b would be as close a match as object c? Both being less close than object a of course.

Killer, Your procedure should work assuming each value only had to be compared with one matching field. Otherwise I'm sure it could be converted to add the extra flexibility needed.
i think your procedural approach would be necessary as the fundamental interface of SQL would not allow for the type of comparing required here to be done in native SQL.
Jan 31 '07 #6
Killer42
8,435 Expert 8TB
... Killer, Your procedure should work assuming each value only had to be compared with one matching field. Otherwise I'm sure it could be converted to add the extra flexibility needed. i think your procedural approach would be necessary as the fundamental interface of SQL would not allow for the type of comparing required here to be done in native SQL.
Of course, the function I wrote was the simplest I could come up with to test the concept. The actual matching to be performed would be up to the OP. I'm glad you agree with the idea.

And yes, I agree that some such procedural approach is probably necessary - don't know how you'd get this sort of functionality out of SQL, though it might be possible by jumping through all sorts of hoops.

Hm...

Does SQL return 1 or -1 for a True condition? Or can't they be interpreted numerically like in Basic?

I'm thinking something along the lines of...
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.*
  2. FROM Table1
  3. ORDER BY (([Field1]="A")+([Field2]="B")+([Field3]="C")) DESC , Table1.ID;
It's ugly, but there might be some way to get it to work.

Anyway, to get back to the point, the VBA function is probably much simpler.

... a few minutes later ...

Hahaha... it worked! :D

Sorry, I just couldn't resist. I added this field in the GUI query designer...
Expand|Select|Wrap|Line Numbers
  1. CountEm: ([Field1]="A")+([Field2]="B")+([Field3]="Q")
It appears to produce the same count that the VBA function. :) Well actually, the negative, since True turned out to be -1, but that just means you sort Ascending with this one, and the job's done.
Jan 31 '07 #7
NeoPa
32,556 Expert Mod 16PB
Good thinking Killer.
There are often ways to do things which are not immediately obvious. You've come up with a good one here :)
You've already discovered the answer to your question TRUE == -1; FALSE == 0.
Any other number is treated as TRUE when tested except when compared to TRUE explicitly.
IE.
Expand|Select|Wrap|Line Numbers
  1. If -1 Then {Does it}
  2. If 1 Then {Does it}
  3. If 0 Then {Doesn't do it}
  4. If -1 = TRUE Then {Does it}
  5. If 1 = TRUE Then {Doesn't do it}
  6. If 0 = TRUE Then {Doesn't do it}
  7. If -1 = FALSE Then {Doesn't do it}
  8. If 1 = FALSE Then {Doesn't do it}
  9. If 0 = FALSE Then {Does it}
This is why I always try to use the form :
Expand|Select|Wrap|Line Numbers
  1. If Value Then
  2. rather than
  3. If Value = TRUE Then
Jan 31 '07 #8
markmcgookin
648 Expert 512MB
Hi Neo

(Just PM'd you with a favour request for editing a similar post) forgot I posted this one to be honest!

I (think) I get where you are coming from, but would that not simply return every value in the table, in an order of relevance?

I only want to display the values that match the criteria, and some of these might be unknowns or things the user can not tell (i.e. Do not refine output via this field!)

so...

If

items a,b,c,d, and e have the following values for val1, val2, val3, val4

a "red" "oak" "purple" "5"
b "red" "oak" "Yellow" "3"
c "Blue" "oak" "green" "2"
d "green" "ash" "green" "4"
e "orange" "oak" "purple" "5"

if a user selected "red" "oak" "I dont know" "I dont know"

It would return a and b

or if they selected

"red" "I Dont Know" I Dont Know" I Dont Know" I Dont Know"
a and b would again be returned


or if they select

I Dont Know" I Dont Know" I Dont Know" "5"

a and e would be returned

or (last one)

"I Dont Know" "oak" "I Dont Know" "I Dont Know"

a,b,c,e would be returned

I think if I got this I would simply order the list by a common field for "how common it is in the UK"

as otherwise it is becoming too complex
Mar 9 '07 #9
NeoPa
32,556 Expert Mod 16PB
Hi Mark,

Was your reference to where I'm coming from a response to my post or was it replying to Killer's earlier post.
My post was simply highlighting what I consider the best way to deal and work with booleans.
If these points are used you will have less likelihood of unexplained errors and the code should be easier to read.
As Killer has the logic fairly well covered here I won't jump in unless required (besides I didn't really get what you were asking for in your last post anyway :p).

-Adrian.
Mar 9 '07 #10
Killer42
8,435 Expert 8TB
Seems to me you just take that calculated field, and put a "not zero" condition on it.
Mar 10 '07 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: AlanR | last post by:
Hi, I have to implement(or possibly acquire) a queue that manages priorities. Low priority elements cannot get lost in the queue indefinitely if there is a sustained, constant flow of high...
1
by: SJH | last post by:
I have been given an older database and asked to make upgrades and what not. One interesting thing I have come across with the database is that it was at one time set up so one of the tables would...
1
by: Ken | last post by:
I wrote a function to use in queries that takes a date and adds or subtracts a certain length time and then returns the new value. There are times when my function needs to return Null values. ...
1
by: JM | last post by:
Hello, Using Access 2000 queries, you can reference(pass) form values directly using syntax like Forms!frmPaint!txtColor. I want to do a pass through query to SQL Server 2000, but I don't know...
2
by: Dan Cook | last post by:
Ok... here's the scoop... I've got an unbound form with Serial Number, Building, and Room... all this is in tableA... Now I've got the form setup with building, room and serial number as combo...
0
by: Jeff Boes | last post by:
I hope this helps someone else ... I had struggled some time ago with attempts to get a rank of values query to work, but then I gave up and set it aside. I had another reason to attack it, and in...
8
by: Chris A via AccessMonster.com | last post by:
I have an interesting problem that I have yet to come accross that I can't change data structure on because it is an export from filemaker I am reformatting for another dept. anyway. I have a table...
1
newnewbie
by: newnewbie | last post by:
Desperately need help in creating a query to count unique values in a table. I am a Business analyst with limited knowledge of Access….My boss got me ODBC connection to the underlying tables for our...
7
by: sparks | last post by:
I am working on a database that has a lot of calculated values on the forms. These were never put into the tables. But were tied to unbound fields on the forms. Now 8000 records later they want...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.