We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Access 2007 Database Table Design - Tracking Transactions

Ricky Nguyen
Ricky Nguyen asked
on
Medium Priority
1,983 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
Comment
Watch Question

Bruce SmithSoftware Engineer II
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Bruce SmithSoftware Engineer II

Commented:
It will probably resemble something like the following:
Sample ERD

Author

Commented:
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
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
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

Author

Commented:
Hi JC,

Did you mean something like the attached image?

Rick


      revised

Commented:
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

Author

Commented:
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
Bruce SmithSoftware Engineer II

Commented:
@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

Author

Commented:
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
Bruce SmithSoftware Engineer II

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

Thankd

Commented:
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

Author

Commented:
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

Commented:
No worries at all. It must work for you...

Cheers
JC
Bruce SmithSoftware Engineer II

Commented:
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
Bruce SmithSoftware Engineer II

Commented:
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.

Author

Commented:
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

Author

Commented:
Thanks guys, your comments were very helpful. There were many more things that I had to consider than I realised.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.