I Need To Match 3 Fields For Duplicate
Sep 26, 2005
I am exporting an Oracle report to excel, and using an Access macro to import into a table.
The records contain some duplicate info that I do want to capture, but I don't want to import the same records. (I import the file on one day, and someone comes in and imports the same file later).
This would work if I could get the table to not duplicate if three of the fields are the same as an existing record. I tried using the index, but it reconizes any/all of the fields that duplicate.
Any help would be appreciated.
View Replies
ADVERTISEMENT
Feb 25, 2014
I am trying to setup a table that tracks the progress of an item from a cradle to grave process with 26 steps in between. I would like a calculated status column that is calculated by finding the max value (Date) in a row and returning the "header" that is associated with the max value.
View 11 Replies
View Related
Apr 14, 2008
Hi,
I need to match the data in all the fields between 2 tables.
eg.
Field 1 (Table 1) = Field 2 (Table 2)
Field 2 (Table 1) = Field 2 (Table 2)
so on......
Using the wizard, I'm only able to match 1 field at a time and this is very time consuming as I have 45 fields to match. Any advise? Can this be done via SQL?
View 2 Replies
View Related
Jan 24, 2015
Is there a way to have a query return only records where the text in two fields doesn't match?
View 1 Replies
View Related
May 20, 2015
I currently have a query that uses a function to get the current users email address. This is matched with a "Teacher Email" address in a table of students to only show students which match the logged in teacher. I'd like to modify this so the teacher can see any student in their department. Is there a way to do this without creating a separate "Teachers" table that would contain a department and email?
View 5 Replies
View Related
Apr 20, 2013
I have done this in Excel before, but not Access. I do not know VBA. I figure this will have to be done in a query or a macro. I don't even know if all of this is possible in Access. I need to be able to split an Address field into:
Street Number
Street Name
Street Type
Street Direction
And the purpose of this is so that I can pull out the Street Type (Drive, Road, Lane, etc) and update the abbreviations (DR, RD, LN) to the Street Type spelled completely out.
I did this in Excel by creating a Named Range "Types" on a sheet that has the abbreviations in Column A and the spelled out versions in Column B so that I could convert the abbreviations to complete street types. I broke down the entire address into each part on another sheet. Then I did a VLOOKUP to look up the Street Type in Column B in the Types range. Works great! And the only way I knew at the time to do that.
But, by doing this, I have to get the data I need from a download into a spreadsheet, break the address fields down on a second sheet, do all kinds of field update conversions (to get the field names from the download to match the field names in my Access table). This takes extensive Excel programming. I just thought there might be a way to do it by simply importing the data from the download straight into Access. That is easily done, but the Street Types aren't consistent.
There may even be a better way to do this than splitting....something like if a field CONTAINS DR update it to Drive. This would be a long process to set up because there are so many different street types to consider.
This is basically for the purpose of finding duplicates. If there is one entry called 123 Main St and another called 123 Main Street, they are not going to show up as duplicates, rather as two separate records.
View 2 Replies
View Related
Mar 12, 2013
Using sql or access query I would like to create an expression that aggregates the first field and I would like to see all records grouped by the relationship with another field. Let me show an example.
My query shows:
field1 field2
apple a
apple b
banana a
carrot a
carrot b
dog b
elephant b
I would like my query to now display a third field and group field :
field1 field3
apple both
banana a
carrot both
dog b
elephant b
View 1 Replies
View Related
Dec 26, 2012
I would likw for my first and last name fields width to match the length of the name. So if the last name is Smith then the field width will resize itself to fit perfect fit the name. Then if I have a last name of Connor then it will grow to fit it perfect. I dont need this but it will be nice so everything looks a bit nicer an cleaner. I thought the following would have worked:
Code : Me.Lastname.width = Len(Me.Lastname]
View 1 Replies
View Related
Dec 9, 2004
Hi, doing my project at college, decided to do a EPOS simulator for my sisters business based around barcodes etc and tied to a database. i've just knocked up something after normalising my data to prototype it to see if it is viable. I'm having problems with duplicate field entries. My db consists of essentially 4 tables at this point:
User : user ID, User Details
Product: Product ID, Barcode, Description, Cost Price, Sell Price
Transaction: Transaction Number, Date, User ID, Transaction Total
Transaction/Product - a compound key which is to manage the many to many that exists between Product and Transaction tables.
I am wanting to allow multiple entries for the same item, i.e. multiple scans, not a single sacn followed by a quantity. I keep getting an error message though that syas I am duplicating fields. I have tried altering the Index property for the fields in the Product/Transaction table but htis has had little effect.
Any thoughts please as to how to do this? I have attached the db for examination.
many thanks, Lol :D
View 5 Replies
View Related
Jul 14, 2005
Please can anybody help.
I wanted to have a form containing a list of members forenames and surnames in one record. I wanted to be able to input the members reference number and then the forename and surname would be automatically filled in.
I have achieved this through a query based on a table with ten foreign keys for the members. The query uses the members table ten times to join the ten foreign keys.
This works fine and the form runs with space for ten members and I can enter a members reference number in each field and their forenames and surnames are filled in.
The problem is that I wish to run queries with members surnames as criteria to bring out all records containing those members. How can this be done without having to set the criteria in the query in each of the members surname fields.
I know I can do this if I have a separate record for each member in the form
but I really need ten members in each record.
I hope this makes sense and somebody can help.
Thanks in advance
John
View 2 Replies
View Related
Mar 12, 2007
dear all
i managed to get checking for duplicate record by ONE field ie member_no
code :
If DCount("*", "runner", "member_no = " & Me.member_no) > 1 Then
MsgBox " This member is already exist!" & vbCrLf
Me.c_memberid.SetFocus
Else
DoCmd.CancelEvent
End If
how i want to get checking duplicate record by TWO fields ie member_no and run_no ?
thanks in advance
View 1 Replies
View Related
Feb 14, 2008
Hi
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.
Any help would be greatly appreciated.
Thanks
View 8 Replies
View Related
Jul 3, 2006
I have a document database that often deals with multiple copies of a document. Each copy must have a record of its own. With the add new record form I would like the user to be able to add the extra copies automatically by duplicating the first entry "n" times but also incrementing the copy number field by one for each copy. Got the duplicate copy done OK but am stuck on how to increment the copy number. This database forms part of my yearly assessment and is due in a couple of weeks so would welcome some help.
many thanks
View 1 Replies
View Related
Sep 21, 2012
I have a table that is going to track people. First Name, Last Name, and Date of Birth in three separate fields.I dont want to be able to add the same person in the table. How do i do this when the data is in separate fields.
View 9 Replies
View Related
Feb 22, 2015
I have a report based on a query.
I have this grouped by a jobid.
For each field on the report I have hide duplicates set to yes. for each job id most of the line should be hidden except for a couple of fields (part used and description).
This works in 99% of cases but I occasionally I get the whole line repeated
I've treble checked that the fields are duplicates (they are) and am at a loss what to look for.
View 2 Replies
View Related
Jul 2, 2014
Any way to query duplicate data from two different fields from two different tables in the same access 2010 project. I first quried the first part since it combines the first 3 columns to create another value (i.e. 52 & 60 = 5260).
So I created the concatenative value but now I have to compare to another field to display what results are found in both the concatenative and the other (APN in the file). I tried using query wizard but it is for only one field. I understand you have to use joins but the destination filed is what gets me.
View 4 Replies
View Related
Jan 30, 2006
Hi All,
A bit new to this, any help most appreciated.
I'm trying to set up a table which will have multiple fields (a recordset? is that right, if so I don't know how to set one up in access). Its for a skill set which is utilised by specific lines in a production plant.
Each line in the plant has a set of skills required. I have a skills table (SkillID and Skill), what I want to do is have a Skillset which I can then link to each line
So for example Skillset 1 with a SkillsetID will also have in that table skill 1, skill 2, skill 3, but all taken from the Skill table. However that would involve having the SkillID field numerous times in the same table, but this cannot be done?
The idea is that for each line I can link the Line table to the Skillset table and that tells you what skills are needed for that line.
I hope this makes sense.
Thanks all.
View 2 Replies
View Related
Mar 4, 2008
Hello All,
I'm trying to limit the data entered into a specifc field, but also the data must be unique with respect to other fields.
i.e.
Two fields: System A & System B.
Data entered into System A, can not be entered into System B.
Anyway to prevent this from recurring?
Would I use a validation rule?
Thanks in advance
View 4 Replies
View Related
Jan 3, 2006
Hi guys,
I have a question I've been trying to figure out with no luck. I have 2 fields, companyname and zip, these 2 fields may have duplicate values or they may be uniquei.e.
companyname zip
billybobs 68135
dilberts 68137
billybobs 68135
what I need to do is delete all of the duplicate records where both companyname and zip match. Is there a way I can do that with a query?
Any help is greatly appreciated
Thanks
James
View 14 Replies
View Related
Dec 25, 2014
I currently assign multiple 'skills' to departments using a listbox and a combo using the following code
Code:
Private Sub cmdSaveReq_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
[Code]....
However as time goes on we may want to add new skills to the depts - ive used dcount to check if the data exists in one field before however how would it be possible to check to see if the data combination exists over deptid and skill
View 3 Replies
View Related
Mar 19, 2005
I need to know if I can store data in more than one field, without allowing Duplicate entries.
Example: We have to send out letters with a claim number on it, sometimes we need multiple claim numbers on a letter, up to about 10 claim #'s is the most I've seen thus far.
We mail merge into Word to create the letters.
However, these claim numbers cannot be duplicated within the database and in order for this to work with the mail merge, I need several fields to store the claim numbers.
Fields are:
ClaimNumber
ClaimNumber2
ClaimNumber3
etc....
To be more specific. I probably have a bad data structure as I was kind of rushed and a little inexperience with putting the database together. It's for work and sometimes management just doesn't want to wait, they are so negative.
In brief, most everything is in 1 table and I know from reading this forum that I shouldn't do that, but that's another story.
Further, a claim# is tied together by the same claimant, the relationship code is what separates the claimant with the family, the insured id# is used for all claimants within the family.
You can also tie the claim# to a provider id#, some providers such as clinics and large hospitals have multiple providers that use the same provider id#, so a suffix code is used to separate the providers within that same #.
Please feel free to ask additional questions and I will give you the additional information you need to help me. Thanks again.
P.S. - Last year I created the database and this year I am improving it, so expect more posts from me in the future. I really appreciate all the help I get from everyone here!!!!
View 9 Replies
View Related
Apr 6, 2006
Basically i have a mortgage application system which im running a query on to see which applications have been completed in the last week or period up to now ie. last week, last month, last 3 months.
basically each application or CaseID has one or more applicants. The majority of them have two applicants. A few have one.
Problem is when i run this query which shows me: - CaseID, CLIENTNAME, LENDER, PRODUCT, MORTGAGE RATE, LOAN AMOUNT -
it brings up these fields but i only want one occurence of each CaseID????
CaseID Forenames Surnames PRODUCT RATEDateCompletedLoan required
2821NothandoDube71116.6920/01/2004£63k
2821Sipho Dube 711 16.69 20/01/2004 £63k
2909Hobbly Chise 726 15.69 31/01/2002 £164k
Get the picture? Both Sipho and Nothando are applicants on the same mortgage but i only want to show one name. Anybody know how to help me out???
Ive taken up this existing system, so i think there's possibly a normalisation problem thats causing this. But i need a quick fix for now rather than redesign the whole system.
View 2 Replies
View Related
Nov 15, 2005
how or what function (DLookup) should I use to prevent duplicate records based on multiple fields? I want to look at data in three fields that can't match existing data in those three fields. It's ok if one or two of the fields match but not all three.
View 2 Replies
View Related
Mar 19, 2005
I need to know if I can store data in more than one field, without allowing Duplicate entries.
Example: We have to send out letters with a claim number on it, sometimes we need multiple claim numbers on a letter, up to about 10 claim #'s is the most I've seen thus far.
We mail merge into Word to create the letters.
However, these claim numbers cannot be duplicated within the database and in order for this to work with the mail merge, I need several fields to store the claim numbers.
Fields are:
ClaimNumber
ClaimNumber2
ClaimNumber3
etc....
Any help would be appreciated.
Thanks.
View 3 Replies
View Related
Aug 29, 2013
I want query to find duplicate records, i have two field in one table
Cusip and category
cusip and category are many or duplicates
but in one cusip category should be the same if not then provide the cusip which has different category used
like this
CusipCategory
123R
456P
123R
456P
678Q
678Q
123A
result should be
CusipCategory
123R
123R
123A
View 6 Replies
View Related
Jan 10, 2008
Hi all,
I have the following issue:
In my job we work with several raw data .txt files exported from Oracle ERP system. These data include information about: inventory, sales, backorders, purchase orders, forecasts, product line.
My goal is to put information from all of these imported txt files together to create an easy to use snapshot file.
The common field between al of these files is the item description. I have an issue where records are repeated for several fields every time the same item description is showed. For example for the same product description the january sales forecast QTY is repeated on several rows because there are several orders for this product description. Is there a way to make forecast QTY appear only once but keep the multiple orders and their information?
Example of current result
http://img178.imageshack.us/img178/5659/currentsu7.th.png (http://img178.imageshack.us/my.php?image=currentsu7.png)
to achieve result
http://img179.imageshack.us/img179/5100/toachievefk4.th.png (http://img179.imageshack.us/my.php?image=toachievefk4.png)
thank you for any comments
View 4 Replies
View Related
