Table entry comparison

[MOCaseA]

Hello again,

I am having problems in designing a (hopefully) simple, I think it would be a query. Here is the issue:

I have a table that tracks time spent on specific tasks. It only hase a few fields, but the two that matter are [Name] and [Date]. The table is filled from a form. What I want to do is prevent multiple entries from being submitted for any given day via any given name. Example:

Doe, John made an entry for 01/01/2010, later he came and entered a new entry for the same date. Both entries were saved with unique ID (Primary Key) numbers and when running the reports shows in the totals.

What I would like is for it to either deny the second entry (prefered method), or update the original entry with the new data instead of creating a new (unique) entry.

Can anyone help me?

[Mariostg]

You can modify your table structure to have two fields as primary key. In the table design click on the very left section (it's grey) on your Name field. Hold down Ctrl key then select the Date field. When both field's row are selected click the primary key button.

[MOCaseA]

Unfortunately you suggestion did not work... I have to allow for duplicate name and date entries (40 employees over several days) with unique identifiers. I tried multiple combonations of allowing/disallowing duplicates and it still allows me to create multiple entries on the same date with the same name, or doesn't allow me to make multiple entries with either same name/different date, or different names/same date.

[Mariostg]

I probably don't understand what you try to achieve. Your last reply seem to contradict your first one. When you say "I have to allow for duplicate name and date entries" yet in the first one yousay "What I want to do is prevent multiple entries from being submitted for any given day via any given name"
So my understand was that you cannot have John Doe - 1 Sep 2010 twice...

[MOCaseA]

Correction... I was including the ID in the PK as well which was throwing things out of whack. Fixed it though. Thank you!

[OldBirdman]

I do not like to contradict Mariostg, but I think they intended to create an index with two fields, not a new primary key. Apologies if I'm wrong.

In design view for the table:
1) Put the Primary key back to what it was (AutoNum field? or ...). Create a new index using both [Name] and [Date] field

2) From the 'View' menu, choose 'Indexes' and in the first blank row, type a name, like NameDate, then in the Field Name column, click the arrow and select the first field for the index (Name).
In the next row in the Field Name column, select the second field for the index (Date). (Leave the Index Name column blank in that row.)

3) In the footer, set Primary = No and Unique = Yes. Close the window. If the new index isn't unique in the table, this will fail until you start with these two fields unique when combined.

A further comment: "Date" is a reserved word in Access. Although there is no conflict with a field name, eventually something will go wrong, and you will struggle to find out what. Change to something more meaningful to the database, like "TaskDate"

[MOCaseA]

Actually what Mariostg recommended worked perfectly. I know that Date is a reserved word, as is Full Name (another table) and a few other of the fields I'm using across 18+ tables. It actually works quite well as a few of the functions I'm using to propegate data have these reserves in place and save me a lot of headaches. Only once have I run across and issue with the reserve interfereing and it was a relatively easy work-around to fix it.

[OldBirdman]

Not sure I understand your reasons, but if it works for you, it works for me. Just thought that "BTW... I'm a coding "dummy" " might mean you wanted suggestions along with solutions.

[MOCaseA]

This wasn't so much a coding issue as it was an unfamiliarity with Access primary keys (I've never had to use more than one field as a key before, so I learned something new).