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
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.
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.
I have a Contacts subform and would like to be able to double click to fill addresses, and details from the previous row...every person in our database even those living at the same address needs thier own record.. and since we only need to fill up to 5 records and review them, double clicking on the first name to fill up the next row with the previous addres phone number ect would be grand.
I am assuming we could fill out the new Full Name and double click on it to get the previous record filled to the new one. Don't really want to work with a query unless needed, but I have a little vba understanding.. how do I code this to make it work?
Full_Name_Dbl_Click
getPreviousRecord.Address(-1)
getPreviousRecord.Phone Number(-1)
getPreviousRecord.City(-1)
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.
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.
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!
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
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
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
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?
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
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.
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 RelatedThis 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
I have a material usage table that tracks material going into products...Oftentimes the same material will be used and multiple records will contain that material.
I want my query to return only records with unique material used. I'm clicking the "Unique Values" and "Unique Records" property setting but its not working.
I have a query with over 85,000 lines of data where each row has a unique id but the unique id appears multiple times within the table. For example, there is a unique numeric id tide to various types of pets across four columns. So the id number 1 could appear in four different rows each with different pets. Please see the first attachment "Original Table"
I want to be able to create a table that has one for for each unique id with all of the corresponding pet data. Please see the attached "Desired Table".
Attachment 300829
I have a query with over 85,000 lines of data where each row has a unique id but the unique id appears multiple times within the table. For example, there is a unique numeric id tide to various types of pets across four columns. So the id number 1 could appear in four different rows each with different pets. See the first attachment "Original Table"
I want to be able to create a table that has one for for each unique id with all of the corresponding pet data. See the attached "Desired Table".
I've used this guide [URL] .... to only show unique values in a combo box. I can't get it to work, it just shows a load of blank values. If I run the expression builder part from the background, it works but it doesn't work in the combo box. Not sure what I can post to give more details?
View 13 Replies View RelatedI am currently using the below code to query values that have a unique "OverrideDescription" and where by the "Upload Date" is always the latest.
SELECT t1.*
FROM [DaisyServiceRates-Amended] AS t1 LEFT JOIN [DaisyServiceRates-Amended] AS t2 ON (t1.OverrideDescription = t2.OverrideDescription) AND (t1.[Upload Date] < t2.[Upload Date])
WHERE t2.OverrideDescription IS NULL;
However I also need to be able to do a unique count of the [OverrideDescription] field.
I have found methods whereby I can do use, but they all use a GROUP BY function, the issue being that when I use this method it prevents be from updating the values saying "recordset not updateable".
Any method that does not use the GROUP function. I did also try preforming the COUNT on a separate table and doing as JOIN, but this also prevented updates to the date.
Here's my dilemma: I have a table that keeps track of job postings and how many people applied for each posting. So, for instance, I can have 20 people apply for a job posting say....on July 18. On another column I have the shift name for that posting such as DAY Shift. So, I have 20 people that have applied for the Day Shift on July 18.
What I'd like my combobox to show me in just one instance of July 18 in column 1 and one instance of Day Shift in column two. I have tried using the wizard and then going to the properties of the combobox and attempted to edit the SQL statement to use the DISTINCT value to no avail.
I have built a database that is fed from tables provided from outside computer systems, so the architecture isn't exactly how I would design it.
I am trying to use a combo box on a form to quickly jump to a specific department. I have about 130 personnel that are assigned to 10 different departments. I have to use a subform to update the position info vs. the personal info.
The combo box works, but I can't get it to display each department once. It shows the department 130 times for each employee instead of 10 unique departments.
if I got a 4 x 4 matrix table - 4 rows and 4 columns - MS Access 2007/2010 the values should exist as below with no repetition of any number in any of the cells.
1 2 3 4
5 6 7 8
9 10 11 12
13 14 15 16
a number should not repeat in any of the cells.I set a primary key on cloumn 1 and defined unique on all the fileds but that doesn't work since 1 columns does not know what the other cell contains and no relationship exists.
I have a set of devices that are assigned to a truck, on occastion they are moved from truck to truck, I need to be able to track where they are AND where they have been. Here is what I have:
ID (autonumber)
GPS ID (number, this is the specific ID to each device)
Truck Number (this is the specific ID for each truck
Installation Date (Date installed / moved)
I have created a query to show the data I need, I have put them in order based on date.
- I must keep each event stored on the table
Goal:
To show a list of the current location of each device, nothing more.
I have the following code:
Code:
Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT DISTINCT tbl_contract_lev.ContractID FROM (tbl_leverancier_gegevens INNER JOIN
[Code] ....
The result of the query is a list fo unique values but by including the "count" function in SQL seems not te be working in combination with "DISTINCT"...
I have duplicate data in a cell, I want to hide duplicate data and display only non-duplicate data.. I changed the property sheet to only show unique values, but it keeps showing data I don't want to see...
View 3 Replies View Related