Query To Filter Out "duplicate" Data
Oct 6, 2005
Ok i've got a table with about 105,000 records. It has fields: AccNo, PatientName, MRNO, TestCode, OrderLoc, DOS, TOS, etc, etc.
The table does have records that WE consider as a "duplicate". If two records both have the same "AccNo" & "TestCode", it is CONSIDERED a "duplicate".
I need a query which will FIRST "filter" out these duplicates as ONE RECORD only. The query should only display records that don't have a same "AccNo" & same "TestCode". And also display all other misc fields.
How do i go about doing this?
View Replies
ADVERTISEMENT
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
Dec 30, 2014
I would like to filter data from a table using a query (from an data input form). The objective is to output all results if input form field is empty and to output results higher or equal to the type in the field if field is not Null. The query code is as follows:
IIf(IsNull([Forms]![Form2]![MaxDiffInput]);[Maximum operational pressure (bar)];[Maximum operational pressure (bar)]>=[Forms]![Form2]![MaxDiffInput])
However, is not providing any result when the input field (MaxDiffInput) as a value.
View 5 Replies
View Related
Jun 13, 2007
I have a database that allows multiple entrys of the same information. I also have a report based on a query that pulls out said data.
I just need my query to pull out every record regardless if it is indentical to a previous one. I found under queries > design > properties there are two options unique values and unique records. According to a source online, these are what control allowing duplicates through, but I have swapped them around with no luck. Currently they are both set to "no".
Is there some other setting I have to switch up in order to allow the duplicates to come through?
Thanks
View 1 Replies
View Related
May 5, 2014
so i had created query using query design wizard for which i i needed to enter year of the data everytime the query is being made all my data are correct when just using the query.however when i turning it into report where the query will still be runing and with adding in another field from my original table to get on the montly rearrangement that's where the some record are shown duplicate on the report
View 2 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
Nov 1, 2012
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.
Here's the SQL for what I've written so far:
UPDATE [Customer_Data Query], Customer_Data INNER JOIN Baseline ON Customer_Data.ID = Baseline.ID SET Baseline.[Unit Hours] = [Customer_Data]![Dur_Days]*[Customer_Data]![Dur_Hours]*[Customer_Data]![Number_Units], Baseline.Availability = [Customer_Data]![Perceived_Avail], Baseline.[Hours Available] = [Customer_Data]![Dur_Days]*[Customer_Data]![Dur_Hours]*[Customer_Data]![Number_Units]*[Customer_Data]![Perceived_Avail],
[Code] ....
I've also tried to force the update to the proper row by adding a criteria based on ID.
Select query, here it is:
SELECT Customer_Data.ID, Customer_Data.Data_Set_Version, Customer_Data.Number_Units, Customer_Data.Perceived_Avail, Customer_Data.MTTR_MTBF, Customer_Data.MT_TR_OR_BF_Hours, Customer_Data.Utilization, Customer_Data.Percent_Scheduled, Customer_Data.Sched_Percent_of_PM, Customer_Data.Sched_PM_Duration, Customer_Data.Sched_CBM_Duration, Customer_Data.Sched_CBM_From_PM, Customer_Data.React_Detect, Customer_Data.React_Rework, Customer_Data.React_False_Alarms,
[Code] ....
View 3 Replies
View Related
Feb 1, 2008
Hi there,
I was wondering when I am filtering out data from a table, is there a way for me to filter out specific lines (as in, if I have a 100 line datasheet, can i choose to filter out lines 5, 10 and 20 out of the query)?
For instance:
Line Name Address Phone Number
1 A1 fjlafs 453453454
3 A2 fsdfsd 343534534
4 A3 gsdgsdg 354543534
5 A4 gsdgsdgsd 345345345
I want to make it so that lines 2 and 4 are NOT included in the query when I click "RUN".
Thanks in advance!
View 4 Replies
View Related
Aug 14, 2012
I have large dataset(more then 15,00,000 rows) In one column I have Amount stored as text eg: $15,000.00 $ 12,000.00 $ 17,520.00
Data is imported from various small files and is updated regularly. Now I need to filter dataset based on this column criteria like > 15000, <= 5000
How can I configure query to filter data.For example data are:
Id
Detail
Value
Part
25252
Some text here
$17,294.00
1
[Code]...
View 14 Replies
View Related
May 30, 2015
table name : Schedule
Field 1 = Vessel code
Field 2 = Voyage
Field 3 = ETA
Field 4 = berthed
Field 5 = Sailed
there is a query by using above table and data entry form based on that query.
need to add following facilitate
While data entering, if given voyage number is already exist for the particular vessel code, msg should be pop up immediately at that time saying " This voyage number is already exist"
How could this be manage ?
View 4 Replies
View Related
Mar 14, 2006
I have a form which is linked to a query. The main form which calls this form sets the filter for the query. This works fine and I can navigate all the filtered records.
The problem is when I go to add a new record the filter information goes away.
For backround this is DB for project information. The user clicks a button to view notes on the project. The notes are stored in a different table and the project_id is the common field which links the two together. I want this to function where the user can open the subform read the notes and add a new one if needed.
View 1 Replies
View Related
Jul 27, 2013
problem when I am trying to use Form to filter data from a cross tab query though I have already defined the parameters. The SQL is as under :
//Code start
PARAMETERS [FORM]![START_MONTH] DateTime, [FORM]![END_MONTH] DateTime;
TRANSFORM Round(Sum(CLng([A_GAS_m3]+[NA_GAS_m3])/1000000),3) AS GAS_MMSCM
SELECT maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block
FROM maindata RIGHT JOIN PRODUCTION ON maindata.field_block = PRODUCTION.FIELD_BLOCK
WHERE (((maindata.field_block)<>"Panna" And (maindata.field_block)<>"Mukta" And (maindata.field_block)<>"CB-ONN-2000/2(NSA)" And (maindata.field_block)<>"CB-ONN-2000/2(BHEEMA)") AND ((PRODUCTION.MONTH) Between Format([FORM]![START_MONTH],"dd-mm-yyyy") And Format([FORM]![END_MONTH],"dd-mm-yyyy")))
GROUP BY maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block
ORDER BY maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block
PIVOT PRODUCTION.ACTIVITY;
//Code End
When I save it prompts for the Start and End Month and When I run the query it pops up the form twice...
View 1 Replies
View Related
Jul 24, 2014
Running Access 2010 and developed this query to filter out data from a table between times on 2 dates (day before report run and day of report). Covers data from a shift that carries over to the next day.Trouble is, the PC i developed on still operates the query as expected. However, on the PC the database resides (not networked just stored) and operates, the query brings up no data at all unless I remove the Time filtering.
This PC used to operate correctly up until early this year (about 18 months operated correctly) when the PC was replaced due to failure. Access version is the same and I am at wits end to what the cause is. Here is what my query looks like:
Quote:
SELECT Breakdowns.BreakdownDate, Breakdowns.Time, Breakdowns.Shift, Breakdowns.Downtime, Breakdowns.Equipment, Breakdowns.Conveyor, Breakdowns.Fault, Breakdowns.Stopper, Breakdowns.Gate, Breakdowns.Dolly, Breakdowns.Carrier, Breakdowns.FaultType, Breakdowns.Comments, Breakdowns.Tradesman
FROM Breakdowns
WHERE (((Breakdowns.BreakdownDate)=Date()) AND ((Breakdowns.Time) Between #00:00:00# And #6:29:00#) AND ((Breakdowns.Shift)="Night")) OR (((Breakdowns.BreakdownDate)=Date()-1) AND ((Breakdowns.Time) Between #22:30:00# And #23:59:00#) AND ((Breakdowns.Shift)="Night"));
View 14 Replies
View Related
Feb 10, 2008
Hi I've just signed up to the forum.
I'm currently working on a booking system, where clients can make appointments.
However as I am fairly new to access, I would like to know how I can inforce some sort of validation where for example if a client makes an appointment on 17/02/08, then it checks to see if there are any current appointments of theirs on the day, if there is, then an error message would occur
View 1 Replies
View Related
Jun 29, 2006
I have a medical database (Access 2003) containing information about patients. There is 1 form that is used to enter the information and a check box to say whether the patient has cancer.
I run a simple query to select the cancer patients.
I want to be able to add a large amount of information about (only) the cancer patients through a separate form. I also want a neighbouring hospital to be able to add cancer patients.
The ideal solution (I think) is to create an extra table to contain the extra information for cancer patients, and some of the informationfrom the original table. Cancer patients need to be automatically added to the cancer table once the "cancer" box is checked.
So far I have not ben able to achieve this - a create table query does not update automatically. I have tried creating a new table with the cancer information and creating a relationship between the tables and basing the form on the cancer table and a select cancer query. This does not work either.
Please can anyone suggest a solution! Do I need another database?
Thanks
View 2 Replies
View Related
Jul 11, 2007
I am trying to sum values of duplicate data in a table using Make Table query. Can anyone help? I have attached a sample of the data. At the end of the day, the objective is to have a single line with the total of the multiple lines.
BU_Asset IDYTD Depr
US031-00000001 12,962.82
US031-00000001 142,591.07
US031-00000002 356.87
US031-00000002 1,784.36
US031-00000002 2,141.23
US031-00000003 5,344.72
US031-00000003 58,791.92
US031-00000004 37,315.37
US031-00000005 -
US031-00000006 294.13
US031-00000006 1,470.66
US031-00000007 -
US031-00000008 -
US031-00000009 562.04
US031-00000009 2,810.23
US031-00000009 3,372.26
Thank you very much.
View 1 Replies
View Related
Aug 28, 2006
Hi,
Anyone has experience for barcode. Example, if you scan the first time then it will appear total 1 and if you scan again with the same barcode, then the total will change to 2 ...etc.
Anyone has experience, please share.
Thank you
Regards,
Richard Tan
View 2 Replies
View Related
Jul 11, 2006
All,
Is it possible to filter parent records by child records (or subdata) without showing multiple instances of the parent data in the form/query?
Cheers,
Bobadopolis
View 2 Replies
View Related
Apr 28, 2006
I have inherited a 5 table database which is associated with membership details and all tables are linked via the membership number which appears in every table..eg
Main Register
membernumber, surname, forename, age etc etc etc
Home address
membernumber, address1, address2, address3, postcode
Business address
membernumber, company name, address1, address2,etc etc etc
Subscription
membernumber, grade, subscription, bank account, etc etc
Training
membernumber, college, results, etc etc
I am informed that it is bad practice to have duplicate data in multiple tables (ie. membernumber field) as updating the data item would be difficult (eg. if the members number needed to be changed for any reason). If this is the case how would I restructure the tables but maintain the link through the membernumber?
I am fairly new to Access....as you can no doubt tell.
View 4 Replies
View Related
Nov 2, 2006
Hi,
I have got a table that has following fields display on a form:
ClassNo Class Date ClassType ExecutionDate Order Quantity
The Table CLASSES has got many records which are duplicate. Mostly ClassNo is duplicated. I could have stopped duplication by restricting table not to accept duplicate values, but my requirement is such that i dont want to stop the table from accepting duplicate values. When i display the same table data on the form, all the duplicate entries are displayed.
All i need is to find a way to stop the FORM showing duplicate ClassNo Field. The Duplication restriction i wish to set only on ClassNo. If there are more than one ClassNo reptition than it should show that entry only once. For example the form looks like this:
ClassNo Class Date ClassType ExecutionDate Order Quantity
1 12-12-2006 A 12-01-2007 Ok 10
1
1
1
1
1
2 20-10-2006 A 20-11-2006 Ok 100
2
2
2
2
I wish to see the form this way:
ClassNo Class Date ClassType ExecutionDate Order Quantity
1 12-12-2006 A 12-01-2007 Ok 10
2 20-10-2006 A 20-11-2006 Ok 100
Regards,
Darno
Sorry i am posting it second time coz i got no response from any one, Hope this time some one genius might surface to help me.
View 1 Replies
View Related
Mar 23, 2005
I have a form that contains 5 fields and want to ensure that duplicate information is not entered. I am aware that when creating the form you can set Duplicate Records to No but each record has an AutoID which I assume means that even if the information is the same it will not register as a duplicate record due to the different AutoID.
There should only ever be 5 records entered per day. One for each region
I have created a Sanity Check that is displayed as a sub form that shows if the relevant information has been entered for a given day in Bookings and Backlog but this doesn't stop the user from putting in the same information twice. i.e. the User starts inputting the information gets called away and then trys to put it in again but doesn't check the sub form.
The Table in which the information is stored is called Bookings and Backlog.
The 5 fields on the form which is called Bookings and backlog are
ID (Autonumber)
Date (Short Date)
Combo26 (Text) (5 Regions:- North, South, East, West, Central)
Bookings (Number)
Backlog (Number)
I have put the following statement in AfterUpdate on the Combo26 field
If Me.Date = DLookup("[Bookings and backlog]![Date]", "[Bookings and backlog]", "[Bookings and backlog]![Date]=[Date]") And Me.Region = DLookup("[Bookings and backlog]![Region]", "[Bookings and backlog]", "[Bookings and backlog]![Region]=[Combo26]") Then
'Define Message Box
MsgBox "The Region you have chosen already has data entered for this day." & vbCrLf & vbCrLf & "Please check your records and amend were neccessary.", vbCritical, "USER INPUT ERROR"
Else
End If
However I'm not to hot with writing VBA and probably got something wrong somewhere as it doesn't seem to do anything and lets the user progress regardless. I have tried amending the above script and asked it to look at just one field but this just seems to give the message regardless of the information selected in the Combo box.
Can someone give me an indication as to what I have done wrong so I can put it right and in a language that is easy to understand so that if I have a similar issue later on I don't need to post another question.
View 3 Replies
View Related
Jul 5, 2006
Hi,
I have searched through the whole posts but could not find my query. I have a question related to prevent duplicate data entry in a form field.
I have a Form with 5 fileds on it: SNO, NAME, ROLL NO, CLASS, SECTION.
in the table design i have set the option of NO DUPLICATES on SNO(Autonumber) ROLL NO (text).
I wish to stop a user entering duplicate data in ROLL NO field before he reaches at the last field. What i meant is as soon as a user enters data in ROLL No field, it should check in table if the same data is already there. If the same data is found then it should give a message to prompt user of duplicate data, else it should continue to next field.
I have searched the whole forum but in vain. I am positive that there must be some one who holds the key to this solution.
Regards,
Darno
View 1 Replies
View Related
Dec 1, 2006
Hi all: I currently have a database with a list of clients, and it is to be accessible to others to enter information on clients and to add new ones. However, I want to make it so that the user who wants to add a client can easily search the database for that particular client so that they won't enter a duplicate.
I have created a search function on my form, but then it occurred to me: what if there are two people with the same last name's? At first, I wanted a search operation to display a list of client names, along with their respective ID numbers and possibly a link next to each that would take the user to the corresponding record.
However, I have no idea how to do this, and I'm unsure as to whether or not this is the best way to tackle the problem. Please, someone show me the way!
View 7 Replies
View Related
May 29, 2005
This mind sound like a pretty lame question to the pro's....
Is it possible to duplicate an Access database creating a new blank database - retaining only the database/table structure? I have a website content management system and I want to duplicate the database for each new site.
Also, is it possible to reset the 'auto-numbering' to zero?
Whilst I've build a reasonably advanced web-based system based on a database containing a dozen or so relational tables plus queries, I'm no expert on advanced Access functions.
Cheers!
Penny.
View 1 Replies
View Related
May 21, 2007
Hello All,
I have a database where a user imports data into a table monthly. She saves the Excel file and then clicks a button in Access to import the data. My instructions say to only click that button once, but so far, she has clicked it THREE TIMES, thus importing the data three times. How can I prevent this? Here is a copy of my import code:
Option Compare Database
Option Explicit
Public Function ImportTableData()
On Error GoTo ImportTableData_Err
Dim strPeriod1 As String
Dim strMM1 As String
Dim strYY1 As String
Dim strYYYY As String
Dim strFormName As String
Dim strMDPath As String
Dim strMDFileName As String
Dim strDCPath As String
Dim strDCFileName As String
'No Warnings
DoCmd.SetWarnings False
'Import Current Month data in a temporary table
DoCmd.OpenForm "frmEnterAgingDates", , , , acFormReadOnly, acHidden
strFormName = "Forms!frmEnterAgingDates"
strPeriod1 = Forms!frmEnterAgingDates.Period1.Value
strPeriod1 = Format(strPeriod1, "mmddyyyy")
strMM1 = Left(strPeriod1, 2)
strYY1 = Right(strPeriod1, 2)
strYYYY = Right(strPeriod1, 4)
strMDPath = "S:FinanceAccounting OperationsNational AccountsAccount Recs116405.100 and 117630.300116405.100-AR Due from PAR Plans NASCO"
strMDFileName = "MD AR Due from PAR Plans_" & strMM1 & strYY1 & ".xls"
strDCPath = "S:FinanceAccounting OperationsNational AccountsAccount Recs116405.100 and 117630.300117630.300-AR NASCO Refund Due from PAR Plans117630-2007"
strDCFileName = "DC AR Due from PAR Plans_" & strMM1 & strYY1 & ".xls"
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "tblData_temp", strMDPath & strMDFileName, True
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "tblData_temp", strDCPath & strDCFileName, True
'Append tblData_temp to tblData
DoCmd.OpenQuery "qryAppend_tblData_temp_to_tblData", acViewNormal, acEdit
'Delete any blank rows that may have resulted from the import
DoCmd.OpenQuery "qryDeleteBlankRows", acViewNormal, acEdit
'Delete the temporary table
DoCmd.OpenQuery "qryDelete_tblData_temp"
Beep
MsgBox "The file has been successfully imported", vbInformation, "Files Imported"
ImportTableData_Exit:
Exit Function
ImportTableData_Err:
MsgBox Error$
Resume ImportTableData_Exit
End Function
Thank you for your help!
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
