very slow "not matching query"

[Guest]

I have a "not matching query" that displays the rows in the "customersupport"
table taht are non existent in the"customersupport2"..."customersupport" is
a table linked to the inbox of outlook.
The query is running very slowly,could any body help mme to make it run
faster?

Regards

 

[Guest]

There's only one practical way for us to know: Show us the SQL. Open the
query in design view. Next go to View, SQL View and copy and past it here.
Information on primary keys and relationships would be a nice touch too.

Of course a table linked to Outlook can't be indexed or use some of the
other tricks used to speed things up.

You might just have to live with it or throw hardward at it if your computer
is old.

 

[Guest]

Here's the SQL code:
SELECT customersupport.Importance, customersupport.Icon,
customersupport.Priority, customersupport.Subject, customersupport.[Sender
Name], customersupport.Received, customersupport.From,
customersupport.[Message To Me], customersupport.[Message CC to Me],
customersupport.CC, customersupport.To, customersupport.[Message Size],
customersupport.[Normalized Subject], customersupport.Contents,
customersupport2.Serial, customersupport2.sr, customersupport2.gdate,
customersupport2.Status, customersupport2.[Replaying Date],
customersupport2.Language, customersupport2.[Service level],
customersupport2.[Receiving date], customersupport2.Sender,
customersupport2.[E-mail], customersupport2.Subject,
customersupport2.[Original message], customersupport2.Pick,
customersupport2.[Customer type], customersupport2.Agent,
customersupport2.Answer, customersupport2.Time, customersupport2.Comments,
customersupport2.Type, customersupport2.[Receiving Method],
customersupport2.AHT, customersupport2.Month2

FROM customersupport LEFT JOIN customersupport2 ON customersupport.Received
= customersupport2.gdate

WHERE (((customersupport.Received)>Date()-3) AND ((customersupport2.gdate)
Is Null));

 

[Guest]

As the customersupport table is linked to Outlook, you can't index the
Received field which could help. An option would be to import the Outlook
data every so often using a Timer event of a form and have the Received field
indexed in the Access table. That would have the second advantage of having
the data inside Access too. You could manually import the data once for
testing an see if that helps.

Other than that make sure that the gdate field is indexed. It's possible
that having a default value of something bogus like #1/1/1950# and searching
on that date could be more efficient than searching on nulls. You'd have to
update all the current nulls to the bogus date.

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Here's the SQL code:
SELECT customersupport.Importance, customersupport.Icon,
customersupport.Priority, customersupport.Subject, customersupport.[Sender
Name], customersupport.Received, customersupport.From,
customersupport.[Message To Me], customersupport.[Message CC to Me],
customersupport.CC, customersupport.To, customersupport.[Message Size],
customersupport.[Normalized Subject], customersupport.Contents,
customersupport2.Serial, customersupport2.sr, customersupport2.gdate,
customersupport2.Status, customersupport2.[Replaying Date],
customersupport2.Language, customersupport2.[Service level],
customersupport2.[Receiving date], customersupport2.Sender,
customersupport2.[E-mail], customersupport2.Subject,
customersupport2.[Original message], customersupport2.Pick,
customersupport2.[Customer type], customersupport2.Agent,
customersupport2.Answer, customersupport2.Time, customersupport2.Comments,
customersupport2.Type, customersupport2.[Receiving Method],
customersupport2.AHT, customersupport2.Month2

FROM customersupport LEFT JOIN customersupport2 ON customersupport.Received
= customersupport2.gdate

WHERE (((customersupport.Received)>Date()-3) AND ((customersupport2.gdate)
Is Null));

There's only one practical way for us to know: Show us the SQL. Open the
query in design view. Next go to View, SQL View and copy and past it here.
Information on primary keys and relationships would be a nice touch too.

Of course a table linked to Outlook can't be indexed or use some of the
other tricks used to speed things up.

You might just have to live with it or throw hardward at it if your computer
is old.

 

[Guest]

Thank you very much Jerry,

