query (Access2000)

Topic

New Reply

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].[tract Totals]) 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);

Reply | Quote

Replies

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].[tract Totals]) 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);

Reply | Quote

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;

Reply | Quote

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])

Reply | Quote

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])

Reply | Quote

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;

Reply | Quote

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].[tract Totals]) 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);

Reply | Quote