473,320 Members | 2,164 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,320 software developers and data experts.

Comparing two columns for differences

maylortaylor
72 64KB
This might be hard to explain what I am going for, but i will try.

Ok, I have two tables: (1)"ZIPS" which contains zipcodes,states,and counties for everywhere in the U.S. and (2) "UserCriteria" which contains username, state, county, rank, and a few other criteria.


In the UserCriteria table, I can have one user (mtaylor) representing many states..let's say 10. Each state is assigned a Rank based on how well that user knows that state (typically 1 - 3...3 being 'they do not understand it at all').

So let's say mtaylor's first 8 states have a Rank of 1, and his 9th and 10th state has a Rank of 2....well I want to let the table know that the rest of the 40 states that mtaylor does not have should have a rank of 3 ('meaning he should not do these states')


I am clueless on how to do this.


As of now, I have in the Usercriteria table, in the State column, I have the word "REST" (meaning rest of the states) and the Rank given to "REST" is 3....but how would i go about finding out what the 'rest of the states' are?

My SQL strings are very rough, but i have this so far...

Expand|Select|Wrap|Line Numbers
  1. Function RestOfStates(ByVal _user As String)
  2.         Dim TaxConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ConfigurationManager.AppSettings("Database")
  3.         Dim dbConnection As OleDbConnection = New OleDbConnection(TaxConnStr)
  4.         Try
  5.  
  6.             Dim queryString As String
  7.             queryString = "SELECT DISTINCT State from Zips"
  8.             Dim dbCommand As OleDbCommand = New OleDbCommand
  9.             dbCommand.CommandText = queryString
  10.             dbCommand.Connection = dbConnection
  11.  
  12.  
  13.             Dim queryString2 As String
  14.             queryString2 = "SELECT UserName, State, Rank from UserCriteria WHERE State = 'REST' AND Username = '" & _user & "'"
  15.         Catch ex As Exception
  16.             Console.WriteLine(ex.Message)
  17.             myLogger.Log(ex.Message)
  18.         Finally
  19.             dbConnection.Close()
  20.         End Try
  21.     End Function
...the queryString2 section is unfinished cause i dont know how to compare what i collect in the first queryString to the second queryString....any ideas?
Apr 5 '13 #1
1 1253
Rabbit
12,516 Expert Mod 8TB
First you'll need a cross join query to list every combination of state and distinct user. Then you outer join that back to your user criteria which will show you which ones are missing from the user criteria.
Apr 5 '13 #2

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

Similar topics

12
by: NickName | last post by:
I have the following situation. One set of data has 274 rows (set2) and anther has 264 (set1). Both data sets are similar in structure as well as values for both of them were extracts from the...
2
by: Jorge Apolinário | last post by:
Hi, does anybody know a nice tool to automatically compare and show only the structural differences between two databases, this is, the differences in the columns of a table, constrains, data type,...
3
by: dataguy | last post by:
I can't find it anywhere in the manual, but I have a developer that wants to know if we can code a trigger to capture the data that has changed in a table only if certain columns have changed. It...
19
by: Will Lastname | last post by:
In one of the applications that I'm working on I have 2 sets of functions that build different datasets. Imagine 4 columns in a datagrid. Inside those 4 columns I have nested datalists. Two of...
2
by: c_shah | last post by:
Scenario: I have to update my SQL server table from an ORACLE database to keep my SQL table in sync with ORACLE. I have one data table that is populated form ORACLE and other one is populating...
2
by: Shum | last post by:
Hi! i am working on a hospital data base. i have two tables Administration and knowlege base. in Administration i have columns ID, NumberOfDoctors etc, and in KnowlegeBase i have some similar...
8
by: irek.szczesniak | last post by:
Hi, I have table pairs that I need to compare, and produce another table that shows differences. I can't just open them in separate browser and look for differences, because I have many such...
2
by: wonny | last post by:
I am sure this will be easy for some of you but it is beyond my novice SQL skills. I have two tables with a common column (Let's call this Column A) but with different rows. The rest of the...
7
by: bcw802000 | last post by:
Thanks ahead of time you’re your expert advice. Here is the scenario: I have several Queries that finally end up like the following. Filekey | Date | Last Name | First Name | Sum of dollars |...
2
by: jumperbl | last post by:
I want to look at two columns lets say B1 to B8 and C1 to C8. I want to compare those ranges and if the columns don't match i want to color the cell. Here is how much i have figured out. ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
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
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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...
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: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
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...
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...

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.