Question need some pointers to get started..

[sunset1215]

I'm trying to track my payments of vouchers in my current database.

i have 3 tables that stores the voucher numbers and payment date of the paid vouchers to our clients.

and currently, we have an excel file that goes something like this:

date received|voucher no.|Amt|Qty|Total|UsedQty|UsedTotal|BalQty|BalTot
1 may|NTUC 1-50|$5|100|$500|50|$250|50|$250


so now, i'm wondering how i can incorporate this into my current database. i need some ideas on how i could store the information or how the process should flow, or something that could help. also, it doesn't have to look like the current excel file.

i'm really grateful for any help or advice given.

 

[Beetle]

it doesn't have to look like the current excel file.

Nor should it. When working with Access you have to "unlearn" some of the methods you use with Excel.

Looking at your Excel example;

date received|voucher no.|Amt|Qty|Total|UsedQty|UsedTotal|BalQty|BalTot
1 may|NTUC 1-50|$5|100|$500|50|$250|50|$250

The first thing I would ask is whether or not you need to retain the history for the original voucher qty (in this case 100) and then compare each transaction against that original amount?

Or, are you just going to deduct each transaction from the original amount until it reaches zero?

I would think the former, in which case you would likely need a Vouchers table to hold the general info about the voucher including the original qty and value, and a Transactions table (which would be the child of the Vouchers table) to record each transaction for each voucher.

Another thing to keep in mind is that, in a relational database like Access, tables are for storing raw data only, not calculations. Calculations are done outside of the tables in queries, forms and reports. Looking again at your Excel example;

date received|voucher no.|Amt|Qty|Total|UsedQty|UsedTotal|BalQty|BalTot
1 may|NTUC 1-50|$5|100|$500|50|$250|50|$250

The values that I changed to red are calculations and would not be stored in a table. The UsedQty I put in green because I'm not sure what this is supposed to represent in your spreadsheet. If they used 50 in a single transaction then that would be raw data. If this is supposed to represent the accumulated total of all the transactions then it is a calculated value.

If you consider some of these things ahead of time, it will save you a lot of wasted time when designing your tables. Assuming that the former of the two scenarios mentioned earlier is the case, the tables might look like;

tblVouchers
************
VoucherNo (Text field - primary key as long as each voucher no. is unique)
DateReceived (Date/Time field)
Amount (Currency)
Qty (Number)

tblTransactions
****************
TransactionID (Autonumber primary key)
VoucherNo (Text - foreign key to tblVouchers)
TransactionDate (Date/Time)
AmountUsed (Number)

 

[sunset1215]

Another thing to keep in mind is that, in a relational database like Access, tables are for storing raw data only, not calculations. Calculations are done outside of the tables in queries, forms and reports. Looking again at your Excel example;

date received|voucher no.|Amt|Qty|Total|UsedQty|UsedTotal|BalQty|BalTot
1 may|NTUC 1-50|$5|100|$500|50|$250|50|$250

yes. i understand that we should not store calculated fields in a table.

If you consider some of these things ahead of time, it will save you a lot of wasted time when designing your tables. Assuming that the former of the two scenarios mentioned earlier is the case, the tables might look like;

tblVouchers
************
VoucherNo (Text field - primary key as long as each voucher no. is unique)
DateReceived (Date/Time field)
Amount (Currency)
Qty (Number)

tblTransactions
****************
TransactionID (Autonumber primary key)
VoucherNo (Text - foreign key to tblVouchers)
TransactionDate (Date/Time)
AmountUsed (Number)

i think this isn't a bad idea. however, you might have been mistaken by my excel example.

date received|voucher no.|Amt|Qty|Total|UsedQty|UsedTotal|BalQty|BalTot
1 may|NTUC 1-50|$5|100|$500|50|$250|50|$250

it should have been "NTUC 1-100" in this case. this actually means voucher number 1 to number 100, and so the Qty is 100 pcs, and Amt is value of $5/pc. sorry about my mistake.

so when there are no more vouchers, we would buy a stack of 400 pcs at the same time. also, in the 3 tables where i'm storing the voucher numbers and payment date? it actually looks like this:

tblXtInc
pkXtIncID
fkXtPdtID
fkTypeID - this lookups the voucher type in a table named tblIncType
payment date
voucher number

the voucher numbers are stored separately in each record. so while i think that your idea is good, how could i possibly type in each voucher number piece by piece at the point of purchase?

i'm sorry if you find it hard to understand, but i'm trying my best to explain it.

 

[sunset1215]

some additional info:

i can pay out multiple vouchers to a client in a single transaction.(as the value of each voucher is only $5). when this is the case, i would think that tblVouchers would have a fkTransactionID. however, it is not possible to have transactions without any vouchers, so having a fkTransactionID in tblVouchers is not possible.

