Help Required with table design structure - MS Access please

[ultramel1987]

Ii there

Please can someone point me in the right direction with this Ms Access Db

The db needs to do the following:

The db will be used by one manager - this manager receives cash from the company for petrol for his staff. So
we need to see amounts received from the company for petrol. Then it needs to track amounts paid out to various staff for petrol.

Then, sometimes this manager loans money to the "petrol account" and then distributes it to his staff, so this :loan needs to be tracked, along with 'repayments" made to him from the company. however he also receives petrol cash which has nothing to do with the loan

So basically we need to see amounts received for petrol (whether from him or company)
amounts paid to staff from this petrol account
as well as to keep track of the loan from him to the company

Do I create a table for the petrol account and relate it to a payment table and a receipts table
and then a seperate table for the loan and seperate table for loan repayments? I just cant seem to get the general table design worked out, and help will be greatly appreciated

I also tried creating a query linking the loan amounts to the amounts repaid, but it keeps repeating a loan repayment for each line for the loans. ie: Manager loaned R500 to the company today, then 2moro ,loaned another R300. and we only repaid him R100 yesterday, the query shows as follows:

LoanId SumOfLoanAmount PaymentAmount
1 R500 R100
2 R300 R100

the R100 repayment gets duplicated for each entry in the loans table?

plz can someone help me, im getting so lost

thank you so much



thank you so much

melissa

 

[rrh]

Create two tables:

1. The petrol account; and
2. The manager's loan account.

Credit company (or manager) payments into the petrol account; debit payments to staff.

Credit company payments into the loan account; debit manager payments.

 

[mattcremerband]

Hi Melissa

One way I think you can do it:

Primary Table 1
Petrol_Money_Received
id (PK)
loan_from_manager {this will be true if it is from the manager}
loan_repayment (true/false) {if money is received as repayment, this would be true}
amount

Primary Table 2
Petrol_Money_Paid
id (PK)
staff_id (FK) {0 for payment to manager}
loan_repayment (true/false) {if the manager pays money back to himself, this will be true}
amount

Secondary Table
Staff
id
name

select * from Petrol_Money_received
id loan_from_manager repayment amount
1 false false 250
2 false false 250
3 true false 300
4 false true 200
5 false false 200

In the example above the company pays in R900, R700 of which is money up front, R200 is loan repayment.
The Manager loans the account R300, and receives R200 back.

select * from Petrol_Money_Paid
id staff_id loan_repayment amount
1 1 false 150
2 2 false 100
3 3 false 550
4 0 true 200

Above, the manager pays R800 in total, R150 to member 1, R100 to member 2, and R550 to member 3
He then receives a payment himself (repayment for loan) of R200


You would then need to cross check the 2 primary tables to calculate the totals paid, loaned, and repaid.

This is just a quick analysis, and I hope it doesn't confuse you more.

 

[aleo1203]

Awesome, this was what I was looking for, thanks for the help.

 

[nfbs]

No I would make is simpler. Combine the tables so you have one petrol Money. If the amount is a positive it is money he received. If it is a negative it is money he paid to staff. Tally up the amount and you know how much he hasn't paid out.
It work like in accounting but you use the + and - to differentiate between debit and credit.