Results 1 to 6 of 6
  1. #1
    sakmsb123 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    3

    Combination of the 3 fields has to be Unique.

    Hi All,

    This is my First Post here.

    Need help with AccessDB, it should not accept duplicate values.

    So I have a DB, there are many columns, but 3 fields should always be unique; Time, Date and Name.Click image for larger version. 

Name:	Duplicate.PNG 
Views:	9 
Size:	3.2 KB 
ID:	16685

    I attached a pic, such entries should not be accepted.

    Values are being entered into DB via Excel using ADODB connection. And, like infinite Dup values have already been entered.



    How to go about this? Please help!

    Thanks!

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    i you just need a unique list of values just create an aggregate query, I would recommend staying away from reserved words like name, time and date but if you are stuck with those column names you could do something like:

    SELECT [TableName]![Name], [TableName]![Time], [TableName]![Date] FROM TableName GROUP BY [TableName]![Name], [TableName]![Time], [TableName]![Date]

    If you are looking to append your data again and only keep unique values, then the easiest thing may be to create an index/primary key based on all three fields. You'll have to dump your current data and re-import, you should get a notification saying how many records were NOT added due to key violations and you should be left with non duplicated items.

  3. #3
    sakmsb123 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    3
    Quote Originally Posted by rpeare View Post
    i you just need a unique list of values just create an aggregate query, I would recommend staying away from reserved words like name, time and date but if you are stuck with those column names you could do something like:

    SELECT [TableName]![Name], [TableName]![Time], [TableName]![Date] FROM TableName GROUP BY [TableName]![Name], [TableName]![Time], [TableName]![Date]

    If you are looking to append your data again and only keep unique values, then the easiest thing may be to create an index/primary key based on all three fields. You'll have to dump your current data and re-import, you should get a notification saying how many records were NOT added due to key violations and you should be left with non duplicated items.
    Hey Thanks for replying. My DB has them as S_Name S_Time n S_Date(should have mentioned this before, Apologies).

    Yes I have tried creating an index to no avail, may be I'm doing it wrong, I'm totally new to Access.

    Tried this but... http://office.microsoft.com/en-us/ac...005187564.aspx

    I'm ok appending entire DB, Could you please help me with exact Steps of doing this, I'd really be thankful.

  4. #4
    sakmsb123 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    3
    hey, it's is working fine now.

    one issue:- quoting from link pasted above--The default sort order Ascending. Select Descending in the Sort Order column of the Indexes window to sort the corresponding field's data in descending order.


    Successfully created index with those 3 fields and working fine but I want to be default Ascending Sorting only by ID of generation of records... currently the IDs are randomly sorted based on my above sorted index...

    and if I put ID in index, it starts taking dup values again.
    Last edited by sakmsb123; 06-04-2014 at 09:47 PM. Reason: test

  5. #5
    hapm is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    197
    There should be a difference between your index and the primary key (id field). The id field should be a separate index marked as primary key. Your index should not be the primary key, but should be marked as unique only. Default ordering in a table is always by primary key, if you don't save a user defined ordering when viewing the table in table view.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    as hapm default sorting in a table is by the primary key but you can sort it any way you want with a query, just create one based on your table and sort in ascending order on the field you want.

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

Similar Threads

  1. Unique ID's across multiple fields
    By mackoski in forum Access
    Replies: 3
    Last Post: 08-14-2013, 05:15 PM
  2. validation of unique fields
    By thanosgr in forum Programming
    Replies: 3
    Last Post: 05-02-2012, 09:59 AM
  3. Replies: 3
    Last Post: 08-25-2010, 07:04 AM
  4. Replies: 1
    Last Post: 05-21-2010, 02:22 PM
  5. Prevent Duplicate Values on Combination of Two Fields
    By LornaM in forum Database Design
    Replies: 8
    Last Post: 05-05-2009, 11:16 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