Costing file issue - running out of time (1 Viewer)

Hi all,

I have an payroll general ledger file which has employee numbers and credits and debits. Due to system issues, there have been a lot (thousands) of extra rows written to the file which Finance do not want to load in to their systems. I'm trying to think of a way of deleting the unwanted rows but I'm failing miserably. I'm on leave from Tuesday so need to crack this today/tomorrow. I have attached the file. Yellow entries are the ones to keep, the others to delete. Basically, any entry for an employee that has the same values in both the credit and the debit column need to be deleted. This may be something to be achieved using VB so sorry if it's posted in the wrong place.

I've stared at this for hours now and can't see the way forward so any help much appreciated.

Cheers

Paul

You've posted an Excel file but the thread is in Access queries.
Which is it-Excel or Access? If the latter, what is the table name?

You seem to have some examples with more duplicate values in one column than the other. If the total of each don't match, do all need deleting or should just matching pairs be deleted?

Have you tried using a duplicates query in Access.

Next question is how are you going to modify your table design to prevent future duplication?

this can be done manualy. just highlight with same color on same column the ones you want to retain.
make a backup after in case it goes wrong.
filter your data and filter on color/no color.
select all visible data after filtering.
type alt-colon.
delete using delete key.

Hi both, it's an access table but I put it in excel so I could highlight the correct entries. there's over 3,800 rows in this data, so manual isn't really possible. It's the matching pairs that need deleting - they are the adjustments for the previous month. Trouble is, in many cases the matching pair (last month) values are the same as the value for this month (which is a credit only).

I don't see any reliable pattern. It looks like you can create two queries. One that selects non null values in Debit and the other that selects non null columns and credit. Join the two on Personnel number and delete the credits that are not equal to the debit but that isn't 100% since you seem to have additional cases such as 1006.. I don't know why you want to keep 265.76.

As Pat says, this is not easy to solve & I have ignored your colour coding in the Excel file. I have done the first part for you but haven't time to complete it this evening.

This isn't an elegant solution & if it was my database I could create a procedure to loop through each step in turn.

I haven't time to do that but at least the steps below mean you can monitor what going on. The main thing is make sure you don't lose any records you need so work on a BACKUP

As you didn't provide your database, I have done the following
1. Imported your Excel file into a new database - 3847 records
2. Used query qryUNIONALL to display both credit & debit amount in the same column. We need UNION ALL to ensure all records are included
3. Used qryAppendUNIONALL to add 3847 records to table tblUNIONALL.
4. Check for duplicates qryDupesUNIONALL & qryCountDupesUNIONALL
The problem is you don't always have matching pairs of values SO ...
5. Split into 2 queries - qryCountCreditDupes & qryDebitSupes
6. Use query qryUpdateMatchingDupeTagTrue to update a boolean tag field to True where the number of duplicate credit/debit records match - 1418 records
7. Use query qryDeleteTagTrue to remove those 1418 records leaving 2429

After all that you will still have dupes where there were more duplicate credits than debits or vice versa
So you need to build on the above to remove 1 pair of each
Rinse & repeat

If I just delete ALL dupes you're left with just 388 records but that's not what you said you need

Its far from perfect but hopefully it will at least get you started.
I would suggest building a procedure as mentioned before

When you've finished you NEED to redesign the structure to prevent future duplicates. I can't advise on that as I've no idea how your data is structured

One other thing - why is Personnel Number a text field?

Good luck

isladogs that sounds like a really good start, I'll work on that tomorrow first thing. The data comes from the payroll system and it's a one off issue (thankfully). The Personnel Number is a text field because many of them start with a zero, so I made it a text field. It wasn't until I imported the data that I realised that the excel file was not a csv but an xls file which had dropped the leading zero anyway!

why not just sum the values by employee, treating DR as positive and CR as negative, then split the total back out to Dr or Cr columns

