Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Nothing in the Help file on this one (Access 2003 SP2)

    I work at a Bank. The Bank manages Trust Accounts which hold Assets. My job relates to Real Estate Assets, (Properties). An outside Vendor manages the properties. The Properties are of various types: Residence, Farmland, Commercial, Vacant Land, etc. The Bank exists in various locations referred to as Markets, i.e. New Jersey, Kentucky, Philadelphia, etc. The Vendor is paid a fee based on the number of assets per asset type it manages. Each type of asset has a particular fee base so the Vendor's fee is calculated by taking the total number of each type of asset and multiplying it by the particular fee base, and then summing them together. The Markets which hold the assets pay the fee.

    Of course I have a database that holds all this information and produces an invoice each month, and has done so for a number of years. Up until recently assets were only in one Market so it was quite simple to charge back the fee. Now however I have three assets that are held in two Markets, so the fee must be divided between them.

    I use tWO queries to produce my invoice. The first simply isolates the particular Vendor and excludes the asset type that does not apply. The second is built on the first and the table that holds the fees. The query and table of fees are joined by the Vendor Name and the Asset Type. The fields are grouped with the Asset total as Count.

    Since my fees are calculated by the count of assets I can't figure out how I can isolate the Markets that have to share a fee. I created a new table of the Asset and Market with the key being both the Asset and the Market. I then built a query with two copies of this table joined by the Asset so I could group on the Asset using Count >1 in the first column, and show the Asset and Market from the second copy of the table in the next two columns. This gave me the three Assets (6 records) I was looking for. But where do I go from here?

    Help!!
    Thanks
    chuck

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Nothing in the Help file on this one (Access 2003 SP2)

    Could you put together a small sample database with fictitious data and post it so that we have an idea what you're working with? It's rather hard to come up with ideas out of thin air. See <post#=401925>post 401925</post#> for instructions on posting a database.

  3. #3
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Nothing in the Help file on this one (Access 2003 SP2)

    Hans, I am sorry I couldn't explain myself better. I was able to work out the issues so I know you would have had you understood what my problem was. The idea came to me as I was walking home last night.
    Thanks
    chuck

Posting Permissions

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