Query to hide duplicate records

  I recall that this used to easy in previous versions (Unique values 
only ??), but in 2007, I can't get this to work at all.

  I have a table with our companie's job numbers in it.  The job numbers 
show up multiple times because of different phases of the project:

ProjNum	ProjDescription
3077	Univ. of Vermont/UC/LEED
3077	Univ. of Vermont/University Commons: Building Fee
3077	Univ. of Vermont/University Commons: Excess Professiona...


I need to jut have a single listing of each project number, otherwise, I 
get repeated records in the query that looks at this information (which 
of course, causes a bad report).

  I'm following the section on duplicate records in Help, but none of 
it's instructions are working for me. The result is always all the records.
0
Jim
2/20/2008 10:13:59 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
2194 Views

Similar Articles

[PageSpeed] 49

Jim,
Create a query with one field only - ProjNum.
In query design set Unique Values to Yes.
If you include the field ProjDescription, you will get the results you 
posted.

Jeanette Cunningham

"Jim Helfer" <JimHelfer@newsgroup.nospam> wrote in message 
news:%231li22AdIHA.4712@TK2MSFTNGP04.phx.gbl...
>
>  I recall that this used to easy in previous versions (Unique values only 
> ??), but in 2007, I can't get this to work at all.
>
>  I have a table with our companie's job numbers in it.  The job numbers 
> show up multiple times because of different phases of the project:
>
> ProjNum ProjDescription
> 3077 Univ. of Vermont/UC/LEED
> 3077 Univ. of Vermont/University Commons: Building Fee
> 3077 Univ. of Vermont/University Commons: Excess Professiona...
>
>
> I need to jut have a single listing of each project number, otherwise, I 
> get repeated records in the query that looks at this information (which of 
> course, causes a bad report).
>
>  I'm following the section on duplicate records in Help, but none of it's 
> instructions are working for me. The result is always all the records. 


0
Jeanette
2/20/2008 10:19:08 PM
Reply:

Similar Artilces:

Hide a sheet
Is there a way of hiding a worksheet and only allowing it to be unhidden if a password is supplied? Or is there any other way of achieving this? Thanks for any help! Gavin I do believe that this might do the very thing I need - I'll test it first thing in the morning! http://www.vbaexpress.com/kb/getarticle.php?kb_id=380 "gavin" <no-one@home.co.uk> wrote in message news:dg9md0$g2l$1@newsg4.svr.pol.co.uk... > Is there a way of hiding a worksheet and only allowing it to be unhidden if > a password is supplied? Or is there any other way of achieving this? &...

how do i delete cells or duplicate cells in a table ?
I went to make a table with 8 colums and 3 rows, which turned out fine..but now i need to make 3 more rows added on but half the size long with only 2 colums.. so i figure i can just duplicate or copy from my table 3 of these rows and shirk it and delete 1 cell...but trying to get help on how to add or remove cells seems the hard part ...

CRM 4.0 records reverting to old state; caused by upgrade, rollup?!?
Hi all, A quick timeline before explaining my issue: - We installed CRM 3.0 a couple of years back. We imported old data from ACT! and used a custom drop down field with the value "Migrated from ACT!" to distinguish. - Users have put in a lot of work cleaning up those records, merging when necessary, assigning to right owners... - At some point we installed rollup 1 for 3.0 successfully. - Last year, mid-December, we upgraded to CRM 4.0 (I believe) successfully. - A month later, mid-January, we installed rollup 1 for CRM 4.0. - A few months later, some users were complaining about d...

Query Text in the Mid of a memo
I am trying to query off a memo field [Description] the text that follows "User Name: " until the first space the "User Name: " is not in the same position nor is the text of the user name a fixed size. I am working with an InStr function but I don't know how to stop at the " " after the name. Thanks so much for any help. AngieSD wrote: >I am trying to query off a memo field [Description] the text that follows >"User Name: " until the first space the "User Name: " is not in the same >position nor is the text of the user n...

Help creating two listings from one record
I have extracted "members" from my database. One record will contain LastName and FirstName, and may also contain SpouseName. Because I am creating membership cards in Publisher, I think I probably should actually create another query or ?? that contains one record with either [FirstName LastName] or [SpouseName LastName] Probablyl something like ... If SpouseName greater spaces List SpouseName LastName else next sentence. List FirstName LastName I don't have a clue how to do this in Access. Appreciate all help. Try this -- [FirstName] &a...

Recording a charitable donation in MSM 2005
How do I record a charitable donation in MSM 2005? There is no one answer. You also provide precious little context to give a precise answer. Many people assign the transaction to some expense category like "Charitable Donation:Cash" or "Charitable Donation:Non Cash". "Cary" <carygee@aol.com> wrote in message news:OKrvtsfaFHA.3048@TK2MSFTNGP12.phx.gbl... > How do I record a charitable donation in MSM 2005? Dick Watson wrote: > There is no one answer. You also provide precious little context to give a > precise answer. > > Many peop...