You have no additional columns, so I can't see that it matters if there are currently two or more unmatched rows which get merged into one. I did a quick manual inspection and only found one (5976) - but that matches 2 debits to one credit but that is not to say there aren't others.

I called your table rw.
Q1
Q2

Hi Chris
I did start down that route but gave up on it ... rightly or wrongly

What I can tell you is there are more cases of unequal (unmatched) credit/debit dupes than the number where the counts match



v2 attached - I've added an extra query qryUnmatchedDupeCountsCreditDebit in this version and left the table ready for pruning

Hi Colin

I think the point is that you might have 1 debit and 2 credits for the same amount, you match one off and that leaves one - take employee 10016 as an example.

My suggestion does result in a single 'row per employee. By just eliminating the duplicates employee 5976 would have all 4 rows brought through



using my basis the two debits match off against one of the credits so only one credit would be brought through.

There is no data to say whether all the debits and credits relate to this month, all we have is

the matching pairs that need deleting - they are the adjustments for the previous month. Trouble is, in many cases the matching pair (last month) values are the same as the value for this month (which is a credit only).

Click to expand...

Taken literally then 4 rows should be returned - but the net result is the same - a credit of £125.92.

From an accounting perspective, given the lack of any other information, I would think it would suffice, but it is down to the OP to clarify.

I extended the query set, created a union query (Q3) (much the same as yours)


then left joined Q1 to it to identify those employees where the ttl did not match an existing amount

Q4
there are 140 of them

So if you wanted to you could then join Q4 to Q3 on personnel number to get the individual transactions.

Then SELECT DISTINCT left join Q1 to Q4 to get those where there is a match

and union these two queries together before splitting the amounts out into debits and credits. However you would still need to then manually inspect and delete matching rows from Q4 - the 140. Although chances are, like 5976 above, there would not be matches

on the attached is the sampleData table (imported from your excel file).
on Module1, run the function fncCorrectMistake (make sure to read and Follow the instruction first on this function).

it will only match exact amount of debit against credit. if the credit is broken down into two or more (say debit for Personnel Number 1 is 1000, credit should also be 1000 and not broken like 500, 500, etc.)

after running the function, view the table.
you should be able to filter those without tick mark and delete them (make sure you have a copy of the table just as instructed in the function, in case you want to revert).

eg:

"Delete * from yourTable Where [Matched]=False;"

@Rincewind
I've looked at the solutions suggested by both arnelgp & CJ_London.
Both are better than my original version which should now be consigned to the dustbin of history

I've taken the liberty of doing a bit more with both of these approaches

1. If you run arnel's version (based on a function) & then run this query to delete matched records you will be left with 1065 records.

Using this method, there will be 54 Personnel Numbers missing from the table where all results were matching pairs. You can identify these using:
If you wish you could add an blank record for each of those Personnel Number. If so you end up with 1065+54=1119 records

Finally this query shows there are 2 personnel numbers (5976/792) with duplicate data at the end:

If one of each should be deleted, you end up with 1117 records

Modified version of arnelgp's solution attached with these 3 queries added

2. I've done an alternative based on CJ_London's suggested solution
Use a union all query as before but make debit values negative and append these records to tblUNIONALL
Next run another append query to add the total amounts to tblFINAL marking them a C if total >=0 or D if not.
Total records in tblFinal = 1117 (as above)

This needs just 3 queries and no code

Which is better? Your choice
Do both give the same / correct results? Probably - though I'll leave you to check that for yourself

Have fun

Well, my thanks to all on here who contributed to my thought processes - went to bed last night mulling over the suggestions, which was probably not a good thing as I laid awake. Anyways, I got there eventually using two Sum queries, one for credits and one for debits, then finding the difference and that's what needed posting in most cases except for about 20 that needed intervention. All balances out - only took me about 8 hours!!!It's the first and hopefully the last time I've seen a General Ledger file from payroll

Glad you found a solution.
If you can spare the time, it would be interesting to know if either of the solutions in my last post give the correct results