another ? about using distinctrow (or some alternative)

Hi all
I have a query that I would like to eliminate records where one field
may have duplicate values. I  was of the impression that there was
something i could put in the "criteria" field in the query design view
that would eliminate duplicate records for the field.  I have tried
"unique" , "distinct" "count(*)=1" ect. but none of these seem to
work. Is this more complicated than I think?  Any suggestions would be
appreciated. BRC

0
BRC
4/16/2007 12:31:10 AM
access 16762 articles. 3 followers. Follow

2 Replies
685 Views

Similar Articles

[PageSpeed] 0

On 15 Apr 2007 17:31:10 -0700, "BRC" <sgos4v-googrp@yahoo.com> wrote:

>Hi all
>I have a query that I would like to eliminate records where one field
>may have duplicate values. I  was of the impression that there was
>something i could put in the "criteria" field in the query design view
>that would eliminate duplicate records for the field.  I have tried
>"unique" , "distinct" "count(*)=1" ect. but none of these seem to
>work. Is this more complicated than I think?  Any suggestions would be
>appreciated. BRC

You can get this result in a couple of ways. What if there are records which
are duplicate for field A, but *different* for fields B, C and D? Do you want
to see just one record - and if so, which?

If you just want to see an arbitrary record, create a Query based on the
table. Change it to a Totals query by clicking the Greek Sigma icon (looks
like a sideways M). Leave the default Group By on the fields which identify
the group of which you wish to see one example; select "First" for the
remaining fields. You'll see the first record in disk-storage order (an order
which you can't control so it's basically arbitrary).

             John W. Vinson [MVP]
0
John
4/16/2007 1:28:35 AM
On Apr 15, 6:28 pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On 15 Apr 2007 17:31:10 -0700, "BRC" <sgos4v-goo...@yahoo.com> wrote:
>
> >Hi all
> >I have a query that I would like to eliminate records where one field
> >may have duplicate values. I  was of the impression that there was
> >something i could put in the "criteria" field in the query design view
> >that would eliminate duplicate records for the field.  I have tried
> >"unique" , "distinct" "count(*)=1" ect. but none of these seem to
> >work. Is this more complicated than I think?  Any suggestions would be
> >appreciated.BRC
>
> You can get this result in a couple of ways. What if there are records which
> are duplicate for field A, but *different* for fields B, C and D? Do you want
> to see just one record - and if so, which?
>
> If you just want to see an arbitrary record, create a Query based on the
> table. Change it to a Totals query by clicking the Greek Sigma icon (looks
> like a sideways M). Leave the default Group By on the fields which identify
> the group of which you wish to see one example; select "First" for the
> remaining fields. You'll see the first record in disk-storage order (an order
> which you can't control so it's basically arbitrary).
>
>              John W. Vinson [MVP]

John
Thank you, your solution did what I was looking for.  The table has
several duplicate records for the address field. What I wanted was a
list of unique addresses.  Thanks again.

0
BRC
4/16/2007 5:16:32 AM
Reply:

Similar Artilces:

I am trying to write a macro using If...Then
I am trying to write a macro that will look at the value in column G. If that value is blank or zero, then select the entire row and delete it. Then I want it to populate columns A with a number "111119" and column H with "TEXT" Here's what I tried, but it's not working. (There are other sorts, etc. that precede this item in the macro, that seem to be working fine.) If Range("g:g") < 1 = true Then ActiveWorkbook.Worksheets("Tab3 Upload").Rows.Select Selection.Delete End If If Range("b:b") >...

