Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2013
    Posts
    6

    Unanswered: Prevent duplicate dates

    I have tblWO_Points and column with firstname and lastname and dateWO column. What code do I use to prevent duplicate dates for same firstname and lastname? Also if a date is a duplicate that it wil ask if you want to go to that record?

    Example:
    Jane Doe Wednesday, January 07, 2012
    Jane Doe Tuesday, January 06, 2012
    Mike Doe Wednesday, January 07, 2012
    Mike Doe Tuesday, January 06, 2012
    Mike Doe Thursday, January 12, 2012

    So the same dates can occur for different people but the same dates cannot be recorded for the same person.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The classic way of handling this is to create a UNIQUE constraint.
    Whether you do that by making the person a d date values the primary key or just declare an additional index with a unique constraint.

    However there are a few problems.
    First offyou would better using a numerical representation the person (effectively a foreign key to a table holding details of persons). Why:- its a lot easier to constrained the data. Ifyou force users to select a person from list then they are less likely to enter duplicates through typos. Computers are dumb. They do not recognise that mike doe, Mike Doe, Michael Doe could all be the same person.

    Dates Can be an issue in any DB. They should always be stored in the DBs appropriate format usually date or datetime NOT as text or string. However many store time alongside the date. Access is no different. But for your app you need to make certain you only store the date portion. This means using the function date() to use the current date not now() which uses the current date and time.
    A way round this in this case is to force the date part by setting the datatype to long (integer). However that practical approach may cause problems over time as the data storage mechanism the DB may change.

    Another problem is that you will have to get your data sorted before attempting to create the unique index. Ie remove all duplicates prior to attempting to define the index.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    i agree healdem -Sometimes if the format for a date is now - he may never have a duplicate
    i always use the format(mydate,"short date") function for date fields as I really have not developed apps that conatin time sensitive issues - but in looking at this - i think bishop is more concerned with duplicating dates for the same person - did I misread that?
    Dale Houston, TX

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by axsprog View Post
    i agree healdem -Sometimes if the format for a date is now - he may never have a duplicate
    i always use the format(mydate,"short date") function for date fields as I really have not developed apps that conatin time sensitive issues - but in looking at this - i think bishop is more concerned with duplicating dates for the same person - did I misread that?
    the stated requirement is that you cannot have a row with the same date AND the same person.

    hence why I suggest a composite index that identifies the person and date. As said before I see two issues. one the date needs to be stored as 'just' a date with no time element AND making certain the person is uniquely identified.
    using short format is precisely that a format. using date() in place of now() stores only the date portion.

    using an integer to represent a person (as a FK to a table defining persons) stops problems with typos of a persons names.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2013
    Posts
    6
    I am new to all of this. I tried to research on what you suggested but cannot find anything that I can replicate. Could you walk me through the steps for this?

    TIA

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    work on a copy of your tables
    freeze the existing db and take a safety backup copy. you cna never have too many backups when doing theis sort of thing

    first clean your existing data, remove any duplicates

    make certain you are using datetime datatype for your column that refers to the date

    create a table for Persons that stores anything related to the person... use an autonumber or other form of value to create a unique index for each person called say ID
    using an INSERT INTO query copy all the existing personal details (the names) to the persons table)

    then add a column to the current table, called say PersonID, make it the same type as the unique column in the Persons tabel
    run an update query that sets the value of this new column based on the ID in persons where the lname & fname match

    eg:- update mytable set PersonID = persons.id where
    mytable.fname = persons.fname and mytable.lname = persons,fname

    then define a (RI) relationship between persons and your current table on the primary key of persons (ID) = PersonsID in your current table

    then check your data
    attempt to set a new primary key which includes both the personID and the datecolumn (to do that click the personid And then hold down the control button and click the datecolumns) then select primary key

    once you are happy its working delete the fname and lname from your current table. this will stop any existing forms, reports or queries setting these values anmd report an error to you

    in short its about implementing normalistion
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jan 2013
    Posts
    6
    Can you take a look at this? I am stuck...
    Attached Files Attached Files

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what are you stuck on
    what have you tried
    what isnt working
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK you got most of the way there

    however I think you have some real problems building with that design
    its not normalised

    The Relational Data Model, Normalisation and effective Database Design
    Fundamentals of Relational Database Design -- r937.com

    I don't want to dive into a homily about normalisation and table design but when ever I see something like beginning, current and end is always rings alarm bells

    if what you have got there meets your requirement then run with it
    Attached Files Attached Files
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •