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.

http://www.accessforums.net/editpost.php?do=editpost&p=14576

 

[ColinEssex]

Get a book - work it out. Then if you have a specifice problem, we will help.

Members here tend not to write databases for you. (although some may do it for a fee)

Col

 

[williams22]

Get a book - work it out. Then if you have a specifice problem, we will help.

Members here tend not to write databases for you. (although some may do it for a fee)

Col

I do have a book (2 in fact) and I'm currently taking a course in Access, but I learn faster than most and was hoping someone could help talk me through things. I do not want someone to build this for me, but rather I'm looking for someone who I can chat with and make sure I'm doing things correctly.

 

[georgedwilkinson]

Your request seems sincere enough. However, the reason we come to the forums is to help the community (all the people in the world). If we work it out with you via PM, nobody else gets the benefit.

If you have specific questions, post them on this forum (or one of the other fine Access forums) and if your question is well thought out, somebody will most likely offer a solution to that specific problem.

Since you haven't really asked a question, I will give a bit of advice about your problem:
1. Forget everything you know about Excel when working in Access. They are nothing alike.
2. Learn data normalization.

If you have specific questions, it's probably best to start a new thread for each new question.

Sounds like a fun project.

 

[williams22]

Ok well I'll try to keep my questions here then.

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.

 

[MSAccessRookie]

Ok well I'll try to keep my questions here then.

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.

Already I see a couple of things to consider.

• tblUsers should have a UserID field, and other tables should refer to the User ID Field as opposed to the User Field.
• There does not appear to be any connection between tblStatus and the other tables.

As for your question regarding CreditScore, since CreditScore is a calculated value, it should not be stored, since it is expected to change and can always be recalculated whenever you need to use it.

 

[williams22]

Already I see a couple of things to consider.

• tblUsers should have a UserID field, and other tables should refer to the User ID Field as opposed to the User Field.
• There does not appear to be any connection between tblStatus and the other tables.

As for your question regarding CreditScore, since CreditScore is a calculated value, it should not be stored, since it is expected to change and can always be recalculated whenever you need to use it.

Ok, so use an AutoNumber field to speed up queries in the future instead of using the usernames?

The LoanStatus in tblLOANS comes from tblLOANSTATUS.

Do you have any good reference you can point me to for figuring out how to do queries that calculate the scores?

 

[georgedwilkinson]

Ok, so use an AutoNumber field to speed up queries in the future instead of using the usernames?

Not JUST to speed up queries. There are a host of data anomalies that can be prevented by using surrogate keys. I am a surrogate key bigot because of a several million dollar mistake one of my employees made in her design many years ago. It costs A LOT of money/time to fix this stuff once you've done it wrong.

Do you have any good reference you can point me to for figuring out how to do queries that calculate the scores?

Usually, you can do regular old math inside your queries. If you have the formula on paper, somebody here can help you figure out how to translate it into SQL.

 

[williams22]

Not JUST to speed up queries. There are a host of data anomalies that can be prevented by using surrogate keys. I am a surrogate key bigot because of a several million dollar mistake one of my employees made in her design many years ago. It costs A LOT of money/time to fix this stuff once you've done it wrong.



Usually, you can do regular old math inside your queries. If you have the formula on paper, somebody here can help you figure out how to translate it into SQL.

Ok, I'm really not sure how to write SQL yet so hopefully someone can help with this.

First, from advice I gained from another forum I have made separate LENDERS and DEBTORS tables to make things more clear. Aside from that change, things are set up as I explained above.

I would like to create a query that will list loans, in order of Debtor, that will also include a credit score for each loan. The score is calculated in the following way:

Credit Score = AmountPaid*PaidFactor+(AmountDue-AmountPaid)*UnpaidFactor

Please let me know if you need any other info to help me with this. Thank you.

 

[williams22]

So I used the Builder in Access to build the formula for my query, but when I go to run the query I get dialog boxes asking me to Enter parameter value? What am I doing wrong?