Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Help with creating an access database


(!)

Mwacuka's Avatar
Mwacuka Mwacuka is offline
Computer Specs
Member with 15 posts.
THREAD STARTER
 
Join Date: Feb 2012
Location: Oftringen Switzerland
Experience: Beginner
02-Feb-2012, 05:15 AM #1
Help with creating an access database
Hello all,

Am trying to create an access database, which will be used in updating client records in an insurance firm. The biggest challenge is that I know very little about Access,(my I.T expertise is limited to viewing videos on youtube) I currently have the 2010 version, windows 7. 64 bit.

I would really appreciate all help in understanding how to make the database.
so far, I have been able to import the data from excel, create forms.

I need help with creating search forms, making reports and queries.

The database is for internal use, and will not be published on-line.

Thank you for your response and help.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
02-Feb-2012, 06:21 AM #2
Mwacuka, welcome to the Forum.
The first thing that you should do after importing the data from Excel is to ensure that the data is "Normalised".
I have posted many search forms on this forum that you can look at.
To really assist you we would need more detail on what you want the Search form, queries and rpeorts to do.
__________________
OBP
I do not give up easily
Mwacuka's Avatar
Mwacuka Mwacuka is offline
Computer Specs
Member with 15 posts.
THREAD STARTER
 
Join Date: Feb 2012
Location: Oftringen Switzerland
Experience: Beginner
02-Feb-2012, 06:37 AM #3
Database details
Hello,
Thank you for the prompt reply.

Here are more details about the database and how we intend to use it:

Requirements for the system.

1. Database system.
The system should have the ability to reduce the amount of time taken to upload the new clients into the system. Records must be updated automatically and available to the administrators.
The database must also allow for the capturing of clients details easily and this should include saving of PDF and other forms of correspondence between the clients and the office. This should facilitate client support such as instant access to all the client details when they call to inquire.
Data access should be limited in that the office workers cannot erase the database and only have access to certain segments of the database, such as new client’s entry and access to public data such as names and insurance firms. The administrators however should have the ability to access all the information such as the amounts paid.
Reporting on predetermined time when the client details must be completed fully, such as when all contract details must be verified, when there are certain details missing. Incomplete entries should be reported to the admin within a predetermined timeframe.
Since the firm makes money by receiving compensation from the Insurance firms, there should be financial reports that details which clients have made payments above a certain amount to the Insurance Company and thus a commission is due from the Insurance firm. The insurance firm’s sends out the payment bill to the firm’s account, and the firm would like to have a system that verifies that the payments are accurate. This would mean that the system should be able to calculate the amount due, from the all the clients whom the Insurance firms have been satisfied with. The process works like this: the call agents make an appointment for a consultant to make an insurance pitch to a client, the client sign on with an insurance firm of his/her choice, the consultants submit the contract to the Firm(VIZ), the firm collects the clients details and saves them on the database(currently Excel) then forwards the contract to the Insurance Company, the Insurance company then verifies that the client details are correct and once they receive the payments from the client, they send a confirmation of acceptance to the Firm. The Firm then calculates manually the amounts that are due to them based on a pre-agreed compensation scheme from the Insurance Company. At the end of the month the Insurance Company send the amount due to the Firm and the Firm makes payments to the Consultants. Herein lays the challenges- the Firm does not want the consultants to see how much the Firm makes since they are paid only a portion of the amount. The administrators then must make a payment report which details the salaries payable to each consultant. This is a long and complicated process since there are 6 insurance Companies and around 10 Consultants and it involves a tedious process of collecting all the clients that the consultants signed up, those that there was a compensation due from them, and also the follow up with the insurance Firms to confirm the amounts due, since each Company has its own compensation scheme. Also sometimes, the compensation that was paid out is recalled-in the instances where the client cancels the contract with the Insurance Company or they do not activate their membership. This means finding out the client, the consultant and making the subsequent deduction.

Thus there is need to ensure data security since the payment details must be stored electronically. The idea is to have a system whereby, each consultant can be able to see all the clients that they have signed up, have a link to a table that only they can access to view the salary that is due to them, and the Firm directors must have an archive of all the information about the payment system, when who was paid, what they were paid and etc.