there's gotta be something that i haven't thought of yet. that is why i'm having these problems.

 

[Beetle]

OK, so I've got a few questions;

Each voucher is numbered individually, correct? So the actual data in the Voucher No field in your table would look like;

NTUC 1
NTUC 2
NTUC 3

And are there vouchers with different codes like ABCD 1 (or whatever), or is it more like when you run out of vouchers you start over with a new group that is also numbered NTUC 1 through 100?

Also, are they always given out (or received - I'm not sure which way the transaction is going here) in sequence, or could you give out, say, NTUC-1 and NTUC-59 at one time?

When you receive (or purchase) a new batch of vouchers, are they always in groups of 400?

Do all the vouchers in the new batch have a sequential code? Like they will all be NTUC-1 through NTUC-400?

OR are they divided into groups like NTUC 1-100, ABCD 1-100, etc.?

When you pay out multiple vouchers to a client, you still need to track them individually right? Because the client may not necessarily redeem all of them at once?

If I have to put in my 2 cents worth, then why do I only get a penny for my thoughts?

I think that's all for now -

 

[sunset1215]

first off, i have to say i'm really happy you're willing to help. big thanks to you! ok, now to answering your questions.

Each voucher is numbered individually, correct? So the actual data in the Voucher No field in your table would look like;

NTUC 1
NTUC 2
NTUC 3

Actually, i have split them into 2 fields. VoucherType and VoucherNumber. The field VoucherNumber is a text field, as the NTUC $5 stack starts with zero.

And are there vouchers with different codes like ABCD 1 (or whatever), or is it more like when you run out of vouchers you start over with a new group that is also numbered NTUC 1 through 100?

Currently, we are only using 2 types of voucher, "NTUC" and "Capita". Furthermore, for each type of voucher, they have 2 different values, $5 and $10. The numbers will never be the same. Example, for the first time, we purchase NTUC 1 - NTUC 400. The next time, it could be NTUC 588 - NTUC 688.

Also, are they always given out (or received - I'm not sure which way the transaction is going here) in sequence, or could you give out, say, NTUC-1 and NTUC-59 at one time?

We're actually paying our clients with vouchers when they complete testing our products for us. As each voucher is valued at $5 or $10, most of the times, we have to send them multiple vouchers so that it can total to like $30 or $40. They are mostly in sequence, until the stack of vouchers is almost finished. When we pay the clients from the old stack and new stack, the numbers would not be in sequence anymore. As addressed above, when we buy new stacks of vouchers, the numbers do not come right after the old stack.

When you receive (or purchase) a new batch of vouchers, are they always in groups of 400?

No, they are not. However, when we enter this data into our excel file, we would split it into 4 groups of 100. Like this:

date received | Voucher Number
1-May | NTUC 001 - 100
1-May | NTUC 101 - 200
1-May | NTUC 201 - 300
1-May | NTUC 301 - 400

Even so, this is not always the case, as there are times we would purchase less than 100 pcs to tide us over.

Do all the vouchers in the new batch have a sequential code? Like they will all be NTUC-1 through NTUC-400?

OR are they divided into groups like NTUC 1-100, ABCD 1-100, etc.?

No, each voucher is individually numbered.

When you pay out multiple vouchers to a client, you still need to track them individually right? Because the client may not necessarily redeem all of them at once?

We actually mail the vouchers to our clients. I'm not sure if we do need to track them individually or not. My concern here is how i could check what voucher numbers are missing, when there might be an error in data entry, or when we misplaced the vouchers.

If I have to put in my 2 cents worth, then why do I only get a penny for my thoughts?

I think that's all for now -

Once again, many thanks for your help!

 

[sunset1215]

i've thought about this myself over the weekend, and this is what i came up with for my database. i will have 3 tables.

tblTransactions
pkTranID
fkCusID
Payment Date

tblGroup
pkGrpID
Voucher Type
Voucher start number
voucher end number
date received
amount

tblVouchers
pkVoucherNumber
fkTranID
fkGrpID

tblVouchers would be called a junction table here, i think...

my idea is this. when we purchase new vouchers, it will go into tblGroup. the data would be like this:

pkGrpID|Type|Start|End|Receive|Amt
1|NTUC|001|100|9 may|$5

there will be other fields created in queries.

so when we pay out to our customers, we will enter the data into tblTransaction and the individual voucher numbers into tblVouchers. right now, i'm not sure how to make my fkGrpID in tblVouchers to auto-update itself, but i think it could be done with VBA.

i created this section of the database in another file, so if anyone could look through and see if i'm going in the right direction. thanks all.