Can somebody please help me?
From: Melody (anonymous_at_discussions.microsoft.com)
Date: 03/08/04
- Next message: kim: "Unmatched Query Wizard"
- Previous message: Bob barnes: "RE: Current Month"
- In reply to: Ted Allen: "Can somebody please help me?"
- Next in thread: Ted Allen: "Can somebody please help me?"
- Reply: Ted Allen: "Can somebody please help me?"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 8 Mar 2004 10:41:52 -0800
Ted,
Thank you - your response does help. I will post my
question on the programming and module groups with more
specific relevant information. With your Visual Basic
idea, I think I am finally starting to see the light...
Melody
>-----Original Message-----
>Hi Melody,
>
>It may or may not be possible to do what you want to do
>with a series of update queries, but it sounds like this
>would be much better to do in a simple Visual Basic
>module.
>
>Without knowing more about your workflow, it is hard to
>give specific recommendations or code though. I would
>recommend posting in the Forms Programming or Modules
>groups, and also add a little more info about the
>workflow (such as when is the code run, is it run
>immediately each time a credit is entered, or is it
>batched every so often). It sounds like it is probably
>batched.
>
>It shouldn't be too much of a problem to do what you want
>to do, even if you have no previous programming
>experience. The code will be fairly simple, and there
>are a great many in the groups that are happy to help
>with it. You can make it easier for them by letting them
>know how/when you want to run the code, and how the info
>is stored in the tables and how the tables relate. You
>don't have to list all of the fields in your tables (or
>queries), just the key fields that will be used by the
>code.
>
>I would also suggest that you specify that you would like
>to use transactions to make sure that all updates are
>applied or otherwise roll them all back (so that if the
>code will verify that all updates can be made before
>committing them, this will avoid revising a credit amount
>without an equal change to a debit amount).
>
>HTH, I'll look for future posts.
>
>-Ted Allen
>>-----Original Message-----
>>I sent this earlier, sorry if you have read this twice.
>I
>>am trying to use Access to create an A/R aging report
>for
>>old accounts but I am stuck. So far, I have debits and
>>credits listed by account number and transaction date. I
>>have created a column called 'Age' using datediff which
>>calculates the age of the transaction, and I used the
>IIf
>>function to create a field called 'Bucket', which sets
>the
>>field as '0-30', '31-60', etc for each record.
>>
>>At this point, I would like to subtract the total credit
>>amount per account number from each line of debit
>>transactions, so as to only age the
>>debit transactions left outstanding after the all the
>>credits have been applied, using the First-In, First Out
>>method.
>>
>>Logically I'd like to sort the records by account number
>>and then by age (descending order), and use the
>following
>>logic:
>>
>>reference and join the following query tables:
>>debit transactions by account
>>credit totals by account
>>
>>for each account number,
>>if debit <= total credit,
>>set debit = 0
>>set total credit = credit - debit
>>if total credit > 0, loop
>>else stop
>>
>>then i would take my remaining records where debit <> 0
>>and use a pivot table to classify by account number and
>>Age bucket.
>>
>>I assume I will need to write SQL code for this. Can
>this
>>even be performed in Access? Since I have no SQL
>>experience, do you have any tips on how to write this?
>Any
>>help would be appreciated.
>>
>>Thanks,
>>Melody
>>.
>>
>.
>
- Next message: kim: "Unmatched Query Wizard"
- Previous message: Bob barnes: "RE: Current Month"
- In reply to: Ted Allen: "Can somebody please help me?"
- Next in thread: Ted Allen: "Can somebody please help me?"
- Reply: Ted Allen: "Can somebody please help me?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
