Queries :: Query Duplicate / Replacements Are More Than 1
Aug 14, 2013
I have a database which holds information about repairs. Each repair is called a task and among other things data is held about things(s) that caused the failure and what need to be replaced. Usually it's one fault but it could be many.Replacements are usually more than 1. Bottom line, in a simplistic view, I have 3 tables task, fails and replacements. There is one to many relationship between tasks and fails and also a one to many relationship between tasks and replacements.
If there is one fail and many replacements my query is good enough as it will produce
I hve a query that I want to total soldAtPrice *quantity This information is stored in my order details table and mus not change .I can do a calculated field to get the answer but the Problem is if there are 2 recodrs to be totaled the query displays 2 records if there are 3 records the query dislplays 3 records and so on .I need one record to be displayed with the total of all the recodrs
I have a load of data in an excel spread sheet that I'm trying to put into an access using Excel VBA (Jet/ACE SQL I think!?). I do this by constructing INSERT INTO strings and looping through my spread sheet. Let's say the primary key is associated with the field [Company] which holds the company names. A simplified version of what I was using (it all works great ) is below:
Code: Set rs = New ADODB.Recordset Set cn = New ADODB.Connection cn.ConnectionString = GetConnectionString() cn.Open cn.Execute strSQL
where strSQL =
Code: INSERT INTO tblTestMDB ([Company], [Date]) VALUES ('BP', '30/09/2012')
Unfortunately, I am having issues with users adding the same company twice. I'd like it to:
- update the existing company info if the company already exists - add the company if it is not already there.
I'm trying to do it in two stages. First an update, then an add. The first part works great :
Code: UPDATE tblTestMDB SET [Date]='21/03/2015' WHERE [Company]='BP';
The second part: (I have tried numerous variations on the syntax):
Code: INSERT INTO tblTestMDB ([Company], [Date]) VALUES ('BP', '30/09/2012') WHERE NOT EXISTS (SELECT * FROM tblTestMDB WHERE [Company] = 'BP');
I find the error messages rather cryptic but they include things like "Query input must contain at least one table or query." or "Missing semicolon ( ; ) at end of SQL statement." (<== No I'm not!?
I'm trying to count the number of records within a region range using a lookup table however I keep getting duplicate values, SQL code, what is happening:
SELECT Count([summary].Key) AS CountKey FROM Summary, lookup WHERE ([Region])) Between [Region 1] And [Region 2]));
I have a question on hiding duplication record using query.
The fields in the query are:
full name(trainee), NRIC(trainee), gender(trainee), preferred language(trainee), company(trainee), course name(course), course date(course), competent(course), class(course), L3 survey(trainee), L4 survey(trainee), num of month(course)
When the query is being run, it will show all the people that have not done the L3 and L4 survey after 3 month. The the query will be convert into a form. However the problem is that the record will show a few same name due to one person can take more than 1 course. therefore, the data in the course table will always be different.
How can i make the record only show 1 name even though they have different course name.
I had tried putting 'yes' for unique record and unique value but it did not change the result.
current SQL query:
SELECT DISTINCT trainee.[Full Name], trainee.NRIC, trainee.Gender, trainee.[Preferred Language], trainee.Company, course.[Course Name], course.[Course Date], course.Competent, course.Class, trainee.[L3 survey], trainee.[L4 survey], DateDiff('m',[Course Date],Date()) AS [Num of Month] FROM trainee INNER JOIN course ON trainee.NRIC = course.NRIC WHERE (((course.Competent)="c") AND ((trainee.[L3 survey])=False) AND ((DateDiff('m',[Course Date],Date()))>=3)) OR (((trainee.[L4 survey])=False) AND ((DateDiff('m',[Course Date],Date()))>=6));
Im trying to write a query that shows all the container movements. Yet when I run the query qryFullHistory I get a duplicate value for container Off Island. Ive tried adding some criteria that says that the DateRequested has to be between the ImportDate and ExportDate but that doesnt seem to work. There are duplicate entries for container Off Island in tblContainerDetails as the same container has arrived and left and then returned on another voyage. Yet there is no entry for the second voyage in the tblMEMRContainer.
A brief description of the tables is: tblMEMR Movement requests details tblMEMRContainers the containers that were moved on the movement request. There can be more than 1 container for each request. tblContainerDetails details and dates for the container when it arrived and when it left
There are other tables but these are the 3 that are used in the query.
I am trying to create a query to find duplicates and delete the duplicates. The result will eventually be used in another query (append query) to update a table.I have a table with 4 columns lets say for simplicity they are A, B, C, D
I want my query to find duplicates within B and deleting them. The catch is before deleting them I need to look into column A to ebsure they are actually duplicates. Example below
Example A B John Doe Tires John Doe Wipers Allison Doe Tires Allison Doe Tires
As you can see from the above Tires is a duplicate and need to be deleted.
A B John Doe Tires John Doe Wipers Allison Doe Tires
Why I am getting duplicate results for some of my records in a query. I have unique values set to Yes. I have also validated that the tables I am using don't have duplicate data. SQL is below.
SELECT DISTINCT [tbl_Rewards Activity Report - By Member Number].[Member Number], [tbl_Rewards Activity Report - By Member Number].[Last Four], [tbl_Rewards Activity Report - By Member Number].[BAL ID], [tbl_Rewards Activity Report - By Member Number].[Primary Name],
I have this small database, I would like to have your support to setup this query "QryResults" in order to remove the duplicate records, I can't find a way to get shown only true records, for some reason I'm getting duplicate rows and fake values, the query is calculating operations from two different queries and a table.
ITEM , SIZE A , 12 B , 12 C ,100 D ,12 E ,100 F ,100 G , 150
I would like to do a query that Joins the "Size" in each table, and then matches an "Item" to a "Location". However, because of how a normal join works, I cannot seem to figure out how to limit the "Location" field from producing duplicates in the match.
I'm trying to determine the SQL to return only those records in a table which have duplicate values in each of two fields, but different values in a third field. Here's an example:
Code:
AcctNum FoodType FoodClass ------- -------- --------- A123 Apple Fruit A123 Apple Fruit A123 Grape Fruit A456 Potato Vegetable A456 Potato Perishable A789 Carrot Vegetable A001 Banana Fruit
For the above table, I'm trying to return records which have multiple entries for AcctNum + FoodType, but DIFFERENT values for FoodClass. So for the above table, the query would return:
It returns these two records because there is more than one record with for the AcctNum + FoodType (i.e. 'A456' + 'Potato'), but DIFFERENT values for FoodClass (i.e. one record has 'Vegetable' while the other has 'Perishable').
I thought I had just about finished my DB but now Ive printed out and checked my reports I notice I have several records in different types of reports showing duplicate records. Iv'e gone back over the queries and there are one or two duplicates in several of my queries that I did not notice before because there are are only the odd one or two. No matter what I do I can't stop this happening. I think it must be something to do with the dates in my payment table. PaymentID is key field - foreign key is MemberID. One member can have several paymentID's a new one every year when membership renewed. The problem seems to be when the member has two payment dates in the same year. This shouldn't be anyway but the database has not been used properly with entry dates missing, written over of wrong dates etc. Ive tried to correct this but do not want to tamper with past payment date records. I already have 'select distinct' in my queries and have tried 'distinct row' which seems to return even more duplicate records. Im pulling my hair out over this there must be a way to return the records from members showing just 1 only of their very latest payment record/date. Im using this expression in the query.
LastPaid: (SELECT MAX(PaymentDate) FROM S_Payments_Table WHERE S_Payments_Table.MemberID =S_Members_Table.MemberID)
I have been hitting my head against the wall to have my query not show any DUPLICATE STOCK CODES in my report..I have two "AQN.PR.A" records, I only want my report to print one "AQN.PR.A"...I have three "ALA" records, I only want my report to print one "ALA"AQN.PR.A
I'm comparing two tables in a query, the tables are information that was imported from other sources. When I compare the tables, I'm trying to get a one to one result (a reconciliation of sorts). The problem is that there are duplicate line items in one of the tables that may or may not be valid, but all are considered matched. How can I create my query to match to only one line item and leave the others unmatched for analysis? I've tried everything but VBA (of which I have little experience in). Any suggestions?
I'm trying to use access to identify how many times an indiviual appears in my database. I've got a large file which gives me all referrals, each referral comes with two identifiers, one unique to the person, another unique to the referral. This means that the person unique number may be repeated multiple times (if a person has more than one referral) but the referral number would be different each time.
I've run a query to identify the duplicates, i.e. the records where the person unique identifier is repeated. What I am trying to do is work out now how many people have been referred twice, how many three times etc. The original query looks something like this (but on a much bigger scale).
Person Referral 1 A 1 B 1 C 2 D 2 E 3 F 3 G 3 H
What I'm after is a query that will tell me that two people were referred three times (1 and 3) and that one was referred twice (2) and so on.
I have a union query. I am pulling in data from two (almost identical) SQL views in two different databases.
My MS_Access application is linked to the two views
The issue is A.GroupId. In view finance_WILPMonthEndReview the field is set to NVARCHAR255 in view iq4bisfinance_3DMonthEndReview the field is set to NCHAR255.
This is my union query.
SELECT A.Dataareaid,A.a_year, A.a_month, A.GroupID, A.dimension, A.accountnum,A.companygroupaccount,A.account_type, C.groupiddesc,D.costcelldescription, B.PL1assignment, Round([A.SumOfActual_amt],2) AS ActAmt, Round([A.SumOfBudget_amt],2) AS BgtAmt
[Code] .....
When I link my Excel pivot table to the query I get two lines
I have the following code and I am trying to show a COUNT of duplicate ID numbers, so for example
ID ID_COUNT 2000 2 2000 2 2001 1 2002 1 2003 1
Code:
SELECT [DaisyServiceData-Import].*, Companies.CompanyName, Company_vs_Services.[Tariff Code], IIf([ClashForce]=True,"",IIf([ClashOveride]=False And [ClashClear]=False,IIf([DaisyServiceData-ClashExc].[DaisyServiceData-Import].[ID] Is Null,"",1),"")) AS ClashExc, IIf([Sell Price]="","",IIf([SellRateForce]=True,"",IIf([CompanyName] Is Null,"",IIf([SellRateOveride]=False And [SellRateClear]=False,IIf([Total Cost]>[Sell Price]+0.001,1,""))))) AS SellRateExc, IIf([Tariff Code]='1',[T1Rate],
[Code] ...
A GROUP BY query will not work for me as the recordset needs to remain updatable.
My query lists duplicate records which I append to a new table. from that table I would like to delete the duplicates but save the first record of each duplicate. example the fields are employee and date worked. I would like to keep joe smith 1/1/14 but delete the other joe smith 1/2/14 and joe smith 1/3/14 ...
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'm having a problem removing duplicate records from a table. It should be easy but I can't suss it.This is an example of the data in my table:
Code:
RefDateStatus 113007111/06/2015Do Not Pay 114454306/07/2015Do Not Pay 115760714/05/2015Do Not Pay 116520705/05/2015Do Not Pay 117670108/05/2015Do Not Pay 118036218/05/2015Do Not Pay 118517015/05/2015Do Not Pay 178734020/07/2015Do Not Pay 182809915/07/2015Do Not Pay 184226010/07/2015Payment Due 184226022/07/2015Payment Query
As you can see, there are 11 records here but the last two records have the same reference number. I need my query to show the first 9 records + the record from the bottom two with the latest date (22/07/2015).
