Solved

# Design Question

Posted on 2014-09-19
196 Views
I have a table:  tblDisbursements
and each disbursement is divided up between 3 banks.

example:
100 disbursement:  25: Bank A, 50: Bank B, 25: Bank C

Would an expert have 3 separate records for each disbursement or have only one record for 100 and then click a button and detail the exact amount per bank?

thank you
0
Question by:pdvsa
[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

Author Comment

ID: 40332563
fyi:  all disbursements are on the same date in this example.

see example form:

0

Author Comment

ID: 40332568
You can see that I dont have a way to show a total amount if I break up the disbursements per bank, which makes me think that I should only have one record for the total amount of disbursement and detail it some other place (like a button leading me to another form).
0

LVL 14

Assisted Solution

ThomasMcA2 earned 167 total points
ID: 40332676
Use one record per disbursement. That way, if the number of disbursements ever change, all you have to do is add another record.
0

Author Comment

ID: 40332693
OK.  How do you suggest to detail various splits between the banks?
0

LVL 47

Accepted Solution

Dale Fye (Access MVP) earned 167 total points
ID: 40332703
3 records

Disb_Num  Disb_Date  Disb_Org   Disb_Amt
1                   9/19/14      Bank A         25
1                   9/19/14      Bank B         50
1                   9/19/14      Bank C         25

As Thomas stated, your business rules might change and you could end up with 2 or 6 banks.  If that were the case, you would have to build your table with as many columns as you might eventually need.

Relational databases are designed around organizing your data vertically (rows).  You should never have two or more columns with similar data in your table, if that ever occurs, you need to modify the structure so that you have three columns
a.  A column that references the row that the data belongs to (the foreign key)
b.  A column that describes the data (what would normally be your column headers in a spreadsheet)
c.  A column for your actual data value.

This would all be in a table of its own, so in your case, you might have tbl_Disbursements, which has a Disb_Num, Disb_Date, and other fields.  and then you would have tbl_Disbursement_Amounts with Fields Disb_Num, Bank#, Amount
0

LVL 37

Assisted Solution

PatHartman earned 166 total points
ID: 40333273
You can see that I dont have a way to show a total amount if I break up the disbursements per bank
Add a total at the bottom of the form/report.  Since you are using a data sheet style form, you have the option of adding totals by just clicking a couple of buttons.  The sample I posted has a count but you can do sum/avg etc.
0

Author Closing Comment

ID: 40333351
thank you.  I am on the right path now.  I am going to follow what Dale said.  I will make another table with the amounts and link them.
0

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
###### Suggested Courses
Course of the Month6 days, 11 hours left to enroll