I have a modest sized data base and have been having trouble keeping out duplicate records with the same last name, first name, and DOB. I know I could use all 3 fields as a primary key, but I would like a simpler way of checking for duplicates.
View RepliesHi there
I want to avoid duplicate dates in the date field of my form. It's a little more tricky than it sounds though. Imagine two fields in a form; one list box of eleven values and one date field. Once i have chosen a value from the list box i enter the date in the second field. It is ok for one value to have the same date as another value (in another record) but i don't want to have duplicate dates for one value. Any idea how to SIMPLY overcome this problem?
Not really looking to fully understand how it all works, i would be happy just to fix the problem.
Cheers
If different people will be entering data into a "new donor" form, how do I keep them from adding the same person a second time?
It's possible people could have the same name, and it's possible that two people at the same address could be donors. So I assume I would want a "no duplicates" on the combination of name and address, right? But how to actually do that.
Hi,
nuther question from the forum dunce I'm afraid,
I want to ensure the user of the form cannot enter the same [cboPlayerName]
twice for the same [txtMeetDate] (thanks for the advice trubolotta, I changed
the name of the [Date] field.) The user will select the PlayerName from a combo
box. Can I have the list in the combo box diminish by each player as they have
been selected for a [txtMeetDate] I suspect a monstrous amount of code &
intellectual input will be required to achieve this. Basically, what I'm asking
(this time !) is how can I have my program search my tblScratchScores for
duplicate names for the [MeetDate] that I am currently inputting Data for?
Can someone tell me how a primary key in the attached can be set on the index to duplicates OK yet, when you click on the indexes icon on the toolbar, the UR is set to be aprimary key..
View 1 Replies View RelatedHi - it's been a while since I did much access and am trying to help someone out and have got into a pickle! A quick job has turned out to be a lot more complicated! Plus I'm having to use *spit* Acc 2k.
When I go into frmHighCostDrug (from the "add new drug episode" button on the frmPatient) I often get the message "The changes you requested were not successful because they would create duplicate values in the index, primary key, relationship etc etc."
Now, I didn't think I'd forgotten that much. I've got primary keys, values in the table set to Null, relationships set and ref integrity set and cannot for the life of me see why this is happening.
I know though, that one of you will say "oh it's that" and point it out and I'm going to feel completely stupid, but at the moment, I don't mind! I really can't figure it out.
I've attached db - it's in Acc2k and I tried to do a copy Acc'97 for those who don't have it but it caused no end of problems. Came up with messages that this form contains data that Access doesn't support!!!! ??? :confused: If I can sort it I will.
If anyone's got any ideas, I'd be v grateful!
Tnx
Ally
Hi All,
I have one table where ID field is autogenerated, and another field orderno which is not primary key. Now it has 2 duplicate values in it so when I try to make field Orderno as primary key, it is not allowing me to do so.
Can any one help me how to find the duplicate values using query.
Thanks in advance,
Jigs
I've set up a table with 2 primary keys but it is allowing me to input the same combinations twice!
View 4 Replies View RelatedThis is a real mind bender.
I am running a2k. I am merging two databases. table1 is in the backend database. table2 is linked to the second database.
I run an append query to add table2 entries to table1. The append fails for 96 recs saying key violation. Turns out I can rerun the append if I drop the zip code field.
I then try to manually change the zip code and receive the duplicate values in index, primary key, or relationship.
I ran a compact & repair the databases, still no go. I can't edit the value of the zip code. I import the table to a new database. Still can't change the zip code. I drop MANY of the indexes. Even the index on the zip code field. Run compact&repair. still getting the index message. I even re-imported to a new db again. Still same error message.
I may start again by importing JUST these two tables to a new blank db and see if I get the same issues. I'll keep you posted...but maybe there is someone out there who has seen this error before.
fyi-if I enter a new record, the autonumber field correctly increments to the next available autonumber field. (I say this because of another thread on this matter w/ autonumber fields and bug in A2003)
All seemed to be working well, however, I noticed that all my subtable records in the database are exporting with each Primary table record. In my output, I'm looking to see each primary table record followed by one or more subtable records from a one to many relationship.
(Office 2010) Access/Word
Private Sub cmdPrint1_Click()
Dim objWord As Word.Application
Dim docm As Word.Document
Dim db As DAO.Database
Dim rstLandSales As DAO.Recordset
[Code] ......
In my simple database (attached), I need to mass duplicate Tasks and their Notes.
I have three tables: tbTasks (PK: Task_ID), tbNotes (PK: Note_ID), jtbTaskNotes (FKs: Task_ID and Note_ID). jtbTaskNotes is my many-to-many junction table that ties Tasks to Notes.
The main form (fmTasks), bound to tbTasks, has a subform (sbfm_TaskNotes) that displays notes associated with each Task. On themain form,you select which Tasks you want duplicated via a checkbox. The append query (quCopyTasks) will duplicate all tasks that have the checkbox checked. All good there. However, I can't figure out how to also duplicate each task's Notes.
I found Allen Browne's solution [URL] ....., but that only handles duplication of one record at a time, whereas I need to duplicate many records at a time (sometimes 10+ records). How do I go about duplicating multiple Tasks and their associated Notes?
Before you ask "why are you duplicating records?": There are times when tasks need to be re-accomplished and therefore need to have a new record. It's easier to duplicate records than it is to hand-jam everything again.
I have built a qry that initially shows the correct information. For example.
tblContent has 289 records with a Type = Class.
I built a Query to select from tblContent Type = Class and I get 289 records. I add additional criteria of Progress <>"Not Scheduled", I then get 206 records. I then add additional criteria Last Name <>"Demo" And <>"Care" And <>"Support". This brings up 200 records, but the query appears to duplicate each record 3 times. I do not have 3 of the same types of records.
The SQL Statement is below
SELECT tblProfile.LoginName, tblProfile.FirstName, tblProfile.LastName, tblProfile.Organization, tblProfile.CostCenter, tblContent.Title, tblContent.Type, tblContent.Code, tblContent.[Date Assigned], tblContent.[Date Started], tblContent.[Last Accessed], tblContent.Progress, tblContent.[Date Completed]
FROM tblProfile INNER JOIN tblContent ON tblProfile.LoginName = tblContent.LoginName
WHERE (((tblProfile.LastName)<>"Demo" And (tblProfile.LastName)<>"Care" And (tblProfile.LastName)<>"Support") AND ((tblContent.Type)="Class") AND ((tblContent.Progress)<>"Not Scheduled"));
The qry is named qryPhysical Class. I have provided the link to view the database. Can you help me?
http://briefcase.yahoo.com/turnerbkgabrobins
Thank you in advance for your assistance.
I want to find duplicate records based on FirstName and LastName and delete the duplicate. Also, I want to delete any records which have a blank FirstName and LastName.
How can I do this?
Thanks,
Dave
I have a table, and there are many records, each with a unique value in the OrderNumber field
I have a text box on a form, and i want it to work so that the user types in a OrderNumber value, and it loads that record into the form (the form is bound to the table)
I already know how to load a record by record number, but how can i do it by OrderNumber value?
thanks
I recently acquired a database that is already established. When entering new date the primary key is null. I've looked at the properties and the best I can figure is it has something to do with a missing property called New Values.
View 12 Replies View RelatedI have a table which has a primary ID and then date/time then an empid field relating to an empid table,
Is it possible to design a query to return all records per empid which exceed 30 minutes for the last 30 days? or even 'today' or a specified date whichever is easiest?
I'm sure there's an easy solution to this somewhere...
I have a query that is supposed to assign a wage to a production job if the range output falls within a certain percentage range. For example, if they produce 125% to 130% of their goal, their wage is 15 cents higher. If it's between 130% and 135% of their goal, their wage is 30 cents higher. There are about 20 ranges that my bosses want set up. Short of making the longest Iif-Then-Else statement in history, does anybody have any suggestions? Should I use a separate table and somehow try to integrate it into the query?
OK, I've tried everything I can think of with this query/subform/records problem. I've searched the internet for days, even bought some big fat Access reference books and read them cover-to-cover, but I still can't get my database to do what I want. This newbie would *really* appreciate some help...
I am trying to create a database which I can use to store client’s advertising expenses. I currently have three tables: tblListings (which stores basic information about each client, with the field ListingID as the primary key); Advertising Rates (which stores the different types of advertising products and their prices, with the field ProductNumber as the primary key); and tblAdvertisingExpenses (which stores each “order” of an advertising product, including the client’s ListingID as a foreign key, date, the type of advertising, and the total cost - the primary key is an auto-numbered field called OrderNumber).
I have a main form called frmListings, which displays the client’s information from the tblListings table, and includes a subform. I wanted the subform to show only the advertising expenses corresponding to the ListingID (ie. client) displayed on the main form, so I based the subform on a query which displays all records from tblAdvertisingExpenses with a ListingID that matches the ListingID displayed from tblListings. So far so good - the records displayed in the subform change correctly when the record in the main form changes.
Now the tricky part…
The subform displays a blank row for a new record, and I would like to be able to enter a new advertising expense in that blank record (date, type of ad, and cost), and have that data added as a new record in the tblAdvertisingExpenses table with a new auto-numbered OrderNumber. I don’t know whether it’s because the subform is actually a dynaset from a query, or because I’ve constructed the query incorrectly (highly likely, I’m still pretty new to queries), but when I add in the details for the new record, I get the following error:
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship (etc, etc).
I suspect that Access is attempting to write the record (along with the ListingID) back to the tblListings table, where the same ListingID already exists (uniquely) as the primary key. But I don’t know for sure, because I don’t really know what the heck I’m doing. What I actually want to do is have the new record I’ve entered in the subform added to the tblAdvertisingExpenses table, which is the record source for the qryListings query which displays records in the subform. It shouldn’t be a problem, should it? So why can’t I get it to work? Perhaps there’s a fundamental flaw in the way I’ve broken up the database into tables, or my assigning of primary keys and foreign keys. Or perhaps there’s a flaw with the way I’ve constructed my query. Or am I forced to have a completely separate form solely for entering data into the tblAdvertisingExpenses table? Or will I need to use a macro or VBA to accomplish what I want?...
Sorry if this is terribly confusing. I can provide more specific details (and screen captures) of the tables, queries, forms, and relationships (if I haven’t provided enough specific details already), and would really appreciate any help that anyone can provide. Help?!
I have a calculated field in a query. The field name is TotatPt (this is to calculate the total points students have earned during the term). The expression is as follows:
Code:
TotalPt: [Att1Pt]+[Att2Pt]+[Att3Pt]+[Att4Pt]+[Att5Pt]+[Att6Pt]+[Att7Pt]+[Att8Pt]+[Att9Pt]+[Att10Pt]+[Att11Pt]+[Att12Pt]+[Att13Pt]+[Att14Pt]+[Att15Pt]+[Att16Pt]+[Att17Pt]+[Att18Pt]+[Att19Pt]+[Att20Pt]+[Att21Pt]+[Quiz1Pt]+[Quiz2Pt]+[Quiz3Pt]+[Quiz4Pt]+[Quiz5Pt]+[Quiz6Pt]+[Quiz7Pt]+[Quiz8Pt]+[Quiz9Pt]+[Quiz10Pt]+[MidtermWritPt]+[FinalWritPt]+[Proc1Pt]+[Proc2Pt]+[Proc3Pt]+[Proc4Pt]+[Proc5Pt]+[Proc6Pt]+[Proc7Pt]+[Proc8Pt]+[Proc9Pt]+[Proc10Pt]+[Proc11Pt]+[Proc12Pt]+[Proc13Pt]+[Proc14Pt]+[ProcPracPt]
I think this should be no complicated expression (though a bit long) and should just add the fields together. But what I get is that the calculated field appears as expected for records with an odd primary key (1, 3, 5, 7, ...) and turns out blank for records with an even primary key (2, 4, 6, 8, ...)!
I've attached a screenshot.
I have updated a database I created with additional reports, some UI
enhancements, etc., but have not changed anything about the table structure, field types, etc. The database has been in use for about a week by the user and I was working on my local copy. How do I provide the updated database to the user without incurring key violations on the auto-number PKs when their data is imported into the new version?
I wish to avoid the SumOf, AvgOf prefix in queries. I am aware that I can write sales:sales but in a query with 50 columns that is somewhat annoying.
Any help would be appreciated.
I have a query that is pulling data from a table containing a Tamestamp field. I want to set the query to pull my data but don't include anything that was logged in TODAY. How can I set this in my criteria?
Thanks
Hi all.
I have three subforms on my main form. They all display salary details from different sources, and each subform has 2 calculated fields in the form footer to show the average salary and the number of records on which the average has been calculated.
I then have a field on the main form to calculate the average of the averages which works OK unless one of the subforms has null or zero in it, in which case it returns no information.
Can anyone suggest a way of bypassing this divide by zero error? The code I'm using to calculate the average of averages is as follows:
Me!Text44 = ((Me![Frm1 subform].Form!text4 * Me![Frm1subform].Form!Text6) + (Me![Frm2 subform].Form!text4 * Me![Frm2 subform].Form!Text6) + (Me![Frm3 subform].Form!Text6 * Me![Frm3 subform].Form!Text8)) / (Me![Frm1 subform].Form!Text6 + Me![Frm2 subform].Form!Text6 + Me![Frm3 subform].Form!Text8)
Thanks for any help you can provide...
I have 4 queries needed to power one report. I've tried combining the queries into one so I can run the report off that. However, there is too much data and I get all sorts of errors. My only solution was to toss that data into a table thereby eliminating all the calculations and expressions and cleaning up the data. Is there a more elegant solution to this issue?
View 3 Replies View RelatedI have 4 queries needed to power one report. I've tried combining the queries into one so I can run the report off that. However, there is too much data and I get all sorts of errors. My only solution was to toss that data into a table thereby eliminating all the calculations and expressions and cleaning up the data. Is there a more elegant solution to this issue?
View 2 Replies View Relatedpls can someone point me in the right direction
have searched through the forums.
i have two fields Forename & surename i need to prevent duplicate records if the forename and surename match another record.