I know that mine executes in far less than that, using an XML with approximately the same size.

Are you doing the checking against the XML, and then also doing the...

You might try explicitly qualifying your columns with table names.

My guess, looking at this, is that you are ordering on a column from the left joined table. That could...

As a note, using the upsizing wizard to change your database to an ADP is nice ONLY to get an idea of what kinds of changes you'll need to do.

But,...

There are legitimate reasons for using an MDB over an ADP.

1) Local data storage. If you're using an ADP, all data must be stored in the SQL Server....

MS is looking to phase out Access altogether. Its architecture is largely obsolete, and doesn't work well with either SQL Server or .NET. My money is on them...

Also, if you must have non-numeric characters, the char datatype is going to be better for joining than varchar. Assuming that SessionID values are pretty much all the same...

I'm presuming that it is the identity column on the Transactions table that is the primary key in question?

When you "no more records can be inserted," do you mean inserted...

Sometimes yes, and sometimes no. It depends a great deal on how complicated the query is. The query optimizer is a strange little beast, that seems to operate...

Use the left and first instance method, on the REVERSE of the string.

stax68, the performance somewhat depends on where this will be used. Since it sounds like a flag during the order process, or when a manager is reviewing the order,...

I'd go with Brent's suggestion of breaking the TOP 5 bit out into a separate view. Depending on other factors, possibly even an indexed view.

Incidentally, I also noticed in...

You've got several things backwards here.

Sales:

Why is mobileNo in here? I would think that this would either be in a separate table (if it is used like an account...

A composite key is a primary key. It's a primary key made up of more than one field.

Wow. So many comments come to mind.

First of all, nested queries do not necessarily imply poor design. However, it is entirely possible that you do have poor design,...

I'm more or less with SQLBill on this one. The syntax differences between Access and SQL Server are pretty huge. Tracking down a syntax error when you first...