Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    query (Access2000)

    I need to make a monthly report of packages, amount of letters received and amount of product sent out. I just made a simple query that used to work, but now I always get a message that says: "This expression was typed incorrectly or it is too complex to be evaluated". I have used this query for several months now and just now it gave me this problem. All I need is to know how many tracts, packages and letters per country. Can anyone help me? The SQL is below:

    SELECT Count([dataInternational Totals_test].Country) AS CountOfCountry, Sum([dataInternational Totals_test].<!t>[tract Totals]<!/t>) AS [SumOfTract Totals], Sum([dataInternational Totals_test].Packages) AS SumOfPackages, Sum([dataInternational Totals_test].Count) AS SumOfCount
    FROM [dataInternational Totals_test]
    ORDER BY Count([dataInternational Totals_test].Country);

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

    Re: query (Access2000)

    As it is, this query will return only one record, so the ORDER BY clause is useless. Are you sure the query didn't include a GROUP BY clause originally?

    SELECT [dataInternational Totals_test].Country, Count([dataInternational Totals_test].Country) AS CountOfCountry, Sum([dataInternational Totals_test].<!t>[tract Totals]<!/t>) AS [SumOfTract Totals], Sum([dataInternational Totals_test].Packages) AS SumOfPackages, Sum([dataInternational Totals_test].Count) AS SumOfCount
    FROM [dataInternational Totals_test]
    GROUP BY [dataInternational Totals_test].Country
    ORDER BY Count([dataInternational Totals_test].Country);

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: query (Access2000)

    I realized that I posted an SQL that I had done today as I was trying to get through this. I pulled the below SQL out of a backup and tested it and it still gives me the same message. Is the underscore "_1" a problem? That is not the name of my query and I don't remember making any duplicates of it. Any help would be appreciated. I'm learning Access.

    SELECT [dataInt Tract Totals_1].Country, Sum([dataInt Tract Totals_1].[SumOfTract Totals]) AS [SumOfSumOfTract Totals], Sum([dataInt Tract Totals_1].Packages) AS SumOfPackages, Sum([dataInt Tract Totals_1].Count) AS SumOfCount
    FROM [dataInt Tract Totals] AS [dataInt Tract Totals_1]
    GROUP BY [dataInt Tract Totals_1].Country;

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

    Re: query (Access2000)

    The name dataInt Tract Totals_1 is an alias for dataInt Tract Totals. You can see/modify this as follows:
    - Open the query in design view.
    - Click on the field list in the upper portion of the query design window.
    - Activate the Properties windows (View | Properties)
    - You will see the Alias property.
    Access will assign an alias with _1 if you add two copies of the same table/query to the upper portion of the query design window. Perhaps you inadvertently did this, then deleted the original copy. It should have no negative effect on the functioning of the query, though.

    The only thing that makes me suspicious is the use of Count as a field name. This is not a good idea, since Count is also a built-in function; in general, you should avoid using reserved words as field names. But in a test, it does not seem to be a problem. You might try putting the field name in brackets: Sum([dataInt Tract Totals_1].[Count])

Posting Permissions

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