Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query question (Access 2000)

    I have a query which has the following fields : RECNO, NAME, FUND, and AMOUNT. What I

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (Access 2000)

    so if rows containing SFM1 and SFM2 have null amounts, you want to show the row containing INTL SHARES instead?

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (Access 2000)

    INTL SHARE is the grand total amount, which is being split into few different funds including SFM1, and SFM2. I want to show INTL SHARE fund only when it has been split into SFM1 and SFM2 (Not when SFM funds are null but when there not null).

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (Access 2000)

    I could probably write it in SQL for you, but I don't believe it's something that could be represented in the graphical query designer, would this be of any use?

  5. #5
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (Access 2000)

    Ya, that'll be great, but will I be able to pull this data from the SQL into a cross tap query?

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (Access 2000)

    I would have thought so. You can just create a new cross tab query which uses the SQL written query as it's source of data.

    A bit busy right now, but SQL query will follow ASAP...

  7. #7
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (Access 2000)

    Thank you Adam <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (Access 2000)

    So, here i am in the Query Question thread, but I really don't understand what you wan to do.
    Can you be a little more specific and if possible attach or mail me the tables and queries you already have ?
    I'll see what i can do.
    Francois

  9. #9
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (Access 2000)

    Hi Francois,
    Thank you for having a look at that post. I have a Cross tab query (Citco Fax), which is using a select query as its source (CrossSource). In this query I

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (Access 2000)

    I did not receive an attachment on your mail but lets have a try.
    If i understand well, you are trying to put a condition on a value in a crosstab query. That's not possible.
    What you can do, is make a new query based on your crosstab query and put the conditions in the new query.
    See the attached database
    Attached Files Attached Files
    Francois

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (Access 2000)

    one further question, when INTL SHARES is not split into SFM1 and SFM2, do you have no rows for SFM1 and SFM2, or do you have rows with NULL amounts?

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (Access 2000)

    assuming you don't have any rows for SFM1 and SFM2 when INTL SHARES is not split into them, the following SQL is the skeleton of what you want

    select
    d1.RECNO,
    d1.NAME,
    d1.FUND,
    d1.AMOUNT
    from
    DATA d1
    where
    FUND<>'INTL SHARES'
    union
    select
    d1.RECNO,
    d1.NAME,
    d1.FUND,
    d1.AMOUNT
    from
    DATA d1,
    DATA d2,
    DATA d3
    where
    d1.FUND='INTL SHARES'
    and d2.FUND='SFM1'
    and d3.FUND='SFM2'

    of course, this assumes you only have on instance of INTL SHARES , SFM1 and SFM2 in your table, if you have more, say with a differing RECNO each time, then add

    and d1.RECNO=d2.RECNO
    and d1.RECNO=d3.RECNO

    on the end, and if you want to restrict by RECNO then add

    d1.RECNO='myvalue'

    Hope this helps,

  13. #13
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (Access 2000)

    Hi Francois,
    I tried yours and it works but I have to create three queries just to do one task. Im gonna try Adam's Idea and if it works, it works otherwise I'll stick to your idea Francois.
    Thank you guys,
    You guys are such <img src=/S/angel.gif border=0 alt=angel width=15 height=21>s

Posting Permissions

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