Hide 0 results in query (1 Viewer)
- Thread starter kenrav
- Start date
I'm sorry - I thought I had mentioned I tried that. My mistake. Yes, normally that would work. However, I just realized that my query is of a qryDef and I bet that's the problem. Thanks.
Yep! That was it.
You shouldn't store credits ([Received]), and debits ([Dispensed]) in separate fields. The values they represent should go into the same field. Then you either use positive/negative numbers or a transaction type field to denote if credit or debit.
1. You are storing a piece of information not in the table, but in the field name (Received, Dispensed).
2. You want those values to work together. If the are in just one field, you simply SUM the column, the above method requires the addition of SUMmed values.
3. You're probably wasting space. I've seen others implement this using your current method and one record only holds day for Received or Dispensed, which means the other field is always blank. Which means one field is always not used in every record.
2. You want those values to work together. If the are in just one field, you simply SUM the column, the above method requires the addition of SUMmed values.
3. You're probably wasting space. I've seen others implement this using your current method and one record only holds day for Received or Dispensed, which means the other field is always blank. Which means one field is always not used in every record.
- Local time
- Today, 11:44
- Joined
- Feb 19, 2013
- Messages
- 16,619
think it is a matter of personal choice although I prefer the one column method. just less coding overall around data capture, reporting and interrogation. a null value (for numbers at any rate) takes up no space in storage,
Thank you for your advice. I'll keep this in mind going forward.
- Local time
- Today, 06:44
- Joined
- Feb 19, 2002
- Messages
- 43,302
Storing credits and debits as separate fields harks back to the days when accountants wore green eyeshades and did everything with pencil and paper and they didn't even have adding machines let alone computers. Keeping them separate in a computer system isn't wrong, it just complicates the internal processing. Do whatever your accountant wants.
