Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    San Francisco, CA, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update query (2002)

    I have two controls: AccountNumber and Account. That have expense Account Numbers and Accounts entered into them. i.e 650 AccountNumber and Plumbing Comanies Expense for Account.
    There are 6 possible expense accounts that could be put in this control.
    When the controls have data in them they are appended to tblChartOfAccounts-LineItems.
    This works fine and gives the Expense part of the Chart of Accounts.
    I need to update each entry to a Balance Sheet account to have a double entry booking system.
    So Plumbing Companies Expense with expense account number 650 needs to be converted to 350 Plumbing Companies Payable etc. There are 6 possible expense accounts that need to be converted to there corresponding Balance Sheet accounts.
    Frank

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Update query (2002)

    I am not sure what the question is, but here goes anyway!

    You could have a table which links the expense codes to the balance sheet codes. This would just have the two account numbers. then you could use dlookup to find what to post the balance sheet entry to.

    As an accountant I think you should rethink your approach to the process. The point of double entry is that whilst there is always a credit for every debit the credits in one account do not necessarily match the debits in another. I would have a creditors account and post the credit side of each expense entry (other than cash purchases) to the creditors account. You need to be able to deal with situations like purchasing plumbing supplies and building materials from the same supplier.

    I would consider having two combo boxes to select the debit account and the credit account. In many cases you will want to make successive postings to the same account on one side or other. To allow for this you need to set a variable to the value of each combo box (in its change event) and then use the variable as the default for the combo box - each new record will start off with the same two account numbers as the previous one.

    Not enough space here to give full details about how to write an accounting system! Have i answered your question? If not can you be clearer about what your problem is.

    HTH
    David Grugeon
    Brisbane Australia

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    San Francisco, CA, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update query (2002)

    Whew!
    Thanks for enlightening me on some points I did not consider.
    Well back to the old drawing board.
    Frank <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Update query (2002)

    Hi Frank -
    I've been following your posts for a while, and it appears that you are trying to build a Job Cost Accounting package in Access. Having had some experience in this area (we offered a commercial system for the construction industry - focused on plumbing and electrical contractors - which we spent nearly a year developing on top of a widely used Access based accounting system), I thought I should share some things with you. We actually put our package into about a dozen clients at what we thought was a fair price, and then PeachTree added Job Cost accounting to their package, and sold it for ~$500. At that point we pulled our product back, even though it had some bells and whistles that PeachTree didn't, but they had enough capability to satisfy most customers. In our view, we owe it to clients to tell them about the availability of such packages. Otherwise, if they find out on their own, we have a very unhappy client, and we make a bad name for Access developers in general. I hope you don't end up in that situation, but the development you are working on sounds perilously close to it.
    Wendell

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    San Francisco, CA, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update query (2002)

    Thanks Wendell for replying.
    I'm developing a job management system for a water damage restoration business, it's mostly finished.
    I'm not a super expert like most of the moderators are but I've managed to slop together a fairly workable system with VBA and macros.
    My client wanted a system that gave them more capabilities then Quickbooks and a plumbing job costing program on the market.
    Both have deficencies in what they are looking for.
    My program is super intuitive. You just tab through the sequence of the actions you would on the the job.
    You enter the customer info, tab to the job entry form, tab to billable services and expenses, tab then to non-billable expenses, look at your results then push a button and an invoice is created, this data is posted to the chart of accounts both as expense, income and balance sheet accounts, then you veiw your chart of accounts for the job. then you tab to File where you create and send dunning letters etc., schedule followups etc. Additionally, there is a report section that has graphs, reports, and lists.
    I still have some problems with the double entry part and a few other things.
    It's been a monstrous undertaking and I totally misbid the project.
    Now it's a labor of love. (Hard Love)
    Due to the wonderful people in the lounge I've got most of the bugs worked out.
    I've watched newbies operate simple programs like Quickbooks etc. get wrapped around a pole. What they don't take in mind is the flow of the work. They make you jump and around in a disjointed manner, this does not follow how I think people want to work i.e. Most people don't want to read the manuels or get the concept of the technology. Most peole want to just jump in and do this, next do this, next do this etc. and get a product. They don't want to think about bourceing all over the place to get a product.
    With my product you just tab your way down the cycle of the production of a product from the vendor to the customer to the job etc to the end result of the invoice and reports. A person can be trained I hope in minutes to run this program.
    Well tomorrow I install the first beta version for the company, it sort of works, in a week or so it will be fairly polished.
    Would I do it again? Well....
    Frank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •