Queries :: Duplicate Data From Two Different Fields
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.
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
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.
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!!!!
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.
I'm running an update query that's based on a select query (that runs some calculations). The update query is updating ALL the rows that should be updated with the information in the select query with data from the final row in the select query, and not on a per ID basis as I think I have it set-up to do. The data looks correct in the update query, but again it's not coming out right.
I have 10 tables linked in one query. 9 tables are linked to one main table (one to many relationship).I want to get rid of duplicate records that show different data in multiple columns. I want only one record of this but retain with different data under different columns to be separated by commas. For example: I want this...
Code: Employee # Name Course Start Date Completion Date 1 John Smith MS Office Training 1/1/2010 5/1/2010 1 John Smith Python Training 1/30/2011 4/1/2011 1 John Smith Leadership Development 6/27/2013 9/1/2013 1 John Smith Sensitivity Training 9/5/2010 - 2 Hank Joel MS Office Training 8/1/2010 10/1/2010 2 Hank Joel Sensitivity Training 8/1/2010 10/1/2010 2 Hank Joel WHMIS Training 11/15/2010 12/1/2010 3 Jane Doe Leadership Training 7/18/2011 9/26/2012
To turn like this:
Code: Employee # Name Course Start Date Completion Date 1 John Smith MS Office Training, Python Training, Leadership Development, Sensitivity Training 1/1/2010, 1/30/2011, 6/27/2013, 9/5/2010 5/1/2010, 4/1/2011, 9/1/2013, - 2 Hank Joel MS Office Training, Sensitivity Training, WHMIS Training 8/1/2010, 8/1/2010, 11/15/2010 10/1/2010, 10/1/2010, 12/1/2010 3 Jane Doe Leadership Training 7/18/2011 9/26/2012
I am using two tables to find the data (main - "employee tbl" and "courses tbl")I have been trying to follow Allen Browne`s method, but I`m unsuccessful. This is the code I've put in SQL of this query:
However, I am prompt with "Syntax error in query expression".It also prompts another expression to be in error when I include the above but it runs okay when I don't do the above:
Quote:
WHERE (([Employee Tbl].[Employee #]) Like [Enter Employee ID or leave blank for ALL employees] & "*")
I placed the following in VBA module:
Code: Public Function ConcatRelated(strField As String, _ strTable As String, _ Optional strWhere As String, _ Optional strOrderBy As String, _
I have a table with duplicate records (which is ok) and I want to return distinct data for each client.
It works fine when there is only two fields returned however, when I add a third field to the query it no longer returns only the Distinct records - I am getting Duplicates returned.
I.E
SELECT DISTINCT tblClient.ClientNo, tblClient.Name FROM tblClient
Works fine with only the Distinct records for each client returned
However
SELECT DISTINCT tblClient.ClientNo, tblClient.Name, tblClient.Address, tblClient.OrderValue FROM tblClient
Now returns Duplicates!
Is there a limit to the number of fields to be returned using DISTINCT or what else could be the problem? Should I be doing this some other way?
I have a table called tbl Meeting Response. It records the data about churches where I have scheduled a representative. Two of the fields in the tbl Meeting Response are 1. Normal Attendance (how many the church normally runs) and 2. Tour Year (the year we were there). Reps have been scheduled at many of these churches more than once over the years, so there are records for different years for the same church in the Meeting Response table.
I want to create a query that will show a field with the most recent normal attendance from the Meeting Response table. This would require comparing the information between two different fields -- Normal Attendance and Tour Year. 1. If a rep was at that church in 2014, 2013 and 2012, and we have a Normal Attendance number for all 3 years, I want the field to show the attendance submitted in 2014 (the most recent). However, if the rep didn't call in a normal attendance in 2014, and the most recent attendance we have is 2012 (i.e., the Normal Attendance is blank for both 2014 and 2013), then I want that field to show the attendance for 2012 (the most recent).
I have a table in a database where the telephone numbers are in two separate fields, i.e. [123] (which is the areacode) and [456-7899]. Is there a way to take the two fields in this table and put combine these two numbers into one field so the new field will be in this format.... [123-456-7899]?
I then have various support queries that group customers based on reason for visit, type of visit etc. I need to be able to link this to my main table so that each customer has a category against them. The issue I have is that some customers will have more than one visit ref, which can also mean they have multiple reason for visits. I need to make sure that when I link my queries back to the main table that the right category appears against the customer and their correct visit ref.
I have a query that pulls data from the following fields in 2 different tables:
Area1FloorPrep (tblFloorPrep) ex. remove ceramic tile Area1Size (tblInstallationAreas) ex. 20 s/f, or just 20 Area2FloorPrep (tblFloorPrep) Area2Size (tblInstallationAreas)....
All the way to Area20 (Floorprep and InstallationAreas) for both tables. I have created an installer invoicing form that pulls the data from the workorder that these fields are located in, but the problem I'm having is that I don't know what kind of query to create to concatenate the data in the 20 fields and concatenate the size of the areas next to the appropriate concatenated floor prep description. Is there a way to do this without coding?
All; using 2010. I have a table that I need to update some data from another table. I want to use the SSN but one of the SSN fields in the table has letters at the end of it and doesnt return any records. How can I join fields?
I'm trying to create a query that will compare the data in 3 fields in a record, choose the largest (I also have a criteria to order by if more than 1 field has the same entry and it's the largest of the 3), and then group by that.The fields I will need are as follows:
PRODUCT table: ProductName Chemical ChemicalAbstract PhysicalState NFPAHealth NFPAFlammability NFPAReactivity
qryQuantityOnHand query (which doesn't link directly to the PRODUCT table, it links through associations with other tables):QOH...I will eventually need information from another table for the final reports, but I don't think it has to be included in this query.
The fields NFPAHealth, NFPAFlammability, and NFPAReactivity each may be 0, 1, 2, 3, or 4...I need to ignore blanks; if 1 of the above fields is blank, they will all be blank.For any record, I need to compare the number in those 3 fields to each other, and choose the largest number and group by that rating.
In other words, if the largest of the 3 numbers is a 3 in the NFPAFlammability field, all those products need to be grouped together.If the same number appears in at least 2 of the fields, the order that determines the grouping is: Flammability, then Health, then Reactivity..Ultimately the report will be grouped as follows:
and each of the groups will be sub-totalled.I'm stumped at trying to create the query in the first place.The added aggravation here is that we are dealing with 23 stores, each with their own mix of products. I have another table that contains the information about which products are in which store.
I have a search form with 12 fields. In my query I use
Code:
Like "*" & [Forms]![CustomerRetestDatabaseSearch]![RetestLocation] & "*" Or Is Null
for each field on the search form.
I get the results I expect, it finds all records that match the criteria. Even if some of the fields in a record are null.
But if the query finds a record that matches one field I enter criteria into, and nulls for the other fields I enter criteria into it displays the record. I want to show exact matches. (If what I entered is null... don't show the record).
The reason I have "Or Is Null" is to include the records for the fields I left blank on the form.
Search Form with Criteria.PNG
Search Query.jpg
Search Results With Missing Entered Criteria(Dont Want These Records Included).jpg
I have a query that pull data from hours worked fields that have values with decimal places. Hours is multiplied to rates to create my amount to charge in my query which is then displayed on my invoice report.
The issue is that when I sum my amount, my subtotal is off by $.01 due to the decimals entered for the hours worked (e.g. hours worked is 1.5*32.75=49.125)
I need the rounding to work in the query and display on my report as $49.13 and the subtotal to reflect it. I have the properties in my table as double with decimal place at 2, and my query and report properties with decimal place at 2 as well. It still isn't working.
I need to create a form to enter data from a survey, and ideally some sections of the form would only be completed when the answer to a first question is "yes". How to do this enabling/disabling of fields for data entry?
I have a query that I made for about five fields, where the criteria is
Like "*" & [Forms]![DATA SEARCH]![Box] & "*"
Where box is the name of the field that I am searching for.
However, some of the fields in my form are left blank, which makes this refuse to give any results when I try to query it with a form. The other problem that I have is that the fields are bland in different parts of the 1,000 some-odd row table, which was imported from MS Excel.
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:
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.
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.
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.
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 ?
