Upload and append data monthly

[notsoevilgenius]

I have to develop a new commissions database. The data will come from a corporate company each month in xls format.

The user will need to upload the sheet each month - from a simple user gui

a. An automated process will have to strip out the data that doesn't pertain to the client (I'll use only date that matches my client's name from the PaidVendor field),
b. if possible identify and apply where the user has indicated a commissions "split" based on a ProductID already in the table, (that will require auto populating a new field not in the uploaded data set with a value default of null)
c. append the data into the working table (we can call that tbl_fhs)
d. clean up the upload process for the following month.

The user will need
a. a "new" record report to identify ProductIDs that have no prior input so the user can go in via user input form and identify commission splits and tie the commissions to two brokers without a broker id
b. a monthly report of all applicable commissions by broker
c. an end of year report of commission totals by broker identifying those that qualify for a bonus based on amount sold.


Normally I'd build at least 3 tables here. tbl_subscriber, tbl_broker and tbl_commission, but as I said all this data will be coming from single xls sheets each month and can't be input by hand record by record.

Thanks!

 

[SkipVought]

hi,

We're going to need some good sample data, or better yet a sample workbook to download. Your description is pretty meaningless without something concrete to work with.

So what does the source worksheet look like?

What does your working table look like?

"identify and apply where the user has indicated a commissions "split" based on a ProductID already in the table, (that will require auto populating [highlight #FCE94F]a new field[/highlight] not in the uploaded data set with a value default of null)"

What is this [highlight #FCE94F]new field[/highlight]? Sound as if a whole bunch or new fields are going to be generated over time and that would be a huge problem.

We just need a LOT of missing information.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Darrylles]

Yep Skip,

Also, requirement numero uno (in my experience) would be:

Ensure that there is a well defined, management supported / backed process for 'process / structure changes' - on a monthly basis BEFORE they occur.

Failure to implement this = probable monthly chaos / knee-jerk manual fixes everywhere / SLA failures (which will of course be YOUR fault).
Implementing this covers you for 'unagreed' changes (quite correctly).

If you can't depend on static data structure / format, then DEMAND to be MADE AWARE of all changes AS SOON as they occur.
If you can't depend on knowing the data-structure - no automated solution can ever work - it'll ALWAYS be manual.

ATB,

Darrylle










Never argue with an idiot, he'll bring you down to his level - then beat you with experience.