Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jan 2002
    Location
    Rochester, Kent, United Kingdom
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Queries (Access 97 Sr2)

    Hi All.... i've written 2 crosstab queries and a then a make table query from the 2 crosstabs. This works fine in the _be (split database) but not from the user screens.
    Error Message:- "This expression is typed incorrectly, or it is too complex to be evaluated"
    Any ideas?
    Thanks Si

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

    Re: Queries (Access 97 Sr2)

    We'd have to know details to say anything about this. Could you post a stripped down copy of your database(s)? See <post#=401925>post 401925</post#> for instructions.

  3. #3
    Lounger
    Join Date
    Jan 2002
    Location
    Rochester, Kent, United Kingdom
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (Access 97 Sr2)

    Sorry, can't strip the db down, donations info with names, addressed and how much they donate. What i really want to know is if there is a way of running thr _be query from the user end. It works fine in the _be, but when i copied it over the error message pops up

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

    Re: Queries (Access 97 Sr2)

    Without knowing the design of the queries, it's impossible to say. Sorry.

  5. #5
    Lounger
    Join Date
    Jan 2002
    Location
    Rochester, Kent, United Kingdom
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (Access 97 Sr2)

    Heres the SQL for all 3 queries
    Donations_crosstab
    TRANSFORM Sum(donations.Amount) AS [The Value]
    SELECT [area code].Area_code, Sum(donations.Amount) AS [Total Of Amount]
    FROM donations RIGHT JOIN [area code] ON donations.area_code = [area code].Area_code
    WHERE (((donations.[Date of Donation]) Between #1/1/2004# And #12/31/2004#))
    GROUP BY [area code].Area_code
    PIVOT Format([Date of Donation],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

    Donations_crosstab_count
    TRANSFORM Count(donations.Amount) AS [The Value]
    SELECT [area code].Area_code
    FROM donations RIGHT JOIN [area code] ON donations.area_code = [area code].Area_code
    WHERE (((donations.[Date of Donation]) Between #1/1/2004# And #12/31/2004#))
    GROUP BY [area code].Area_code
    PIVOT Format([Date of Donation],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

    Income Analysis XL
    SELECT donations_Crosstab_Count.Area_code AS AreaCode, [area code].Area_description AS Description, [area code].Region, donations_Crosstab.Jan, donations_Crosstab_Count.Jan AS [No of Gifts Jan], ([donations_crosstab]![jan]/[donations_crosstab_count]![jan]) AS [Jan Average], donations_Crosstab_Count.Feb, donations_Crosstab.Feb AS [No of Gifts Feb], ([donations_crosstab]![feb]/[donations_crosstab_count]![feb]) AS [Feb Average], donations_Crosstab_Count.Mar, donations_Crosstab.Mar AS [No of Gifts Mar], ([donations_crosstab]![mar]/[donations_crosstab_count]![mar]) AS [Mar Average], donations_Crosstab_Count.Apr, donations_Crosstab.Apr AS [No of Gifts April], ([donations_crosstab]![apr]/[donations_crosstab_count]![apr]) AS [Apr Average], donations_Crosstab_Count.May, donations_Crosstab.May AS [No of Gifts May], ([donations_crosstab]![may]/[donations_crosstab_count]![may]) AS [May Average], donations_Crosstab_Count.Jun, donations_Crosstab.Jun AS [No of Gifts June], ([donations_crosstab]![jun]/[donations_crosstab_count]![jun]) AS [Jun Average], donations_Crosstab_Count.Jul, donations_Crosstab.Jul AS [No of Gifts July], ([donations_crosstab]![jul]/[donations_crosstab_count]![jul]) AS [Jul Average], donations_Crosstab_Count.Aug, donations_Crosstab.Aug AS [No of Gifts Aug], ([donations_crosstab]![aug]/[donations_crosstab_count]![aug]) AS [Aug Average], donations_Crosstab_Count.Sep, donations_Crosstab.Sep AS [No of Gifts Sept], ([donations_crosstab]![sep]/[donations_crosstab_count]![sep]) AS [Sept Average], donations_Crosstab_Count.Oct, donations_Crosstab.Oct AS [No of Gifts Oct], ([donations_crosstab]![oct]/[donations_crosstab_count]![oct]) AS [Oct Average], donations_Crosstab_Count.Nov, donations_Crosstab.Nov AS [No of Gifts Nov], ([donations_crosstab]![nov]/[donations_crosstab_count]![nov]) AS [Nov Average], donations_Crosstab_Count.Dec, donations_Crosstab.Dec AS [No of Gifts Dec], ([donations_crosstab]![dec]/[donations_crosstab_count]![dec]) AS [Dec Average]
    FROM (donations_Crosstab_Count INNER JOIN donations_Crosstab ON donations_Crosstab_Count.Area_code = donations_Crosstab.Area_code) INNER JOIN [area code] ON donations_Crosstab.Area_code = [area code].Area_code;

  6. #6
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (Access 97 Sr2)

    Try using much shorter names for the cross tab queries you create. Each time you are referencing a field e.g. donations_Crosstab_Count.Area_code you are having to use the "donations_Crosstab_Count" at the start of the field. Using shorter names gives a smaller query. I have found in the past that if I use too many characters in a query it goes wrong.
    John

Posting Permissions

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