Queries :: Total Excluding Duplicates?
Aug 30, 2013
I have a table with the following fields:
Customer
Month
Qty
Unit Price
Extended Price
I want to know, by month, what the total extended price is for all customers. But some customers appear more than once in a month. So I also want to know the total extended price for the month, but I only want to include the extended price for each customer once, using only the record with the highest unit price (or extended price, qty for each customer is consistent), and ignoring additional records with the same customer in that month.
I am an excel user, and I could quickly do this with pivot tables... but I have too many records for Excel to handle this efficiently (it just keeps crashing!), so I thought I'd move to access... but I don't know how to use access! So I may need a little hand-holding here.
View Replies
ADVERTISEMENT
May 17, 2013
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
Dec 2, 2004
What expression do i use to count the number of patientID when there are duplicates (ie patient has come back for more than one visit but I just want to know the number of patients who visited (not how many times))
Count([patientID]) doesn't exclude duplicates
View 3 Replies
View Related
Aug 9, 2006
How can I run a query on the table attached to show the number of UR's that were operated on. As you can see some of the UR have more than two procedure dates so I want to exclude duplicates. i have tried "count" but that just gives a "1" value for each UR on each procedure date
View 1 Replies
View Related
Nov 14, 2007
Dear Friends,
I have one query based on code, rate, quantity. I created field for total (rate x quantity). What expression i can write that the query will calculate total only with the first figure as per code and if code repeats again, should not calculation total.
Please see the following query structure. Thats how i want my total to be calculated.
Code, Rate, Quantity, total
50,100,10,1000
50,100,10
60,200,20,4000
60,200,20
60,200,20
70,200,30,6000
Thanks
mithan
View 7 Replies
View Related
Mar 9, 2006
Hello,
I imported an excel worksheet (~20,000 entries) into Access, and I am
trying to extract some information. First I wanted to find duplicates,
and rank by duplicate appearance. I have gotten that part.
In my db, I have a column 'Qty' that specifies the number of times the
event occurred. At the moment, I only have the number of times the
event appeared in the db. I would like to factor in the number of times
now.
An example of my db would look like:
ID(access) Event Qty
1 A 10
2 B 100
3 C 30
4 A 60
5 A 50
So in my initial query, I get
Name:Query1
CountofID Event
3 A
1 B
1 C
However, to take this further, I want to be able to total the Qty field for each event. So, in this case, A actually needs to be 120, B needs to be 100, C needs to be
30.
My idea is to make a second query based off the first query, where one
item would be the Event# from Query1 (because that cut out all the
duplicates), and the second would be the all the number of occurences
of that event added up.
I did some searching before posting and this thread seems somewhat similar to what I need, but he is looking for a moderately different solution.
http://www.access-programmers.co.uk/forums/showthread.php?t=103526
Any help is appreciated, Cheers.
View 1 Replies
View Related
May 10, 2005
High everyone I've had a look around the FAQ's and other threads but haven't been able to find what I'm looking for.
I have a table that contains two fields, one being 'part number' and the other being 'quantity'. Unfortunately there are multiple duplicate 'part numbers'. I am looking at keeping only one instance of the duplicate 'part numbers' but at the same time I want to add the 'quantities' together to keep a running total.
For example:
Before
Part Number Quantity
a1 1
a2 2
a3 2
a1 1
a2 1
a1 4
a1 1
After
Part Number Quantity
a1 7
a2 3
a3 2
Any help would be greatly appreciated.
Cheers
View 5 Replies
View Related
Aug 22, 2012
I have a database consisting of two tables. One is "articles" and the other is "tasks". To put it simply, I would like to find how much time the article spends in tasks, but one article can have many tasks, and they often (but not always) overlap. Tasks have a start and end date field.
View 8 Replies
View Related
Jul 23, 2015
I am trying to create a query that has a self referencing running total based on the values (point totals) of itself (running total of values in the running total column that have already been calculated for all previous records) plus the total of new points being added in the current record, less the total of points being removed in the current record. This running total can never go below 0, if it does, the running total should restart at zero and add in only new points and begin the process again with the next records
I am able to do this in Excel in less than two seconds so I know there has to be a way to port this into a query. I've attached an excel example of what I am exactly trying to do
If it takes multiple queries to complete the required output I am ok with it. In my previous outtakes I have had up to 8 queries but just couldn't seem to do it..
View 9 Replies
View Related
Mar 8, 2008
Hi,
1) I am pretty newbie to this access programming, do forgive me if my questions sounds stupid.
2) Basically I create an application in access capturing or production information for my company. now the top management suddenly wanted whats their main concern:- Total Daily/Monthly, Quarterly, Annual Sales (By Model If possible)
3) I start with daily (Lets don't be too overly ambitious).
4) I try to let user select dates from my calender control and reflect daily sales (in Total & By Model break down) insert into my form.
5) Understand someone told me from my previous post in Calender control I can achieve it either through forms or queries, which is a better way. (in terms of flexibility to change for program maintenance/ scalibility) wise ?
PS: Please forgive my ignorance :o:(
Thanks (In advance) & God Bless.
View 2 Replies
View Related
Jun 8, 2006
Hi all, I know this is a real easy one, but I am not the smartest when it comes to access. Can you help me out.
I am running a crosstab query to count and sum records in my database. I have a fied called "Amount". In my form the user is not always required to enter an amount. When I run the query, I would like the results to exclude the records that have a null value or $0.00 in the "Amount" field.
How do I format the query to exclude those records?
Thanks in advance.
View 3 Replies
View Related
May 3, 2013
All using access 2010. I have a query1, query2 and query3. Query1 is my master. Query2 and Query3 was created based on different criteria but derived from the Query1. I now want to exclude the records from Query1 that are in Query2 and Query3. When I try to put isnull in criteria of both queries Im trying to exclude; instead of returning the remainder records in the master I get none.
View 3 Replies
View Related
May 2, 2013
I have a table of around 6000 records comprising 4 fields (A,B,C,D).
- Each field can contain numbers or Nulls.
- Each record can comprise all numbers, a mixture of numbers or Nulls, or all Nulls.
I'd like to build a query that excludes all records that contain any number from a small list of numbers.
This sounds very simple but I am having problems when trying to include records that have Null's in my query output.
For test purposes I tried to exclude all records that contain the numbers 1 or 9 (these numbers can be present in any field).
This works perfectly, in isolation, on Field A (i.e. 1 or 9 but not Null are excluded from field A):
Code:
WHERE Table.A Not In (1,9) OR Table1.A Is Null;
When I try to copy the above, referencing fields B-D, I run into problems - no matter how I try to alter the Boolean operators.
View 2 Replies
View Related
Jul 23, 2015
I want to run a query on a table that holds all speed information for our trucks imported from a third party. Some of the speed alerts in that table are not correct so we set up a second table managed by the users to enter a speed exception. So if we know that Main St in Dallas TX generates false alerts for speeding we know not to call the driver, the third party db speed limit is not up to date.
So I want my query to pull all the speed data from tblSpeedData, except leave out the records where the street and zip are listed in the tblSpeedExceptions.
View 3 Replies
View Related
Sep 12, 2014
I am trying to write a query that will check all records in a table but exclude the 10 newest records in the table.
The table is from a stock program i have wrote for the company i work for (i am a novice access user). what ive been asked to do is write a duplicate order system that will flag up if the order has already been packed.
the table logs the [OrderID] with each item [barcode] scanned out with a barcode scanner what i want is a query that checks the OrderID for a duplicate entry in the entire table but because the OrderID is entered with every item scanned i want to ommit the last 10 records as prety much no order has more than 10 items i understand this may not be 100% fool proof but it is close enough.
The other option is to have it ommit all records within the last 15 minutes there is also a [Time] and [PackDate] Field which im guessing could be used for this the time field records Now() and the PackDate records Date(). After searching the web i cant seem to find anyway to ommit the last 'n' records and the few things i have found with the Date("m",-15,Date()) doesnt seem to work
View 7 Replies
View Related
May 6, 2014
Is there a way, in a query or via the use of a module, to calculate the workdays between two dates excluding holidays without needing to maintain a separate table with the holidays listed out?
For example, I would like it to calculate 21 work days for the month of May. However, I don't want to have to go in the first of each year and manually list out all the holidays for the year. Is there a way for the Holidays table to just contain the number of holidays in a given month (i.e. in December we get 3 holidays (Christmas Eve, Christmas Day, and New Years Eve).
View 5 Replies
View Related
Aug 6, 2015
I have a search form with 12 fields. In my query I use
Code:
Like "*" & [Forms]![CustomerRetestDatabaseSearch]![RetestLocation] & "*" Or Is Null
for each field on the search form.
I get the results I expect, it finds all records that match the criteria. Even if some of the fields in a record are null.
But if the query finds a record that matches one field I enter criteria into, and nulls for the other fields I enter criteria into it displays the record. I want to show exact matches. (If what I entered is null... don't show the record).
The reason I have "Or Is Null" is to include the records for the fields I left blank on the form.
Search Form with Criteria.PNG
Search Query.jpg
Search Results With Missing Entered Criteria(Dont Want These Records Included).jpg
View 2 Replies
View Related
Feb 10, 2014
I need to delete duplicate rows (or create new table without them) but the duplicate is not the entire row of data.
Date Time LeagueMatchingId League HomeMatchingId HomeName AwayMatchingId AwayName
HomeScore AwayScore HomeRedCardCount AwayRedCardCount FTOU FTOverOdds FTUnderOdds
20121202 10/02/2014 20:34:02 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.88 -0.98
20121202 10/02/2014 20:34:02 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.88 -0.98
[Code] .....
In this example I am only interested in the first and last rows, since the ones in between have the same data in the last few columns. They are not true duplicates since the time stamp for each is different.
Currently thinking I need to compare each and every row to the prior row in VBA and delete if criteria match.
View 4 Replies
View Related
Apr 2, 2014
I need to create a query that will pull duplicate names out of my db.
I would like it to pull all names that have:
duplicate LastName and duplicates of the first 3 letters of the FirstName.
For example, if I had the names:
Bland, Abe
Brown, Abe
Brown, Bill
Buster, Jon
Buster, Jonathon
I would like my query to return only Buster, Jon and Buster, Jonathon.
View 4 Replies
View Related
Oct 23, 2013
I'm looking for a way to grab values from one table that match a criteria, and append them/update them to another, but only grab one value once. Example:
Part ID ---- Profile Profile ID ---- Locations
Part1 - Profile 1 Profile 1 ---- Location 1
Part2 - Profile 1 Profile 1 ---- Location 2
Part3 - Profile 1 Profile 1 ---- Location 3
I have a database of parts that fit in certain profile sizes. Each profile size has multiple open locations. I need a query to grab a location, assign it to a part, grab a new location, assign it to the next part, etc, without assigning duplicate locations to different parts.
Right now, I'm doing this by using the First/Last aggregate functions, marking them as used, and looping it again and again until all have unique locations. Is there a better way to do this?
View 8 Replies
View Related
Jun 4, 2015
I want to be able to find duplicate numbers in the "claim #" field that exceed 20 within the "date of service" field for a month. I have a date box which allows me to select data from only one month at a time or all. What I'd like to be able to do is group the results by month. Currently it is only matching numbers by day not month.
SELECT HMR.[Claim #], Count(HMR.[Org #]) AS Total, HMR.[Date of Service], Count(HMR.[Date of Service]) AS [CountOfDate of Service]
FROM HMR
GROUP BY HMR.[Claim #], HMR.[Date of Service]
[Code] ......
View 14 Replies
View Related
Apr 5, 2013
I have the following columns in my table 'Catalogue'.
Contract_contractitem
contract
Supplier name
Item number
Short description
Long description
I need to identify duplicates if the short desciption is the same, long desciption is the same but the item number is NOT the same.
e.g.
4700000551_978, 4700000551, FISHER SCIENTIFIC BIOTECH LINE ApS, mbp-6014, 0,2 ml PCR Tube, Hotstart, wax bead, NS, 0,2 ml PCR Tube, Hotstart, wax bead, NS 96 tubes/pack, 10 packs/Cs
4700000551_977, 4700000551, FISHER SCIENTIFIC BIOTECH LINE ApS, mbp-6010, 0,2 ml PCR Tube, Hotstart, wax bead, NS, 0,2 ml PCR Tube, Hotstart, wax bead, NS 96 tubes/pack, 10 packs/Cs
View 4 Replies
View Related
Nov 4, 2013
I bounced into a big problem with Access 2010 where I cannot seem to be able to remove duplicates from a table containing millions of entries midway through a series of queries.
The table is formed like this:
Date - Serial Number - Reading 1 - Reading 2 - Reading 3
30.7.2013 - 1122334455 - 12345 - 987654 - 654321
30.7.2013 - 1122334466 - 12345 - 987654 - 654321
29.7.2013 - 1122334455 - 12210 - 987654 - 643210
29.7.2013 - 1122334455 - 12210 - 987654 - 643210
29.7.2013 - 1122334466 - 12344 - 986013 - 453213
As you can see, there are some rows which contain exact duplicates and some are a bit different. I wish to remove those fields with exact duplicates and only the duplicated fields. Running "Find duplicates" in Access gives me about 250,000 rows with such data.
I've tried a few options already:
- I cannot use the date as primary key as there are several serial numbers. I cannot use serial numbers as primary key, because there are several dates. Using reading value as primary isn't an option either.
- Microsoft says I should mark all duplicate values with an x and then make a delete query to get rid of all the x-marked rows. For 250,000 duplicates, that's a bit too much manual 'x-ing'.
- If I do a delete query using the Find Duplicates query as a base, it removes all 250,000 entries from that table, instead of just the 125,000 which had a duplicate elsewhere in the table.
- If I make a query which identifies duplicate data and gives me just one row for each duplicate, the delete query still deletes both entries from the original table.
- I could make a new query which would have only unique values using Totals as criteria (for instance, using First for the Date-column). However, this still leaves the duplicate values in the original table. Note that this database is already 800 MB large and new data is imported once a week, for the next decade or so. I cannot have a table get duplicates every week and leave it there.
- If I make a macro which would create the unique values table first and then deletes the old table, what happens next week when I try to import new readings? I would need to make a new macro each time I try to import new data as the table names change. Or is there a way to first run the unique numbers out, then replace the original table with the new one? With a 800MB database, this would put me dangerously close to the 2GB size limit. I wouldn't be able to use this as a part of a macro, as the database would have to do Compact & Repair each time it deletes the original table, midway through a longer series of queries.
- Having duplicates removed from the original import isn't an option either, as it comes in as a overriding excel sheet for the past 3 months, once a week.
As you can see, it's quite a pickle getting the duplicates out of the original table. This is just a small part of a very long macro, which takes about 15 minutes to complete, but due to duplicates the database is getting way too large.
View 9 Replies
View Related
Aug 1, 2013
I have a query showing duplicates, I want to hide the duplicates that have a null value in one of the rows..
Trunc_Name M_UNDERLYIN MaturityDate Duration SumOfSumOfM_VEGA Volatility
SPX P1400 DEC13 INX 21-dec-13 4,766666667 -108 812,00 0,22437
SP6652 CALL SP6652 04-okt-17 50,86666667 124 376,00 0,21
SP6652 CALL SP6652 04-okt-17 50,86666667 124 376,00
Here's an example of the a duplicate, all 4 first rows are duplicates, but the last value is null for the last duplicate. I want to hide the one the duplicate that has null value!
View 4 Replies
View Related
Oct 19, 2013
I am trying to combine two peculiar tables in Microsoft Access and have been unable to do so even after doing a lot of brainstorming and searching on the internet.
The two tables are spend and export
Spend
+-------------+--------+-------+-------+
| Country | Metal | Month | Spend |
+-------------+--------+-------+-------+
| China | Iron | Jan | 100 |
| China | Iron | Feb | 200 |
| China | Iron | March | 300 |
[code]...
View 2 Replies
View Related
Feb 12, 2008
Hi this is my first post... so hi all :)
ok what i have is a table with contact details 900k plus
there are about 90k of which are duplicates.
this is the basic feilds that are important in this case.
Id, data_source, data_recived, data_code,
what i want is to have a table with unique records (no dups in data_code)
this table will look like this...
Id, data_code, Num_dups, dup1_source, dup1_date, daysbtw_Dup1_dup2, dup2_source, dup2_date, daysbtw_Dup2_dup3 ,dup3_source, dup3_date, daysbtw_Dup3_dup4 ,dup4_source, dup4_date,
I know there is no more than 4 dups of each record.
what i want from this is a table that will give me a record of how many dups for each record then all the dates that they were added and the date between each record entry.
if anyone can help it would be great .
thanks in advance.
View 6 Replies
View Related
