Case Management DB project

[Eirualmac]

Hello All!


I am embarking on a project to re-educate myself from decades old version of access.
Our business is too small to hire outside to create this database, so I've been tasked to do so and welcome ideas, suggestions and any help you'd be willing to throw my way.
Concept:
Creating a db to help manage cases. Specifically, debit card charges disputes.

• DB will be used by 1 person, (occasionally this person's boss wants to see status), and only these 2 people have access on their pcs. The researcher will enter (create) most of the new cases, however all staff are able to and "do" take in the case information (on an excel spreadsheet that is saved to a network share), and supplying different items (attachments) to the case. Need the db to import the sheet so this research person doesn't have to re-enter all cases. Would be a magical gift if the db could save the attachments to the db as well.
• Need the db to automatically pull in the data from the excel spreadsheet, mark the new items as "NEW" (status in db).
• Need the db to auto fire off letter to customer (notification of case opening & next steps- pre written, however will need to calculate total amount of dispute if multiple items)
• Need the db to track aging & to fire off reports/notices/emails according to type of case/charge. One output needs to be a form to automate posting credit to customer's account saving to another network share so the core system can read & credit the account.
• If the total dispute amount is 25.00 or less, I need the db to fire off (or fill in a form in csv format) to credit the customer's account, fire a different letter telling the customer their account is being credited, and the case will be closed, and close the case.
• I also need to fire an automated report if the merchant appears to be hitting a lot of people (the reason I have merchant in their own table).
• As the case ages, I need the case to move to the top of the list of outstanding, with an email to a couple of people that the deadline is 7 days, again on 6 days, again on 5 days....& so on....
• at deadline, need to fire off letter to customer with findings. OR if research has answer prior to deadline, can kick off account closure.
• And finally I need one single data-entry form, where the customer information can be entered (verifying doesn't already exist to avoid duplicates), a datasheet layout for entering the disputed items, merchant, date charged, then total these items. Need the data-entry page to show any previous cases for this customer (closed or still open).

originally, I thought the northwind template would be the best, but due to the additional supplier/products tables & invoicintI'm starting with the Customer Service Template, have modified to include additional tables (account number, debit card number, disputed items, and merchant).



I am sure I'm missing some important points, but can address as needed. I sincerely hope there are some patient guru's out there that would love to help me with this challenge.

[orange]

Here is a link I often refer to people starting to work with Access or database generally. There are several articles in different formats that help with concepts, data models, analysis techniques... but I strongly recommend you work through 1 or 2 of the tutorials form RogersAccessLibrary mentioned in the link. You will learn how to outline/describe your "business" and translate that into a database design --but the key is to work through the tutorial(s), then apply what you have experienced to your own proposed database.
Good luck with your project.

[Micron]

Suggest you reconsider using Excel because
- it's column oriented data & properly designed db table data is row oriented.
- importing/exporting to/from Excel is fraught with issues: truncated data, numbers become text or vice versa, formatting is often lost
- workbooks are opened exclusively
- it makes no sense to allow someone to input into Excel when they can input directly into Access
- Access is quite robust at restricting users, allowing concurrent users, restricting data access, recording who makes changes, validating data entry and so on.
However, I'll hazard a guess that Access licenses will be the deciding factor, even if Office 365 for businesses allows license sharing (private does, not sure about business). There's practically never a month that goes by in this forum without somebody seeking help to migrate Excel data into their database.

What you want to build is very doable, but if you are without the background knowledge, you are looking at many hours of work to do it poorly/wrong and many, many hours to learn first then do it right. Would not surprise me if you report at the end that it took more than 3 or 4 hundred hours to get it all done, assuming you're starting from ground zero. That might surprise some of my peers, but the devil is in the details, and you're looking a lot of robust features, some of which are driven by dependent logic.

[Eirualmac]

Thank you- will do!

[Eirualmac]

Thank you, Micron-
It’s the 100’s/1,000’s or hours that help me learn and retain. Excel isn’t choice, it’s what we have already created as a form with formatting restrictions- end users follow its use, actually understand saving as csv, as yes licensing is the issue.

[ssanfu]

I will defer to Micron's and/or orange's knowledge, but you might check out the Access Runtime option. You need the full version of Access to create an Access dB, but the users that just enter data *should* be able to use the runtime version of Access. Then you could have an Access only option.

Something to check into.....

[Micron]

I will defer to Micron's and/or orange's knowledge, but you might check out the Access Runtime option. You need the full version of Access to create an Access dB, but the users that just enter data *should* be able to use the runtime version of Access. Then you could have an Access only option.
Something to check into.....

Exactly why I was asking, but I wouldn't bet on the enterprise dropping the notion of using Excel as a data entry point. Not sure what the comment means around formatting, as Excel doesn't provide any formatting that can't be done in Access AFAIK.

"Defer to my knowledge"? True for Orange, I'm sure.
If there was a competition, I'm certain you'd leave me in your Access dust, oh Great Ssanfu (notice that I spelled your handle correctly).