Query hangs, why?

Ö

Özden Irmak

Hello,

I'm trying to compare to table datas and get the equal rows. I've linked
those two tables onto a third database and run this query :

"Select [Source_tblAdverse_Actions].[AA_Priority] AS
[AA_Priority],[Source_tblAdverse_Actions].[Adv_Act_ID] AS
[Adv_Act_ID],[Source_tblAdverse_Actions].[Adverse_Action_ID] AS
[Adverse_Action_ID],[Source_tblAdverse_Actions].[Application_ID] AS
[Application_ID] FROM
[Source_tblAdverse_Actions],[Destination_tblAdverse_Actions] WHERE
[Source_tblAdverse_Actions].[AA_Priority] &
[Source_tblAdverse_Actions].[Adv_Act_ID] &
[Source_tblAdverse_Actions].[Adverse_Action_ID] &
[Source_tblAdverse_Actions].[Application_ID]=[Destination_tblAdverse_Actions].[AA_Priority]
& [Destination_tblAdverse_Actions].[Adv_Act_ID] &
[Destination_tblAdverse_Actions].[Adverse_Action_ID] &
[Destination_tblAdverse_Actions].[Application_ID]"

But this query never ends, believe me even a day long. Both of the tables
contain around 30000 records. What is wrong?

Database is Access 2000 and are on my local computer.

Anybody can help?

Regards,

Özden
 
B

Brian

Özden Irmak said:
Hello,

I'm trying to compare to table datas and get the equal rows. I've linked
those two tables onto a third database and run this query :

"Select [Source_tblAdverse_Actions].[AA_Priority] AS
[AA_Priority],[Source_tblAdverse_Actions].[Adv_Act_ID] AS
[Adv_Act_ID],[Source_tblAdverse_Actions].[Adverse_Action_ID] AS
[Adverse_Action_ID],[Source_tblAdverse_Actions].[Application_ID] AS
[Application_ID] FROM
[Source_tblAdverse_Actions],[Destination_tblAdverse_Actions] WHERE
[Source_tblAdverse_Actions].[AA_Priority] &
[Source_tblAdverse_Actions].[Adv_Act_ID] &
[Source_tblAdverse_Actions].[Adverse_Action_ID] &
[Source_tblAdverse_Actions].[Application_ID]=[Destination_tblAdverse_Actions
].[AA_Priority]
& [Destination_tblAdverse_Actions].[Adv_Act_ID] &
[Destination_tblAdverse_Actions].[Adverse_Action_ID] &
[Destination_tblAdverse_Actions].[Application_ID]"

But this query never ends, believe me even a day long. Both of the tables
contain around 30000 records. What is wrong?

Database is Access 2000 and are on my local computer.

Anybody can help?

Regards,

Özden
Click to expand...

I have no idea what you mean about a third database, but it might work
better if you joined the tables properly, like this:

Select [Source_tblAdverse_Actions].[AA_Priority] AS
[AA_Priority],[Source_tblAdverse_Actions].[Adv_Act_ID] AS
[Adv_Act_ID],[Source_tblAdverse_Actions].[Adverse_Action_ID] AS
[Adverse_Action_ID],[Source_tblAdverse_Actions].[Application_ID] AS
[Application_ID] FROM
[Source_tblAdverse_Actions] INNER JOIN [Destination_tblAdverse_Actions] ON
([Source_tblAdverse_Actions].[AA_Priority] =
[Destination_tblAdverse_Actions].[AA_Priority] AND
[Source_tblAdverse_Actions].[Adv_Act_ID] =
[Destination_tblAdverse_Actions].[Adv_Act_ID] AND
[Source_tblAdverse_Actions].[Adverse_Action_ID] = [Destination_tblAdverse_
Actions].[Adverse_Action_ID]
[Source_tblAdverse_Actions].[Application_ID] =
[Destination_tblAdverse_Actions].[Application_ID])

Also, make sure that all of the joining fields are indexed.
 
D

Douglas J. Steele

Brian said:
Özden Irmak said:
Hello,

I'm trying to compare to table datas and get the equal rows. I've linked
those two tables onto a third database and run this query :

"Select [Source_tblAdverse_Actions].[AA_Priority] AS
[AA_Priority],[Source_tblAdverse_Actions].[Adv_Act_ID] AS
[Adv_Act_ID],[Source_tblAdverse_Actions].[Adverse_Action_ID] AS
[Adverse_Action_ID],[Source_tblAdverse_Actions].[Application_ID] AS
[Application_ID] FROM
[Source_tblAdverse_Actions],[Destination_tblAdverse_Actions] WHERE
[Source_tblAdverse_Actions].[AA_Priority] &
[Source_tblAdverse_Actions].[Adv_Act_ID] &
[Source_tblAdverse_Actions].[Adverse_Action_ID] &
[Source_tblAdverse_Actions].[Application_ID]=[Destination_tblAdverse_Actions
].[AA_Priority]
& [Destination_tblAdverse_Actions].[Adv_Act_ID] &
[Destination_tblAdverse_Actions].[Adverse_Action_ID] &
[Destination_tblAdverse_Actions].[Application_ID]"

But this query never ends, believe me even a day long. Both of the tables
contain around 30000 records. What is wrong?

Database is Access 2000 and are on my local computer.

Anybody can help?

Regards,

Özden
Click to expand...

I have no idea what you mean about a third database, but it might work
better if you joined the tables properly, like this:

Select [Source_tblAdverse_Actions].[AA_Priority] AS
[AA_Priority],[Source_tblAdverse_Actions].[Adv_Act_ID] AS
[Adv_Act_ID],[Source_tblAdverse_Actions].[Adverse_Action_ID] AS
[Adverse_Action_ID],[Source_tblAdverse_Actions].[Application_ID] AS
[Application_ID] FROM
[Source_tblAdverse_Actions] INNER JOIN [Destination_tblAdverse_Actions] ON
([Source_tblAdverse_Actions].[AA_Priority] =
[Destination_tblAdverse_Actions].[AA_Priority] AND
[Source_tblAdverse_Actions].[Adv_Act_ID] =
[Destination_tblAdverse_Actions].[Adv_Act_ID] AND
[Source_tblAdverse_Actions].[Adverse_Action_ID] =
[Destination_tblAdverse_
Actions].[Adverse_Action_ID]
[Source_tblAdverse_Actions].[Application_ID] =
[Destination_tblAdverse_Actions].[Application_ID])

Also, make sure that all of the joining fields are indexed.
Click to expand...
I'd agree with Brian's assessment. If it's possible that the fields may
contain Null values, try:

Select [Source_tblAdverse_Actions].[AA_Priority] AS
[AA_Priority],[Source_tblAdverse_Actions].[Adv_Act_ID] AS
[Adv_Act_ID],[Source_tblAdverse_Actions].[Adverse_Action_ID] AS
[Adverse_Action_ID],[Source_tblAdverse_Actions].[Application_ID] AS
[Application_ID] FROM
[Source_tblAdverse_Actions] INNER JOIN [Destination_tblAdverse_Actions] ON
Nz([Source_tblAdverse_Actions].[AA_Priority]) =
Nz([Destination_tblAdverse_Actions].[AA_Priority]) AND
Nz([Source_tblAdverse_Actions].[Adv_Act_ID],0) =
Nz([Destination_tblAdverse_Actions].[Adv_Act_ID],0) AND
Nz([Source_tblAdverse_Actions].[Adverse_Action_ID],0) =
Nz([Destination_tblAdverse_
Actions].[Adverse_Action_ID],0) AND
Nz([Source_tblAdverse_Actions].[Application_ID],0) =
Nz([Destination_tblAdverse_Actions].[Application_ID]),0)
 
Ö

Özden Irmak

Hello,

Thank you for your responses...

Sorry for the not clear description...In short, there are two same tables
(in structure) from two separate databases linked onto a third database and
I'm running this query on this third database...

As you suggested joining, I noticed that joining do not work on OLE,
HyperLink and Memo fields? I can understand this for OLE and HyperLink but I
somehow should do this in Memo fileds, any suggestion for this also?

Thanks in advance,

Özden

Douglas J. Steele said:
Brian said:
Özden Irmak said:
Hello,

I'm trying to compare to table datas and get the equal rows. I've linked
those two tables onto a third database and run this query :

