Queries :: Filtering Out Similar Records
Jun 20, 2013
I have a query that shows member check-ins to a fitness facility. Customer scans their membership card and it logs their customer id, scan date/time (date and time are one field), name, etc. Sometimes the customer will scan their card multiple times if they've left and come back, but I only want to count their first scan. I have a second query that runs off of this one and counts visits by hour for a date range.
So my question is, how do I filter out subsequent scans on each day in a query and how do a I do that when running the query for multiple days? So in the multi-day query I want to count each day they came, but only the first scan of each day? How to do this in the query design grid...
View Replies
ADVERTISEMENT
Nov 21, 2013
I have a query that carries all the properties in our database, and data for when our company 'worked on' properties. Each property falls within a certain province and municipality (this is slightly irrelevant info) , we worked on properties in a period from 2009 - 2013/06/30 this period is called '2009', and we have again worked on properties during a period 2013/07/01 onward - this period is called '2013'.
Now I require this query to display all the properties, but only the entries in certain fields, that relate to '2013'.
I have about 7 fields where this date (either 2009 or 2013) can be displayed, I tried to filter them all simultaneously by using the criteria: Is Null Or "2013", in every one of these fields, but this criteria results in me losing entire records that contained 2009 data, instead of the data simply not being displayed. Even in cases where some of the 'date' fields contained 2013.
How I can keep all records but simply display info relating to work we did in the 2013 period?
View 8 Replies
View Related
Oct 28, 2005
This is a tough one. I am developing golf management software. There is a table of caddies. And there is a table of members. The Caddies carry two bags each one for each member. They are assigned their jobs through an assignment form. Therefore, a single caddie will carry for Member1 and Member 2.
Now I need to produce a report of how many times each member plays. So how can I query the assignment table where Member 1 and Member 2 fields exist so that I can have a single field of allMembers so that I can count their rounds in a report.
Thanks.....Jeff
View 2 Replies
View Related
Jul 31, 2006
I have a table of ~165k records and need to delete records that are similar (2 or more records have the same lastname, firstname, dob). These records are not duplicates as other field values in these records are different. I've tried several sql scripts and Access methodologies but can't get valid results.
Thanks,
Phil....
View 1 Replies
View Related
Nov 13, 2006
I have a query that accesses data from 6 tables. The query displays 6 records where as there are 7 records that have to be displayed. The one record it doesnt display is similar to other records. I dont know why its not being displayed. Kindly help.
View 2 Replies
View Related
Jan 31, 2008
This may be a simplistic question but I'm having some diffuculty figuring it out. I have multiple rows of data where I need to use the "Unique Values" property in my query to limit only what I need. However, I have a "time" field and their are similar times on different records. So say I have a 100 records but 8 of them have the same "time" value, how can I use someting like the "Unique Values" property to get my rows down from several hundred to 100, but not lose the 8 similar rows and end up with 92 rows, when I have to turn around and sum these times and need all the times to be there?
Maybe I'm approaching it the wrong way and should be doing a SUM of "times" to begin with for that field, but I'm not being able to get that to work either. Any suggestions? /Thanks
View 6 Replies
View Related
Mar 3, 2015
I am creating a simple inventory db. I have a form that we enter into the table inventory. Right now we enter in everything once as inventory, then again to out to coating or ship to customer. I’m looking for the code to create a button enter the record twice but make two fields change.
So in short terms I want the form to enter a record with all the data as displayed. Then create another record with all the same data but the quantity would need to change to a negative and the Status would change to “Coating”.
I know this code won't work but this is what I want to do.
Private Sub Command78_Click()
.AddNew
!Employee = Me!Combo68
!Material = Me!Combo48
!Length = Me!Combo29
!Caliber = Me!Combo31
[code].....
View 9 Replies
View Related
Jan 14, 2014
Using Access 2010. Have been using a simple query to count similar items. All of a sudden I cannot get the sum of the count. I don't know if I have done something wrong or my program won't work correctly.
The DB is attached. The only query shown is an example of what I want to total, but I cannot get any total.
View 7 Replies
View Related
Apr 22, 2006
Hi guys,
need your help again with the following problem i'm having. I'll give an example to make the problem clear.
I have a table, eg table 1 with the following records:
Tag________Desc________Msgno____DateTime
016GB001___Alarm OFF___1403______21/4/06 11:02:02
016GB001___Alarm OFF___1605______21/4/06 11:02:02
034GB005___Alarm ON____1403______21/4/06 11:02:04
016GB001___Alarm ON____1403______21/4/06 11:02:07
016GB001___Alarm ON____1605______21/4/06 11:02:07
048GB001___Alarm OFF___1403______21/4/06 11:05:31
048GB001___Alarm OFF___1605______21/4/06 11:05:32
048GB001___Alarm ON____1403______21/4/06 11:06:51
048GB001___Alarm ON____1605______21/4/06 11:06:52
I need a query that looks at records with similar Tag fields that occur with a 1 second difference between them and deletes those with "1403" in the Msgno field. I'm a newbie and i've got no idea where to start. The query should return the following records:
I have a table, eg table 1 with the following records:
Tag________Desc________Msgno____DateTime
016GB001___Alarm OFF___1605______21/4/06 11:02:02
034GB005___Alarm ON____1403______21/4/06 11:02:04
016GB001___Alarm ON____1605______21/4/06 11:02:07
048GB001___Alarm OFF___1605______21/4/06 11:05:32
048GB001___Alarm ON____1605______21/4/06 11:06:52
I hope the problem is clear. I know this is going to be one heck of a challenge, and i'm not sure whether this can actually be done in access!! Anyhelp from anybody would be enormously appreciated. Hope to see a solution soon!!
Kind regards,
Monty
View 1 Replies
View Related
May 20, 2014
I am trying to write some code to search for similar strings. I am creating a database with records that all contain street addresses. These addresses may have more than one record attached to it, and we would like for folders to be created containing the records with similar street addresses. Problem is, all the existing records are from an excel spreadsheet that did not contain any data validation, so there are several instances of:
123 Street
123 st
123 street job 1
123 st job 2
etc....
So I am trying to write code to prevent this from happening in the future, by searching the database for a similar street address and asking the user if this is the address they are trying to enter. I have been trying to do this with the DLookUp function, as such:
Private Sub ProjectName_AfterUpdate()
Dim stLink, pName As String
pName = Me.ProjectName
stLink = DLookup("[ProjectName]", "tblMaster", "[ProjectName] LIKE '" & pName & "%'")
If IsNull(stLink) Or stLink = "" Then
[Code] ....
I have worded the stLink line different ways, and have used (*) instead of (%) but nothing is working. The CODE is working, as in no errors, but it is not finding a similar project that I know is present.
View 14 Replies
View Related
Apr 21, 2014
I have been looking around for a while now to learn how to show in a subform similar records from the same table, and a way to link them together.
I work for a mental health organization and we have a call log database that we create a new record every time a person calls us - this allows us to track the outcome of these calls. Over the last 3 years we have about 10% repeat callers. I am trying to find a way, when creating a new entry, to see if this person has called before, and if they have, link their past contact (record) to the new contact (new record).
For example.
Caller: John Smith (555) 555-5555 (this is the new record)
in the subform, a list would populate all the 'john smith' records with an option to link or attach them to the new record.
View 8 Replies
View Related
Jun 9, 2014
Is there a way to merge duplicate/similar Access 2010 records into one record?
I have an Access table with 1,000 duplicate records, although they are similar and not exact duplicates. As you can see below, some records contain information that other records do not. Yet, the primary key is the same for all duplicate records. I want to find a way to merge data from filled cells of duplicate records into empty cells for each duplicate record. I do not want to concatenate the data (i.e. combine last and first name, etc.). I only want to fill empty cells if there is a match for it in a duplicate record. I will delete the newly exact duplicate records later. Short of correcting the records by hand.
Example
Code:
LastName FirstName SSN Address Phone Email
Doe John 123-45-7891 123 Anywhere St. NULL john(at)gmail.com
Doe John 123-45-7891 NULL (123)456-7890 NULL
Desired Result
Code:
LastName FirstName SSN Address Phone Email
Doe John 123-45-7891 123 Anywhere St. (123)456-7890 john(at)gmail.com
Doe John 123-45-7891 123 Anywhere St. (123)456-7890 john(at)gmail.com
View 2 Replies
View Related
Sep 11, 2005
Good Evening All,
I want a cheep calendar that links to queries and can input data as well as display it. It has been on my wish list for 7 or 8 years.
If anyone know how to make one or knows where to buy a cheep one I would really appreciate it.
View 5 Replies
View Related
Sep 11, 2013
I'm doing a database for a friend and the challenge I have is there will be a lot of attachments going into this database, which will hit the 2gb file size limit in no time.
My solution is have each year of attachment in a separate databases and link them to the main database. The attachment are tax documents.
They table layout is all the same. It will be like this:
TaxYear, ClientID, 1040, 1040NR, 1040X, etc.....
TaxYear will be whatever year the database is designated and ClientID is the client for that particular year. The same client can shows up in multiple years.
I have thought of inserting each table in a form, but when a new year come, the form will need to be adjusted. And it will has empty field if a client skip a year or two.
View 6 Replies
View Related
Oct 11, 2013
I need to do a query to find a field value from similar fields. Table 1 has fields(customer id,...) and table 2 has fields (customer id, address,...). I need to use customer id from table 1 to find address in table 2. Both customer id fields in both tables is the same.
View 4 Replies
View Related
May 25, 2005
Hi
I wonder if anyone can help. I attach a table that is a material list (27000 records). When I add a new material I want to be able to see the last part code I created for a particular supplier.
For example C001, C002 and C003 are for a supplier company called Chapters so I would scroll through the combo box search list in the form for the last one and create a new record called C004. The problem is that E00001, E00002 for a company called Edwards supplies 20,000 items so it's a long way to scroll through to create a new part code called E20001. How can I show in the form what the last part code is for each Supplier?
I attach the form and table as I'm not very good at explaining this, although I have had to delete approx 20000 records as the database was too big to be posted. Any advice/help would be greatly appreciated, many thanks
View 4 Replies
View Related
Mar 20, 2006
I have a database to record drawings received. On the form there is a combo box to select discipline (Civil, Electrical, Mechanical) and a combol box to select the volume the drawings are in (Volume 1 - foundations, Volume 2 etc). The disciplines are stored in tblDisciplines with a relationship between this and the main tbldocument. I also have a table , tblLocation which has the volume ref and description. However when I click on the location field it shows me all volumes for all disciplines. What I want is if the first field selected is Civil only the civil volumes are displayed.
Can any one point me in the reight direction
View 3 Replies
View Related
Nov 12, 2007
Hi - I am working on a database where i need to update a table with a "UniquePersonID"
I have a master table which stores:
UniquePersonID
Surname
Forename
DOB
SEX
Postcode
This table is approx 600k records, now the problem i have is that i have further tables which contain Surname, Forename, DOB, Sex and Postcode and need to resolve these with the "UniquePersonID", unfortunatley in some cases names, DOB's may have been spelt slightly differently, (i.e. "Abbas" should have been "Abbass" or the DOB has been entered with a typo)
I have managed to do an exact match and update the majority of records but i am still left with just over 16,000 records to match manually... I have created a form which allows me to tick which boxes to search against to resolve record by record, but estimates suggest that this will take around 12 solid days work :(
Is there a way of matching similar records and coming up with a probability score that anyone out there is aware of???
Many thanks
Ian
View 2 Replies
View Related
Apr 7, 2008
Hi all
I have a rather simple problem that I do not have the time to solve. I have a time series taken at 30 second intervals over 5 months and the records total over 250,000. I only want data from every 15 minutes, so only one out of 30 records. How do I filter the table to extract only the records I need?
Nick
View 1 Replies
View Related
Mar 14, 2008
Hi there,
I'd like to filter out so that the name only appear once base on LastOfDate.
Sample data in query
Customer_____Date__________Product
John_________02/02/2008_____A
John_________05/03/2007_____B
John_________03/06/2006_____A
Nick_________04/04/2006_____A
Nick_________04/04/2007_____B
James_______05/04/2008______A
So in the query, I just want to have John, Nick and James appear once with the LastOfDate.
The result should be
John_________02/02/2008_____A
Nick_________04/04/2007_____B
James_______05/04/2008______A
I included the db file which only perform LastOfDate and unique name, however since John has product A and B, it won't get rid of B.
SELECT DISTINCT query1.Name, Last(query1.Date) AS LastOfDate, query1.Product
FROM query1
GROUP BY query1.Name, query1.Product;
I just want one Name, LastOfDate and one product, regardless of A or B.
Thanks,
Isabel
View 1 Replies
View Related
Dec 18, 2011
how I can modify this code to make it filter results in a combo box by what the user types in to search for.
Private Sub txtSearch_AfterUpdate()
With Me.RecordsetClone
.FindFirst "[FirstName]=""" & Me.txtSearch & """ OR [Lastname]=""" & Me.txtSearch & """"
If .NoMatch Then
Beep
[code]....
View 1 Replies
View Related
Feb 19, 2007
I have a database that I have secured by using Access 2003 user security. I need for the database to allow the user to login and once they have logged, to open a form and filter out only their open "tickets" - records.
How can I do this?
Thanks in advance.
View 3 Replies
View Related
Nov 28, 2007
Dear Experts,
I have created a database with queries that is working fine, however there are many records that have the same information that can not be removed easily, is there a method anyone knows of that can filter the records produced from a query to remove duplicates? i.e. 'select distinct' but for the whole query after the search parameters have been entered.
e.g. the search query is producing, after the refined search values are entered:
414353p - Fiat - 1.6 - MLS
414353p - Fiat - 1.6 - MLS
How can i then apply a filter to automatically reduce these duplicates to just one entry for the whole form?
Thanks
View 6 Replies
View Related
Jan 19, 2007
I have a database with 2 tables. Table1:drawing number and drawing title and Table2: date, action, status. Table2 gives the complete history of our internal review process.
Example:
Number=001, Title=Drawing001
date=01/01/07, action=drawing received, status=
date=02/01/07, action=drawing checked, status=approved
date=03/01/07, action=approval sheet issued.
Number=002, Title=Drawing002
date=04/01/07, action=drawing received, status=
date=05/01/07, action=drawing checked, status=not approved
date=06/01/07, action=approval sheet issued.
I need a report which lists just the 'not approved' drawings. I've been able to add a query which lists the drawings but I loose the complete history, i.e. drawing recieved date.
Is there anyway I can display all the fields but only for those drawings which are 'not approved'
View 4 Replies
View Related
Feb 18, 2006
Hi, I need help on my search command. I am hoping to be able to use a combo box or a list box coming from a table as my criteria to use to filter records from a form and present it a subform/subreport upon clicking the command button. Ideally I should have a form wherein I will have a either a combo box or list box for my criteria, a subform/subreport, and a command button. When I select a particular item on the combo box or list box and I click the command button, the subform/subreport would show me records matching only the particular criteria I selected.
I tried using several approach but it's not working, I don't know what I'm doing wrong. Please help me, I am just learning how to do this all by myself.
First Approach:
I tried using a list box to list all the countries I have available from the country table and a command button so when I select a country from the list box and click on the command button I will be able to show on a datasheet view only records matching the country criteria.
This is the code I used:
__________________________________________________ _______________
Private Sub Preview_Click()
DoCmd.OpenForm "qrysumcountry subform", , "Country", "Country = [List4]"
End Sub
__________________________________________________ _______________
But everytime I click on the command button Preview, I am always asked to enter parmeter value then when I type the country that's when it shows the record in forms format matching the criteria country but when I dont type anything and click ok, it just shows a blank form and indicates it's filtered but no record is showing. But I click cancel, it shows a Run-time error '2501'. why does it still have to make me type the parameter if I have selected it on the list box already?
Second Approach:
On the form: I used a combo box, a subform/subreport and a command button. On the combo box I have to show different countries available on my country table. on the subform/subreport I have used my a form created from a query. I want to select from the combo box a particular country and used it as my criteria to filter the records I have on my subform when I click on the search command button. I tried following the sample given by gromit but it doesn't want to work on my database.
This is the code I followed:
__________________________________________________ _______________
Private Sub btnClear_Click()
Dim intIndex As Integer
Me.cmbCountry = 0
End Sub
Private Sub btnsearch_Click()
Me.frmqrybyCountry1.Form.RecordSource = "SELECT * FROM qrybycountry" & BuildFilter
Me.frmqrybyCountry1.Requery
End Sub
Private Function BuildFilter() As Variant
Dim varWhere As Variant
varWhere = Null ' Main filter
'Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere
' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If
BuildFilter = varWhere
End Function
__________________________________________________ _______________
After selecting on the combo box and click on the command button it just shows all record. It doesn't seem like it is reading what I selected from the combo box as my criteria to filter the records. What could be I be doing wrong? Honestly, I dont understand what is happening to the code here especially the BuildFilter function.
Please help me, I would really want to figure this problem out. Thank you so much.
View 2 Replies
View Related
Jul 19, 2006
1 table contains the records I needs to be queried.
The result Im looking for is to view all the clients that have a different advisor assigned to a duplicated ssn. - not same advisors assigned to a duplicated ssn.
Example of desired outcome.
ssn name advisor plan
111223333 joe smith john doe b
111223333 joe smith jane sony c
111223333 joe smith unknown a
I am getting all data.
ssn name advisor plan
111223330 Jack Jack john doe b
111223330 Jack Jack john doe c
111223330 Jack Jack john doe a
Ive tried query with using 'first' for the ssn and the advisor still give me all data.
Please help me....
Thanks
View 3 Replies
View Related
