Relationships

[Sunnylei]

I created 3 tables below for Accounts receivable:

Main Contractor
Fields:
ID - Primary Key
A/C No- Text
Name - Text
Address - Text

Claims
Fields:
ID - Primary Key
A/C No - Number
Date - Date/Time
Nominal Code - Number
Net Amount - Currency
VAT - Currency
Gross - Currency
Payment Received - Currency
Balance - Currency

Aged Debtors
Fields:
ID - Primary key
A/C No - Number
Balance - Currency
30 Days - Currency
60 Days - Currency
90 Days - Currency
Older - Currency

I set up relationship between Main Contractor and Claims is one to many,and relationship between Main Contractor and Aged Debtors is one to one. For some reason is not working properly. Can someone help.

Thanks

 

[GinaWhipp]

I'm not sure how you set up the Relationships unless you used ID which would not work. I made a changes...

Main Contractor
Fields:
cContractorID - Primary Key
A/C No- Text
Name - Text
Address - Text

Claims
Fields:
ID - Primary Key
cContractorID (FK:Number - relate to tblContractors)
A/C No - Number
Date - Date/Time
Nominal Code - Number
Net Amount - Currency
VAT - Currency
Gross - Currency
Payment Received - Currency
Balance - Currency

Aged Debtors
Fields:
ID - Primary key
cContractorID (FK:Number - relate to tblContractors)
A/C No - Number
Balance - Currency
30 Days - Currency
60 Days - Currency
90 Days - Currency
Older - Currency

I will also recommend you remove spaces and special characters as they just create more typing when trying to code. Have a look at...

For Naming Conventions see...
http://www.granite.ab.ca/access/tablefieldnaming.htm
http://www.regina-whipp.com/index_files/NamingConventions.htm

And just in case...
Jeff Conrad's resources page...
http://www.accessmvp.com/JConrad/accessjunkie/resources.html
The Access Web resources page...
http://www.mvps.org/access/resources/index.html
A free tutorial written by Crystal (MS Access MVP)...
http://allenbrowne.com/casu-22.html
MVP Allen Browne's tutorials...
http://allenbrowne.com/links.html#Tutorials
Sample data models...
http://www.databasedev.co.uk/table-of-contents.html

 

[Sunnylei]

I'm not sure how you set up the Relationships unless you used ID which would not work. I made a changes...

Main Contractor
Fields:
cContractorID - Primary Key
A/C No- Text
Name - Text
Address - Text

Claims
Fields:
ID - Primary Key
cContractorID (FK:Number - relate to tblContractors)
A/C No - Number
Date - Date/Time
Nominal Code - Number
Net Amount - Currency
VAT - Currency
Gross - Currency
Payment Received - Currency
Balance - Currency

Aged Debtors
Fields:
ID - Primary key
cContractorID (FK:Number - relate to tblContractors)
A/C No - Number
Balance - Currency
30 Days - Currency
60 Days - Currency
90 Days - Currency
Older - Currency

I will also recommend you remove spaces and special characters as they just create more typing when trying to code. Have a look at...

For Naming Conventions see...
http://www.granite.ab.ca/access/tablefieldnaming.htm
http://www.regina-whipp.com/index_files/NamingConventions.htm

And just in case...
Jeff Conrad's resources page...
http://www.accessmvp.com/JConrad/accessjunkie/resources.html
The Access Web resources page...
http://www.mvps.org/access/resources/index.html
A free tutorial written by Crystal (MS Access MVP)...
http://allenbrowne.com/casu-22.html
MVP Allen Browne's tutorials...
http://allenbrowne.com/links.html#Tutorials
Sample data models...
http://www.databasedev.co.uk/table-of-contents.html

Hi
Thanks it works. But I still have question, In table Aged Debtor, the Balance equals sum of 30 days, 60 days 90 days and older. how to set up relationship between Aged Debtors and Claims?

Thanks

 

[GinaWhipp]

Hmmm, for me to better assist (as I am not sure that the *Aged Debtors* is set up correctly) what is it you are doing? In other words, what business issue does having this database solve?

 

[Sunnylei]

Hmmm, for me to better assist (as I am not sure that the *Aged Debtors* is set up correctly) what is it you are doing? In other words, what business issue does having this database solve?

I got a work from other company, and I should get paid when I finished each work. So I need to make claim from the company by issuing self-billing invoice. They should make payment within 30 days. However, they didn't pay me on time. Sometimes they pay me after 90 days. From accounting point view, they are called my Aged Debtor which is company owe me for more than 30 days. It's like you did some works for someone, you send them an invoice to ask for they pay you within 30 days,at this point, they are called your debtor, but they didn't pay untill 90 days later, so they become your aged debtor. I hope it helps.

Thanks

 

[stopher]

Here's how I would do an aged debtor (attached). The aged debtors should not be in a table. Instead they should be derived from your invoices table.

I've done it as two queries to show how it comes together. But it could easily be done as one query.

To answer your question, the second query shows a join with the contractor table.

hth
Chris

 

[spikepl]

It seems Gina correctly smelled a problem. The data structure seems to me a bit off, on first sight.

You have a Contractor, one or more Claims (against the contractor), and then, in realtiy, Payments, which is one or more payments related to a each claim, with date and amount. The classification of the age of the debt, or amounts outstanding could then be done in a query, and not be buried as an element in the data structure. Likewise the balance.

tblPaymentsReceived
-----------------------
PaymentID
ClaimID
Amount
PaymentDate

