Queries :: Hide Duplicate Record Using Query

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 Replies


ADVERTISEMENT

Queries :: Save First Record Of Each Duplicate

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

Queries :: Duplicate Record Do Not Export

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

Reports :: Move Or Hide Duplicate Records?

May 8, 2013

I'm having trouble with one of my reports. attached is a screencap of my report. The second name which is highlighted i need to either nudge it to the right so it is not directly under the first name OR remove it completely but keep the times and a gap for the next record.

I am creating is a sign in database where staff sign in/out for each day of a week. the issue is when the staff member signs in/out in the morning and then does the same when they get back from lunch it is difficult to read. This report is only printed once a week and will not show after a time frame of 5 days.

So, i still need that name to show up for the next day.

View 1 Replies View Related

Queries :: Hide Zero Fields From Query

Aug 14, 2013

I have a query that selects different values from different tables.The values of these fields change all the time and I would like the result of my query to exclude - hide the fields that are zero. However, I only want to hide the specific zero fields not the hole record.Non zero fields should still be visible.

View 10 Replies View Related

Queries :: Hide Duplicates From Query?

Aug 1, 2013

I have a query showing duplicates, I want to hide the duplicates that have a null value in one of the rows..

Trunc_Name M_UNDERLYIN MaturityDate Duration SumOfSumOfM_VEGA Volatility
SPX P1400 DEC13 INX 21-dec-13 4,766666667 -108 812,00 0,22437
SP6652 CALL SP6652 04-okt-17 50,86666667 124 376,00 0,21
SP6652 CALL SP6652 04-okt-17 50,86666667 124 376,00

Here's an example of the a duplicate, all 4 first rows are duplicates, but the last value is null for the last duplicate. I want to hide the one the duplicate that has null value!

View 4 Replies View Related

Queries :: Hide Field In Union Query?

Dec 11, 2013

Is there a way to hide a field in a union query? I need to keep the field in the SQL statement because I need to order by it. The field is "Rank," but I don't want it showing up.

View 3 Replies View Related

Queries :: How To Show / Hide Columns In A Query Using A Form Checkbox

Jul 16, 2015

Currently I have a query where the criteria is dependent on the combo boxes on my form. I would like to add checkboxes to my form which determines which fields are shown or hidden. For example if I had a checkbox for address, selecting it on my form will show the address column in my query results.

View 4 Replies View Related

Queries :: Print Shipping Labels - Hide Query Results

Jul 5, 2015

I have a button on a form which is used to print shipping labels. This button runs a Query and then a report using the code below.

Private Sub SaveBtn_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL "Update BookInTable SET DateBookedOut = '" & Me!DateTxt & "' WHERE BarCode ='" & Me![BarTxt] & "'"
DoCmd.RunSQL "Update BookInTable SET BookedOut = True WHERE BarCode ='" & Me![BarTxt] & "'"
DoCmd.OpenQuery "PrintLabelQuery"
DoCmd.OpenReport "Labels", acViewPreview
DoCmd.PrintOut , , , , 1
DoCmd.SetWarnings True
End Sub

But I need both the query results and the report to not be seen. All I want to happen when clicking the button is that a label is printed.

View 5 Replies View Related

How To Delete Duplicate Record In Query?

Sep 1, 2005

Hi,
I have table tblSample (IdNo, SCode, StaffNo) where IdNo is AutoNumber. This is sample of data,

IdNo SCode StaffNo
10 ABC 50424
11 DEF 50424
12 GHI 50424
21 ABC 50424
22 DEF 50424
23 GHI 50424
26 ABC 50424
27 DEF 50424
28 GHI 50424

Is it possible for me to delete records from IdNo 21-23 and IdNo from IdNo 26-28. Any idea? Thanx in advance..;)

View 1 Replies View Related

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 14 Replies View Related

Queries :: Query Shows Duplicate

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

Queries :: Inserting With Duplicate Key Query

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

Queries :: Duplicate Count From Group By Query

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

Queries :: Removing Duplicate Records In Query

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

Queries :: Custom Delete And Duplicate Query

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

Queries :: How To Manipulate Find Duplicate Query

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

Queries :: Duplicate Results For Some Of Records In A Query

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

Queries :: How To Remove Duplicate Records From A Query

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

Queries :: Creating Query Without Repeating Duplicate Relationships

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

Queries :: Insert Into Query - Duplicate Records In A Subform To New Form

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

Queries :: Syntax In Query To Remove Duplicate Data For Field

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

Hide 1 Record

Apr 10, 2007

I was wondering if there is a way to hide 1 record from a query

say I have 3 user accounts and on a form I only want to display 2 of them form the query,so the other record is still present but cant be deleted by accident or viewed

View 2 Replies View Related

Hide Record

Jun 8, 2005

Is it possible to just hide a record? My form is based on a query so I know I could remove the record that way but I also have a box that calculates a average time, so the data that is produced by that record still needs to be taken into account. I want to hide some records pureley so the user dosent have to keep scrolling thriugh loads of records. Is it possible to do this?

View 9 Replies View Related

Hide New Record Row

Jun 28, 2005

I have a continuous form "components" where the data is added by a query. There is no need to manually add records to the form, I just want to edit certain fields.

Is it possible to hide/disable the blank "new record" row at the bottom of the form?

I tried setting the form's "Allow Additions" property to no, but the line still appears.

Thanks for your help!

Frank

View 3 Replies View Related

Forms :: How To Hide A Record

Jan 18, 2015

can i hide a record when i look it in a form. For example i have a table which has 100 records. Can i make some kind of a check box and if that check box is checked to make the record disappear. I want the record to be in the table but i dont need it in the Form.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved