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?
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.
8 1744
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.
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.
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...
Correction... I was including the ID in the PK as well which was throwing things out of whack. Fixed it though. Thank you!
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"
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.
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.
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).
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Acorn Tutors |
last post by:
Hi folks, what I would like to do is to store the name of a link, such as
link1.php in a field, lets say the field is called Favoritelinks, in a
database as a bit of text. Then, on a logged in...
|
by: RobertGl |
last post by:
Hi everybody!
My problem is as following:
I have a trigger which fires on delete of a table row. The body of the
trigger checks and manipulates entries in other tables.
If the entries have...
|
by: Robert Oschler |
last post by:
I'm running MySQL 4.0 on SuSE Linux 7.3 Pro. I accidentally granted
priveledges to a user with the following user label, quotes are part of the
user label:
// NOTE: replace with @, this was...
|
by: bbrazeau |
last post by:
Greetings,
I am developing a database that tracks our products life cycle.
It has a table with a memo field for describing revisions to the
product. I have a form (form1) for entering revision...
|
by: geepeetee |
last post by:
Hi
My daughter is creating a db for some homework (age 13) is there an easy way
to link a photo so it shows with the form entry or datasheet.
thanks
Gary
|
by: larry |
last post by:
Ok I am re-coding our apps in PHP and am looking for ways to make parts
easily updateable, One of the challenges in my field (non-rpofit) are
various lookup tables (for incomes etc).
An example...
|
by: Howie |
last post by:
Hi all. Hope you can help.
I occasionally need to enter info directly into a table. However,
when I do, I often need to enter the same value into a large
number of fields.
It would be nice if...
|
by: Addy Smith |
last post by:
I have 2 tables with different versions in access. Both are same but the latest version obviously has some new records and some deleted records from the previous version. How would I compare the new...
|
by: pravin gawande |
last post by:
Hi Folks,
I wanted to put check for the existing table. I got the table entry in
two different tables.
1. SYSCAT.TABLES
2. SYSIBM.TABLES
|
by: gixxer5 |
last post by:
Okay here goes,
I have 3 tables
Person_ID: text;
first_name: text;
last_name: text;
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |