Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have the weirdest request. On the attached mini-db, I've put three tables and a query. I need to know how to create an append query that will take the information from the last column (NextYearRequest) and append it to the second to last column (AmountWithoutDecimals) but ONLY when that column is showing a blank field.

    For example, on the screenshot (also attached), I've highlighted a few rows. What you see in the AmountWithoutDecimals column is the line item detailed budgeting. In the Next Year Request, you see the TOTAL for all four entries repeated. Looking down a few rows, you'll see that AmountWithoutDecimals is empty for a few accounts. I need to have the data in the NextYearRequest moved over to the AmountWithoutDecimals column and then preferably have a column that will simply show the sum grouped by each Account.

    The reason this is such a messy db is that I had to query multiple tables to create these from a new finance system and people did not enter all the line item details correctly (as is very obvious to the naked eye). So far all my append queries have failed. As always, any help is much appreciated. Thanks.
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Does this query do what you want? (Please test on a copy of your database!)

    INSERT INTO BPDTI ( Account, AmountWithoutDecimals )
    SELECT BPFLI.Account, BPFLI.NextYearRequest
    FROM BPDTI RIGHT JOIN BPFLI ON BPDTI.Account = BPFLI.Account
    WHERE BPFLI.NextYearRequest<>0 AND BPDTI.AmountWithoutDecimals Is Null

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Certainly does. I had to play with it a bit since I was off by about a million dollars or so in the 'real' database but figured it out. Created a separate query to sum by account. What is the easiest way for me to add that to the final product (query)?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try this:

    INSERT INTO BPDTI ( Account, AmountWithoutDecimals )
    SELECT BPFLI.Account, Sum(BPFLI.NextYearRequest) AS SomVanNextYearRequest
    FROM BPDTI RIGHT JOIN BPFLI ON BPDTI.Account = BPFLI.Account
    GROUP BY BPFLI.Account, BPDTI.AmountWithoutDecimals
    HAVING Sum(BPFLI.NextYearRequest)<>0 AND BPDTI.AmountWithoutDecimals Is Null

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hmm, that didn't work for me. I'll keep playing with it though until it does! I have one final question. If in the final version of this multi-query, I want to combine the fields in Dept, Div, Func Area into one field and then have them added to the beginning (to make it easy for people to sort), what is the easiest solution for that? As you can tell in the screenshot below, in the Account field, they are combined into the entire string.

    Thank you again for all your help. Much appreciated by me and everyone else in Finance!
    Attached Images Attached Images

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='pooja' post='772955' date='29-Apr-2009 21:56']Hmm, that didn't work for me.[/quote]
    I don't really understand what you want...

    Access can sort on multiple fields, but if you really want the combined field, you could add a calculated column to a query:

    SortField: Format([Fund],"00") & [Dept] & [Div] & [Function Area]

    or alternatively

    SortField: Left([Account],8)

  7. #7
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry about that! But your answer gave me an idea on what to do. Thank YOU.

Posting Permissions

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