 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / September 2005how can i combne duplicate records in a table by using a query
I have a table with a lot of duplicate records ie; same customer name, customer number, assigned to the same branch. How can I run a query for a given month that will show me all of my customers but when ever there is a duplacte record, it will combine them?? My data is as follows customer number, Name, service branch, service company, revenue code, amount. and date. How can I combine customer records that have identical or duplacte above info? 1. Create a query and add the table. 2. Insert the required field with whatever criteria. 3. Click the Total buttons from the Query Design toolbar. 4. Save and run.  SignatureMaha Aruppthan Pappan Nacap Asia Pacific (Thailand) Co., Ltd. > I have a table with a lot of duplicate records ie; same customer name, > customer number, assigned to the same branch. [quoted text clipped - 8 lines] > > How can I combine customer records that have identical or duplacte above info? Thank you for the reply however my query was already set as you had advised. My data is arranged as follows: Customer Number, Customer Name, Revenue Code, Servicing District, Servicing branch, date and amount. My current query pulls the above data from one talbe and lists everything by date. I would like my query to list everything however, when it finds record that have the same customer number, name, revenue code, servicing district, servicing branch and date combine those records and add their amounts togather (even if the amounts are different but everything else is the same) That way I can see: for xyz customer, for the month of april I had a total of x dollars. My current method lists xyz maybe 4 or five times in a given month when the only difference is just the amount. I hope this makes sense, and I hope you can help. Thank you for your time. > 1. Create a query and add the table. > 2. Insert the required field with whatever criteria. [quoted text clipped - 13 lines] > > > > How can I combine customer records that have identical or duplacte above info? > Thank you for the reply however my query was already set as you had advised. > [quoted text clipped - 18 lines] > only difference is just the amount. I hope this makes sense, and I hope you > can help. Thank you for your time. If I understand you correctly, you will be able to list totals OR ELSE all the records, but not both. (Actually, you could do both, by combining the results of two Queries using UNION, but I think it would make no sense for you to do that.) So here's a suggestion: [T_Transactions] is a Table containing transaction data: Customer Customer Revenue Servicing Servicing date amount Number Name Code District branch -------- -------- ------- --------- --------- ----- ------ 2 Jim 66s a c 9/13 $15.00 2 Jim 66s a c 9/13 $18.00 5 Mary xxx a c 9/10 $25.00 [Q_Transactions] is a Query defined this way: SELECT T_Transactions.[Customer Number], T_Transactions.[Customer Name], T_Transactions.[Revenue Code], T_Transactions.[Servicing District], T_Transactions.[Servicing branch], T_Transactions.date, Sum(T_Transactions.amount) AS SumOfamount FROM T_Transactions GROUP BY T_Transactions.[Customer Number], T_Transactions.[Customer Name], T_Transactions.[Revenue Code], T_Transactions.[Servicing District], T_Transactions.[Servicing branch], T_Transactions.date; and when you run [Q_Transactions] you get this: Customer Customer Revenue Servicing Servicing date SumOfamount Number Name Code District branch -------- -------- ------- --------- --------- ----- ----------- 2 Jim 66s a c 9/13 $33.00 5 Mary xxx a c 9/10 $25.00 If you want to sum all the transactions for the whole month, not just a day, then add a field to your Query that displays just the month and year and use that, instead of the date. -- Vincent Johns <vjohns@alumni.caltech.edu> Please feel free to quote anything I say here. >>1. Create a query and add the table. >>2. Insert the required field with whatever criteria. [quoted text clipped - 13 lines] >>> >>>How can I combine customer records that have identical or duplacte above info? Vincent Johns, thank you for your help. I will try it and let you knwo how it goes. I appreciate you time. > > Thank you for the reply however my query was already set as you had advised. > > [quoted text clipped - 85 lines] > >>> > >>>How can I combine customer records that have identical or duplacte above info? Sorry Newbie. It was my mistake. I missed one important issue. Like Vincent mentioned, you can use the UNION query to make this records or you can use the GROUP query too. SignatureMaha Aruppthan Pappan Nacap Asia Pacific (Thailand) Co., Ltd. > Vincent Johns, thank you for your help. I will try it and let you knwo how it > goes. I appreciate you time. [quoted text clipped - 88 lines] > > >>> > > >>>How can I combine customer records that have identical or duplacte above info? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.