Combining duplicate rows in Access without lossing unique data

[angeltink99]

I have a table that has dulicate account numbers but unique charges for each row. I need to combine them into one new table without the duplicates but with the unique data in tact.
The fields are:
Invoice #
Charge Type
Monthly Charge
Tax Charge
Usage Charge

The charge type is either AA or BB. Some invoice #s have both types of charge types, therefore creating two rows of charges for the same invoice #.

I have already split the tables into two tables...one with just the AA charge rows and one with just the BB charge rows. In the all BB table, all of the charges have been combined into a single new column called LD Charge.

Here is what I need:
In the two new tables, there are a few invoice #s that have duplicate rows with different charges. So, invoice #123 in the AA table has one row with a charge of 5 and another row with a charge of 2. I need to run a query to find and combine these possible dups without affecting invoice #s that only have one row of charges.

Then, I need to put table AA and BB together with the fields:
Invoice #
Charge Type
Monthly Charge
Tax Charge
Usage Charge
LD Charge
and New column TOTAL CHARGE (sum of all charges in row)

Can someone please help me with this? I have limited VBA knowledge, but can kinda figure out what stuff means.

Thanks!!

 

[plog]

You need to use your original table (AA and BB charges) and perform an aggregate query. It sounds like the [Invoice #] and [Charge Type] fields are what make your rows unique--if so these are the fields you use GROUP BY on. It sounds like the other fields are what you want to sum together if there are rows with the same [Invoice #] and [Charge Type] fields--if so these are the fields you SUM.

Using your original table to get the data you explained your query would be this:

SELECT [Invoice #], [Charge Type], SUM([Monthly Charge]) AS totMonthlyCharge, Sum([Tax Charge]) AS totTaxCharge, Sum([Usage Charge] AS totUsageCharge, SUM([LD Charge]) AS totLDCharge, SUM([Monthly Charge]+[Tax Charge]+[Usage Charge] +[LD Charge]) AS TOTALCHARGE
FROM YourTableNameHere
GROUP BY [Invoice #], [Charge Type];

Replace YourTableNameHere with your table's name. If this doesn't give the data you expect please post sample data from your table and the results you expect.

 

[angeltink99]

I think this will get me close to what I expect, however, the original table is not arranged correctly off the bat for me to go straight into this combining query.

The original table does not have the LD Charge column in it at all. I created the column using all the BB charge types. In the original table, the LD Charge that I created is actually contained in the Monthly Charge and Tax Charge columns...but they do not belong there.

So first, I really do need to take the data from the two split tables that I created with the data rearranged in the columns that I need them in. So, I guess I need to do an append query?? Can you walk me through how to combine data from two tables? It's a skill I need to learn anyway. Say table AA has 15 rows, and Table BB has 10 rows. How do I combine the two tables so that I get table AABB with 25 rows.
The fields in table AA are:
Invoice #
Charge Type
Monthly Charge
Tax Charge
Usage Charge
The fields in table BB are:
Invoice #
LD Charge

I don't know how to write the query so that I get all 25 individual rows from the 2 tables...obviously with a lot of blank fields, because the only field that will be filled in each row of table AABB would be the Invoice # field.

After this, I'd have all my data in the correct columns, with all my dups still present. Then I can run the query as you instructed above I think. Can you help me out with this one?

Thanks!!

 

[angeltink99]

Okay, never mind. A union query solved this portion of my problem. I now have all the data in one query in the right columns with all the duplicates. Now I am off to try the query from the reply by plog in order to pull it all together and simplify with totals. I will be back to report on my success...I hope.