how to close an application using automation
Hi I am writing an application using VB.NET and office 2003. I write some data into the Excel sheetand tehn close it but, I can still see teh excel.exe running in the task manager. .Please help me...:(( The code I am using is : Dim Excel As New Excel.ApplicationClass ' Get a new workbook Dim oBook As Excel._Workbook = CType (Excel.Workbooks.Add(oTemplate), Excel._Workbook) ..... .... oBook.SaveAs("c:\SearchResults.xls") Excel.ActiveWorkbook.Close(False) System.Runtime.InteropServices.Marshal.ReleaseComObject (oBook) ' releases the workbook object oBoo...

why CStatic.m_hWnd not valid use realtime thread
Hi All I build an MFC app, and loadlibrary a dll which use high priority (realtime level ) threads for serial communication but after freelibrary the dll, some CStatic and CEdit objects not valid, their m_hWnd becomes to NULL How can I restore these CWnd objects? Is OS release them thanks, waiting online ...

A strange crash in VC++ using VS 2002
Hi, I have encountered a pretty strange crash in by VC++ program using VS 2002. First, I have a class A, and a member variable m_var of type_B (type_B is a simply struct with some simple integer non-pointer fields). One of the member functions Func() calls a function foo(type_B var). (I have no access to the source code of foo). The question is that when I pass m_var into foo (i.e., foo(m_var)), Func() will crash. But when I pass a local variable in Func() to foo (i.e., foo(local_var)), it works fine. Please see the following codes for more details: // The crashing scenario #inc...

When was a DL last used?
I am trying to script or develop some sort of way to see which DLs are not being used. I know promodag has something to this effect but I am at a new job that apparently does not have message tracking turned on. Can this be done by date or other means? ...

Consecutive dates using two cells at a time.
I want cells a1 and a2 to have a date of Jan. 1/09 then have cells a3 and a4 to be Jan.2/09 etc.. in this consecutive pattern for the entire year. Is there any fast way to accomplish this other than manualy entering the date two cells at a time? Any help much appreciated! On Wed, 14 Jan 2009 10:52:05 -0800, B.G. - Canada <B.G. - Canada@discussions.microsoft.com> wrote: >I want cells a1 and a2 to have a date of Jan. 1/09 then have cells a3 and a4 >to be Jan.2/09 etc.. in this consecutive pattern for the entire year. Is >there any fast way to accomplish this other than manu...

Client Server using TCP/IP
Hi there... I want to write a small utility. It should be front- and backend running on different machines. I don't want to handle error correction or timeouts by myself. Communication is TCP/IP. How could I easyly do the communication? CSocket or something else. Thanks in advance Stefan "Stefan Kittel" <stefan.doofnase.kittel.NoSpam@web.de> schrieb im Newsbeitrag news:OFsMb9ijFHA.2644@TK2MSFTNGP09.phx.gbl... > Hi there... > > I want to write a small utility. > It should be front- and backend running on different machines. > > I don't want to...

Use VBA to design Excel report
Hello - I've got an Access application that uses VBA to programmatically create a series of Excel reports. I'm having a little trouble formatting the cells correctly. The way you would code it in Excel isn't always working in Access. Does anyone know where I can find a good reference on the proper format? I've figured out basic font changes, but I'm having trouble with number formats and borders. Thanks! Sorry, I should also include sorting. Can't seem to get the wording correct for that either. Thanks again! "Michael" wrote: >...

Error when using 'Insert Hyperlink' connecting to a web page
It is now impossible for me to insert a hyperlink throughout the MS Office 2000 Suite. For instance, in Word 2000 the normal procedure to add a web hyperlink to a word or phrase in a document is to: 1. Highlight the word or phrase 2. Click the insert hyperlink icon 3. Click Web Page button 4. Navigate to the website to link to 5. Return to the insert hyperlink box, where the link is displayed 6. Click OK I get as far as 3 above and an error message box pops up with the message: 'An error has occurred while browsing files'. I have tried 'Repairing' Office an...

Using Office 2007, How do I set up a layout for printing a book?
How do I set up Word for writing a book? Open up Word and start typing. I know I am being facetious, but it is a rather broad question. What KIND of book? Plus your subject is a question asking about PRINTING a book, but your post asks about WRITING a book. Perhaps more specific questions may help. miriamartn wrote: >How do I set up Word for writing a book? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/word-programming/200912/1 See "So You Want to Write a Book with MS Word" by fellow MVP Daiya Mitchell at: http://daiy...

Moving a sheet from one Excel file to another?
I have two Excel files containing several sheets each. How do I move a sheet from one file (workbook) to another? I tried the move or copy function in the file I want to move the sheet from, however, the file I want to move the sheet to does not appear in the "To book" drop down list. You need to open both files. -- Greetings from New Zealand Bill K "Kathy" <Kathy@discussions.microsoft.com> wrote in message news:AF5B4D23-CBB8-4D7D-8C3D-FA2C0DBD7A67@microsoft.com... >I have two Excel files containing several sheets each. How do I move a >sheet > f...

'have server reply using a specific message' rule
First let me apologize for just posting without reading the 3500 entries my reader found. If this has been discussed before I didn't find it using some simple search on the newsgroup. Anyway here is what I want to do: I'm trying to setup some rules that will take an incoming email with specific words in the subject, then with specific words in the body move it to subfolders and then reply back to the sender with a specific message. In this case we are having customers use a registration form. Once they register if they are not some spam locations we get filling out forms etc e...

Using Outlook for Internet Mail
I just switched from Outlook Express to Outlook. I have set Outlook as my default e-mail client in Internet Explorer but if I click on an e-mail address on a web site, it starts OE to send the message instead of Outlook. How do I set it up to only use Outlook to send messages from the e-mail links? I am using Windows XP Pro, w/ Outlook 2000. ...

Audit trail of case assigment using Workflow
I am trying to create an audit trail of case assignments in MS CRM1.2. When a user assigns teh case to another user, the application logs who is reassigning the case, who the new owner is, and who the old owner was, preferably with date and time. I had thought about using workflow to create notes on the case, but cannot see how to establish the original owner, and the user who is re-assigning. Is there a way around this, and is workflow the best method to achieve this? You might take a look at this ISV... does more than you want, but it is cool! http://www.vscrm.com/ Larry "NickB...

Vlookup
Hi, I have different numbered catalogs. I also have a spreadsheet showing the item price on the particular catalogs. 120 143 150 160 item 1 $20 $30 $35 $34 item 2 $30 $40 $40 $35 item 3 $40 $50 $45 $39 I get catalogs every month where I have to do a vlookup to check that the prices on the catalog match the one's on the spreadsheet above. When I do the vlookup function, I would like to identify the catalogs by their name (number) and not by the column index number when I input the col_index_num of the vlookup function. I want the col_index_num to ind...

Cannot Utilize a Macro When Excel Is Opened By Another Program
I have created a macro in the Personal.xls workbook that is located in my XLStart folder that works as I desire whenever I open a workbook. I have a second program that manipulates various Access databases and will automatically export an Excel spreadsheet of data by pressing one key. My problem is that when this second program automatically open a spreadsheet with the data in it I can not execute the macro for some reason. I am using Microsoft Office 2003. Any help is appreciated. Doesn't anyone have any idea or info on this issue? "Jim Chesher" <jchesher1@sc...

Is is possible to make Exchange 5.5 relay mail using SMTP?
I am currently in the process of migrating from Exchange 5.5 to 2003. I would like to have 5.5 send mail to other 2003 Exchange Servers in the orginization via SMTP rather than X.400. Is this possible? How can I accomplish this. Thank You. Why you do you need 5.5 to do that ? you can configure your Exchange 2003 to do that , you can confiugre SMTP connectors between your exchange servers to deliver internal emails if you don't want to you X.400. Regards Alaa Alian Al-Ankar Blog: http://ExchGuru.Blogspot.com "Ed" <Ed@discussions.microsoft.com> wrote in message ...

IF statement inside a SUMIF statement.... or alternative method
I'm just not sure whether this is possible or not. Can I create a function that goes as below: =SUMIF(IF(......),Z,A:A) It seems when I insert an IF statement inside a SUMIF statement, Excel returns #VALUE! If this is not allowed, how do I get around this problem? Hi, the sumif already has the criteria, what do you want to achieve, please post an example "Sungibungi" wrote: > I'm just not sure whether this is possible or not. > > Can I create a function that goes as below: > > =SUMIF(IF(......),Z,A:A) > > It seems when I in...

How do I return to 1st slide when not in use?
I'm makeing a touch screen ppt and I want the ppt to return to the homepage after 5 min of non use. Can I do that? On 12/8/09 1:55 PM, in article 1DEBBFEC-0713-4A6E-90F5-8138A0BC3C2B@microsoft.com, "mpmb" <mpmb@discussions.microsoft.com> wrote: > I'm makeing a touch screen ppt and I want the ppt to return to the homepage > after 5 min of non use. Can I do that? Kiosk mode (Slide Show > Set Up Show > Browsed at a Kiosk) includes this functionality, but it has only worked sporadically in some versions. Which version of PowerPoint are you using? ...

Using XMLDocument to load chinese characters
Hi, I am getting an error while loading a file having Chinese characters. I am using XMLDocument to parse the XML. Please let me know if you have any idea about the same Thanks Hitesh wrote: > I am getting an error while loading a file having Chinese characters. I > am using XMLDocument to parse the XML. What error message do you get exactly? If the encoding of the XML file is not properly declared, for instance in the XML declaration, then the parser might find bytes it can't decode and that is an error. So make sure you use an encoding that allows Chinese characte...

Double click to send cell to another app?
I have NO clue how this is done, or even if it is an excel command... But I have a spreadsheet that work has sent me that if I double click on any cell in the A column, it captures that cell data and sends it to another window then does an Enter command. I have looked all through the spreadsheet and I can't find any macro at all, or anything that might do this. I doubt it is another application because I setup another spreadsheet to mimic it, and it will not do it, so it has to be tied to that spreadsheet somehow. Does anyone have any clue how this works, or if it is no...

Using Autofill to continue a sequence
Dear All, I have a sequence which goes:- =If('Name 1'!$AG$8>0,'Name 1'!$AG$8>0,"") =If('Name 2'!$AG$8>0,'Name 2'!$AG$8>0,"") The spreadsheet is large so if possible I would like to know if there is a quick way of continuing the sequence. e.g. =If('Name 3'!$AG$8>0,'Name 3'!$AG$8>0,"") Anyone got any ideas? Many thanks, Danny DannyJ, Entered into a cell on row 3, then copied down. If not entered in row 3, then adjust the $2:$2 to be one less than the actual row of entry. =IF(INDIRECT(&qu...

Using DYNSA for to give Users access to new Company
We are using DYNSA as our System Administrator. Setting up new users is no problem, but when we attempt to give them Company Access, we cannot do it. The Tools/Setup/System/User Access menu path works and it presents the "User Access Setup" screen no problem. However, when we try to click the checkbox next to the company name, we cannot tick the checkbox. Has anyone else encountered this problem? Is this a bug of some sort? -- Pete Power Pete, SA is a special user id that has rights not only in GP but also in MS-SQL. When you attempt to give access rights to companies...

Update field names using a query
I have to do a daily import from our accounting software. I have queries created that update a lot of the information that I need. However, I was wondering if there was a way to do a Make table query or an update query that I can rename the field names instead of me mannualy doing it each day. After I clean the data, I have to export it into our leads database and the field names have to match. There are about 15 fields in this import that have to be renamed every day, I was just wondering if I could write some kind of query that could update the field names for me. Thanks for any in...

Cache not working as expected when using list(of integer)
Hi, Please can someone explain what is happeing here. I'm caching a list of integers but they appear to dissapear when the original values are changed even though there is no dependencies set up. Example code below. Why does the cache dissapear? <%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http:// www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server"> Dim my_int As Integer Dim mylist As New List(Of Integer) Dim myotherlist As New List(O...