Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Additional Design Help

Posted on 2011-09-20
10
Medium Priority
?
220 Views
Last Modified: 2012-08-13
Experts,
I thought that I had a working solution yesterday but if I did I don't see it. Here's my need:
Customer make loans
Each loan has a status, e.g. Open, Closed, Bankruptcy, etc.
Each status can have more than one reason, e,g, Bankruptcy, Chapter 7, or Chapter 11. I want to associate only that reason against the corresponding LoanStatus so people can not pick the wrong reason for a loan status (there can be multiple reasons against one LoanStstus.)
In my tblLoans the fields I'm interested in are:
LoanStatusID (fk)
ReasonForStatus (fk)
How can I build this?
0
Comment
Question by:Frank Freese
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 48

Expert Comment

by:Dale Fye
ID: 36568143
You simplest way is to create a table (tbl_Status_Reason) that contain two columns (Status and Reason) and contains all of the accepted combinations of Status and Reason.

Then, on your form, you use a query that looks like the following for a the Status combo box on your form.  This combo box would be bound to the Status field in your primary data table.

SELECT Distinct STATUS from tbl_Status_Reason

Then, the query that would be the RowSource of your Reason combo box would look something like the following:

SELECT Reason FROM tbl_Status_Reason WHERE [Status] = Forms!yourMainForm.cbo_Status

Finally,  in the After Update event of cbo_Status, you would requery the combobox cbo_Reason, so that it only displays the acceptable value for the selected Status.  That code would look something like:

Private sub cbo_Status_AfterUpdate

    me.cbo_Reason.Requery
    me.cbo_Reason = NULL

End Sub
0
 

Author Comment

by:Frank Freese
ID: 36568277
Let's take a look at the structure:
tblLoan                              LoanStatusReasonID (fk)
tblLoanStatusRdeason     LoanStatusReasonID (pk)
                                          LoanStatusID (fk)
                                          LoanReasonID (fk)
tblLoanStatus                    LoanStatusID (pk)
                                          LoanStatus
tblLoanReason                  LoanReasonID (pk)
OK?
                                          LoanReason
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 36568407
Will all the reasons be "canned" predefined reasons or will they be unique comments make by the user (or some mixture of the twoi?
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:Frank Freese
ID: 36568416
each reason will be independent of the status, though you may have the same reason twice.
0
 
LVL 48

Accepted Solution

by:
Dale Fye earned 1800 total points
ID: 36568428
You could do it that way, although then you would only need a single combo cbo_StatusReason, that would join tblLoanStatusReason, tblLoanStatus, and tbl_LoanReason and would have two fields (LoanStatusReasonID, and a combination of LoanStatus and LoanReason).

I would normally do it by putting fields for both LoanStatusID and LoanReasonID in tblLoan.  I generally prefer to keep the two field separate in the main data table, and only use the linking table to associate the Status with appropriate Reasons.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 36568446
did you mean "reasons will be dependent on status"?

If they are independant of one another than you can associate any reason with any status and don't need tblLoanStatusReason.
0
 

Author Comment

by:Frank Freese
ID: 36568506
I understand your suggesrtion this way:
tblLoan
    LoanStatusID (from the table tblLoanStatusReason as fk)
    LoanReasonID (from the table tblLoanStatusReason as fk)

Then the table tblStatusReason looks like this:
LoanStatusID (here the data comes from the table tblStatus)
LoanReasonID (here the data comes from the table tblReason)

I'd then create an unique index on LoanStatusID and LoanReasonID in the table tblStatusReason

same page?
0
 

Author Comment

by:Frank Freese
ID: 36568674
actually, I have to associate a status with a particular reason. For example, if the status of the loan was Collections, I do not want to associate Collections with Chapter 7, with is a type of Bankruptcy
0
 
LVL 15

Assisted Solution

by:David L. Hansen
David L. Hansen earned 200 total points
ID: 36568766
tblCustomer
-Cust_ID (pk)

tblLoan
-Loan_ID (pk)
-Cust_ID (fk)

tblStatusLog
-Loan_ID (fk)
-Status_ID (fk)
-Reason (fk)
-Date_of_Status
-Comment

tblStatus
-Status_ID (pk)

tblReason
-Reason_ID (pk)
0
 

Author Closing Comment

by:Frank Freese
ID: 36569478
thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question