Results 1 to 4 of 4
  1. #1
    dodell is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2010
    Posts
    2

    Retrieve similar records

    Hi

    Can anyone tell me how to write a query that will retieve any records where the same instance appears in a field 3 or more times? I have created a database with one of the fields being name. People can be entered into the database more than once, but if they are in there 3 or more times, I want to be able to report on this. Help!

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    if the records are identical across all fields, then this will return you one record as an indicator of the duplicates:
    Code:
    select distinct from table

    where dcount
    ("name""table""[name] = '" & [name] & "'") > 
    if the records are NOT identical across all the field, you will be forced to see all of the duplicates when you run that sql.

    NAME is a reserved word by access, and as a result it is a good idea never to use them in your object naming

  3. #3
    dodell is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2010
    Posts
    2
    So is it not possible to do it using a query in design view? To give you a bit of background of what I am trying to do, I am a teacher, and I have created a database to log when children are put in detention. We then want to report on pupils that have been put in detention 3 or more times within a specified date range. The fields I have got to use are 'name' (I didn't realise that was a reserved field name - would I be better to change it to 'full name'?) and detention date.

    If I do need to write a select statement, where would I input this, as I have never done it before.

    Thanks for your help.

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    I would write the select statement, but I never use the grid.

    if you do use the grid, realize that dynamically created fields (which are ones you create with functions, expressions, etc...) use this syntax in the "field" name in design view:
    Code:
    TemporaryFieldNameCodeHere 
    So in case your whistler 2000 didn't follow that...as an example, if I use a DCOUNT() function like I've already done in this thread and wanted it as an extra field, I say this in a column of the query grid to make that new field:
    Code:
    CountOfOccurancesdcount("fieldname""table""criteria"
    for simple criteria, you simply type the code I've already posted into the CRITERIA line of the field column in the query grid. it is identical to writing the statement out by hand.

    the different views of the query object are located under the VIEW menu (pre 2007 versions of access) and are on the DESIGN tab in 2007 versions, all the way to the left under VIEW. SQL VIEW is where the statements are kept.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 01-22-2010, 03:21 AM
  2. Replies: 3
    Last Post: 10-06-2009, 02:11 PM
  3. If columns are similar combine?
    By westcoastbmx in forum Queries
    Replies: 0
    Last Post: 09-04-2009, 12:54 PM
  4. Help reqd with Count of Similar Vals
    By AnthonyT in forum Access
    Replies: 1
    Last Post: 05-18-2009, 12:15 PM
  5. Replies: 1
    Last Post: 06-20-2007, 07:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums