Filter/ Sort Query To Eliminate Duplicate Fields
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 Replies
ADVERTISEMENT
Jun 5, 2007
I know there a many posts about eliminating duplicate records because I did search, but I did not see anything that really answers my question.
I have a query in a local Access database that is using a Linked table from an SQL database managed by our corporate IT department. I have read access to only certain tables / fields in the SQL database. My query is based on 3 tables from the database and I have them linked together in my query.
When managers move from one site to another, they are given a different TeamID number. However, until they are replaced, the manager will have more than one TeamID assigned to him in the SQL database. Evidentally, the way our IT has the SQL database structured, when I query open items it duplicates the record for that manager because he has 2 TeamID numbers. So the only field that comes up differnt in the duplicate records is the manager's TeamID number.
I would like for my Select Query to ignore the duplicate record. I know I can use an Append Query to copy the data temporarily to a local table and set the proper fields as Primary Keys to do this. However, it would be nice if I did not have to go to all that trouble.
Is there a way to eliminate duplicate in my Select Query?
As always, I appreciate the help.
Jim
View 2 Replies
View Related
Oct 11, 2007
I need to create a query for an error report.
What I need is a query that will find any duplicate records from field '1' in my table.
But then I want it to check another field '2' in the same table for any records that meet criteria 'A' but also have entries with a different critiera.
Not sure if I'm explaining that right.
So say we have a table like this
field 1..........field 2
XX.................A (the criteria we want)
XX.................C (oops, somone entered another entry with a different criteria)
So it would tell us that the 'code' in field 1, we have the criteria A and C at the same time. not good.
I hope that makes sense.
View 14 Replies
View Related
Sep 15, 2006
Hi all,
This is an intersting question that my friend ask me... hope someone can help to solve it.
In a combo box i use to list out all the company in a table that with the duplicate of the company is allowed. Do someone have a good idea to filter the combo box when detect a duplicate data and just showing one of each company only? or can i create a query with just listing one of each company from the table with all those duplicate company data?
Thank you.
Regard,
alex
View 2 Replies
View Related
Jun 7, 2013
I've got a hopefully an easy question - how to create a form filter? I've got a form with a subform. Form is showing all records from a table, but some records on the subform are blank. I want to create a filter which will eliminate any record from the form where there is nothing to show in the subform.
View 8 Replies
View Related
Apr 24, 2014
I have a form that shows multiple rows of linked/child records.The form in question is the "frmFilterNumberTypeView" form. When a particular filter is used (installed on an automobile), I would like to be able to click on the corresponding "Installed 1" button left of the filter number, so that by code, I can reduce the Qty on Hand by One, and insert the corresponding filter ID to the History table, along with the date/time the filter was used.
how to reduce the Qty on Hand, nor how to do the updates to the History table, I'll figure that out myself over the next week or two (hopefully it won't take that long). What I'm trying to figure out here is how I can associate the red "Installed 1" button with the filter to the right of whichever button I press.
how to sort the filter column on this same form. I'm fairly certain that this would be very easy to do if the subform in question was populated by means of a corresponding query, but I'm afraid that if I go that route that I'll spend another 20+ hours trying to figure out how to get the proper filter records to match the filter manufacturer, not to mention having to next figure out how to link the table so that if I modify any particular filter record, that I'll be able to have the associated table update accordingly.
Is it possible to sort the filter column without the subform record source being a query?
View 2 Replies
View Related
Feb 27, 2015
I am using Access 2013 and have created a music inventory database.I have a question about formatting some text in a table. One of my tables has the following fields:
tblArtist
Artist ID
DVD Number
Artist Last Name
Artist First Name
Artist Full Name
In my form, I want the artist's name to appear like this:
Mozart, Wolfgang
Dylan, Bob
Beatles
Aerosmith
I know how to format the table to do this, and I know how to construct the form for this. To format the name, in the tblArtist table, I use the following expression in the Artist Full Name field:[Artist Last Name] & ", " & [Artist First Name]
If an artist has only one name, such as Aerosmith, I enter it in the Artist Last Name column. Artists such as The Beatles or The Rolling Stones, I just omit "The."When a one-name artist's full name appears in the form, there is a comma after their name.
Is there a simple way to eliminate the comma if there is no text in the Artist First Name column? If so, what is the simplest, easiest, most practical novice-friendly way to do this?
View 1 Replies
View Related
Nov 10, 2013
I'm getting back into Access after retiring and about 10 years of isolation (from Access). I have 2010 version and know that one should avoid duplicate entries and a way to do this is make those fields unique key fields. I have a Customers table and have bounced back between CusID (AutoNumber) and Compound Keys (CusFName and CusLName) as the key fields. The compound keys prevent duplicates but become very hard to work with later in code and expressions. The CusID is preferable from that standpoint, but can't prevent inadvertent entry of duplicate names.
View 2 Replies
View Related
Apr 30, 2013
Any way in report that I have 2 reference queries just 1 is to populate all details and 2nd query to filter details and will be the final reporting information???
View 5 Replies
View Related
Aug 4, 2005
I have some very simple code on a button on a form to sorts my data by company name. But as some of my data is for friends, I want to be able to add a line in that looks at the "Category" field and only includes those that are a "business". Can I do this.....?? So some sort of filter.
Private Sub Command43_Click()
On Error GoTo Err_Command43_Click
Me.OrderBy = "CompanyName"
Me.OrderByOn = True
Exit_Command43_Click:
Exit Sub
Err_Command43_Click:
MsgBox Err.Description
Resume Exit_Command43_Click
End Sub
View 2 Replies
View Related
May 4, 2005
I am using a handheld scanner to scan in barcodes. However the part numbers contain more data than I need. Is there a way to get ride of the extra data I scan in in Access.
Say I have part ABCD- 7G675 .. I only need 7g675 to be in the field. Is there a way to do this besides a find replace, that would take way to long.
View 3 Replies
View Related
Jan 10, 2008
Hi all,
I have the following issue:
In my job we work with several raw data .txt files exported from Oracle ERP system. These data include information about: inventory, sales, backorders, purchase orders, forecasts, product line.
My goal is to put information from all of these imported txt files together to create an easy to use snapshot file.
The common field between al of these files is the item description. I have an issue where records are repeated for several fields every time the same item description is showed. For example for the same product description the january sales forecast QTY is repeated on several rows because there are several orders for this product description. Is there a way to make forecast QTY appear only once but keep the multiple orders and their information?
Example of current result
http://img178.imageshack.us/img178/5659/currentsu7.th.png (http://img178.imageshack.us/my.php?image=currentsu7.png)
to achieve result
http://img179.imageshack.us/img179/5100/toachievefk4.th.png (http://img179.imageshack.us/my.php?image=toachievefk4.png)
thank you for any comments
View 4 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
Sep 24, 2013
I am using Microsoft Access 2010. It provides great filtering and sorting options in the ribbon for any object. But what if I hide the ribbon and want to create custom buttons on the form to do the filtering and sorting job? There are some filter options available in macros but are not quite like the ribbon's own Filter button. When the Filter button is clicked from the Robbin, a filter menu pops-up under the active field, which doesn't happen when I try to do it using Macro functions like "Apply Filter" or "Set Filter" etc. I want to have that big "FILTER" button from the Ribbon on my form.
View 2 Replies
View Related
Dec 27, 2013
I use the following xml to create a ribbon for Sort and Filter
Code:
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="true">
<tabs>
<tab idMso="TabCreate" visible="false" />
<tab id="dbSearchtab" label="Sort and Filter" visible="true">
<group id="SearchScreenSort" label="Sort and Filter">
[code]....
The first problem is that this ribbon appears in forms where it is not included.The second problem is that in addition to the "File and Filter" tab, there appears a "File" tab Whe you click on it is goes backstage allowing you to Print and Exit.
View 2 Replies
View Related
May 2, 2015
I'm using a dropdown that has the months of the year in it. The user selects a month and the form is then filtered by a date if its the first of whatever month is selected. It works fine but when the filter is applied I cant select the little arrow on the column headings and select the boxes (Select All, Blank, ETC...). It doesn't work on any column, just the apply text filter works but I cant select anything.
It works fine when I clear out the filters on the form. I tried to use vba to set the menusetting property to true and that didn't work either.
View 1 Replies
View Related
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 1 Replies
View Related
Jan 11, 2006
I am terrible with databases, but have designed a database that uses two text field types and 48 Yes/No check boxes. The two text fields describe documents (name and location), and all the check boxes indicate what types of documents they are.
What I need to do is to be able to run queries or reports where I can select CERTAIN of the Yes/No field types. For example, some of those check box fields are named 1A, 1B, 1C, 2A, 2B, 3A, 3B, 3C, 3D, 3E, 3F, 3G, (up through 8E) etc.
How can I get the query or report to bring up all of the records that have a check mark in either 1A, 1B, "or" 1C plus list the Field 1 and Field 2 that are text fields? I don't want it to list every record in the database, which is what is happening, because I want to then be able to sort the results.
I know it USED to be in my head how to do this, but I just can't seem to get it to work!
Thanks,
Toni
View 3 Replies
View Related
Nov 27, 2007
Quick questions and I'm sure this is easy enough to warrant a quick response:
I have a query.
I have 12 different fields that may or may not have the value "Sabbatical".
I want to view only the records that have the value "Sabbatical" in AT LEAST one of those 12 fields.
Thanks in advance.
View 6 Replies
View Related
Feb 3, 2015
I have a table with two numberfields (let's call it NumbersToFilter and FilteringField).
I want a query that shows me ALL NumbersToFilter data WHERE FilteringField is the highest.
Eg. if this is my table:
NumberstoFilter / FilteringField
999 / 50
947 / 44
920 / 50
941 / 48
I want my query to return 999 and 920 (filteringfield max = 50)
I'm currently using an aggregate query, using group by NumberstoFilter and max FilteringField, but it returns all the fields (the maximum is simply on top )
View 2 Replies
View Related
Mar 6, 2015
I'm trying to sort and filter a continuous form. Sort ascending and filters works perfectly fine. But my descending button doesn't work. I basically have a combo box which contains a field list. Then two buttons (asc. and desc.), then a text box for filter, a button to filter, and another button to reset filter. Here's my code:
Code:
Private Sub cmdAscending_Click()
If IsNull(Me.cboField) Then
MsgBox "Please choose a field.", vbOKOnly, "No field to sort."
Else
Me.OrderBy = Me.cboField
Me.OrderByOn = True
[Code] ....
View 8 Replies
View Related
May 27, 2005
Hey,
Is it posible to eliminate an append query or update, delete querey message. So that users wouldnt have to click "yes" in a msgbox that show?
THX
View 3 Replies
View Related
Dec 28, 2007
I have a query with a calculated field as follows: Age: (Now()-[DOB])/365
Even though I have set the property sheet to '0' decimal places, the results are showing about nine decimal places. Is there a way I can get it to just show the age in years without all the decimal places? I'm pretty new to DB and have seen a couple of postings, but couldn't understand how to apply them to this simple situation.
Many thanks
Mohani
View 3 Replies
View Related
Oct 10, 2005
I trying to change empty values in a query/Table to zeros so that I can use them in calculations.
I try to use the Nz function and the IsNull function but both give me the same result: "Compile Error" when I try to switch from design view to datasheet view.
This is what I put in a field: 'Expr1: nz([cancelamount],0)'
(IsNull has he same effect).
I went to multiple sites and this is how they explain the function should look like.
My question is:
do I have to enable something to use this function?
Am I typing it in wrong?
(using access 2002)
View 5 Replies
View Related
Nov 18, 2014
I have a table, tblDailyCalls, that contains agent_name, date, calls_ answered, and talk_time. Ideally on a form, the user will select an agent, enter the date range in txtStartDate and txtEndDate and a report opens to show what the total amount of calls and talk time is for that date range.
All I've managed so far is doing a simple expression on the report itself to sum the fields I want. But my method returns every date in the range. I would like to only display the total.
I've been trying with Totals in the query and crosstab queries but am not familiar with them.
View 4 Replies
View Related
Oct 20, 2014
The recordsource is a query with over 6,000 records. The form currently lists the records in datasheet format with header and footer for things like buttons and filtering. The client wants to be able to go from page to page of the souce query, showing 100 records on the form at a time.
But at the same time, they should be able to filter or sort the data source in it's entirety. The person who created the form came up with what seems like an awful solution to the problem. It seems to use a random number generator to determine how many records to portray at a time. I see this in the code as well as in operation, because the number of records on page to page varies. It doesn't even start out at 100! Worse yet, using a sort on the page only sorts the records that are visible.
View 9 Replies
View Related
