Unique Values On Similar But Different Records
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 Replies
ADVERTISEMENT
Jan 13, 2006
Here's my situation...
I'm making a query that uses information on company members from a database. The most important data field is the MemberID. Many members are listed in the table more than once because different records are put in when they have more than one contact person or health plan, etc.... However, right now I am trying to have the query make a table that produces only one record for each company so I can get a total number of companies. Therefore, I dont want the query to produce records with duplicates of the MemberID. My problem is that by setting Unique Values to 'Yes' doesn't work because if a different field in the record is different, it makes the record unique. I can't figure out a way to make it so only one field is considered for unique values. The funny thing is that it is simple to do this in Excel, you just gotta go to advanced filters. Does anybody know how to do this in Access?? Thanks.
View 14 Replies
View Related
Apr 5, 2006
Can someone please help with the following:
I am trying to join two tables in a query. One of the tables has only the fullname while another has only the surname (this table is imported from an external database). I need to be able to match the two tables up. Can anyone please suggest how to do this or even if this is possible.
Thanks
View 6 Replies
View Related
Jul 29, 2015
I am having an issue with duplicates in my table. I have a table, called "Part Mods", that has about 12 fields in it. the first 2 of them are "Mod" and "Part Number". I have a form where someone can insert data into this table and what i want is for them to NOT be able to insert a record with the same Mod/Part Number combination of an already existing record. I have "indexed" turned on but I am pretty sure that is for every field.
View 3 Replies
View Related
Oct 24, 2011
I am looking for the way to represent data with the similar column value to one row.A query retrieves 3 records AS IS, but I need one record.
Table:
Name / 1 year / 2 year /3 year
AS IS
Name 1 year 2 year 3 year
1 John Smith A
2 John Smith B
3 John Smith A+
=================================
TO BE
Name 1 year 2 year 3 year
1 John Smith A A+ B
View 4 Replies
View Related
Dec 5, 2014
I have the following dataset in a table called NR_PVO_120. How do i pick out a number (which can change but let's say, 6) of UNIQUE OtherIDs without excluding any OtherIDs under any fax numbers?
So, if you pick OtherID from Row7 you then also must pick OtherIDs from rows 8 and 9 because they have the same fax number. Basically, once you pick an OtherID you're then obligated to pick all OtherIDs that have the same fax number as the one you picked.
If the number requested (6 for this example) isn't possible then "the closest number possible but not exceeding" would be the rule.
For example, if you take OtherIDs from rows 1-10 you will get 6 unique OtherIDs but row 10 shares a fax with rows 11 and 12. You either need to take all 3 (but that will raise the unique count to 8, which isn't acceptable) or skip this OtherID and find one with a fax that has no other OtherIDs and that isn't on the result set already. My result of 6 UNIQUE OtherIDs will need to contain ALL OtherIDs under any fax the existing OtherIDs are connected to.
So one solution is to take rows 1-6, 26. Another is to take rows 1-4,10-14.
There will be many possibilities (the real dataset has tens of thousands of rows and the number of people requested will be around 10K), as long all OtherIDs connected to all faxes on the result set are part of the requested number (6 in this case) any combination would do.
A few notes.
1.Getting as close as possible to the requested number is a requirement.
2.Some OtherIDs will have a blank fax, they should only be included as a last resort (not enough OtherIDs for the requested number).
my table (NR_PVO_120)
Row OtherID Fax
1 11098554 2063504752
2 56200936 2080906666
3 11098554 7182160901
4 25138850 7182160901
5 56148974 7182232046
6 56530104 7182234134
[code]....
A few sample outputs
one solution is taking rows 1-6 and 26.
OtherID
11098554
56200936
25138850
56148974
56530104
56148975
Another solution is taking rows 1-4 and 10-14.
OtherID
11098554
56200936
25138850
56024315
56115247
56148974
This is for a fax campaign, we need to make sure no fax number is faxed twice, that all people connected to that fax number are contacted under one fax sent.
View 12 Replies
View Related
Feb 7, 2014
i have one table in which ID is Primary ID with Different Values
Like
ID NAME PAN
1 A X
1 B Y
1 A X
2 C Z
2 C G
3 D U
it shows that ID 1 having 2 Name (A& B,with PAN, X & Y ,respectively).how can i get this that ID having More than 1 Value like 1 and how can i select only these records ID which having more than 1 value and how can i update values for 1 ID.
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
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 2 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
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
Mar 1, 2007
I have a query that has an Item, ItemPrice, Allocation, and DatePurchased ordered by DatePurchased with only unique values.
I'm using it for a PO Database, when entering items purchased, I have the Item field populated with a items previously purchased, when the item is selected the Unit Price and Allocation Fields are autofilled in with the most recent values from the PO details table.
My problem is if I purchase 10 of the same Items on 10 different dates then the combo box will show 10 of the same Items, because they are not unique values because of the DatePurchased.
What I want is a query to show one unique Item entry with the most recent Purchase Price. I can get a list of unique Items, by only making a query of those and showing only unique values, but as soon as the date is introduced it no longer works, for obvious reasons.
Any help appreciated,
JOe K.
View 1 Replies
View Related
Jan 15, 2008
I have a database to track client activity per session.
Each client has a unique client number but multiple activities.
Therefore, if I have 20 clients in the database and each has 3 activities, I have 60 client numbers, 3 *20 = 60. ( Duplicated)
My elementary problem is how do I display the client activity unduplicated so that my query returns only 1 unique client number per session.
I tried the unique records/values in the property box but still.....
Thanks for your help.
View 1 Replies
View Related
Oct 4, 2006
Hi,
I have a situation and i do not know how to handle it.
I am developing an application for a lawyer. In the data base i have the following tables: Files, the people table (that are involved in a lawsuit), and other tables. The problem is the following: in the people table, beside the primary key i have another unique element that is social security number.
In the files table beside the primary key another unique element exists: the number and date of the file (File 1 from 15.02.2006). A lawsuit file can have more law terms: today it has one law term and maybe next week another law term so i must have the same file many times in the table with the same code and another date, but i cannot add the same file many times in the data base because a file has data about one person and the social security number of the person is unique.
If anyone can offer me a solution it would be good.
Thank you!
View 2 Replies
View Related
Oct 25, 2006
Hi guys,
I've tried searching all the threads for an answer to my problem and I can't seem to find it....
Ok I know this should be simple...
I know to use Distinct and DistinctRow to only show unique records in a combobox but all I want to do is that once a selection is chosen in the combobox to add a record to a subform, it can't be selected again.
I know in some cases you do, e.g. items on an invoice sometimes need to be entered more than once, but I'm listing Skills for Employees and I only want a user to enter a Skill once per Employee (although Employees can have many Skills, I just don't want a User to accidentally put the same Skill in twice or more per Employee...)...
What's the best optimized way of achieving this?
Sorry for my newbiness.
dazza61
View 5 Replies
View Related
Oct 25, 2006
Hi guys,
I've tried searching all the threads for an answer to my problem and I can't seem to find it....
Ok I know this should be simple...
I know to use Distinct and DistinctRow to only show unique records in a combobox but all I want to do is that once a selection is chosen in the combobox to add a record to a subform, it can't be selected again.
I know in some cases you do, e.g. items on an invoice sometimes need to be entered more than once, but I'm listing Skills for Employees and I only want a user to enter a Skill once per Employee (although Employees can have many Skills, I just don't want a User to accidentally put the same Skill in twice or more per Employee...)...
What's the best optimized way of achieving this?
Sorry for my newbiness.
dazza61
View 1 Replies
View Related
Feb 17, 2005
I have the following SQL definition:
SELECT DISTINCT tblFile_Management_Received.Claim_Number, tblFile_Management_Received.Date_Checked_In, tblFile_Management_Received.Checked_In_By, tblFile_Management_Received.Copy_or_Original
FROM tblFile_Management_Received;
The results would show as:
ClaimNumber Date Checked In Checked In By Original or Copy
111111111 2/16/2005 8:34:24 AM XXXX Yes
111111111 2/16/2005 1:23:19 PM XXXX Yes
222222222 2/16/2005 8:34:52 AM XXXX Yes
222222222 2/16/2005 1:24:49 PM XXXX Yes
I am trying to use "Distinct" to show the following records when the query is ran (the desired results would be each unique claim number based on the earliest check in date):
ClaimNumber Date Checked In Checked In By Original or Copy
111111111 2/16/2005 8:34:24 AM XXXX Yes
222222222 2/16/2005 8:34:52 AM XXXX Yes
Can anyone help me with this??
Any help is greatly appreciated.
Thanks
View 4 Replies
View Related
Mar 6, 2014
I have an file with 500.000 + lines.Each line has an ID with an amount.
For example:
ID Amount
1 2
Often the ID appears with a different amount.
I want to have a cleaned up acces file where each ID only appears once but where the amounts are add up.
For example:
ID Amount
1 2
1 4
Should be in the new file
ID Amount
1 6
View 1 Replies
View Related
Jul 29, 2015
I would like to sum up choices made by a user. I'm not sure whether I should create a new table and a couple of fields (not including primary key) with Fruit & Fruit Value.
Where: Apple (Y or N)=4 or 0, Oranges (Y/N)=3/0, Bananas (Y/N)=2/0, etc. As the user chooses the fruit, the value rises.
For example, if the user selected Yes for APPLE and ORANGES the sum is 7-- or if the user selected ORANGES and BANANAS the sum is 5, etc.
Is it possible?
View 1 Replies
View Related
Nov 29, 2005
I don't know if I'm going about this problem correctly. In a nutshell:
I have a table which contains a number of fields, only a two of which are important for this task, as indicated below.
| ID | Type |
Where ID is an alpha-numeric identifier (say a store number), and there are three potential types, plus Null values. For this, we'll assume the three types are Fruit, Vegetable, and Grain (plus Null).
An example dataset might look like:
0-4 Vegetable
1-2
0-1 Grain
1-2 Fruit
0-4 Vegetable
1-2 Vegetable
0-1
0-2 Fruit
0-4 Grain
1-2 Fruit
0-4
0-1 Vegetable
0-3 Fruit
If I want to know how many stores have Vegetable, I could put "Vegetable" as the criteria in the query and set UniqueValues to Yes. Then I would get three unique values, one for each Vegetable existing at a unique store.
But, what if I want to know at which stores Fruit exists and Vegetable DOES NOT. Is there a way within Access to find all the records with "Fruit" as the criteria and then eliminate those with ID numbers matching the dataset with "Vegetable" as the criteria?
In other words, if I do my "Vegetable" criteria I end up with the resulting dataset:
0-4 Vegetable
1-2 Vegetable
0-1 Vegetable
If I then use "Fruit" as my criteria, I get the following dataset:
1-2 Fruit
0-2 Fruit
0-3 Fruit
Since Store 1-2 has Vegetable, I want to eliminate it from the list so that my resultant list is:
0-2 Fruit
0-3 Fruit
Is that possible?
Sorry for the long bizarre example, but I thought it the easiest way to make the situation clear. I'd really appreciate any suggestions anyone has on dealing with this situation!!
Thanks
View 2 Replies
View Related
May 11, 2007
Hey guys, I'm relatively new to Access and I need some help building a query. So I work with a website that handles credit card transactions, and we keep a log of activity ie. NameOnCard, Date, Status. About once a month or so, I do an audit to make sure that all the attempted transactions are successful. Sometimes the server needs to restart, or the users computer has a problem, etc. Typically, someone who's had a failed attempt will have a successful attempt minutes or seconds later, so the NameOnCard is exactly the same, but the Date and Status are different. Right now I've created a query that look for failed attempts and one that looks for successful attempts, but what i want is one that returns only people with failed attempts that don't also have a successful attempt. Everything I've thought should work hasn't. Filtering for unique values doesn't help, because each row is slightly different, and the NameOnCard isn't the primary key. I recently tried this as a column with this in the field value:Success: IIf([Online Gift Errors]![NameOnCard]=[Online Gift Successes]![NameOnCard],"Success","Failure")(NOTE:Online Gift Errors and Online Gift Successes are queries, not tables)But when I try to run the query it thinks [Online Gift Errors]![NameOnCard] is a parametric value and asks for an input. What can I do? Should I be trying something else entirely?
View 5 Replies
View Related