Could you tell me the code that i can run after clicking on a button to
import from the inbox of outlook to my database the new messages recieved
only?(because i cannot import all the data evetime,as ,for fear that i may
lose the data saved in other additional fields like "langauage","original
message"...ecc.

As the customersupport table is linked to Outlook, you can't index the
Received field which could help. An option would be to import the Outlook
data every so often using a Timer event of a form and have the Received field
indexed in the Access table. That would have the second advantage of having
the data inside Access too. You could manually import the data once for
testing an see if that helps.

Other than that make sure that the gdate field is indexed. It's possible
that having a default value of something bogus like #1/1/1950# and searching
on that date could be more efficient than searching on nulls. You'd have to
update all the current nulls to the bogus date.

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Here's the SQL code:
SELECT customersupport.Importance, customersupport.Icon,
customersupport.Priority, customersupport.Subject, customersupport.[Sender
Name], customersupport.Received, customersupport.From,
customersupport.[Message To Me], customersupport.[Message CC to Me],
customersupport.CC, customersupport.To, customersupport.[Message Size],
customersupport.[Normalized Subject], customersupport.Contents,
customersupport2.Serial, customersupport2.sr, customersupport2.gdate,
customersupport2.Status, customersupport2.[Replaying Date],
customersupport2.Language, customersupport2.[Service level],
customersupport2.[Receiving date], customersupport2.Sender,
customersupport2.[E-mail], customersupport2.Subject,
customersupport2.[Original message], customersupport2.Pick,
customersupport2.[Customer type], customersupport2.Agent,
customersupport2.Answer, customersupport2.Time, customersupport2.Comments,
customersupport2.Type, customersupport2.[Receiving Method],
customersupport2.AHT, customersupport2.Month2

FROM customersupport LEFT JOIN customersupport2 ON customersupport.Received
= customersupport2.gdate

WHERE (((customersupport.Received)>Date()-3) AND ((customersupport2.gdate)
Is Null));

There's only one practical way for us to know: Show us the SQL. Open the
query in design view. Next go to View, SQL View and copy and past it here.
Information on primary keys and relationships would be a nice touch too.

Of course a table linked to Outlook can't be indexed or use some of the
other tricks used to speed things up.

You might just have to live with it or throw hardward at it if your computer
is old.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I have a "not matching query" that displays the rows in the "customersupport"
table taht are non existent in the"customersupport2"..."customersupport" is
a table linked to the inbox of outlook.
The query is running very slowly,could any body help mme to make it run
faster?

Regards

 

[Guest]

INSERT INTO customersupport2
SELECT customersupport.*
FROM customersupport
WHERE customersupport.Received > DMax("[Received]","customersupport2");

When I link to my Outlook Inbox, I couldn't see anything like a primary key.
I did see a Received Date/Time field. Assuming that it would be unusual for
two emails to be received in the same second, this might be a safe way to do
it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thank you very much Jerry,

Could you tell me the code that i can run after clicking on a button to
import from the inbox of outlook to my database the new messages recieved
only?(because i cannot import all the data evetime,as ,for fear that i may
lose the data saved in other additional fields like "langauage","original
message"...ecc.

As the customersupport table is linked to Outlook, you can't index the
Received field which could help. An option would be to import the Outlook
data every so often using a Timer event of a form and have the Received field
indexed in the Access table. That would have the second advantage of having
the data inside Access too. You could manually import the data once for
testing an see if that helps.

Other than that make sure that the gdate field is indexed. It's possible
that having a default value of something bogus like #1/1/1950# and searching
on that date could be more efficient than searching on nulls. You'd have to
update all the current nulls to the bogus date.

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Here's the SQL code:
SELECT customersupport.Importance, customersupport.Icon,
customersupport.Priority, customersupport.Subject, customersupport.[Sender
Name], customersupport.Received, customersupport.From,
customersupport.[Message To Me], customersupport.[Message CC to Me],
customersupport.CC, customersupport.To, customersupport.[Message Size],
customersupport.[Normalized Subject], customersupport.Contents,
customersupport2.Serial, customersupport2.sr, customersupport2.gdate,
customersupport2.Status, customersupport2.[Replaying Date],
customersupport2.Language, customersupport2.[Service level],
customersupport2.[Receiving date], customersupport2.Sender,
customersupport2.[E-mail], customersupport2.Subject,
customersupport2.[Original message], customersupport2.Pick,
customersupport2.[Customer type], customersupport2.Agent,
customersupport2.Answer, customersupport2.Time, customersupport2.Comments,
customersupport2.Type, customersupport2.[Receiving Method],
customersupport2.AHT, customersupport2.Month2

FROM customersupport LEFT JOIN customersupport2 ON customersupport.Received
= customersupport2.gdate

WHERE (((customersupport.Received)>Date()-3) AND ((customersupport2.gdate)
Is Null));



:

There's only one practical way for us to know: Show us the SQL. Open the
query in design view. Next go to View, SQL View and copy and past it here.
Information on primary keys and relationships would be a nice touch too.

Of course a table linked to Outlook can't be indexed or use some of the
other tricks used to speed things up.

You might just have to live with it or throw hardward at it if your computer
is old.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I have a "not matching query" that displays the rows in the "customersupport"
table taht are non existent in the"customersupport2"..."customersupport" is
a table linked to the inbox of outlook.
The query is running very slowly,could any body help mme to make it run
faster?

Regards