Shifting similar data around from 30 days to 60days etc is just bound to cause problems down the road and complicate all data extractionm unnecessarily. Remember that presentation of data is not the same as the way in which it is (or should be) stored in a relational database. This is quite different from Excel, where what you see is what you have, more or less.

 

[spikepl]

Ups, crossposted with Stopher - check what he has to say.

 

[Sunnylei]

Here's how I would do an aged debtor (attached). The aged debtors should not be in a table. Instead they should be derived from your invoices table.

I've done it as two queries to show how it comes together. But it could easily be done as one query.

To answer your question, the second query shows a join with the contractor table.

hth
Chris

Hi Chris
I can understand tblInvoice, however I have questions for tblContractor and tblAgeDebtorPeriod. 1)Why you didn't give a primary key to lContractor? 2)You did set a primary key for tblAgedDebtorPeriod as text, however, how can I let the system work out the period fo me ie from invoice date to data that exactly equals 30 days, 60 days, 90 days etc, other than manual typing?

Thanks

 

[stopher]

1)Why you didn't give a primary key to lContractor?

I forgot

2)You did set a primary key for tblAgedDebtorPeriod as text, however, how can I let the system work out the period fo me ie from invoice date to data that exactly equals 30 days, 60 days, 90 days etc, other than manual typing?

I attach another example - assuming I understand you correctly.

Chris

 

[Sunnylei]

I forgot

I attach another example - assuming I understand you correctly.

Chris

Hi Chris
thanks. I understand it now. However, I still have a question. If my client paid me within 30 days, I need to add in a tblpayment, in which the fields are as follows,

tblPayment - number (Primary Key)
tblInvoiceID - number
Amount - Currency
PaymentDate - Currency

It's just like Spikel mentioned in his message. If so, should I need to set a one to one relationship between tblInvoice and tblPayment?

Thanks

 

[stopher]

Well I would recommend that all billing documents are stored in the same table. The only difference is Invoices are +ve and payments are -ve. You may also have debits and credits.

So then the question of receiving and balancing payments...

Suppose an account has an outstanding invoice from May but then payment is received in July. I would expect initially to see this in the aged debtor (+ve in May and -ve in July) even though they balance. Then at some point someone needs to balance off the two transactions so they are removed from the aged debtor. I'd do this using a reference number that references the two transactions together - bearing in mind a payment may cover more than one invoice.

Take a look at the latest version (attached). You can see in the aged debtor query that the €20 and -€20 have not yet been balanced. However, if you look at the transaction table you will see that there is a payment that has been balanced against two invoices and therefore none of these appear in the aged debtor.

Does that help?

Please note I'm not an accountant. I do dabble in credit control though

Chris

 

[Sunnylei]

Well I would recommend that all billing documents are stored in the same table. The only difference is Invoices are +ve and payments are -ve. You may also have debits and credits.

So then the question of receiving and balancing payments...

Suppose an account has an outstanding invoice from May but then payment is received in July. I would expect initially to see this in the aged debtor (+ve in May and -ve in July) even though they balance. Then at some point someone needs to balance off the two transactions so they are removed from the aged debtor. I'd do this using a reference number that references the two transactions together - bearing in mind a payment may cover more than one invoice.

Take a look at the latest version (attached). You can see in the aged debtor query that the €20 and -€20 have not yet been balanced. However, if you look at the transaction table you will see that there is a payment that has been balanced against two invoices and therefore none of these appear in the aged debtor.

Does that help?

Please note I'm not an accountant. I do dabble in credit control though

Chris

Hi Chris
I'm trying use your way to solve my problem. However, in query stage, it's not working. I'm using Access 2007. Can you have a look what went wrong.

Thanks

 

[stopher]

Take a look at the relationships window (Database Tools=>Relationships). Although it appears that there aren't any, they are in fact hidden. So right-click on the screen and choose Show All. Then delete the relationships to the AgeDebtorsPeriod table. There does not need to be any relationships to that table.

Chris

 

[Sunnylei]

Take a look at the relationships window (Database Tools=>Relationships). Although it appears that there aren't any, they are in fact hidden. So right-click on the screen and choose Show All. Then delete the relationships to the AgeDebtorsPeriod table. There does not need to be any relationships to that table.

Chris

Sorry Chris, there is no relationship in your database.

 

[Sunnylei]

Take a look at the relationships window (Database Tools=>Relationships). Although it appears that there aren't any, they are in fact hidden. So right-click on the screen and choose Show All. Then delete the relationships to the AgeDebtorsPeriod table. There does not need to be any relationships to that table.

Chris

Chris

I notes that your query has a problem. Can you have a look the last row.

Thanks

 

[stopher]

Sorry Chris, there is no relationship in your database.

Correct. I didn't put any relationships in my examples. But the database that you posted did have relationships in it. And they are causing data entry problems.

But I don't know what problems you are having.

Chris

 

[Sunnylei]

Correct. I didn't put any relationships in my examples. But the database that you posted did have relationships in it. And they are causing data entry problems.

But I don't know what problems you are having.

Chris

Chris
My problem is I cannot enter a data into the queries.

 

[stopher]

Chris
My problem is I cannot enter a data into the queries.

I assuming you are referring to the queries in the example I gave? That is correct, you cannot enter data into them. It wouldn't make any sense to do so, hence Access doesn't allow you to.

Why do you need to change data?

Chris

 

[Sunnylei]

I assuming you are referring to the queries in the example I gave? That is correct, you cannot enter data into them. It wouldn't make any sense to do so, hence Access doesn't allow you to.

Why do you need to change data?

Chris

Chris
If a Query cannot add new data, then what is going to happen in the Form Stage? Please have a look I updated database.

Thanks