Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    cross-tab (Access2000)

    Citco-Source query code:
    SELECT XTrades.amendnum, XTrades.tnum, Sec.ticker, Sec.name, XTrades.fund, XTrades.q, XTrades.td, tblTransactionType.ClassicTT
    FROM (tblTransactionType INNER JOIN XTrades ON tblTransactionType.OrigTT = XTrades.tt) INNER JOIN Sec ON XTrades.id = Sec.id
    WHERE (((XTrades.fund)="SFM_1" Or (XTrades.fund)="SFM_2" Or (XTrades.fund)="INTL_SHARES") AND ((XTrades.td)=#9/17/2001#))
    ORDER BY XTrades.tnum;

    Citco-cross query code:
    TRANSFORM Avg(CitcoSource.q) AS AvgOfq
    SELECT CitcoSource.td, CitcoSource.ClassicTT, CitcoSource.name
    FROM CitcoSource
    GROUP BY CitcoSource.td, CitcoSource.ClassicTT, CitcoSource.name
    PIVOT CitcoSource.fund;

    Citco query code:
    SELECT CitcoCrosstab.td, CitcoCrosstab.ClassicTT, CitcoCrosstab.name, CitcoCrosstab.INTL_SHARES, CitcoCrosstab.SFM_1, CitcoCrosstab.SFM_2
    FROM CitcoCrosstab
    WHERE (((CitcoCrosstab.SFM_1) Is Not Null)) OR (((CitcoCrosstab.SFM_2) Is Not Null));

    Above are the codes of the queries I have. Citco-Source uses a union query based on two other queries to select data. Citco-cross query uses data from Citco-Source query. Citco is based on Citco-cross query. The proble Im having is that the Citco-cross query is only outputting one record when there are two records with the same name in the name column. Any suggestions?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: cross-tab (Access2000)

    Look at the SQL of your cross tab query. See the group by clause? That tells it to group records by name, which is why you don't get two records with the same name. If they have the same name, why would you want two different averages for them?
    Charlotte

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

    Re: cross-tab (Access2000)

    I don't want to group it or calculate the average. I just want to dispaly the values in the fund feild as my feild names in the citco query, that is the reason why Im using cross tab query. Is there anyway I could do this?

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: cross-tab (Access2000)

    I'm sorry, but I have no idea what you're talking about. Do you mean the column headings you get for CitcoSource.fund? You'll never get two column headings with the same name anyhow, and why would you want to? Two funds with the same name logically have to be the same fund. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Charlotte

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

    Re: cross-tab (Access2000)

    No, u don't understand, what I want to do is: In the fund feild, i have few different funds, INTL_SHARE, SFM_1, SFM_2 etc. I want to display them as my feild headings. The only way I knew how to do was using cross tab query. If you have a look at the attachment, you might understand what Im trying to do.
    Attached Files Attached Files

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: cross-tab (Access2000)

    Hi,
    As Charlotte said, you're grouping by td, ClassicTT and name therefore any records where all those values are the same will be grouped together. If they are different funds, you will have more than one fund column with the average of q for that fund. Is that not what you want?
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: cross-tab (Access2000)

    Your attachment shows exactly what I'd expect from your SQL. I suspect what you may want is something like this:
    TRANSFORM Avg(CitcoSource.q) AS AvgOfq
    SELECT CitcoSource.amendnum, CitcoSource.td, CitcoSource.ClassicTT, CitcoSource.name
    FROM CitcoSource
    GROUP BY CitcoSource.amendnum, CitcoSource.td, CitcoSource.ClassicTT, CitcoSource.name
    PIVOT CitcoSource.fund;
    Is that more like it?
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: cross-tab (Access2000)

    oops - I just noticed you don't always have an amendnum! If you're trying to get it so you get two entries for J C Decaux as per your attachment, then you can't from what you've posted, because there's no way of distinguishing the top 3 entries from the next 3 while still grouping each set of 3 entries together. Do you have any way of relating each set of three records in your table?
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: cross-tab (Access2000)

    Nah, Rory.
    I've tried that before I posted the question on the board that is not how I want it. Have a look at the attachment and you'll see the difference.
    Attached Files Attached Files

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: cross-tab (Access2000)

    Did you see my other post (a reply to myself when I noticed the amendnum didn't exist for every record!)?
    Unless there's a way of knowing that the first 3 J C Decaux records should be grouped together but spearate from the next 3 of J C Decaux records, you can't do what you need - Access will simply group all of the together because the fields you're grouping on are identical for each record.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: cross-tab (Access2000)

    I have to find a way to do that but i don't know how, anywayz thank you for trying to help me.

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: cross-tab (Access2000)

    Is there a field in the original XTrades table that would identify them as a group? Otherwise, I guess you'd need to base something on however it is that you know they're related.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    BAM
    Guest

    Re: cross-tab (Access2000)

    Hi Princess,

    I think I know what you are looking for. You're going to need a junction table between the Names and Share type to track this information correctly and to accomplish the correct results in a crosstab query.

    See the attached database for an example of some of the suggested tables you will need.
    ~~~~~~~~~~~~~
    Cheers! <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>

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

    Re: cross-tab (Access2000)

    Hi Bam,
    I've got all the tables you've suggested, your query is doing exactly what my query is doing right now (I don't see it displaying the second record with the same name).

    Hi Rory,
    The only values the records have in common are the price and the broker name, but I don't think I can depend on this coz it might change.

    Isn't there any way at all? Can I export it to Excel and create something like a cross tab? Is it possible for me to do it automatically? if yes, how?

    Any suggestion at all?????

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: cross-tab (Access2000)

    Hi,
    If you try and do it in a pivot table in Excel, you'll end up with the same thing because you have nothing to say these records are related in sets of 3 (the 3 fund types?) rather than by the fields you're grouping on. I still suspect that if there's a way that you can tell they're related, than there ought to be a field somewhere (possibly in another related table) that links them together.
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

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