The IT platform must facilitate the following:
 Easy follow up of the clients, by both the office and the consultants, e.g when certain details are missing there should be an automated report that specifies that certain action must be taken. This report must be sent to both the consultant and the Firm.
 Automated payment reports to the consultant who can then be able to view these details remotely.
 Allow for the creation of consultants tiered working options, such as teams, team leaders and juniors, who receive different compensation rates.
 Enhanced client support program, such as when the clients call, the office can have immediate access to the client details.
 Employee monitoring, such as which employee handled a particular client, what was the outcome of the consultation, internet usage, database usage. Basically Big Brother, so that when things go wrong, they know who to hang!


When the date back office workers send out to the Insurance contracts, they save the date under the field Versand nach KK, ideally within a week the office should receive confirmation of acceptance of the client by the insurance company. The backoffice workers then this data in the field Aufnahmebestätigung. Therefore we would like a function, that within 10 days, after the entry of the data under the field Versand nach, there is a report generated that shows which entries do not have an entry in the field Aufnahmebestätigung.
Also, when we receive the reply from the insurance company, we have to update the client details. There are 4 possible replies from the Insurance Company: Acceptance of the client, Rejection, Incomplete dossier or Other. This information is entered under the field bearbeitsstand. We would like a function, that when the information entered in this field, if it’s Rejection/Incomplete/Other, the system prompts for a message asking for comments for this entry. E.g If I input Incomplete, there should be a message function that allows me to input the reason for why the entry is Incomplete, e.g No I.D copy attached.


The Financial Reporting Function:

We would like to use this function to prepare payments to the Beraters. We receive a commission from the insurance firms and we pay the Beraters a portion of this amount. This is based on different plans from the 5 insurance firms. The compensation rates are as follows:

1. If the insurance firm is Group Mutuel, we pay the Berater 60% of the commission we receive.
2. If the insurance firm is Swica, and the commission we receive is more than CH 300.-, we pay the berater CH 200.-, if the amount is less, they receive 0.-
3. If the insurance firm is Helsana, Progress, Visana, Avanex, SanSan and the commission we receive is more than CH 300.-, we pay the berater 250.- and if less amount is less they receive 0.-
4. If the insurance firm is Concordia, and the client is between the ages of 19-25 and the amount we receive is less than CH 300.-, we pay the berater 0.- and if the vice versa, we pay the consultant CH 250.-
5. If the client has signed up for the following hospitalisation options:
1) HalbPrivat & Flex, we pay an additional CH 50.-
2) Privat, we pay the berater an additional CH 100.-
6. If we receive a Storno, we deduct it from the consultants in the following scale:
a. 100% in the first year of the berater working for us.
b. 66% in the second year of the berater working for us.
c. 33% in the third year of the berater working for us.
Thus we would like to have the application, pick the age of the client and use it in the calculation of the berater compensation, pick the hospital insurance that the client has signed up for, which is entered in the field Spitalversicherung.
Also in entering the data, there should be automation of the choices in the fields KVG Modell and VVG, since each option is unique to the Insurance firm that offers it.


Thus in the report, we would like to prepare a payment schedule for each of the berater. We would like them to see all the clients that they have signed up, each amount that is due to them from us. However, the Berater should not see the commission that we have received from the insurance firm. Only the company directors should be able to see this amount. There are only 3 of them.
The reports should be scheduled, to be sent out to the beraters at the end of each month.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
02-Feb-2012, 08:23 AM #4
What time scale do you have for developing this database?
The Security of the database as regards Deletion would need to be controlled by the IT department controlling the Server that the database resides on.
For the other User Security considerations it would be better if the database was developed in Access 2003 format, i.e. a .MDB database. Access 2003 has User level security built in. In Access 2007/2010 you would have to arrange it using VBA.

Out of interest will this be a database in Germany?
Mwacuka's Avatar
Mwacuka Mwacuka is offline
Computer Specs
Member with 15 posts.
THREAD STARTER
 
Join Date: Feb 2012
Location: Oftringen Switzerland
Experience: Beginner
02-Feb-2012, 09:30 AM #5
the timescale would be in about a month's time.
and yes, the database will be in german.( the language) not in Germany.

we are planning on just using one of the computers as the database "residence", thus the database can only be access from one point.
In terms of IT department, am the IT department( we are a small start-up company).
Rockn's Avatar
Computer Specs
Member with 21,189 posts.
 
