I have a table of ~165k records and need to delete records that are similar (2 or more records have the same lastname, firstname, dob). These records are not duplicates as other field values in these records are different. I've tried several sql scripts and Access methodologies but can't get valid results.
Thanks,
Phil....
This is a tough one. I am developing golf management software. There is a table of caddies. And there is a table of members. The Caddies carry two bags each one for each member. They are assigned their jobs through an assignment form. Therefore, a single caddie will carry for Member1 and Member 2.
Now I need to produce a report of how many times each member plays. So how can I query the assignment table where Member 1 and Member 2 fields exist so that I can have a single field of allMembers so that I can count their rounds in a report.
Thanks.....Jeff
I have a query that accesses data from 6 tables. The query displays 6 records where as there are 7 records that have to be displayed. The one record it doesnt display is similar to other records. I dont know why its not being displayed. Kindly help.
View 2 Replies View RelatedThis may be a simplistic question but I'm having some diffuculty figuring it out. I have multiple rows of data where I need to use the "Unique Values" property in my query to limit only what I need. However, I have a "time" field and their are similar times on different records. So say I have a 100 records but 8 of them have the same "time" value, how can I use someting like the "Unique Values" property to get my rows down from several hundred to 100, but not lose the 8 similar rows and end up with 92 rows, when I have to turn around and sum these times and need all the times to be there?
Maybe I'm approaching it the wrong way and should be doing a SUM of "times" to begin with for that field, but I'm not being able to get that to work either. Any suggestions? /Thanks
I have a query that shows member check-ins to a fitness facility. Customer scans their membership card and it logs their customer id, scan date/time (date and time are one field), name, etc. Sometimes the customer will scan their card multiple times if they've left and come back, but I only want to count their first scan. I have a second query that runs off of this one and counts visits by hour for a date range.
So my question is, how do I filter out subsequent scans on each day in a query and how do a I do that when running the query for multiple days? So in the multi-day query I want to count each day they came, but only the first scan of each day? How to do this in the query design grid...
I am creating a simple inventory db. I have a form that we enter into the table inventory. Right now we enter in everything once as inventory, then again to out to coating or ship to customer. I’m looking for the code to create a button enter the record twice but make two fields change.
So in short terms I want the form to enter a record with all the data as displayed. Then create another record with all the same data but the quantity would need to change to a negative and the Status would change to “Coating”.
I know this code won't work but this is what I want to do.
Private Sub Command78_Click()
.AddNew
!Employee = Me!Combo68
!Material = Me!Combo48
!Length = Me!Combo29
!Caliber = Me!Combo31
[code].....
Hi guys,
need your help again with the following problem i'm having. I'll give an example to make the problem clear.
I have a table, eg table 1 with the following records:
Tag________Desc________Msgno____DateTime
016GB001___Alarm OFF___1403______21/4/06 11:02:02
016GB001___Alarm OFF___1605______21/4/06 11:02:02
034GB005___Alarm ON____1403______21/4/06 11:02:04
016GB001___Alarm ON____1403______21/4/06 11:02:07
016GB001___Alarm ON____1605______21/4/06 11:02:07
048GB001___Alarm OFF___1403______21/4/06 11:05:31
048GB001___Alarm OFF___1605______21/4/06 11:05:32
048GB001___Alarm ON____1403______21/4/06 11:06:51
048GB001___Alarm ON____1605______21/4/06 11:06:52
I need a query that looks at records with similar Tag fields that occur with a 1 second difference between them and deletes those with "1403" in the Msgno field. I'm a newbie and i've got no idea where to start. The query should return the following records:
I have a table, eg table 1 with the following records:
Tag________Desc________Msgno____DateTime
016GB001___Alarm OFF___1605______21/4/06 11:02:02
034GB005___Alarm ON____1403______21/4/06 11:02:04
016GB001___Alarm ON____1605______21/4/06 11:02:07
048GB001___Alarm OFF___1605______21/4/06 11:05:32
048GB001___Alarm ON____1605______21/4/06 11:06:52
I hope the problem is clear. I know this is going to be one heck of a challenge, and i'm not sure whether this can actually be done in access!! Anyhelp from anybody would be enormously appreciated. Hope to see a solution soon!!
Kind regards,
Monty
I am trying to write some code to search for similar strings. I am creating a database with records that all contain street addresses. These addresses may have more than one record attached to it, and we would like for folders to be created containing the records with similar street addresses. Problem is, all the existing records are from an excel spreadsheet that did not contain any data validation, so there are several instances of:
123 Street
123 st
123 street job 1
123 st job 2
etc....
So I am trying to write code to prevent this from happening in the future, by searching the database for a similar street address and asking the user if this is the address they are trying to enter. I have been trying to do this with the DLookUp function, as such:
Private Sub ProjectName_AfterUpdate()
Dim stLink, pName As String
pName = Me.ProjectName
stLink = DLookup("[ProjectName]", "tblMaster", "[ProjectName] LIKE '" & pName & "%'")
If IsNull(stLink) Or stLink = "" Then
[Code] ....
I have worded the stLink line different ways, and have used (*) instead of (%) but nothing is working. The CODE is working, as in no errors, but it is not finding a similar project that I know is present.
I have been looking around for a while now to learn how to show in a subform similar records from the same table, and a way to link them together.
I work for a mental health organization and we have a call log database that we create a new record every time a person calls us - this allows us to track the outcome of these calls. Over the last 3 years we have about 10% repeat callers. I am trying to find a way, when creating a new entry, to see if this person has called before, and if they have, link their past contact (record) to the new contact (new record).
For example.
Caller: John Smith (555) 555-5555 (this is the new record)
in the subform, a list would populate all the 'john smith' records with an option to link or attach them to the new record.
Is there a way to merge duplicate/similar Access 2010 records into one record?
I have an Access table with 1,000 duplicate records, although they are similar and not exact duplicates. As you can see below, some records contain information that other records do not. Yet, the primary key is the same for all duplicate records. I want to find a way to merge data from filled cells of duplicate records into empty cells for each duplicate record. I do not want to concatenate the data (i.e. combine last and first name, etc.). I only want to fill empty cells if there is a match for it in a duplicate record. I will delete the newly exact duplicate records later. Short of correcting the records by hand.
Example
Code:
LastName FirstName SSN Address Phone Email
Doe John 123-45-7891 123 Anywhere St. NULL john(at)gmail.com
Doe John 123-45-7891 NULL (123)456-7890 NULL
Desired Result
Code:
LastName FirstName SSN Address Phone Email
Doe John 123-45-7891 123 Anywhere St. (123)456-7890 john(at)gmail.com
Doe John 123-45-7891 123 Anywhere St. (123)456-7890 john(at)gmail.com
I have a table InvPrice and Updated Pricing
Need to delete all records from InvPrice that Match UpdatedPricing
InvPrice.StockCode = UpdatedPricing.StockCode
InvPrice.PriceCode = UpdatedPricing.StockCode
I have tried something like this...
Dim dbs As DAO.Database, sql As String, rCount As Integer
Set dbs = CurrentDb
sql = "DELETE * dbo_InvPrice Inner Join (dbo_InvPrice Inner Join UpdatedPricing on dbo_InvPrice.StockCode = UpdatedPricing.StockCode ) ON on dbo_INvPrice.PriceCode = UpdatedPricing.PriceCode "
dbs.Execute sql, dbFailOnError
Hi - I am working on a database where i need to update a table with a "UniquePersonID"
I have a master table which stores:
UniquePersonID
Surname
Forename
DOB
SEX
Postcode
This table is approx 600k records, now the problem i have is that i have further tables which contain Surname, Forename, DOB, Sex and Postcode and need to resolve these with the "UniquePersonID", unfortunatley in some cases names, DOB's may have been spelt slightly differently, (i.e. "Abbas" should have been "Abbass" or the DOB has been entered with a typo)
I have managed to do an exact match and update the majority of records but i am still left with just over 16,000 records to match manually... I have created a form which allows me to tick which boxes to search against to resolve record by record, but estimates suggest that this will take around 12 solid days work :(
Is there a way of matching similar records and coming up with a probability score that anyone out there is aware of???
Many thanks
Ian
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 query of records that are not related to records is the secondary table. I want to delete these records but when I select the records and click delete I get the confirmation to delete answer to delete them and all the records disappear. When I run the query again, all the records are still there. Does anyone know what is happening? This is an access front end that connects to a SQL server database.
View 1 Replies View RelatedThis works perfectly.
SELECT DOCSADM_PROFILE.SYSTEM_ID, DOCSADM_PROFILE.AUTHOR, DOCSADM_SECURITY.THING, DOCSADM_SECURITY.PERSONORGROUP, DOCSADM_SECURITY.ACCESSRIGHTS
FROM DOCSADM_PROFILE INNER JOIN DOCSADM_SECURITY ON DOCSADM_PROFILE.SYSTEM_ID = DOCSADM_SECURITY.THING
WHERE (((DOCSADM_PROFILE.AUTHOR)=21941909) AND ((DOCSADM_SECURITY.PERSONORGROUP)=4038));
Now how do i tell this query to delete these selected records from security table....?
I have a survey form that has the questions on a subform based on the response to one of the survey demographic questions (via a query based on that response). The answers to the questions are on a sub-sub form. How can I get the program to delete the answers that were entered (they are related to the primary survey form with a common ID number) if the user changes the survey response.
In other words, if the user of SurveyID 5 decides that they want to fill out the list of blue questions rather than red questions, how can I delete all the answer blue records for SurveyID 5? Right now, it will display the red questions if you change to red and enter red answers, but the blue answers remain in the database, creating 2 sets of answers for SurveyID 5.
Any suggestions...if this is not too confusing?
Thanks!
Hello all:
Form created based on a query. When i go to the form, the add new record and delete record buttons are disabled.
I have checked and re-checked the form, tables, queries and all seems well.
Does anyone know how I can fix this problem?
Many thanks,
Dion
possible to delete all records from one table except the first; Is that possible?
View 7 Replies View RelatedGood evening,
I am experiencing a problem with the delete buttons I have added into my forms. I have 3 different forms that operators use, I added a delete button using the wizard on each form to delete the data if the user makes a mistake. I have allowed deletes in the form properties. My problem is that the button only works on one of the forms. I have checked the code and it is the same on all 3 forms.
The record is not deleted, all that happens is that the vertical bar down the LHS of the form flashes black, and thats about it
Private Sub Command52_Click()
On Error GoTo Err_Command52_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Exit_Command52_Click:
Exit Sub
Err_Command52_Click:
MsgBox Err.Description
Resume Exit_Command52_Click
End Sub
Any thoughts?
The only difference is that one button is called Command52 and the other one is called Delete
Russ
Hello,
I have a table that has many duplicate records. I tried importing the table and setting the field to no duplicates but did not work. How can I delete duplicate records. The primary Id id fine I have another field that I can use. it is a BAC number that is repeated many times. Here is the table I need fixed.
Thanks
I am creating a web database where users submit entries using an online form. When they submit the entry, I am saving the entry date and time in a column called Timestamp. What I need to figure out is if there is a way to create some sort of expiration date, based on the Timestamp, that will automatically delete a record after it is 7 days old. Is there a way to do this? Thanks so much for any help that you can give.
View 5 Replies View Relatedhow do i delete multiple records in a table, for example if i have a table that as serial_num 123 twice, how do i delete the two records, basically i don't want to delete one record, i want to delete the two records. Thanks.
View 5 Replies View RelatedHi,
I'm trying to delete say 4000 records, out of total 5000 records. I want to keep 1000 records based on the ID.
How can I write the SQL DELETE query, so that only 1000 records remain based on the query?
Thank you in advance for your great help.
regards,
Pranesh
Alberta, Canada
Hi
I'm trying to deleterecords from one table. In the query there are 2 tables both joined. I get an error message come on saying
"Specify The Table Containing The Records you want to Delete"
Below is the query in SQL
DELETE tblStockRequired.StockCode, tblStockRequired.StockQTY, tblStockRequired.OrderNumber
FROM tblOrderCompleted INNER JOIN tblStockRequired ON (tblOrderCompleted.OrderNumber = tblStockRequired.OrderNumber) AND (tblOrderCompleted.StockCode = tblStockRequired.StockCode) AND (tblOrderCompleted.QTY = tblStockRequired.StockQTY);
I want ot delete all records from tblStockRequired which match up with tblOrderCompleted.
Thanks in Advanced
Hi can anyone please help me out. How can I delete records from one table, where matching in a second table?
View 1 Replies View RelatedHi All.
I need to modify my database that give me ability to delete all record form table for last month. How to solve that problem?
Thanks.