- I have one table tblRefctc with the followings fields :
LCtcRef,../..,LRefServ0, LrefServ1,..LrefServ16.
Where all those Lref are long; LCtcRef being the primary key. This table describe a contact from different branches (account, etc..) so while each branch has the relevant information about this contact, they know that that branch too is in contact with the same person.
- I have this other table with describes Events, TblIdxEvt (meeting, whatever) :
LRefidx,IdxEvt,IdxCtc
where IdxCtc is filled with LCtcRef once an event is created.
What I must check for is : For the same IdxEvt, should another branch makes an appointement for LCtcRef (i), I must check if one of the LRefServ0 to LRefServ16 are not already added in the event table.
At this time, I am stuck with making functions which test the existence of the LRefServ(i) (i ranging from 0 to 16) in tblIdxEvt, but I wonder if there is a simpler way to do it with SQL (which, as you could guess, Im not good at).
Hi, been searching this forum for a couple of months now im working with access and up till now i have solved most of my problems:D
Ok the problem.
For the purpose of the question I have a training database: running one table with user id and user name. another for job id and job name. Another for training table with an autonumber, user id, job id, and date
Id like to build a query which bought up the newest only for each user id and job id.
eg. UI JI Date 2 5 01/05/05 2 5 01/05/06 3 5 01/05/05 3 6 01/05/05 4 6 01/05/06
to UI JI Date 2 5 01/05/06 3 5 01/05/05 3 6 01/05/05 4 6 01/05/06
Iv tried using a few different ways of using duplicate queryies etc, but im just too newb to figure it out :(
Any suggestions or help would be appreciated. Thanks
I have a Lost and Found Database that I enter information in usually every day. Lots of items like gloves and glasses are entered into the item/s description field as name of item then color or some other descriptive. How do you make the combo box bring up all of the items that are related so that I can pick from a tighter list? Right now when I click on the combobox for say, gloves, it will bring up only one record of gloves and then I have to scroll through the entire database to find the right record.
Table Properties Lost and Found ITEM/S DESC Field: Field Size - 50 Required - Yes Allow Zero Length - No Indexed - No Unicode Compression - Yes
Form Combo Box After Update [Event Procedure]
Private Sub Combo22_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[ITEM/S DESC:] = '" & Me![Combo22] & "'" Me.Bookmark = rs.Bookmark End Sub
I am just starting Access... Or at least trying to learn it on a small project, and I need a jumpstart, if someone woild be so kind and help me out.
I need to organize my e-mail sendings.
In one table (Table Sent) I have e-mail addresses where I have already sent messages. In an other (Table New) I have some other e-mail addresses, I am preparing to send out.
To make sure to not send mail to those who already got one, I need to compare the two tables, and delete from the Table New those addresses which are already present in Table Sent.
I guess that would be a 2-3 line SQL script... All of my attempts so far produced no results
I am using Access 2010 32-bit on a Windows 7 64-bit platform.I have a database that I have imported existing data into. For the most part, I have eliminated duplicate entries in my Item Number field.
Obviously I have missed at least one (or possibly more) duplicates. When I try to create a NO DUPLICATES index, I am informed that there are duplicate entries.I am looking for a quick way to generate a list of values that are duplicated so I can address those and correct them.Item number field is a number field of type DOUBLE with 2 decimal places.
I've been looking everywhere to find a better way of finding duplicates in a table and then recording and adding another value in that record together.
Let me try to explain better.
example:
I have a table that has 2 columns "Name", "DOB". I would like to find all duplicate "DOB" and add all of the "Name"'s together.
Quote:
Name DOB bob 19800201 Sam 19761211 Jim 19800201
The output I would like is to have Name = bob & Jim DOB = 19800201.
I've tried using the find duplicate wizard in access but I can't seem to group them together and just to find the duplicates it takes upwards of a minute.
I've been working on trying to get this code to work as expected for days. I'm trying to find duplicates (I can't use primary keys or indexes alone to weed out duplicates due to the structure of the tables involved) in a subform as a user enters data. As soon as a project number is added, the code is supposed to count the number of records that contain that particular project number as well as a category number (there can be multiples of the same project numbers as long as their category numbers are different). This is the code I am using in the "Before Update" event of the field in the subform:
Private Sub ProjectID_BeforeUpdate(Cancel As Integer)
Dim strCriteria As String Dim RecCount As Integer strCriteria = "([ProjectID] = " & Me.ProjectID & ") AND ([CatID] = " & Me.CatID & ")" RecCount = DCount("[ProjHrsID]", "tblProjHrs", strCriteria)
[Code] .....
What could I be doing wrong? Nothing about this code seems to work properly - even the Undo and Cancel=True is a problem (I get the "No current record" error).
This is my first post and i searched to try and find an answer on the forums but can't seem to find one so thought i'd ask to see if anyone is able to help. Sorry if this has been answered before and i've been a bit thick not been able to find it. anyway here's my question...
I have 2 fields i want to check in my database DOB1 and DOB2 (date of birth of applicant 1 and date of birth of applicant 2.
i've written a query that looks for any records where the data in DOB1 and DOB2 match the data in another record for DOB1 and DOB2. i hope that makes sense. this is what i've written.
In (SELECT [App1 DOB] FROM [Data] As Tmp GROUP BY [App1 DOB],[App2 DOB] HAVING Count(*)>1 And [App2 DOB] = [Data].[App2 DOB])
this seems to work but please feel free to correct me.
Now the part i'm struggling with is trying to find duplicates where the fields have been reversed. eg App1 DOB and App2 DOB are the same as App1 DOB and App2 DOB in another record but applicant 1's Date of birth has been stored as Applicant 2 and Applicant 2 is now stored as Applicant 1. i hope thats not to confusing.
Basically i'm trying to check for duplicate mortgage applications in the database but sometimes applicant 1 and applicant 2 are reversed.
I have created a database which holds architectural drawings for my company. The drawings have a number (dwgNumber), a name (dwgTitle) and a revision (dwgRevision). Each number should only have one title but cannot be unique as they can have several revisions. I am looking for a way to allow the users of the database to only enter a drawing title for a drawing number once. This is because if more than one person is accessing the front end at a time, they may use the same drawing number for different drawings.
Hi everybody, My question may sound you stupid but i am a newbie and i think you can help me... My problem is, I have records that have the id of a stuff and the error codes of the stuff which are occured in production. Therefore not all of the fields in the record are non-zero. I want to find the non-zero field/fields in a record... Any help will be greatly appreciated... Thanks...
Is there any way I can stop the same permutation of three field values being duplicated in a table. eg a - b - c is ok a - b - d is ok b - c - e is ok a - e - c is ok a - b - c ......duplicate
I have created a database which holds architectural drawings for my company. The drawings have a number (dwgNumber), a name (dwgTitle) and a revision (dwgRevision). Each number should only have one title but cannot be unique as they can have several revisions. I am looking for a way to allow the users of the database to only enter a drawing title for a drawing number once. This is because if more than one person is accessing the front end at a time, they may use the same drawing number for different drawings.
For Example:
----THIS IS FINE Number: A001 Title: Drawing 1 Revision: P1 Number: A001 Title: Drawing 1 Revision: P2
----THIS IS NOT OK Number: A001 Title: Drawing 1 Revision: P1 Number: A001 Title: Drawing 2 Revision: C1
Basically, each number can only have one title assigned to it.
I have a MS Access 2000 database with 136 data tables in it. What I would like to do is execute a piece of VBA code which will list for me in another table, (for example: Field1: TABLE NAME Field2: FIELD NAME 1 Field2: FIELD NAME2 etc), starting left to right, how many fields would have to be combined in each table to represent a unique record.
For instance:
TYPE ID TEXT 1 1 "Cats" 1 2 "Dogs" 2 1 "Rabbits"
In this example a combination of the fields TYPE and ID give a unique record.
I am working on a database to manage newsletter subscriptions. Each subscriber record has the option of having up to four email addresses registered to his/her name.
Is there a way to check for duplicate email addresses in the entire database? It would have to compare all values in all four fields of all records.
Any ideas on how to implement such a thing? I'm clueless...
In access 2003 i have an external Oracle table named EXT-COM_EXTRA_DATA_FIELDS in this table I have the following fields: COM_DATA_KEY, COM_DATA_FIELD_NO, COM_DATA_VALUE
From this I have created a query named: LOOKUP-BARCODES-10 this looks at the above table by filtering the the records on the field: COM_DATA_FIELD_NO where this =10.
Also I have created a second query named: LOOKUP-BARCODES-20 this looks at the above table by filtering the records on the field: COM_DATA_FIELD_NO where this =20
The problem is i would like to combine the numbers in the COM_DATA_VALUE field for the same product (ie the same COM_DATA_KEY record) on the same rows without any duplicate records as shown below:
As you can see from above some records only have data in one COM_DATA_VALUE (like 011C/x) where COM_DATA_FIELD_NO = 10 but no data for the same product where the COM_DATA_VALUE = 20.
Im thinking maybe i can create a new join query that looks at the above two query’s and combines the values in the COM_DATA_VALUE for the same product onto the same record row but im not sure how to go about this without creating duplicates & its been some time since ive done joined queries. Any help would be great?
ive attached a cut down version of the database with the table imported from oracle rather than being linked to oracle if this helps.
I have a large table with many fields and many rows. There is no primary key. I'll call one field ParentPN, and another field ChildPN. There are many other fields as well. I want to identify all rows where BOTH the ParentPN and ChildPN occur more than once. I know how to create a query to identify duplicates of ONE field in the table, but not two. I can solve this with VBA: I will read the two fields of interest in the first row, then compare both values with every other row. If it finds another row with BOTH ParentPN and ChildPN identical with the first, that's a "hit". Then, repeat with all the other rows. I could find ways to make this run faster, but I was wondering if there are any build in functions to accomplish this. I looked at the Find Duplicates query builder, and all I see is I can select ONE field to search for dupes, not two.
I am trying to modify an inventory management database. I want to prevent duplicate entries to specific fields in centralized table. The table is called Work Stations. Its function will be to track various computer equipment by a specific work station name. Each of the items will have a unique asset tag.
The primary Key for Work Stations is WS-ID. The fields I am trying to prevent duplicate entries in are WS-Computer, WS-Docking Station, WS-Monitor_1, WS-Monitor_2, and WS-Switch. Each of those fields are primary keys in 4 different tables that conation more detailed information about item. The exception is WS-monitor_1 and WS-Monitor_2 have a one-to-many relationship with Mon-Asset Tag in the monitors table.
The goal I am trying to accomplish is when a work station ID is created or modified duplicate entries are prevented to those fields listed above. The computer, monitors, docking station, and switch fields in the work station table may contain data or may be null.
I have tried to set the Indexed Option to Indexed (No duplicates) for each of the fields and I have also tried setting Yes to Ignore Nulls in the index option on the table design tab for the individual fields. Both options have returned the same error stating the changes would create duplicate values, in the index, primary key, or relationship.I have not created form for this table yet as I was trying to get no duplicates option to work first. I have verified the data and the only duplicating fields are the Null fields
I have 4 or 5 tables. Most of the fields are exactly the name but they all have at least 1 to possibly 5 or six fields that are not in the other table. Additionally there are some duplicates within the individual tables as well as across tables.
i.e.
I have a
Student Table - with all the info on the student as well as a column called student that identifies them as such however it does not have the columns parent, donor, appeal, designation..... Parent Table - with all the info on the student as well as a column called parent that identifies them as such however it does not have the columns student, donor, appeal, designation..... Donor Table - with all the info on the student as well as a column called donor that identifies them as such however it does not have the columns student, parent, appeal, designation..... Appeal Table - with all the info on the student as well as a column called appeal that identifies them as such however it does not have the columns student, parent, donor, designation.....
-A person can be within one of these tables more than once but with all the same information. -A person can also fall into all of these parameters so they could be on every table with the same information in addition to the missing columns,=.
Question 1 : what is the best way to dedupe and delete the individual tables (they all have account numbers) Question 2: I was thinking create a new table with all the columns available, however how do i dedupe across tables while populating the additional columns from each?
I've been playing around with a new database design and ran into a possible 'error' that I would like to avoid.
It's going to be a payroll database to store time codes for hours spent working on specific projects. I have been struggling on how to put this together to fit with what we've been doing for years and I think I hit a few breakthroughs this morning.
However I want to avoid this error of possible duplication of entry.
Simple table set up - primary key is just a running integer; Employee ID; and Week Ending Date.
I can have multiple week ending dates for a specific employee; but I want to avoid having the same employee with the same week ending date. I cannot set up either field as being unique.
I'm getting back into Access after retiring and about 10 years of isolation (from Access). I have 2010 version and know that one should avoid duplicate entries and a way to do this is make those fields unique key fields. I have a Customers table and have bounced back between CusID (AutoNumber) and Compound Keys (CusFName and CusLName) as the key fields. The compound keys prevent duplicates but become very hard to work with later in code and expressions. The CusID is preferable from that standpoint, but can't prevent inadvertent entry of duplicate names.
How to prevent duplicates on the combination of two fields - text & numeric?
I'm currently using the code below that warns users when the combination of two fields have already been used. (Combination of the TWO fields has to always be unique so if used again will warn the user)
Works well when both fields are numeric but fails when the JobDetails field is changed to text in the main table (tblPPMPLanner)
Code: Option Compare Database Option Explicit Private Function IsDuplicateRecord() As Boolean On Error Resume Next Dim PreviousRecordID As Long IsDuplicateRecord = False
[Code] ....
The field that should be a text field is called "JobDetails"
I have a client database that has recently had multiple duplicate entries. I need to reduce or negate this erroneous activity. I have a client table where I record amongst others, the following;
key [christian_name] [family_name] [dob] ......
I believe that to prevent duplicate entrie via form I have created an additional field called "unique" given it as a unique index which I want to have populated with the joined fields first_name & last_name & dob (IE johndoe01/01/90), and then as user enters a new client it wont allow a duplicate.
However I need to fill all the existing customers (3600+) with the relevant joined existing data. If I create an expression I can cajoin the fields in a select query but when I try to make an update query the same syntax comes up with empty fields.
select query sql that worked to show field ...
SELECT divers.christian_name, divers.family_name, divers.dob, [christian_name] & [family_name] & [dob] AS Expr1 FROM divers;
update query that was empty ..
UPDATE divers SET divers.[unique] = [christian_name] & [family_name] & [dob];
I know there is no more than 4 dups of each record.
what i want from this is a table that will give me a record of how many dups for each record then all the dates that they were added and the date between each record entry.
