select distinct id with other data

Hello everybody,
I have a table with such data:
id      name
1       Mario
1       Mario Krsnic
1       MKrsnic
1       MarioK
The same Id refers to different names of the same person.
how can I select distinct ID with both columns? If I use select distinct 
id,name then I get the above data. I would like to have only:
1,Mario(or some other variante of the name)
In the result should every id appear only once.
Thanks for suggestions!
Mario 

0
Mario
9/30/2007 9:44:07 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1022 Views

Similar Articles

[PageSpeed] 43

Mario Krsnic wrote:
>I have a table with such data:
>id      name
>1       Mario
>1       Mario Krsnic
>1       MKrsnic
>1       MarioK
>The same Id refers to different names of the same person.
>how can I select distinct ID with both columns? If I use select distinct 
>id,name then I get the above data. I would like to have only:
>1,Mario(or some other variante of the name)
>In the result should every id appear only once.


Try using this kind of query:

SELECT ID, First([Name[)
FROM [the table]
GROUP BY ID

-- 
Marsh
MVP [MS Access]
0
Marshall
10/1/2007 3:20:36 AM
Reply:

Similar Artilces:

Synchronize HQ data from SO
I curretly have 2 stores that usually receive inventory update from HQ. However during the last few weeks I had to data entry from each RMS clients (SO). Now I want to update inventory data at HQ using worksheet. But it seems not possible, although sales data show correctly. Is there any way ? -- Many Thanks In an HQ environment all new items must be entered from HQ and sent to stores. Did you create new items at store level ? You may want to create those items in HQ and send to store. Mihir Shah Diviasoft, Inc. www.diviasoft.com "cosamo" <cosamo@discussions.microsoft.c...

Entering Data to make a list?
Is there a way that when I enter something into A1 &B1 it will fill down on a List? Like if I type apples in A1 & $12 in B1 on a certain sheet it will copy Apples and $12 , then if I type Pears & $5 it will copy to the next blank cell and so on -- Thanks in advance for your help....Bob Vance .. .. .. .. Bob I'm assuming you have this list on another sheet. This little macro will do the following: When you enter something in B1 of the first sheet, it will copy A1:B1 to the bottom of the list in Columns A & B of the second sheet. It will then clear (erase) the conte...

Finding the currently selected cell in another worksheet
Hi, In Excel 2002 I want to display the current row of worksheet A in worksheet B (in another format). Does anyone know how to do this? Ideal would be if this updates automatically, but performance wise I guess a macro activated by a button in worksheet A would be the best solution. TBH, Jeroen Jeroen, I don't know of a way in a formula to determine the active cell of the worksheet. Worksheets other than the active one don't have an active cell. I think macros are the only way. Put this in a regular module: Public Roww As Long Put this in the sheet A module: Private Sub W...

how to avoid closing dialog if data validation fails
I would like to validate data entered in a dialog when the OK button is clicked. If there are problems with the data I would like to avoid closing the dialog and keep it open. Can anyone suggest how this is done? Thanks for any suggestions. Ian If you dont want to close your dialog in OnOk, do not call the base class OnOK. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "ian" <ib252@n0spam.com> wrote in message news:F1nnb.26812$He4.1099447@wagner.videotron.net... | I would like to validate data entered in a dialog when the OK button is | clicked. If there are problem...

Select range in macro
Hi all. I have found a macro that will email the active worksheet. On the worksheet I have 2 command buttons and some instructions for the operater so they know where to input info. How can I select a range so that only a specific area will be sent. Below is the macro and the area I want to send is ("A1:J69"). Your help would be of great help. Regards Chris Public Sub Email_Midnight_Report() Dim wb As Workbook Application.ScreenUpdating = False ActiveSheet.Copy Set wb = ActiveWorkbook With wb .SaveAs "Email_ " &...

Push data to safe senders list to Outlook possible?
Is it possible to push a safe domain list to everyone's Outlook Junk Email safe senders list from Exchange (or any other method)? We are on Exchange 2003, all clients are Outlook 2003. Thanks. On Mon, 13 Feb 2006 13:10:29 -0800, "David" <David@discussions.microsoft.com> wrote: >Is it possible to push a safe domain list to everyone's Outlook Junk Email >safe senders list from Exchange (or any other method)? We are on Exchange >2003, all clients are Outlook 2003. >Thanks. Outlook11.adm in a GPO will allow you to set the default directory clients use...

Data Lables, series and Scatterplots
Is there any way to get excel scatterplots to recognize a third column (or row) as a series for a scatterplot? FOr example I have data as: Sequence Long Lat 1 49.12 28.05 2 49.17 28.34 3 49.5 28.48 4 49.17 28.36 I would like to plot Long(itude) as X axis and Lat(itude) as Y on scatterplot (which I can do). But I would also like to identify the sequence number fopr each point. I thought I could do that by getting the Sequence numbers to represent a series, but can't sseem to get that in a scatterplot. Is there any other chart...

Selective Out Of Office Reply #2
Is there a way to configure the ooo reply so it send different messages depending on the sender No, use Wizard Rules to create a custom reply for this. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Navigation Pane Tips & Tricks -Create an Office XP CD slipstreamed with Service Pack 3 ----- "Leon" <greg_wikle@balinet.com> wrote in message news:O3QiWKMcEHA.2660@TK2MSFTNGP12.phx.gbl... > Is there a way to configure the ooo reply so it send different messages > depending on the sender > > ...

Addins attached only to selected workbook(s)??
Using Excel 2003 I have written a set of macros that is used with workbooks with a lot of hand entered data. Right now the macros need frequent updates as more features are added. To facilitate providing updates, I felt it was necessary to separate the code from the data so that the user didn't have to do anything when the code was upgraded. To accomplish this, I created an addin containing the macros. Now I just email the new addin and users are good to go. The problem is that the addin is automatically attached to every workbook I open and there are a number of reasons that ...

Sorting column data
How do I sort the data in the columns, I want to make the appear in alphabetical order? Alan I recommend Excel help. Search for: Sort a range. -- Steve "alan.holmes" <alan.holmes27@somewhere.net> wrote in message news:3S9sk.133571$Mn3.46670@newsfe30.ams2... > How do I sort the data in the columns, I want to make the appear in > alphabetical order? > > Alan > > "alan.holmes" <alan.holmes27@somewhere.net> wrote in message news:3S9sk.133571$Mn3.46670@newsfe30.ams2... > How do I sort the data in the columns, I want to make the appe...

Using SUMIF to add data between a range of dates
Hi, I am developing a cashflow spreadsheet, and need to add a range of values (in column B) based on the criteria that they are relating to a set week, ie in column B has the amount to be paid, and column C has the date the amount is due. I need to find out the total amount due between 2 dates. Does anyone know how I can do this? Hi With start date in B20 and end date in B21 try this: =SUMIF(C2:C5,">=" &B20,B2:B5)-SUMIF(C2:C5,"<="&B21,B2:B5) Regards, Per "Jaspa" <Jaspa@discussions.microsoft.com> skrev i meddelelsen news...

Criteria to return all records if selection from form is null
Hi, I'm trying to pass multiple query criteria from form controls. I'm using the following type of syntax for the criteria: Like Nz([Forms]![ViewEdit Completed Procedures]![SelectOBy],"*") And this works fine for the fields where there are no Nulls in the data, but if there are Nulls in this field I don't get those records. Quite understandable since Like "*" doesn't return Nulls. What I'm trying to do is to return all records when there is nothing chosen in the selection box on the form. I've tried a bunch of different IIfs wi...

distinct values in column
I like to format a column such that it allows only distinct values. fo eg in the column A i have 10 numbers 1to 10 (A1 to A10) . if i ente any value between 1 and 10 in A11 it should not allow. A11,A12.....et should allow only distinct values. pls help me to do this thanks in advanc -- parthaemai ----------------------------------------------------------------------- parthaemail's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3131 View this thread: http://www.excelforum.com/showthread.php?threadid=51928 Data > Validation > Allow whole number > Betw...

Refreshing query data via vba button
I have a wookbook which has 5 worksheets that contains sql query's that are bulling data from an access 2003 db, instead of me right clicking on each worksheet to refresh the data, is there any vba code that i can import that will enable the user to refresh the worksheet via a single button, been racking my brain, could someone help me please.............!!!!!!!!!!!!!!!!! -- RzaXL ThisWorkbook.RefreshAll MIke F "rzaxl" <rzaxl@discussions.microsoft.com> wrote in message news:5A2F6E4B-5A45-4AA2-8F4E-CEA3AFE0C1F5@microsoft.com... >I have a wookbook which...

Excel 2007: Return rownumber when text data on 3 columns match exa
Using: Excel 2007 I want to document Windows Group Policy settings side by side on a single sheet. To accomplish this task I want to return the row number of the current sheet where the data of the row that has an exact match. The data I want to lookup is in three colums. Example: Column B | Column C | Column D ------------------------------------------------------------------------------------------- Policy | Control Panel | Load a specific visual style Policy | Desktop | Do not add shares Policy | Desktop | Hide Network Location...

Event ID 9665
I have been receiving the subject ID (memory settings for this server are not optimal for Exchange) on a Windows 2003 Standard Server that hosts my Exchange Server 2003 AND is also my DC - with a total of 2GB of RAM. The above event refers to Microsoft KB article # 815372 - which, to say the least, is a bit confusing. My questions are this: 1. Should I be using the /3GB and the /USERVA=3030 switches in my boot.ini file? 2. The SystemPages subkey on this server (HKLM\SYSTEM\CurrentControlSet\Session\Manager\MemoryManagement) is 798720 - and I assume that is a system-calculated value. Sho...

Lookup data in the list
Hi All, I have a list as shown bellow, I want to pick up the date correcponding to the value on cell A1 and the value in B2 or B3. So it is some thing like this =Vlookup(a2,a:b[b2+1] or[b+2] {this is just a vierd example} ColumnA ColumnB ID Name 1 12082A IN-tuition 2 Ms. Gayle 3 Gayle Theresa 4 Sofiane 5 12082B tuition 6 Ms. Gayle 7 12298A Language Institute 8 Senouci Bereksi 9 AOUL 10 Bereksi Kindly Help ASAP, As I am Stuck in my assignment. Hi, Manish. The better way, of course, is to have the ID in col A, the company name in B, the individual name in C a...

Need to set up ID on Outlook Express
---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=56dc2f64-f688-4919-a5ea-23b1a55eb054&dg=microsoft.public.outlook Try posting this in an Outlook Express news group - this is not one of them. Outlook i...

Big trouble matching with data on excel!
Hi, I'm required to present some info at my work, using two excel files. This is the task: File 1, has ID numbers in column A, and column B is blank. File 2, has ID numbers in column C and column D has the date the person joined the company File 1 es a select grop of people (about 2500), whereas File 2 is the general database (about 30thou) They're asking me to match the data, in order to end up with the coresponding date they joined the company in column B on File 1 (obtained from column D on file 2), how to do it? taking in account that File 1 is outdated and ...

How to let field sales people use our data base (without ASP/Scripting) ??
We have a data base we want our field sales people people to use daily. We have all our queries and forms and reports done in Access. We do not want to have to re-do it all in ASP or some other Scripting Language. We think we need something like Citrix and Terminal Server... but they are expensive... right? What are the alternatives available to very small businesses? Thanks for any help. Mel "Mel" <MyEmailList@gmail.com> wrote in message news:1180126800.016508.53210@q69g2000hsb.googlegroups.com... > We have a data base we want our field sales people people to use ...

clearing data
hi guys, i'm new to MBS great plains... i needed help in clearing all the data in the financial, sales and purchasing module (includes all transactions, vendors, customers and etc....) in great plains without deleting the set-up (includes chart of accounts and all) for the 3 modules. i would really appreciate it if you guys can give me the procudures on how to do it... -- iandale You could use File >> Maintenance >> Clear Data. Clear data from things that say "transaction" and "history". Keep away from things that say "setup" or "o...

Exporting Extender Data
Does anyone know which table holds the extender data? I tried searching customersource for some sort of extender table structure, but I couldn't find anything. I want to be able to export this data using crystal reports, but I just need to know how to connect to it. Try Techknowledge #899573 "ConnollyandCo" wrote: > Does anyone know which table holds the extender data? I tried searching > customersource for some sort of extender table structure, but I couldn't find > anything. I want to be able to export this data using crystal reports, but I > just n...

How to Extract Data from a CSV file???
I need help on how to write a VB command that will allow me to extract data from a specified column in a CSV file. I have written a command in VB to extract all data from the CSV file and paste it on an excel worksheet. This method is working fine but because the file is too big, it takes a long time for me to extract everything. I only need the data from one column so if anyone out there can help me with this, I would be extremely grateful... Cheers ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly fro...

Link variable data from one worksheet to another worksheet?
I am trying to link variable data from one worksheet (my source worksheet) to several other worksheets. For example, my source worksheet has two variables or columns: 'Team' and 'Bye Week'. The first entry under the 'team' column reads 'NE'. Let's say for this example that the first entry under the 'Bye Week' column is the number '10'. Given this, I want every entry under the 'Team' column in other worksheets that read 'NE', to read the number '10' under the 'Bye Week' column. Let's also say tha...

How do I resize a data label box in a pie chart to stop text wrapp
How do I resize a data label box in a pie chart to stop text wrapping? Excel decides how large to make these pseudo text boxes, based on the chart size, the amount of text, and the font properties. Aside from changing these, you have no control over the label sizes. You could replace the labels with textboxes, but these don't stay attached to a data point. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Eton Careers" <Eton Careers@discussions.microsoft.com> wrote in message news:B18C20DE-FD79...