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

How do i eliminate rows of data after using distinct keyword.

hi all,
I am working on outputting data to be read into excel from access DB.
The situation now is that im having problems filtering off rows of data which are the same but only having 1 field indifferent from the rest.
Example : SELECT DISTINCT Orders.No, PartNo, CustName, CustAdd, DateReceived FROM Orders;

OrderNo PartNo CustName CustAdd DateReceived
1 FF1 ABC ABCADD 14/7/2007
1 FF1 ABC ABCADD 15/7/2007

how do i write an SQL to remove the later date because i only want the row of data from the earlier date(14/7/2007)

i cant change the db design to help me so is there anyway?
Apr 2 '08 #1
5 1274
Delerna
1,134 Expert 1GB
if the only difference is the date then this should work for you
Expand|Select|Wrap|Line Numbers
  1. SELECT OrderNo, PartNo, CustName, CustAdd, min(DateReceived) as DteReceived
  2. FROM Orders
  3. GROUP BY OrderNo, PartNo, CustName, CustAdd;
  4.  
  5.  
Apr 2 '08 #2
yes date is the only difference. i am using query builder in access and it doesnt seem to be able to compile the min() function
Apr 2 '08 #3
i still get the same result set after running that line :(
Apr 2 '08 #4
ok it worked!
thanks!
Apr 2 '08 #5
Delerna
1,134 Expert 1GB
You're welcome, what was wrong, if you don't mind posting it
Apr 2 '08 #6

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

Similar topics

6
by: Jason | last post by:
I need to populate a table from several sources of raw data. For a given security (stock) it is possible to only receive PARTS of information from each of the different sources. It is also...
3
by: Diego Rey | last post by:
Hi everyone. How can I get the unique row from a table which contains multiple rows that have exactly the same values. example: create table test ( c1 as smallint, c2 as smallint, c3 as...
2
by: Jim H | last post by:
I am storing incoming data in memory using a DataTable. After I'm done retrieving the data I need to get the distinct rows. I tried using DataTable.Select but that doesn't work. If I have...
3
by: chris | last post by:
Hello Group, I have a datatable with duplicate values in a column and I need to get distinct rows ordered by <col name>. How do I go abt it without hitting the db? Thanks for ur help, Chris.
11
by: Ron L | last post by:
I have a data table that lists a series of items in my database. In my user form, I want the user to be able to filter by a number of criteria (e.g. location, contract, date modified, etc). Other...
3
by: Niyazi | last post by:
Hi all, I have a dataTable that contains nearly 38400 rows. In the dataTable consist of 3 column. column 1 Name: MUHNO column 2 Name: HESNO Column 3 Name: BALANCE Let me give you some...
3
by: italia | last post by:
I have a database with 2 columns and more than million rows. The first column is the id Example of the data (2 columns)- 04731 CRM 04731 CRM 04731 CRM 04731 RVB 04731 RVB
17
by: pbd22 | last post by:
Hi. How does one return a range of rows. I know that "Top 5" will return rows 0 - 5 but, how do I get 6 - 10? thanks
2
by: HughManity | last post by:
I'm using SQL Express 2005. I have two tables, TABLEA and TABLEB. TABLEA has the following: col1 ----- NAMEAAA NAMEAAA NAMEBBB NAMEBBB
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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...
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: 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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
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
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.