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
task1 fail1 replace1
task1 fail1 replace2
task1 fail1 replace3
On the report I can hide duplicates to get the desired (. = space!!)
task1 fail1 replace1
...............replace2
...............replace3
Problem occurs when I get more than 1 fail. Query returns
task1 fail1 replace1
task1 fail1 replace2
task1 fail1 replace3
task1 fail2 replace1
task1 fail2 replace2
task1 fail2 replace3
Hiding duplicates I get
task1 fail1 replace1
...............replace2
...............replace3
.........fail2 replace1
...............replace2
...............replace3
whereas I'd like to get
task1 fail1/fail2 replace1
.....................replace2
.....................replace3
or something similar.
View Replies
ADVERTISEMENT
Dec 4, 2014
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
View 3 Replies
View Related
May 19, 2013
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!?
View 2 Replies
View Related
Jul 17, 2014
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]));
View 4 Replies
View Related
May 18, 2015
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));
View 6 Replies
View Related
Apr 24, 2014
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.
View 8 Replies
View Related
Jun 12, 2013
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
View 7 Replies
View Related
Jun 15, 2013
I am trying to manipulate a find duplicates query using the following criteria:
Fstnm L2, Lstnm L5, Add1, Zip
This is what I have done so far:
SELECT [Duplicate Identification Dataset].[FSTNM], [Duplicate Identification Dataset].[LSTNM],
[Duplicate Identification Dataset].[ADD1], [Duplicate Identification Dataset].[ZIP],
[Duplicate Identification Dataset].[ID], [Duplicate Identification Dataset].[MIDNM],
[Duplicate Identification Dataset].[SPFSTNM], [Duplicate Identification Dataset].[SPMIDNM],
[Code] .....
View 1 Replies
View Related
Dec 3, 2013
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],
[Code] .....
View 1 Replies
View Related
Feb 11, 2014
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.
View 3 Replies
View Related
Feb 13, 2014
I have two tables that look like this:
Table 1
LOCATION NUMBER , SIZE
1.12 ,100
1.13 ,100
1.14, 12
1.15, 12
1.16 ,150
1.17 ,150
1.18 ,100
Table 2
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 only want to have 1 location for every 1 Item.
View 6 Replies
View Related
Jun 4, 2013
I'm trying to duplicate the records in a subform to a new form but keep getting a too few parameters error.
Code:
strSql = "INSERT INTO [OrderDetailT] ( OrderID, ProductID, Quantity, DiscountPercentage ) " & _
"SELECT " & lngID & " As NewOrderID, ProductID, Quantity, DiscountPercentage " & _
"FROM [OrderDetailT] WHERE OrderNumber = " & Me.OrderNumber & ";"
The debug.print comes out as below:
INSERT INTO [OrderDetailT] ( OrderID, ProductID, Quantity, DiscountPercentage ) SELECT 49 As NewOrderID, ProductID, Quantity, DiscountPercentage FROM [OrderDetailT] WHERE OrderT!OrderNumber = 11;
View 4 Replies
View Related
Apr 26, 2013
I'm having difficulty with the syntax in this query to remove duplicate data for the field "StocktransID".
Code:
SELECT DISTINCT tblStockTrans.StockTransID, tblItem.Brand, tblItem.Category, tblItem.SubCategory,
tblItem.Model, tblItem.Description, IIf(TransTypeID=3,Quantity*-1,Quantity) AS Qty,
tblTransaction.TranstypeID, tblItem.ItemID, tblTransaction.TransactionID, tblItem.ItemType,
tblItem.Origin, tblOption.ParentID
[code]...
View 6 Replies
View Related
Jun 18, 2013
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:
Code:
AcctNum FoodType FoodClass
------- -------- ---------
A456 Potato Vegetable
A456 Potato Perishable
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').
View 5 Replies
View Related
May 10, 2006
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)
View 1 Replies
View Related
Jul 30, 2007
Is there a way to find and remove duplicate entries in tables by using queries.....if so, how specifically?
View 1 Replies
View Related
Oct 25, 2014
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
View 14 Replies
View Related
Aug 18, 2005
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?
View 2 Replies
View Related
Jun 25, 2014
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'm on Access 2010 if it makes any difference.
View 6 Replies
View Related
Jun 16, 2015
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
View 1 Replies
View Related
Sep 15, 2013
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.
View 2 Replies
View Related
Jan 19, 2014
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 ...
View 3 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
Feb 20, 2014
How can I have this query to show no duplicate records.
I would like to only see the first record only of each stock code.
View 2 Replies
View Related
Jul 23, 2015
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).
View 2 Replies
View Related
Sep 9, 2013
I would like to export ALL records but not have the duplicate or triplicate "STOCK SYMBOL" records sent to the CSV file
Stock Smbol
Paul
Paul = 1 Csv record
Bill
Bill
Bill = 1 Csv record
View 1 Replies
View Related
