473,320 Members | 2,098 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.

Unique Records Question

Basically I am trying to do a query that returns records where the count value of a sorted list of a column is equal to 1.

Ex.

Column1 Column2
1 1
7 1
3 2
9 3
5 3


I need a query that only returns '3 2', as for my purposes, the others are duplicates. Ive been trying to use the unique values property, but have been largley unsuccesful so far.

I was thinking of just stripping column 2, and just find the unique values there, and than linking this new table to the old 1 so as to get the needed info from column 1 back. I am curious though if there is an easier way, especially one through the use of intelligent use of criteria.

Any help would be greatly appreciated.
Aug 14 '06 #1
4 2749
comteck
179 100+
Be more specific dude.

comteck
Aug 24 '06 #2
Check out the next 2 columns
(imagine the space marks another cell/field)

1 1
1 2
1 3
1 4
1 5
2 1
2 3
2 5
2 7
3 1
3 5
3 8
3 9


If I sort by the 2nd column I get:

1 1
2 1
3 1
1 2
1 3
2 3
1 4
1 5
2 5
3 5
2 7
3 8
3 9


As can be seen, in the second column there is ONLY 1 occurence of the numbers (2, 4, 7, 8, 9) I want these rows, and all their attacthed information (which in practice include about 1-5 more columns.)

I just want to add, I already found a complex way of doing this, that involves 2-3 different queries, (depending on how you count an append query) and the following criteria:
In (SELECT [Column_2] FROM [Table_2] As Tmp GROUP BY [Column_2] HAVING Count(*)=1 )

after doing a query that all that it does, is display Unique values (Distinct function in SQL).

Cant get them into 1 query, was curious if it was possible at all.
Aug 24 '06 #3
comteck
179 100+
Does it have to be a query. It might be easier to do in VB.

comteck
Aug 24 '06 #4
PEB
1,418 Expert 1GB
The ways that I see ARE

Imagine your table is Table with col1 and col2
1. Using only one query:

SELECT *
FROM Table
WHERE (col2=(SELECT col2 FROM Table GROUB BY col2 WHERE (Count(col2)=1))=1);

2. Using 2 queries:

First you create:
Select col2 FROM Table GROUP BY col2 WHERE Count(col2)=1;

Then

Query with table Table and the previous query, you do the join between col2 and col2 and so you have all rows from Table that are unique!
Aug 31 '06 #5

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

Similar topics

5
by: Westcoast Sheri | last post by:
Which will be a faster lookup of an item by, "color" in the following mySQL tables: "unique key," "primary key," or just plain "key"?? CREATE TABLE myTable ( number int(11) NOT NULL default '0',...
3
by: Beat Scheidiger | last post by:
I do not quite understand this property. Everything is seems clear to me, when I read the corresponding help text. But in practice I have a question: Why are there 3 identical records in the...
3
by: Phil | last post by:
I am looking to set up a hyperlink control on a form to retrieve letters that correspond to a record on a form. That is, there may be 100 form records, and I would like each of those form records...
2
by: D. Dante Lorenso | last post by:
I'm trying to build a table that will store a history of records by enumerating the records. I want the newest record to always be number ZERO, so I created a trigger on my table to handle the...
2
by: ET | last post by:
Hi! I'll need help on what todo with this information in table... There has to be a unique on one column (Item ID, for inventory purpose) but in many cases that ID is not known... thus, many...
2
by: john | last post by:
I have a table with 5 fields. In a query I would like to make a count for every unique combination of field 2,3, and 4. I fiddled with the count and dcount option, but can't get it to work for more...
2
by: banderson | last post by:
Hello, I have a data entry form for a table with information about buildings and am having a problem making a combo box do what I want. I would like to make the combo box show a list of unique bldg...
2
by: srusskinyon | last post by:
I need some help getting unique records from our database! I work for a small non-profit homeless shelter. We keep track of guest information as well as what services we have offered for...
10
by: jmartmem | last post by:
Greetings, I have an ASP page with a 5x5 table embedded inside an Insert Record Form. This table contains several fields (mostly drop down list menus) and is used for corporate timekeeping (users...
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...
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: 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...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
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
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.