Make (save) a file by using macro recorder.
Hi all, I am new to macro and using macro recoder to create macro. On one file which I recieve every week from material department. I need to reformat and create pivot table. I use macro recorder and everything is working good. However I need that when my recorded macro end it create a file with the same name with the date and time it was create. I need every week a new file and I dont want to replace the one. Please let me know how I can create a new file with the date and time everyweek when I run my macro. Thanks -- Farhan Hi Farhan This should do it, just insert you...

Local OLAP cubes in Excel/MS Query
Hi Can someone please recommend some good book or web page about creating local OLAP cubes using MS Query. I have some data in Access that I want to work with using cubes. Kind regards IgorM http://wang.se/en/CreateOLAPCube.html/ Ed Ferrero Hi Thanks for response. Unfortunately I still don't know if the show-details-after-double-click feature is available in pivottables linked to OLAP cubes. I tried all three connection types. With the two first options (rebuild at once and rebuild when needed) I still cannot make the pivot table to create a sheet on the fly and provide detai...

Duplicate the Record in a Form and many SubForms
I am using Allen Browne's tip that is available in his web site. However, I get a compile error stating that the variable dbFailOnError is not defined. I have search the MS Knowdledgeable base and found out the DBEngine (0) (0).Execute is no longer used in current versions. I change it to the following but I still get the same compile error message. If Me.SF_Adjacencies.Form.RecordsetClone.RecordCount > 0 Then strSql = "INSERT INTO [Adjacencies] ( DataRmID, AdjID,_ AdjRoomName, AdjRelevance ) " & _ "SELECT " & lngID &...

Hiding exchange alias in adress list
H Want to know if it's possible to hide the exchange alias from the adress list, only the alias nothing else and is it possible to set the maximum recipients to send to on a group, eg. have users that only should be allowed to mail 1 user a time I know that this can be done under exchange general for a single user, but I have 9000 users that need that restriction thanks in advance Joakim Larsso ...

View query results in MsgBox
I have developed qryQuickLook that brings up rptQuickLook when cmdQuickLook is clicked, everything works great but I would rather see the results (never more than 10 line items) in a MsgBox than have a seperate report window open up. Is there a way to do this? Thanks Dave On Nov 23, 11:00 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com> wrote: > Why "MsgBox"? You can display the results in a dialog form with a command > button or two. > > -- > Duane Hookom > Microsoft Access MVP > > > > "maces...@gmail.com" wrote: > > I have deve...

Using a listbox selection to limit records in a form and report
I am trying to use a listbox of names to limit the results of a form to just those names. On a similar track, I want to use the selected names to limit a report. I have the listbox created and have added a button next to it to initiate the next step, but cannot figure out how to do it. Also, would like to (if possible) use multiple listboxs in the same way (ie, name and/or department). "dascooper" <dascooper@discussions.microsoft.com> wrote in message news:1227B7CC-3345-4FDC-8EB0-94BEF46660FD@microsoft.com... >I am trying to use a listbox of names to limit the resu...

