Extracting Duplicates
Aug 19, 2004Can someone help me out with writing the expression to pull out the IDs that are duplicates?
I know it is something like count >1. thanks!
ADVERTISEMENT
Queries :: Merging Multiple Tables And Extracting Duplicates To CSV File
Feb 27, 2014We are a non-profit that does blind mailings for our membership drive. The company who we buy names and addresses from sends us a delimited file that has these fields as the headings
" ID, FULLNAME,COMPANY, ADR1, ADR2, CITY, STATE, ZIP, FIPS"
Once they send out the mailings, people then send in back a remit slip with a contribution that gets scanned through a program that creates a file that gives us these titles
"ALT ID, AMOUNT PAID, RUN DATE, TENDER, FUND, PURPOSE, SOLICITATION, MEMBERSHIP QUESTION, MEMBER TYPE, CONSTITUENT TYPE, SEGMENT"
The "ALT ID" and "ID" are the same in both tables.
I need to find a way to merge the tables and combine the fields that have the same ID # , and then have it create a csv file that reads like this (see below) for only the files of the people that responded so that I can import it into our membership software.
"Alt ID","Title","First Name","Middle Name","Last Name","Suffix","Address1","City","State","ZIP","ct y_code","Amount Paid","Run Date","Tender","Fund","Purpose","Solicitation","Me mbership Question","Member Type","Constituent Type","Segment"
Find Duplicates And Create Table With Dates Of Duplicates
Feb 12, 2008Hi this is my first post... so hi all :)
ok what i have is a table with contact details 900k plus
there are about 90k of which are duplicates.
this is the basic feilds that are important in this case.
Id, data_source, data_recived, data_code,
what i want is to have a table with unique records (no dups in data_code)
this table will look like this...
Id, data_code, Num_dups, dup1_source, dup1_date, daysbtw_Dup1_dup2, dup2_source, dup2_date, daysbtw_Dup2_dup3 ,dup3_source, dup3_date, daysbtw_Dup3_dup4 ,dup4_source, dup4_date,
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.
if anyone can help it would be great .
thanks in advance.
Find Duplicates Different Than Duplicates Deleted
Dec 8, 2005Hey, all! Thanks for helping, here is my situation.
I have a table with about 70,000 records that have duplicate Address field values. The rest of the field values for those records are different. When I do a find duplicate querry I get the result that 17,000 records have the same address. However, when I do the append qurrey as instructed here: http://support.microsoft.com/?kbid=209183 I get a total of only 600 records in the new table. I have tried deleting all of the indexes for both the new and old table, with no luck.
I'm using Access 2000 on XP Pro.
If anyone could help with this I would greatly appreciate it!
Thanks
Will
Extracting Times
May 17, 2005I've got a field in a table called GenTime. It is in a Date/Time format, like 5/16/2005 7:00:00 PM.
What I need to do is extract from this field the time, like 7:01:00 PM to 7:59:00 PM. I just can't seem to make it work?? :(
Help!
Extracting First Name And Surname
Jan 12, 2006Hi,
I have been sent a file of customer contacts. They are held in a single field and have the format firstname + surname. So that I can load this data into a enterprise database system, I need to be able to extract surname and first name into to separate fields.
Any ideas on how I achieve this through an Access query?
Many thanks
Clay
Extracting A Substring
Mar 1, 2007Hi Everybody,
I have an access database and in a certain table I have a string field which has two values. I want to make a query that will show all the fields which contain a substring.
I'd better give an example:
Table: Kid
Fields:
Name: Ben
Activities: Soccer, Tennis
Name: Dan
Activities: Soccer
Name: John
Activities: Football, Origami
.
.
.
I want to show in a query all the Names which have a "Soccer" substring in their Activities string field
Need your help ASAP !!!
Extracting Odd/Even Or Both Numbers
Sep 19, 2007Hi all,
In my query I can query for ODD or EVEN Street Numbers using the syntax below:
OE: [PROPHOUSENUMERIC] Mod 2
This works fine returning just odd or even numbers.
Now the problem I am facing now is that I wan the same query to return both Odd and Even Street numbers if I did not ask for either Odd or Even Street Numbers.
Is this possible. I have search hi and low in the forum but no answer. I have attached the file I am working with this post.
Thanks in advance.
matomo :confused:
Seeking Help On Extracting Letters From Name
May 5, 2005Hi all: MS Access 2003. I have two text fields family_name and given_name I need to extract 1st, 2nd & 5th letter of family_name along with 2nd & 3rd letter of given_name to form a link_key field. If either name has insufficient letters the missing letter is replaced by the numeral 2. Does anybody know how to help me acheive this please!
View 2 Replies View RelatedExtracting Month From A Date
Jun 15, 2005Hi,
I have a date field in one of my queries (01DEC2004). I have a pivot table linked to the query. I would like the users of the pivot table to be able to group the data by month. Currently the group feature will not work. Does anyone have a suggestion on the best way to extract the month from the date field?
Could I create a new column in the query called month and use an expression to extract the month?
If so can someone tell me how to key the expression to do this?
Thanks for any help.
Extracting Range Of Records
Jul 4, 2005Was just wondering how i go about pulling out a range of records. I do not want to use my Primary Key autonumber value as some records have been deleted and selecting a range of 10 to 50 for example will not guarentee 40 records. Is there some way i can use an SQL command in the SQL View window to achieve this?
Cheers
Extracting Part Of A Field
Jul 27, 2005I found a thread from last year that is close to solving this question but not 100% so I'll post this new thread.
I have a field called ITEM_NUM in a database with the following structure:
XX-YYYYYYYYY
XX-YYYYY
XX-YYYYYYYYYYYY
I need to extract the Y portion of the data and a Query format would be the best.
FYI, the X portion consists of 2 Letters/numbers then the Hyphen and the Y portion is variable in length.
thanks for your help!
Extracting Data Into A New Field
Dec 13, 2005You guys have been so helpful in the past. Perhaps you can help me with this as well. I believe it’s a bit out of my scope. I have a list that reads similar to this:
Field 1
DMA: ALBANY
CAR 1
CAR 2
CAR 3
DMA: BOSTON
CAR 1
CAR 3
CAR 4
CAR 5
CAR 6
DMA: HOUSTON
CAR 1
CAR 3
CAR 4
CAR 5
I need it to read like this:
Field 1 Field 2
DMA: ALBANY CAR 1
DMA: ALBANY CAR 2
DMA: ALBANY CAR 3
DMA: BOSTON CAR 1
DMA: BOSTON CAR 3
DMA: BOSTON CAR 4
DMA: BOSTON CAR 5
DMA: BOSTON CAR 6
DMA: HOUSTON CAR 1
DMA: HOUSTON CAR 3
DMA: HOUSTON CAR 4
DMA: HOUSTON CAR 5
If my list was this short, I would do it all manually, but my record list is in the 10,000s. Basically I want field 1 to read: DMA:ALBANY and Field 2 to read:CAR 1, and so on. I’ve tried linking the table to itself in a query, however, that hasn’t proven to be helpful. I've also searched the FAQ's and newsgroups. Any insight would be appreciated.
Thanks,
Amanda
Extracting First 2 Characters From VAT Number
Dec 20, 2005Hi,
I have a long list of customers / suppliers and their VAT numbers. I would like to create a query that shows only the customer/supplier number and the first 2 characters (countrycode) from their VAT number. for example
Supplier 3 VAT number NL80980808
endresult should show:
3 NL
Is there anyone that can help me out??
Extracting A Portion Of Data
Oct 3, 2006I probably have an easy question for one of you out there, but I just cannot get it myself.
I need to extract the data between two characters in a string.
The data will be similar to this:
T4454: Text Text-Text: $296.07: Text Text
I need to get the dollar amount between the dollar sign and the colon on the right of it.
So far, I have this for my query:
Amount1: Mid$([subject],InStr([subject],"$"))
Which gets the amount to the left side, but also has the $ in it.
In the example above, I would like to just end up with 296.07 as the result from the query. I should also add that the dollar amount may vary from 1.00 to #,###.00
Thanks in advance for any help you can provide
Extracting Random Records
Nov 3, 2006If i have a table with several records let's say 1000 records, and each record has a field called cboCategory. Now from these 1000 records, lets say i have 200 records which has a value of CategoryA in the cboCategory field. Now is it possible that i extract "randomly" a number of records from that particular category chosen.
e.g.
Table -> 1000 records
records which belong to categoryA -> 200
extract 20 random records from the table which have a value of categoryA inthe cboCategory field.
Thanks for any suggestion.
Extracting Data From Combo Box
Aug 1, 2006Hi,
I have a combo box, which the first and last name of an employee is combined using trim. Does anybody know of a way to seperate both fields as these would be used an a query so I would need them seen as 2 fields otherwise there won't be any matches.
Cheers,
Ben
Changing From Duplicates OK To No Duplicates
Nov 1, 2014I have an Access table with an indexed key that is currently set to duplicates OK. There are not supposed to be any duplicates in that field. But it is possible than a small number have crept through.
Two questions:
1. What would happen to those duplicate records if I changed the setting from duplicates OK to no duplicates?
2. Is there any way to ferret out those duplicate records first and change them manually? The table currently has 48000 records so it would be a pain to go through them page by page. (I know how to export them to Excel, where the dups can easily be found; I was just wondering if something could do it within Access.)
Extracting Data From A Table As String
Nov 15, 2005I need to be able to extract individual fields from a table as strings in order to then send this data to my EPOS printer (which will accept only strings to print) I have created the table with a query so the question could equally be how can i output strings as query results. Im using Access97 HELP!!
I have tried to set up a ADODB.Connection but either Access 97 wont accept it or i've got the coding wrong
Extracting Data From One Field In A Table To Another--Help!!!
Jun 27, 2006Hi Guys,
I have a table called "tblEmployees" with the following fields:
EmployeeNo (Primary key)
Name
Address
DOB etc
Letter1Dated
Letter2Dated
I have another couple of tables:
"tblLetter1" with the following fields:
EmployeeNo (Primary key)
Name
Letter1Dated
Letter1RemainingFields...
"tblLetter2" with the following fields:
EmployeeNo (Primary key)
Name
Letter2Dated
Letter2RemainingFields...
I want to be able to put the data from 'Letter1Dated' field (from the "tblLetter1" table) into the 'Letter1Dated' Field in the "tblEmployees" table.
Likewise, I want to be able to put the data from 'Letter2Dated' field (from the "tblLetter2" table) into the 'Letter2Dated' Field in the "tblEmployees" table.
All three tables have the same primary key (EmployeeNo), which makes it even more difficult to do this!
I understand this may sound abit confusing so i apologise for this.
I would really appreciate it if anyone could point me in the right direction please.
Thanks alot.
Extracting Data From A Field In A Table.
Jan 29, 2007when I have created my table I have created a field called name that stored both last and first name. Right now I need to extract the the last name from the the field name and store it in another field "lastName". Any ways to do that?
View 7 Replies View RelatedExtracting Info From Two Date Columns
Nov 9, 2005basically I have two date columns in one of my tables. One is created whenever a record is created in that table the other is added to that record later based on business rules. I need to get all records in the table where the first date exists but not the second. I'm unsure of how to write a query specifying that the second date column must be empty.
View 1 Replies View RelatedExtracting Numbers From A Memo Field
Mar 15, 2006Hi all
I have a field on a database thats generally used to enter notes.
Within these are mobile phone numbers in no particular order(throughout the field)
Im not sure what the code is to enter in the query to display only mobile numbers from this notes field ?
Any help will be appreciated
Thanks
Extracting Record(s) With Most Recent Date
Jul 29, 2006I have a database table comprising inspection records for about 300 process control instruments. A new record is entered for each inspection so that an inspection history builds up for each instrument.
For one report I need to extract all the records with the only the latest inspection date for each instrument.
I attempted to build a query using the design grid screen. Initially I thought I could use the "Max of" approach as I have done with numbers. This did not work so tried without success to convert the dates to serial format thinking I could "Max of".
Have now concluded that a module is needed. However with my Access Basic programming skills this would take me a couple of weeks and still be wrong!
Can anyone point me in the right direction?
Extracting Records From A Memo Form
Aug 25, 2006I need some help. I created a database to capture information from an employee survey. Most of the answers required a check if they agree or no check if they do not agree. There was one "memo" field inwhich we allow the employee to VENT. I have provided the team with all sorts of reports with percentages and figures. Now they want to remove any comment that had money, pay raise, bonus etc. They also want to organize the comments in some type of flow. Like all the "pats on the back" in one area, Day offs in another, plaque on wall grouped together, etc.
Can one of you experts, please give me some help?
Thanks!
Query - Extracting Customers Between Two Dates
Jan 2, 2007My customer searches hires a car between two dates - StartDate and EndDate. I want to build an expression that searches my database for all the customers currently using cars (using cars on the day of the search.) How can this be done?
View 2 Replies View Related