Join Date: Jul 2001
Location: Somalia of the North, MN
Experience: Disenfranchised American
02-Feb-2012, 09:40 AM #6
With all of those deliverables, limited time and expertise I would say this will not get done in a months time. It is a very ambitious project that would take quite a bit of database design and VBA knowledge to pull it off. I don't know why people always think IT departments are programmers?
__________________
My no line signature
a
a
Mwacuka's Avatar
Mwacuka Mwacuka is offline
Computer Specs
Member with 15 posts.
THREAD STARTER
 
Join Date: Feb 2012
Location: Oftringen Switzerland
Experience: Beginner
02-Feb-2012, 09:58 AM #7
thank you for the reply.

Now, that we have established that i have neither the expertise nor the skills to do this, is there any hope for me?
am at my wits ends here.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
02-Feb-2012, 10:20 AM #8
There is always hope. We can help you design wise. But to get all you have described sounds like about 6 to 12 months of development and testing.
Before you can do most of what you describe it is essential to get the basic design right.
Can you start by posting a Screenshot of your Table relationships?
Rockn's Avatar
Computer Specs
Member with 21,189 posts.
 
Join Date: Jul 2001
Location: Somalia of the North, MN
Experience: Disenfranchised American
02-Feb-2012, 10:44 AM #9
It takes time to learn this stuff and I will say that I am definitely not an expert and know enough to get by writing some good code and database structure and I have been learning for years. It is not an overnight thing to learn to create good functional databases that will get you the results you want, it takes time if you are going to do it yourself.
Mwacuka's Avatar
Mwacuka Mwacuka is offline
Computer Specs
Member with 15 posts.
THREAD STARTER
 
Join Date: Feb 2012
Location: Oftringen Switzerland
Experience: Beginner
02-Feb-2012, 11:52 AM #10
Quote:
Originally Posted by OBP View Post
There is always hope. We can help you design wise. But to get all you have described sounds like about 6 to 12 months of development and testing.
Before you can do most of what you describe it is essential to get the basic design right.
Can you start by posting a Screenshot of your Table relationships?
Thank you very much for the offer of help, I do appreciate your kindness.
I have not been able to get that far. I just have the data in a table. I was only able to design the form and import the data from excel.
Mwacuka's Avatar
Mwacuka Mwacuka is offline
Computer Specs
Member with 15 posts.
THREAD STARTER
 
Join Date: Feb 2012
Location: Oftringen Switzerland
Experience: Beginner
02-Feb-2012, 11:56 AM #11
am willing to put time and effort into learning how to do this.

I do appreciate that its not an overnight task, neither is it a stroll in the park. Hats off to all the programmers and specialists in this field!

Having said that, help in learning how to do it would be welcome.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
02-Feb-2012, 02:41 PM #12
Can I suggest as a Start you take a look at a couple of the databases that I have worked on on this Forum to see how data get structured and Forms/Subforms, Reports/Subreports work.
Can you post a screenshot of your current Table in design view?
DBWalk's Avatar
DBWalk DBWalk is offline
Junior Member with 1 posts.
 
Join Date: Feb 2012
02-Feb-2012, 02:45 PM #13
Access 2010 under the create menu has wizards to assist you in creating queries, reports and design.

Do you have all the data in a single table? Did you link the data to a excel spreadsheet or pull it in to Access?

What I like to do is create a query1 tab alongside the table tab and the under Design go to the SQL view and enter the SQL in the query1 tab myself manually using the SELECT,FROM,WHERE clauses. This way I have better control over what I'm entering. Under Design Tab in Ribbon there is a Run button to execute the SQL and test the ouput. Once the SQL is working, I then I save the query with an appropriate name.

I notice that I have to keep going to Design, View, SQLView to get back to the SQL
Mwacuka's Avatar
Mwacuka Mwacuka is offline
Computer Specs
Member with 15 posts.
THREAD STARTER
 
Join Date: Feb 2012
Location: Oftringen Switzerland
Experience: Beginner
03-Feb-2012, 01:54 PM #14
Hello,

sorry that i have not been able to reply much earlier to your post. Been swamped with work over here.
thank you for your help. please send me the links to the databases, or their names.

here is a print screen of the database in design view.
Am unable to paste the print screen!
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
03-Feb-2012, 02:10 PM #15
This is a fairly large one
http://forums.techguy.org/business-a...imary-key.html
and this is a small on
http://forums.techguy.org/business-a...ng-fields.html

Did you use the "Go Advanced" and "Mangage attachment" Buttons.
If it still won't allow it paste them in to Excel or a word doc (2000-2003 format) and zip the word doc.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


Tags
access, beginner, databases

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