Out of Stack Space ... Recorded Macros
Excel2003 ... I know nothing of writing Code ... I Record Macros only ... That said ... SS working fine until I started getting error (Out of Stack Space) Is there "HELP" for me??? ... Thanks ... Kha SS Calculation set to "Manual" ... Here is my Recorded Code ... I am getting "Out of Stack Space" message on "Calculate" ... However, SS allows me to F9 Calculate??? Sub Sort_Type_Rec_Ascend() ' ' Sort_Type_Rec_Ascend Macro ' ' a = MsgBox("SORT ... Type Record (Ascending)?", vbYesNo, "ATTENTION!&...

subtotal
I am using Excel 2003, recently upgraded to service pack 2. I have a macro that opens a delimited text file, I then copy all, paste values and number formats and then run a macro that subtotals a balance by change in every consumer. The problem is that when, upgraded to service pack 2, the last record does not subtotal, all other records do. I have tried copying only the "live" data after the marco opens the text file. Still the same error. I have tried pasting different ways - values, normal paste, paste values and number formats. Same thing happens - the GRAND TOTAL - ends u...

Help: Need to delete duplicates
I have an access table that has over 470,000 rows. I need to delete the duplicates. None of the "append" query ideas will work as it involves copying and pasing the structure, and Access won't allow me to copy 470,000 rows. Does anyone have a suggestion on how to accomplish this? What error do you get when you run the append query? It could be that the file size is nearing the 2 GB limit. First make a backup copy of the database then run a compact and repair. Then try the append query ideas. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bont...

Duplicate Row
i dont know how to make excel automatically highlight a duplicate row any tips on this? cos its very tedious to look for duplicates on th worksheet. :confused -- Message posted from http://www.ExcelForum.com Katkat, take a look at this page from Chip Pearson's Website: http://www.cpearson.com/excel/duplicat.htm#HighlightingDuplicates. -- DDM "DDM's Microsoft Office Tips and Tricks" www.ddmcomputing.com "katkat >" <<katkat.15p2xe@excelforum-nospam.com> wrote in message news:katkat.15p2xe@excelforum-nospam.com... > i dont know how to make excel au...

Report based on parameter query
I have a report that is based on an ordinary parameter query (i.e. right now it pops up dialogs asking for three values that it needs for its where clause). I use this query in several places, so I don't want to make the parameters dependent on specific form controls, which is the only information I've been able to find on google on feeding parameters in a report query's Recordsource. What I actually want to happen is to make a button in the main switchboard which, when pressed, will look up those values in a different query I already have and then use those to populate th...

Last Record Numbering
I need to find a way to display in a form text box the highest numerical value entered in any one of four fields for the last record entered. For example: If Field_1 is 0001 Field_2 is 0008 Field_3 is 0005 Field_4 is 0004 When the form is opened, the textbox would display the number 0008. The textbox should display the highest of the four fields of the last record. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200708/1 Already responded to in another newsgroup. It is rarely necessary to post and re-post the same question in multiple ...

Combining records in access 2007
I have imported a text file into access 2007. However I guess when the person that exported the text files(i think they are from a sql server db), what was one record in the sql server db, is now broken down into two records in the text file. I am needing to try to take the text files once imported into a table in access to combine two records into one. I just have a autonumber field that is the primary key for each field. So my table looks like this(original_table): Column 1 Column 2 Column 3 1 ABC DEF 2 GHI JKL 3 ...

Print records in continuous form
I have a Continuous Form that based on a Query that selects record according to the Month. What I want to do is to be able to print all the records in this Form by one Command button. So I have the report designed and I have created a Macro “Open Report” +Print Out, but it opens only the first record to print. I did the Where condition on the Command Button On Click: [PM#]=[Forms]![FormName]![PM#]. But it’s not working how I want it to work. How I can print all the records? Thanks for the help. I have tried that and it opens the report with all the records from the original table, bu...

Clearing Combo Box When Advancing to a New Record
I have developed a survey with the following tables: TBL_RESPONDENT [RESP_ID] [SURV_ID] TBL_QUESTION [QUESTION_ID] [QUESTION] [SECTION_ID] [COMBO_ID] TBL_RESPONSE [RESP_ID] [QUESTION_ID] [RESPONSE] TBL_LKUP [COMBO_ID] [COMBO_VALUE] [SORT] In order to enter survey responses, I have developed a form (FRM_RESPONDENT) with a tab control consisting of five pages, each representing a different section within the survey. Each of the five pages is a sub form (FRM_RESPONSE_A, B, C, D, E) which is linked to FRM_RESPONDENT by the shared RESP_ID field on both the form and sub forms. The sub forms ...

Multiple Unrelated Queries In One Report or Form?
Hello guys, I've been tasked with generating a report for my boss that provides an overview of our core database statistics. This includes the total number of orders placed each month and the total value of orders placed each month. I also need to provide the same information for purchase orders, quotations and discrepancy reports. Each individual query is a piece of cake on its own but how can I display all of this information either in a form or preferably in a nicely formatted report? The queries are all completely unrelated so there's no way I can somehow joi...

Query to Compare Actual and Budget
One table defines budget (each one has a unqiue ID) Another table defines employees (each one has a unique ID) Another table defines tasks (each one has a unique ID) Another table records budgeted time by project, task, employee Another table records actual time by project, task, employee I created one query which will total up the budgeted time by project and employee (so if an employee was assigned multiple tasks on the same project, you'll just get one total for that employee for that project). I created another query which will total up actual time byt project and employee (so if an e...

Repost-Edit and save query criteria
Sorry to repost this, but I was not sure I would get help with this again and it would get lost in the list. As suggested, I created a table called ExcludeShows1 with the ShowID and Year fields. I have posted the SQL with those changes to the query. When I tested this initially, it appeared to work. Then I tried testing the queries using last year's show data for 2009. No data is being returned for 2009. Not sure what is happening that it is not returning 2009 data. SELECT Shows.ShowID, Entries.Place, IIf([Entries]>1,([Entries]-[Place])*0.5,0) AS Points1, IIf([...

Subform causes Main Form to show the same record multiple times
I have a form named Patients Info with a subform on it for Household Members. The Patients Info form is created from the table: Patients and the Household Members subform is created from the table:FedPovertyLevel. The table Patients is related to the table FedPovertyLevel in a one to many relationship. The problem I have is that when a second, third, etc Household Member is added, the button for the record selector on the main form now has to be clicked forward by the number of household members. For Example, if only one name is entered as a Household member, there is only ...