Automating a manual task - Monthly duplicated items

[Chouz06]

Hi All,

Need some help with a large data set that my team handles each month (sample attached). It is rather manual and I have tried, and failed to overcome the manual aspect so am reaching out to anyone that may have had an issue like this in the past or if not, has any good suggestions.

Currently I receive data on bonuses each month from corporate. A vendor (in our example “ABC”) pay be due a bonus on a specific invoice transaction, however if the customer (in our example “XYZ”) has not yet paid my company for the deal the vendor has done then it will be listed as “0%” in the %Paid column.

This is where the manual part comes into play. Until the status of the %Paid changes to 100%, (i.e. the customer has paid my company so it is okay to now pay the vendor), corporate keep sending the same data each month - as we keep reporting this due bonus to the vendor until the customer pays.

For month end purposes the liability of the bonus needs to be calculated each month, so a member of my team will go and manually look at each invoice transaction (Column F) and tag the duplicate lines and retain the latest months information. This is so that the accounting entries are not doubled or tripled.

This can take a long time and would be a big help if this can be overcome some way shape or form, so that the items that need to be tagged as “duplicate” do so in an automated manner. I’m not sure if access is the way but if it is then great, if there’s a better way then let me know

Thanks!

 

[plog]

This seems like a job Access can essentially automate, however we need to know more about the data and the process.

1. What do you actually receive? Is it that spreadsheet just minus Column W? Or is it a completely different file? Do you guys add more columns than column W?

2. What fields make a record unique? Look at Row 26 and Row 29. At quick glance those are identical. What fields can a computer use to see that a row is unique?

3. What are the "Duplicate" tagging rules? Is it just the first time you receive a spreadsheet where [% Paid] is 100% or does more go into it?

 

[plog]

Actually, thinking about it. Can you send me 2 consecutive raw files? Send me what you get, not what you have after you process it.

 

[arnelgp]

if you will just duplicate invoices whose "%paid" column = 0,
create an insert statement:

insert into bonus (period, [reporting currency], (other fields), invoice) _
select format$(date,"mmmyy"), (other fields), "duplicate" from bonus _
where [%paid] = 0;

 

[Chouz06]

This seems like a job Access can essentially automate, however we need to know more about the data and the process.

1. What do you actually receive? Is it that spreadsheet just minus Column W? Or is it a completely different file? Do you guys add more columns than column W?

2. What fields make a record unique? Look at Row 26 and Row 29. At quick glance those are identical. What fields can a computer use to see that a row is unique?

3. What are the "Duplicate" tagging rules? Is it just the first time you receive a spreadsheet where [% Paid] is 100% or does more go into it?

Thanks for the response - please find attached a version 2 which contains three sheets of the raw data how it is sent to me.
Even if all periods to date are still "0% paid" (say we have 3 months data), out of those three months 2 of those for a specific transaction should have "duplicate" next to it leaving the current period one blank.

 

[plog]

With Access your methodology is obsolete: You don't need to mark "Duplicates", you just need the status of each item. You want to know when each goes to 100% and which ones are less than 100%. Attached is a database that does that. Here's how it works:

It has 2 tables:

MasterTable - which will hold up to date info on every record

MonthlyImport - which is where you will load your monthly files.

Every month you will import your new file into MonthlyImport, then you run 2 queries:

05_UpdateRecords - which updates existing records with their new Paid%

10_AppendRecords - which adds any new records to MasterTable from the new file.

After you do that all your data is up to date and you can run queries to get any data you want. I included 2 queries:

15_Monhtly100% - this query shows which records went to 100% with the new file you received.

20_Outstanding - this query shows which records are less than 100% after running the updated file.

I did not use all your fields. I imported just enough to show you what you need to do to automate this with Access. You can reconfigure my database to work with all your fields.

 

[arnelgp]

here is another demo.
almost complete again with forms (no report yet).

 

[Chouz06]

here is another demo.
almost complete again with forms (no report yet).

With Access your methodology is obsolete: You don't need to mark "Duplicates", you just need the status of each item. You want to know when each goes to 100% and which ones are less than 100%. Attached is a database that does that. Here's how it works:

It has 2 tables:

MasterTable - which will hold up to date info on every record

MonthlyImport - which is where you will load your monthly files.

Every month you will import your new file into MonthlyImport, then you run 2 queries:

05_UpdateRecords - which updates existing records with their new Paid%

10_AppendRecords - which adds any new records to MasterTable from the new file.

After you do that all your data is up to date and you can run queries to get any data you want. I included 2 queries:

15_Monhtly100% - this query shows which records went to 100% with the new file you received.

20_Outstanding - this query shows which records are less than 100% after running the updated file.

I did not use all your fields. I imported just enough to show you what you need to do to automate this with Access. You can reconfigure my database to work with all your fields.

@plog @arnelgp - Thank you both so much for your help. I will try this out today and will let you know how i get on. Again , appreciate your willingness to help