?
Solved

Access 2007 Database Table Design - Tracking Transactions

Posted on 2011-04-18
18
Medium Priority
?
1,670 Views
Last Modified: 2012-05-11
Hi Experts,
Can you please help me come up with a way to keep track of my receipt and invoice transactions to enable the users of these reports to track the sequence of events. I would like to avoid the use of macros if possible.

If you take a look at the attached image, the tbleTransactions table is currently not linked to either tblInvoice or tbleReciept. I was thinking of placing both InvoiceID and ReceiptID into tblTransactions table but that would leave me with null values if i enter transactions for an Invoice and vice versa.

Your assistance is greatly appreciated.

Thanks
Rick

table-structure.jpg
0
Comment
  • 8
  • 6
  • 4
18 Comments
 
LVL 11

Assisted Solution

by:Bruce Smith
Bruce Smith earned 800 total points
ID: 35421386
To resolve your problem, ask yourself a couple of questions...

1a. How many transactions can any given invoice have?
1b. How many invoices can any one transaction have?

2a. How many receipts can any one invoice have?
2b. How many invoices can any one transaction have?

Given your answers to these questions we will be able to tell you what your relationships should look like and which tables the foreign keys should be in.

Cheers
0
 
LVL 11

Expert Comment

by:Bruce Smith
ID: 35421424
It will probably resemble something like the following:
Sample ERD
0
 

Author Comment

by:Ronniel Allan Castanito
ID: 35422102
Hi Patsmitty,

Thanks for your quick response.

I think my question wasn't very clear so i apologise. What i have to have in the tblTransactions table is something like:

TransID1 InvoiceID1
TransID2 InvoiceID2
TransID3 InvoiceID3
TransID4 ReceiptID1
TransID5 ReceiptID2
.. etc

By doing this, one is able to track the sequence of events for future (if any) auditing process  requirement.

Also how would the above work it I wanted to allow for prepayments. Say Customer A pays $100 in advance and then takes 10 lesson costing $10 for each lesson. So each of the 10 x $10 invoice would have relate back to the first payment of $100.

Hope that makes sense.

Thanks in advance.

Rick
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 6

Accepted Solution

by:
JVWC earned 1200 total points
ID: 35422108
To expand on patsmitty,s post:
1. Is it possible that any one transaction could represent a single payment that requires multiple receipts, because (say) it is one payment to cover two Invoices?
2. If it is always one receipt for one invoice, *and*  will it always remain that way. IE: that way you do business now is not necessarily the way you will do business in the future...

A simple principle: Do not build-in future legacy by designing around is business as it is now, such that it will restrict future changes.

One idea would be to create a Many to Many relationship between Invoices and Receipts, perhaps with the transaction table as the center join.

Cheers
JC
0
 

Author Comment

by:Ronniel Allan Castanito
ID: 35422147
Hi Patsmitty,

To answer your question:

1a: 1 invoice = 1 transaction
1b: 1 transaction = 1 invoice

2a: 1 invoice - Can have more than one receipt related to this one invoice
2b: 1 receipt - Can have more than one invoice related to this one receipt (in the case of prepayments)

Thanks

Rick
0
 

Author Comment

by:Ronniel Allan Castanito
ID: 35422215
Hi JC,

Did you mean something like the attached image?

Rick


      revised
0
 
LVL 6

Expert Comment

by:JVWC
ID: 35422623
Hi Rick,
Yes that is a classic Many to Many relationship.
Please remember this is just ONE possibility.

With that basic structure you will cover:
2a: 1 invoice - Can have more than one receipt related to this one invoice
2b: 1 receipt - Can have more than one invoice related to this one receipt (in the case of prepayments)
However this case will not be covered:
1a: 1 invoice = 1 transaction
1b: 1 transaction = 1 invoice
Reason: it will allow multiple transactions  per invoice.
This is necessary to allow case 2b, so perhaps some careful planning with "required" fields and Form level validation would assist.

Hope this helps,
Cheers
JC
0
 

Author Comment

by:Ronniel Allan Castanito
ID: 35423528
Hi JC,

Many thanks for your comment. Just wondering, what would happen to tblTransactions if only an invoice was issued and no receipt. Wouldn't there be a null value for the receipt field in the tblTransactions table?

The Many to Many relationship takes care of the 2a and 2b as you mentioned but doesn't take care of logging the sequence of events. What happens if someone in the future needs to do an audit of the accounts, how would he know which transaction occurred first and which occurred later?

Thanks
0
 
LVL 11

Expert Comment

