Options
Looking for some solutions for this billing / charge back dilemma
Currently I work as a team lead for a service provider. We are insourced and work directly with others that are full time employees. The financial model we work under consist of activities and time and material. These charges are tracked by the technicians and then submitted. The tool used to track their efforts is an Excel workbook. These "activity logs" are then gathered audited and then copied into a master log. This log eventually is cleaned up audited stringently and then submitted. We also have certain activities that are chargeback items.
My question is this, do you have any ideas on how to make this automated or how to tighten this up. Right now I have to go through hundreds of records and make sure there is only one incident per record. And if there is multiple then I have to consolidate the records into one. It flat out sucks to be honest, but the operations manager for the customer is very throughout and rightfully so.
My one idea was this, keep in mind this is very raw and parts are abstract. I will also blend in some "ideas" that might not be possible. So here it goes.
First I want to create an Access Database and essentially set the back end up as a giant flat file. No need to create normal form or relationships between the tables at this point. On the front end I would create an input form where the technicians would access this form and input all there key data. Incident number, date, customers name, activity or time and material or both, notes or resolution. Once they would create this form it would add a record to the database. I would add a constraint on the incident field to prevent duplicate records from being added. If you did type in a incident you would get an error message saying this record has been created already or something like that. Now this where I get a bit lost, how would I incorporate that new data into the same record? Is that possible? My goal is to prevent the technicians from creating duplicate records. It's killing me.
I am limited a bit in regards of what I can and cannot do. My engagement manager uses Excel for the reports, but that would be perfect. I could just do an export from access to excel and there you have it.
So I have that one knowledge gap I am hoping someone could help me overcome.
Another twist to this is the use of SharePoint 2007. What I would like to do is some how give the techs a view/read write access to the form, but not the database. That way they can access the SharePoint site and checkout the form and fill it out and then save it. Is there a way to embed a form into SharePoint so it will interface with an Access database? I really don't want to get sent Access Database files everyday, it kind of defeats the purpose. There is always the possibility of setting it up on a network share, but I have never done that so I am not aware of the capabilities and limitations.
Thanks for any help.
Some other thoughts and questions. Is it possible to limit access to the tables in Access, but not the forms? That would answer one of my questions. Circling back to the form sections, I was thinking after the error box came up saying this file has already been created, is there a way to get Access to pull the original form up so the other technician or the same one could add to the same form?
My question is this, do you have any ideas on how to make this automated or how to tighten this up. Right now I have to go through hundreds of records and make sure there is only one incident per record. And if there is multiple then I have to consolidate the records into one. It flat out sucks to be honest, but the operations manager for the customer is very throughout and rightfully so.
My one idea was this, keep in mind this is very raw and parts are abstract. I will also blend in some "ideas" that might not be possible. So here it goes.
First I want to create an Access Database and essentially set the back end up as a giant flat file. No need to create normal form or relationships between the tables at this point. On the front end I would create an input form where the technicians would access this form and input all there key data. Incident number, date, customers name, activity or time and material or both, notes or resolution. Once they would create this form it would add a record to the database. I would add a constraint on the incident field to prevent duplicate records from being added. If you did type in a incident you would get an error message saying this record has been created already or something like that. Now this where I get a bit lost, how would I incorporate that new data into the same record? Is that possible? My goal is to prevent the technicians from creating duplicate records. It's killing me.
I am limited a bit in regards of what I can and cannot do. My engagement manager uses Excel for the reports, but that would be perfect. I could just do an export from access to excel and there you have it.
So I have that one knowledge gap I am hoping someone could help me overcome.
Another twist to this is the use of SharePoint 2007. What I would like to do is some how give the techs a view/read write access to the form, but not the database. That way they can access the SharePoint site and checkout the form and fill it out and then save it. Is there a way to embed a form into SharePoint so it will interface with an Access database? I really don't want to get sent Access Database files everyday, it kind of defeats the purpose. There is always the possibility of setting it up on a network share, but I have never done that so I am not aware of the capabilities and limitations.
Thanks for any help.
Some other thoughts and questions. Is it possible to limit access to the tables in Access, but not the forms? That would answer one of my questions. Circling back to the form sections, I was thinking after the error box came up saying this file has already been created, is there a way to get Access to pull the original form up so the other technician or the same one could add to the same form?