CODEWITHSUNDEEP
c#sqlvb.netms-accessms-access-2010
Wolfish
Wolfish

Reputation: 970

Resolving multiple records where calculations are involved

I have a table for invoices where the occasional slip-up has led to two invoices being created for certain records. This means I have ~2000 invoice records made up of two rows. I need to merge these duplicate records

Each row has the following information amongst others:

I've drafted some pseudo-code already, but I'm not very good at VB, so it's in C#:

foreach Row record in TableName
  { 
    if (record1.TYPE == "priorfee", record2.TYPE == "priorfee")
      {
         Row newRecord = new Row;
         newRecord.LINK = record1.LINK;
         newRecord.TYPE = record1.TYPE;

         newRecord.VAT = (record1.VAT + record2.VAT);
         newRecord.ExVAT = (record1.ExVAT + record2.ExVAT);

         record1 = Null;
         record2 = Null;
      }
    else if (record1.TYPE == "subsequentfee", record2.TYPE == "subsequentfee")
      {
         Row newRecord = new Row;
         newRecord.LINK = record1.LINK;
         newRecord.TYPE = record1.TYPE;

         newRecord.VAT = (record1.VAT + record2.VAT);
         newRecord.ExVAT = (record1.ExVAT + record2.ExVAT);

         record1 = Null;
         record2 = Null;
      }
   }

Now, I need to get this into a working VB script, or figure out a way to do it as an SQL script (I don't mind doing them in two rounds, one per type).

Also, which is more advisable?

Upvotes: 0

Views: 114

Answers (1)

zaitsman
zaitsman

Reputation: 9499

Ok, I wrote my first Access query :P

Based on your requirements, this will SUM ALL invoice VATs and ExVATs, take the first LINK and TYPE, based on duplicate LINK and TYPE and INSERT new rows to your db.

Note: this will NOT delete the rows operated on. this will also NOT affect the rows that have no duplicates.

INSERT INTO TableName ( TYPE, LINK, VAT, ExVAT )
SELECT TableName.TYPE, TableName.LINK, Sum(TableName.VAT) AS VAT, Sum(TableName.ExVAT) AS ExVAT
FROM TableName
WHERE (((TableName.TYPE)='subsequentfee'))
GROUP BY TableName.TYPE, TableName.LINK
HAVING (((Count(TableName.LINK))>1));

To switch between priorfee and subsequentfee, just update the string variable (or copy paste the query twice)

Upvotes: 1

Related Questions