"Select [Source_tblAdverse_Actions].[AA_Priority] AS
[AA_Priority],[Source_tblAdverse_Actions].[Adv_Act_ID] AS
[Adv_Act_ID],[Source_tblAdverse_Actions].[Adverse_Action_ID] AS
[Adverse_Action_ID],[Source_tblAdverse_Actions].[Application_ID] AS
[Application_ID] FROM
[Source_tblAdverse_Actions],[Destination_tblAdverse_Actions] WHERE
[Source_tblAdverse_Actions].[AA_Priority] &
[Source_tblAdverse_Actions].[Adv_Act_ID] &
[Source_tblAdverse_Actions].[Adverse_Action_ID] &
[Source_tblAdverse_Actions].[Application_ID]=[Destination_tblAdverse_Actions
].[AA_Priority]
& [Destination_tblAdverse_Actions].[Adv_Act_ID] &
[Destination_tblAdverse_Actions].[Adverse_Action_ID] &
[Destination_tblAdverse_Actions].[Application_ID]"

But this query never ends, believe me even a day long. Both of the
tables
contain around 30000 records. What is wrong?

Database is Access 2000 and are on my local computer.

Anybody can help?

Regards,

Özden
Click to expand...

I have no idea what you mean about a third database, but it might work
better if you joined the tables properly, like this:

Select [Source_tblAdverse_Actions].[AA_Priority] AS
[AA_Priority],[Source_tblAdverse_Actions].[Adv_Act_ID] AS
[Adv_Act_ID],[Source_tblAdverse_Actions].[Adverse_Action_ID] AS
[Adverse_Action_ID],[Source_tblAdverse_Actions].[Application_ID] AS
[Application_ID] FROM
[Source_tblAdverse_Actions] INNER JOIN [Destination_tblAdverse_Actions]
ON
([Source_tblAdverse_Actions].[AA_Priority] =
[Destination_tblAdverse_Actions].[AA_Priority] AND
[Source_tblAdverse_Actions].[Adv_Act_ID] =
[Destination_tblAdverse_Actions].[Adv_Act_ID] AND
[Source_tblAdverse_Actions].[Adverse_Action_ID] =
[Destination_tblAdverse_
Actions].[Adverse_Action_ID]
[Source_tblAdverse_Actions].[Application_ID] =
[Destination_tblAdverse_Actions].[Application_ID])

Also, make sure that all of the joining fields are indexed.
Click to expand...
I'd agree with Brian's assessment. If it's possible that the fields may
contain Null values, try:

Select [Source_tblAdverse_Actions].[AA_Priority] AS
[AA_Priority],[Source_tblAdverse_Actions].[Adv_Act_ID] AS
[Adv_Act_ID],[Source_tblAdverse_Actions].[Adverse_Action_ID] AS
[Adverse_Action_ID],[Source_tblAdverse_Actions].[Application_ID] AS
[Application_ID] FROM
[Source_tblAdverse_Actions] INNER JOIN [Destination_tblAdverse_Actions] ON
Nz([Source_tblAdverse_Actions].[AA_Priority]) =
Nz([Destination_tblAdverse_Actions].[AA_Priority]) AND
Nz([Source_tblAdverse_Actions].[Adv_Act_ID],0) =
Nz([Destination_tblAdverse_Actions].[Adv_Act_ID],0) AND
Nz([Source_tblAdverse_Actions].[Adverse_Action_ID],0) =
Nz([Destination_tblAdverse_
Actions].[Adverse_Action_ID],0) AND
Nz([Source_tblAdverse_Actions].[Application_ID],0) =
Nz([Destination_tblAdverse_Actions].[Application_ID]),0)
Click to expand...
 
B

Brian

Özden Irmak said:
Hello,

Thank you for your responses...

Sorry for the not clear description...In short, there are two same tables
(in structure) from two separate databases linked onto a third database and
I'm running this query on this third database...

As you suggested joining, I noticed that joining do not work on OLE,
HyperLink and Memo fields? I can understand this for OLE and HyperLink but I
somehow should do this in Memo fileds, any suggestion for this also?

Thanks in advance,

Özden
Click to expand...

Because a memo field can hold up to 1 Gb data, and as such is totally
unsuitable for a key field/joins.
 
Top