by:Bruce Smith
ID: 35428542
@JVWC is correct by the way.

Here is some further insight. I currently work at a business that runs it's POS (point of sale) off of a relational database in Access (believe it or not). It is a very complex database that tracks more than just transactions and invoices with more that 130 tables. I have written some queries for reporting purposes that pulls this kind of information out so I have had hands-on experience with this kind of stuff.

Take a look here and read the notes / scenario carefully. Hopefully I am describing something close to what you are dealing with: notes/scenario
Note that this doesn't even touch tax.

You may be wondering where the Receipt table went... you don't need one. You can print out a 'receipt' of an invoice with all or any of its payments. This is more accurate because each payment is date-stamped the receipt-query will display the transactions on the invoice, and the payments including all information about each payment and the balance.

I hope this all makes sense and gets you pointed in the right direction.

Cheers
0
 

Author Comment

by:Ronniel Allan Castanito
ID: 35429714
Hi Patsmitty,

How will your example above take care of prepayments?

Also, I don't think your example track the sequence of invoice and payments and especially difficult for an example with prepayments.

Thanks
Rick
0
 
LVL 11

Expert Comment

by:Bruce Smith
ID: 35429763
What exactly is a pre-payment? And what kind of transactions are you tracking? Maybe I'm assuming something different...

Thankd
0
 
LVL 6

Expert Comment

by:JVWC
ID: 35431556
Apologies for the time gap, one of us is on the other side of the globe. :-)
>What happens if someone in the future needs to do an audit of the accounts, how would he know which transaction occurred first and which occurred later?<
I imagine the finished product (tables) would have datetime stamps included. That would provide for chronoligical ordering.

>what would happen to tblTransactions if only an invoice was issued and no receipt<
If there is no reciept, then there is no reason for any entry in tblTransactions. IE: There will be no Null records, simply no records at all.

FWIW: SO much of this design depends on the way you do business. As Patsmitty has demonstrated there are many options open to you. It's refreshing to see someone planning!

Cheers
JC
0
 

Author Comment

by:Ronniel Allan Castanito
ID: 35431787
Hi Patsmitty,

Pre-payments is when customers pay in advance before receiving any services and therefore no invoice exist yet. Future invoice will be applied to this pre-payments.

JC, thanks for your comments, I completely understand where you're coming from now. So that means I can keep the tblReceipt and tblInvoice completely independent and by using date and time stamp to be able to reconstruct the sequence of events. I think the many to many table would be better equip to handle what I'm looking for.

Hope you don't mind JC if I'll wait for patsmitty comments before issuing points.

Thanks
Rick
0
 
LVL 6

Expert Comment

by:JVWC
ID: 35432513
No worries at all. It must work for you...

Cheers
JC
0
 
LVL 11

Expert Comment

by:Bruce Smith
ID: 35434112
Sorry for the bunny-trail Rick, like I mentioned before, I may have misunderstood your original question. I was just trying to show you how i might approach an Invoice model.

SO much of this design depends on the way you do business
This is absolutely correct. For some of my projects I don't even create any relationships at all because I know that I won't need them for my purposes.

Keep thinking these things through and I'm sure that you will have a good result for your requirements.

- I think I might be on the other side of the globe in more ways than 1  :)

Cheers
0
 
LVL 11

Expert Comment

by:Bruce Smith
ID: 35434260
And just another question for you, you don't have to reply, I guess I'm just curious:

Wouldn't a pre-payment be an invoice of it's own?
It seems as if a pre-payment would just be like adding to a credit account like a gift certificate or something in which case a pre-payment would be 1 transaction on an invoice. That way you would have a receipt for the pre-payment itself and then when they use that pre-payment for credit on a future service, you'd just reference that pre-payment. Then the receipt for that future service would show that the pre-payment was used on that invoice of that future service.
0
 

Author Comment

by:Ronniel Allan Castanito
ID: 35439810
Hi Patsmitty,

From an accounting point of view, an invoice is used by the business to reflect that they have fulfill their required duty of sercive. A receipt is used to reflect the customer's fulfilled duty ie made the required payment.

Although the final result would be one and the same if we were to take your approach Patsmitty, but from an audit point of view it would be a nightmare to unravel all the events in the future in order to trace and determine the true financial position of a business especially if one has to check the integrity of each transaction .

Thanks
Ricky
0
 

Author Closing Comment

by:Ronniel Allan Castanito
ID: 35439837
Thanks guys, your comments were very helpful. There were many more things that I had to consider than I realised.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

862 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