Removing Duplicates While Keeping A Running Total
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 Complete Forum Thread with Replies
Related Forum Messages:
Removing Duplicates From A Query
I am running a query to return records from a table. I want to eliminate records from the query if the values contained in one field are duplicated. I have tried using the DISTINCT keyword but it only works if the entire record is unique or if I was retieving just one field Any suggestions appreciated
View Replies !
Removing Duplicates From A Table
Hi, I'm running MS Access (2002 SP-2) I have a table that has the following columns in it: Sent NBR (text) / Sent Date (date/time) / PIC (text) It's a customer listing of our customers and which long distance carrier they have selected. Unfortunately my data also shows OLD customers that no longer own that phone number. Because of this, various phone numbers are listed numerous times, but only the Sent NBR with the most current Sent Date is the one I need to see. The others are irrelevant and need to be deleted. I'd manually delete the duplicated Rows but I have over 115,000 duplicates!! Do you guy know of a way to eliminated the duplicates for each Sent NBR and only show me the Sent NBR's with the Most current date? Any help would be greatly appreciated! Scott
View Replies !
This Maybe Of Use....removing Duplicates From Memo Field
I got this from a fellow in Switzerland. It removes the duplicates in table SalesNotes and puts them in the table nodupeNotes. The two tables are replicas and it appends all the records that are not duplicated in the memo field. Private Sub go_click() Dim dabs As DATABASE Dim recI As Recordset Dim recO As Recordset Dim strI As String Dim strO As String Dim strSQL As String Dim isDupe As Boolean strSQL = "DELETE * FROM nodupeNotes;" DoCmd.SetWarnings False DoCmd.RunSQL strSQL DoCmd.SetWarnings True Set dabs = CurrentDb Set recI = dabs.OpenRecordset("SalesNotes") Set recO = dabs.OpenRecordset("nodupeNotes") With recI recO.AddNew recO!OneOffMerge = !OneOffMerge recO!NameNumber = !NameNumber 'use a similar syntax for any other fields of interest recO.UPDATE .MoveNext Do While Not .EOF strI = !OneOffMerge recO.MoveFirst isDupe = False Do While Not recO.EOF strO = recO!OneOffMerge If strO = strI Then isDupe = True End If recO.MoveNext Loop If Not isDupe Then recO.AddNew recO!OneOffMerge = !OneOffMerge recO!NameNumber = !NameNumber 'use a similar syntax for any other fields of interest recO.UPDATE End If .MoveNext Loop End With MsgBox "nodupeReasons contains no duplicates" End Sub
View Replies !
Keeping Focus On Main Form While Queries Are Running
I'm sure there is a simple solution - but everything I have tried hasn't worked. I have a command button on my main form that opens up a popup form. On the popup form the user makes various selections and clicks on another command button(on the popup form). That button is linked to a macro - that runs several queries. The problem I am having is when the macro starts to run - the main form is maximized again (so now it doesn't take up the full screen - just a part of the screen) and some of the queries start flashing in front of the form. I know how to maximize the form once the popup form closes - that's not a problem. I just cant figure out how to keep the focus on the main form and in front of all the queries as they are running.
View Replies !
Running Balance As Opposed To Running Total
Can anyone tell me how to get a running balance on a report. I know how to create a running total, by setting the "running sum" property of a text box to "Over all". I can't however see how I can adapt this to give a running balance (as in a bank statement for example). Attempts to do so end up in failure!! Many thanks in advance. Peter
View Replies !
Access 97 - Query Help - Newbie - Removing Duplicates
Hi, just finding my way in Access 97 with no real support. I have made a query that creates duplicate records when I only need to see one for a certain criteria (lets say criteria X). This is due to the data feed involved in making the query where criteria X is found in numerous records. What I want to display is only one record per entry containing an exact match for criteria X. Finding it hard to articulate this but I would need something that defines: If criteria X is the same, just display one record. Hope this is somehow clear as I am struggling a bit. Would be gratefull of some help. Thanks
View Replies !
Need A Way To Total Duplicates
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 Replies !
Running Total
Ahhhh this is doing my nugget in!!! I have a simple table with 4 fields ID (unique number) DATE (date) CAPACITY (number of SKU we can hold) ORDERS (number of SKU on order) the data looks like this ID DATE CAPACITY ORDERS 1 01/01/2007 250000 250000 2 02/01/2007 250000 300000 3 03/01/2007 250000 300000 4 04/01/2007 250000 300000 So looking at the above table we can see that we have more orders than capacity in our factory, however they require to see this in graph form, so what I need is for each ID a running total of the CAPACITY and ORDERS so over a given date range i would produce a graph to find the "pinch points" where we could see if the capacity is less than the orders we have over time. so my new table would be: ID DATE CAPACITY ORDERS CAPRUN ORDRUN 1 01/01/2007 250000 250000 250000 250000 2 02/01/2007 250000 300000 500000 550000 3 03/01/2007 250000 300000 750000 850000 4 04/01/2007 250000 300000 1000000 1150000 etc. which i would create my graph from. Ive looked at Dsum and some other methods but cant get my head around it so any help will be much appreciated. Thanks Steve.
View Replies !
Running Total
Hi I'm trying to create a database to keep track of invoices . on work that was done.is there any sample database that I could take a look at.Or can anyone help me on this I'm trying to capture price on parts + price on labor = total the order form in the tradewinds database looks good but don't know where the code is for calulations? can anyone help me out? Thanks Tom
View Replies !
Running Total
Im having a problem doing a running total on my form. I want the result to be displayed in a text box, with the figures being collected from a column of figures. Any help is appreciated
View Replies !
How To Maintain A Running Total
My friends, please help me figure this out. I am new to MS-ACCESS. I am trying to create a simple Leave system for my office. When a user requests a leave, the number of hours will be added to a table. I have created a form for this purpose. What I would then like to happen is, the next field in the Table is the sum of hours requested thus far. So that field would be Requested + Total requested Thus far. I can do it easily in Excel but I can't figure out how to do that in ACCESS. It is a very simple database and I can mail you my sample if you are interested. Thanks in advance. yallah. aliyallah@yahoo.com
View Replies !
Running Total In Query
Does anyon ehave any experience of running totals in an access query. I'm reporting the data through excel not access reports so need a query not a report solution.. I have a table which looks: RegionCategoryTypeDesc Period_IDPeriod_YTDPeriodTotal CanadaEventsWSOP Team67Budget15000 CanadaEventsWSOP Team78Budget0 CanadaEventsWSOP Team89Budget0 CanadaEventsWSOP Team910Budget0 CanadaEventsWSOP Team1011Budget0 CanadaEventsWSOP Team1112Budget0 CanadaEventsWSOP Team1213Budget0 CanadaEventsTOTALAll12Budget15000 CanadaEventsTOTALAll23Budget15000 CanadaEventsTOTALAll34Budget15000 CanadaEventsTOTALAll45Budget15000 CanadaEventsTOTALAll56Budget15000 What I would like is to have an additional column which keeps a monthly summary of spend based on running total month 1to 12. All items have months 1 - 12 and are ordered in that fashion. Any helpo really appreciated. Simon
View Replies !
Percentage And Running Total
Hi guys I am making a query that calculates how much costs i have per job. I'd like to create a function in the query that can calculate how big a percentage each job is. (need total for every job/ total for all jobs) but so far i haven't been succesful in this. Anyone who could help me? Cheers, Takstein
View Replies !
Trouble Getting A Running Total
I have a table called Team Standing that includes [TEAM] [PLAYER] [GAME DATE] [POINTS]. From this table I have a query that provides for each game date and team a count of the players, [GAME DATE] [TEAM] [PLAYER COUNT]. [PLAYER COUNT] is calculated using the total COUNT . What I would like to add is a cumulative running total over the game dates. For example. [GAME DATE] [TEAM] [PLAYER COUNT] [TOTAL PLAYER COUNT] 2007/01/05 --- 1 ------- 11 -------------- 11 2007/01/12 --- 1 ------- 8 -------------- 19 2007/01/19 --- 1 ------- 14 --------------- 33 I am having trouble figuring out how to get a cumulative running total. I have tried a number of different sub-queries and selects but I can’t seem to get something that works. Any help or ideas would be greatly appreciated. Thanks
View Replies !
Running Total Query
Hi, Am trying to create a query for a chart where I can total the employees over time but am having real trouble creating a running total from the "Total" field within a query but cannot seem to get it at all. TotalStartDateLeftDate RunningTotal 126/03/1957 121/03/1971 127/02/1986 115/02/1988 207/03/1988 007/03/198831/05/2007 Here is my current SQL query: SELECT Sum([CountOfStartDate]-[CountOfLeftDate]) AS Total, Atest1.StartDate, Atest1.LeftDate, Sum([CountOfStartDate]-[CountOfLeftDate]) AS RunningTotal FROM Atest1 GROUP BY Atest1.StartDate, Atest1.LeftDate; Can anyone help please?
View Replies !
Running Total Per Employee
I've been tasked to create a report that shows the date an employee hit a loss of $200.00 or greater for the company. Each day they work, they will either have an overage or a shortage in their till fund. They start out each day with a set amount of money in their till and at the end of the day they are expected to turn in that same amount; the tills are used for making change. If they are short, and the amount is fairly large, hence the $200.00 mark, then it becomes an issue that needs investigating. I need to total these amounts up and when the total hits a negative $200.00 or greater, I need to display the date it occurred and then display the amount the company would have saved if the employee had been either terminated or transferred to a non-revenue department. Getting the "saved" amount should not be a problem once I figure out how to get the above indicated loss amount. Any ideas? Kevin
View Replies !
Running Total For Grants
Hello, I am new so if I am posting in the wrong board, please feel free to move this. My question is as follows: I have several tables set up to track the grants my office adminsters. These include Project Information, and Payments. I am attempting to keep a running total (in each funding source) that shows up in the Payments form. I am not sure how to build the query for this. Example: Grant A has the following funding: $20,000 -Capital Projects FY04 $50,000-Federal Funds FY06 The following payments have been made (each payment would be a seperate record: $1,000- Cap FY04 $2,000- Fed FY06 $4,000-Cap FY04 $8,000-Fed FY06 Suggestions? PS-I am really not that great with VB so if you could explain it as if I were 5 that would be great
View Replies !
Query With A Running Total
This should be easy! Right? I have a series of dates with events that occured on those dates. Some events were extended, others were not how do I get a running total, cumulative total, for all records in the RunTotal column? Opened DateOpen IssuesCountOfExtendedNotExtRunTotal 5/21/2007 1 10 1 8/6/2007 1 10 2 10/8/2007 1 10 3 11/1/2007 1 10 4 11/8/2007 1 01 5 12/5/2007 1 0 1 6 Thanks for your help.
View Replies !
Running Total In Forms
I have a form that calculates number of hours each month. So a little box for Jan, Feb, etc. At the bottom I have another field that sums it up. Well Im using just basic + to add the fields and it only works where there is something in each box. I can't go through and put zeros in for everyone since a 0 and a blank mean two different things. I tried Dsum and just now after searching the forum, I tried nz. Am I overlooking a real nice function that would assume a blank entry in the sum is a zero? or perhaps some trick I could try? Also, is there anyway to store the calculated total in the associated table, so I can query it later? It already has its own specific field that up until now was hand calculated and entered manually...like the monthly hours. Thanks in advance
View Replies !
Running Total Question???
I have a form that has a "Quanity" field on it as well as a "# Times Printed" field. I'd like to have the "# Times Printed" be a running total of the "Quantity" field where everytime a "Quantity" is entered, it updates the "# Times Printed" I've played around with a few idea's but haven't had much luck. Thanks Scott :)
View Replies !
Running Time Total
I am trying to put together this report where I can see how many hours the printers were running in a month's time. The sum property for this is working up until it reaches 24 hours then it starts all over again. How can I get it to keep adding hours when it gets to 24? For example: 20:35 + 6:25 = 3:00 learnasugo
View Replies !
Running Balance Or Total ???
I need HELP PLEASE! I have created a table from importing the data from an Excel spreadsheet. Then I created a simple query using the wizard and adding all the fields that the table had into the new query. Here is the problem... I created a couple of expressions using the expression builder for a couple of the fields and I am stuck on how to create an expression for a running balance column. For example: I have a QTY column (not calculated), a Price 1 column (not calculated), another Price 2 column (calculated with an "If" expression used to look-up a code in one column and if it meets the criteria then it would return a different price than what is in the Price1 column, and a Subtotal column. I need a Balance column (which would start with, for example, $10.00 and then deduct the subtotal amount. QTY Price1 Price2 Subtotal Balance - - - - $10.00 1 1.00 - 1.00 $9.00 2 1.00 2.00 $7.00 1 - 2.00 2.00 $5.00 I need very detailed, but very simple directions and fast. I have tried DSum (doesn't work) and every expression I could think of. I need this done in the query I started or in a new query. I don't have Access on this computer, so please sent directions versus a sample database. Please Help...
View Replies !
Capturing Variable Monthly Fee In A Running Total
I have to impose a single monthly fee on a billing report for each month that transpires and capture this fee as a running total on this report, which will eventually span the course of years. Understanding that this rate is variable, what is the best way to accurately capture this information?
View Replies !
Creating A Running Total Calculated Field
I have a query, and I want a field that shows the current balance as of that transaction, eg:__________________________________________________ _______|__Transaction ID__|__Type_______|__Amount__|__Balance__||__1______________|__Deposit_____|__£10______|__£1 0_______||__2______________|__Deposit_____|__£30______|__£4 0_______||__3______________|__Withdrawal__|__£15______|__£2 5_______|So far, I have this:SELECT [Transaction ID], [Type], [Amount], (SELECT Sum([Amount]) FROM tblTransactions AS tblTransactions2 WHERE tblTransactions2.[Transaction ID] <= tblTransactions.[Transaction ID]) AS BalanceFROM tblTransactionsWHERE [Account ID]=1;Although this does not look at the 'Type' field; it just adds the amounts; regardless of it being a deposit or withdrawal. I'm really not sure how to add this.Thanks in advance.
View Replies !
Need A Running Total And When 2000 Is Reached I Need It Set Back To 0
here is the info needed table name is employee time and fields are employee name, date, total hours. i need a running total until 2000 hours are reached and then i need it reset to 0. also any hours over the 2000 need to be added to the 0 in other words if on a wednesday the employee reaches 2006 then i need to start at 6 instead of 0 otherwise if it is exactly 2000 i need it go to 0. can anyone please help. thanks and GOD bless, Mark Lane
View Replies !
|
TRACKER
