New to Access, could use some help

[williams22]

Hello everyone,


First let me start by saying I am very new to Access, although I am extremely proficient with Excel and VBA. I want to begin learning both Access and databases in general and I have a small project to work on to help me learn.

The project is as follows:
I am part of an online community that has it's own form of currency. Members use this currency to buy things, gamble with, or even to just reward other members for informative posts. As a result of this, loan banks have started to emerge and the issuing and requesting of loans has become commonplace. I have been tasked with maintaining a credit history of all the loans that take place. For over a month now I have been doing so through Excel by creating a different sheet for each user with tracks their loans. A summary sheet is then updated by a macro and is what I post to the internet. Obviously this is not the most efficient way of accomplishing this task.

My purpose in joining this forum is to ask for help in creating this (what I imagine will be very simple) database. I'd rather do it myself and have someone talk me through it if that is possible so that I can learn.

If anyone would be kind enough to chat with me for a little bit and help assist me I would be extremely grateful. I have AIM, MSN, and Yahoo messengers.

[jbarrum]

Hello Williams,

Not sure where your knowledge if relational databases lies but a good understanding of this concept is going to be very helpful in designing your database. There are two very important questions that you must ask yourself before you start. This will help in determing what tables, queries, and reports will be needed.

The two important questions are:
1) What is all information (data) do I want to store
2) what do I want to do with that information (data)

before anyone can begin to assist it would be much easier if we knew what fields you are tracking in your spreadsheet. maybe you could upload a copy with sample data in it.

[williams22]

The database will be to keep track of USERS and LOANS. I am planning on the following tables:

tblUSERS(UserName, JoinDate, Posts, Notes)
This table will simply store records of users and basic info about each.

tblLOANS(LoanID, Debtor, Lender, DateIssued, DateDue, AmountLoaned, AmountDue, AmountRepaid, LoanStatus, LastUpdate, URL, Notes)
This table will hold the actual loan info with Debtor, Lender, and LoanStatus being foreign keys. - Is it okay to use USERS as a foreign key twice here or should I have Lender and Debtor tables? One user can be both at times.

tblLOANSTATUS(Status, PaidFactor, UnpaidFactor)
This table holds information about the possible statuses a loan can have (i.e. "OK", "Paid", "Slow Pay", etc.). The factors are a numerical value that will be used in calculating a Credit Score for each loan.

This presents one of my biggest questions - Do I create "CreditScore" as a field in tblLOANS or do I just calculate it in queries? This is where my newness to Access shows. Also, a USER has a Credit Score as well which is the sum of the credit scores from their loans, should this be in tblUSERS or do I calculate it with queries?

That should get me started. Thanks for your help.

[jbarrum]

now you are getting a bit complicated since you can have users that can also be lendors and dedtors. I feel you are going to be best suited to put them in different tables. Otherwise you are going to create duplicate records for the same person which does not meet the criteria for normalization. I would advise you to read through the article in the folliwing link. This will help you in answering all of your questions. http://support.microsoft.com/?id=100139

Also in regards to calculating credit scores, you almost alway calcluate values in a query. very rarely you you calcluate them in a table. However, I am not advance enough to advise you on when to do which. The best article for this answer in this link. http://www.allenbrowne.com/casu-14.html

[williams22]

now you are getting a bit complicated since you can have users that can also be lendors and dedtors. I feel you are going to be best suited to put them in different tables. Otherwise you are going to create duplicate records for the same person which does not meet the criteria for normalization. I would advise you to read through the article in the folliwing link. This will help you in answering all of your questions. http://support.microsoft.com/?id=100139

Also in regards to calculating credit scores, you almost alway calcluate values in a query. very rarely you you calcluate them in a table. However, I am not advance enough to advise you on when to do which. The best article for this answer in this link. http://www.allenbrowne.com/casu-14.html

That makes sense, I will put the users in two tables.

[Microsoft Access Database]

You don't want to store the results of calculations in the table itself. So yes a query would be better for this or even a domain function on the form itself. You may need to research what these do...but essentially they can perform the calculation on the form itself and display in an unbound text box for example.