Jump to content

Sign in to follow this  

My inchoate bookkeeping program

Recommended Posts

I like it when chain stores give their store a location name instead of or (or usually in addition to) a store number.  Target, Menard's, and Byerly's do this.

So, my personal bookeeping spreadsheet has entries where the "Merchant" column reads "Target Ridgedale" (instead of Target #1234), or "Menard's Golden Valley" (instead of Menard's #1234), or (stay with me now), "Lunds&Byerly's Northeast" instead of...


CVS doesn't do this, so those entries read "CVS #6811" and that's just how it's gonna be so quit complaining to me about it already.


In addition to a spreadsheet, my program has a database component; it's a database of all the merchants I visit.  One of the functions it's going to perform is to auto-populate the city & state columns in the spreadsheet; when I make an entry with "CVS #6811" and click "Save," it will run a VBA script that finds that partickler merchant's entry in the database, then pastes "Plymouth" into the City column and "MN" into the state column.


Why this isn't as simple as it might sound is that there are separate database fields for the chain's name and its store # or location name.  There isn't a field in the database that reads "CVS #6811", there's a record with "CVS" in the 'Chain Name' field and "6811" in the 'Store Number' field.


Approach #1 is to glean the first "word" in the Merchant column on the spreadsheet, search the database for a record with that word in the 'Chain Name' field, then search those records for one with the corresponding store number (or location name).  Sounds all right but it seems it is not a best practice if we run into situations with multi-word chain names (Baja Fresh, Tim Horton's, Au Bon Pain) then we are looking at using a LOT of processor cycles.  Many cycles will be wasted if there are no chains called "Baja" or "Tim" or "Au," and if there WERE such chains in addition to the three I named then we have the specter of false positives.


Approach #2 is to have a new field in the database called "Spreadsheet Appearance String."  Access (or whichever database program I ultimately end up using) would simply concatenate the chain name with the location name, or the store number of no location name is available.  Then the program just has to get the entry from the Merchant spreadsheet column and search the database for an exact match in the

Spreadsheet Appearance String' field.  This is not a best practice because the database will be taking up a lot more space than it has to, with all that redundant info.


I know this is inconsequential when it involves an application that exists exclusively on my eight-year-old laptop, but this experience is raising questions for me about how these things are dealt with by the pros, dealing with accessing big ol' databases with zillions of records.






(This is part of why I like post offices.)

Edited by Sidewinder

Share this post

Link to post
Share on other sites

Don't sweat CPU cycles. This stuff is effectively instantaneous and it was fast enough with an IBM AT using Access and their sort of SQL engine. I did a deep dive using that crap on an accounting issue which involved porting the whole DB from a dedicated system and CPU power wasn't much of an issues.


The last DB I used was SQL Server but it was for logging technical simulations, not accounting stuff. Should be a lot of options. Use whatever you are most comfortable with. MySQL gets good reviews. I'd probably try C# and SQL Server as they can easily be linked to Office objects.

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

About Us

Since 2003, creditboards.com has helped thousands of people repair their credit, force abusive collection agents to follow the law, ensure proper reporting by credit reporting agencies, and provided financial education to help avoid the pitfalls that can lead to negative tradelines.
  • Create New...

Important Information