Problem Converting Access 2003 Query to SQL 2

[d222222]

I have a query that we use in Access 97 and upgraded to Access 2003 and works fine. Now we want to run them using a SQL database but an Access 2003 frontend. When I open the new query in Access 2003 it takes close to an hour to run but when I run the same query in the Access 2003 database, it finishes in a short amount of time.

Here is the query:

SELECT DISTINCT dbo_FundBalances.BBA_FUND AS Fund, dbo_FundBalances.FirstOfFUN_NAME AS Description, Sum((IIf([LED_ACCT_TYPE]="31" And ChangeToFiscalYear([LED_RECORD_DATE])=ChangeToFiscalYear(Forms!Reports!txtDate) And [LED_RECORD_DATE]<=Forms!Reports!txtDate,[LED_DOLLAR_AMT],0))) AS Income, Sum((IIf([LED_ACCT_TYPE]="22" And ChangeToFiscalYear([LED_RECORD_DATE])=ChangeToFiscalYear(Forms!Reports!txtDate) And [LED_RECORD_DATE]<=Forms!Reports!txtDate,[LED_DOLLAR_AMT],0))) AS Expenditures
FROM dbo_FundBalances, dbo_VLED
GROUP BY dbo_FundBalances.BBA_FUND, dbo_FundBalances.FirstOfFUN_NAME;

In Access it used DISTINCTROW but in researching the problem I discovered that SQL uses DISTINCT so I tried that and it still doesn't work.

If I remove the GROUP BY it works in a short time but we need the GROUP BY if possible.

Does anyone know how to convert this so that it works?


Thanks.

 

[gmmastros]

Amazing. All you needed to do was change DISTINCTROW to DISTINCT and it ran without errors!

I would have that that other things would have caused problems too. Like [!]IIF[/!], and referencing form fields Forms!Reports!txtDate, and calling those function ChangeToFiscalYear.

Also... I notice there is no join between the tables, and no where clause. Written this way, you end up with a cross join, which is probably returning way more rows than you want/need.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[d222222]

No, it's not working since I changed the DISTINCTROW to DISTINCT. I still have the problem.

I have form fields in other queries and they work OK so that's not the problem. I will look and see if any of them are using IIF.

Looking at the history of the query there was a JOIN at one time but it was removed. I will play with that too.

Anyone else have any suggestions?

 

[d222222]

Since it works without the GROUP BY I was wondering how to get it to work with it.

 

[SQLSister]

I'm with George, it is likely that your query has never worked properly.

When you have a SQL Server back end, it is best to use stored procedures to get the data not Access queries. Access queries have to be translated by the AccessJet engine to SQL Server t-sql in order to run properly which slows them down. Better to learn to properly query SQL Server.

IIF does not work in SQL Server and the replacement is the CASE statment. You also cannot directly refer to a form field value, you need to convert it into a variable and then use it as an input value to the stored proc.

And never write another join using that syntax. What it is doing is joining every record in the first table to every record in the second table. If you had 100 rows in the first table and 100 rows in the second table, the cross join would create 100X100 (10,000) rows in the result set before using the distinct. Now imagine if you had 10 million rows in one table and 40,000 rows in the the other.

And if you convert to a stored proc, you will need to make the function work as a SQL Server function instead of an Access function. But truly it is better for performance to avoid functions altogether. Depending on what the function does, that may be possible.


"NOTHING is more important in a database than integrity." ESquared

 

[d222222]

Thanks for everyone's help. I am new at using Access and trying to run it through SQL is definitely challenging. I will definitely take all of your advice into consideration.

After seeing qmmastros comment about the JOIN I decided to try adding the JOIN back in that was once there and it worked. However after seeing SQLSister's warning about using a JOIN I will see if we can figure out another way to do it.

Thank you both for your responses.

 

[SQLSister]

No, you need to use an inner join , what you have right now is a cross join. There is an FAQ which can help you learn joins

http://www.tek-tips.com/faqs.cfm?fid=4785

Joins are fundametal to querying databases and you need a thorough understanding of them before you do anything else.

Another useful article for someone just begining to do database work is below:

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

It would also help you probably to read through many of the FAQs on this site and things like the WIKI on SQL Server at lessthandot.com

"NOTHING is more important in a database than integrity." ESquared

 

[d222222]

Thank you for all of the references. I will read up on everything.

I added a "right join" and that's what got it to work.

No wonder it didn't work if it was doing a cross join and creating so many records.

I made a mistake in my original post. It didn't work in Access 2003 but it worked in Access 97.


Thanks!