Solved

Design Question

Posted on 2014-09-19
7
195 Views
Last Modified: 2014-09-19
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
Comment
Question by:pdvsa
7 Comments
 

Author Comment

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

see example form:

Form_Disbursements
0
 

Author Comment

by:pdvsa
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

by:ThomasMcA2
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

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

Accepted Solution

by:
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 36

Assisted Solution

by:PatHartman
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.DS total line
0
 

Author Closing Comment

by:pdvsa
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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 …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

830 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