VBA or SQL query?

[mahmudich]

Hi everyone!

I wrote not a small query and as my database growing it takes more and more time to get a result out of it. Right now it takes about 3 minutes, some people may say - it's cool...you've got a good reason for a coffee break, but I'm afraid that in a few month I'll be able to take a holydays. Anyway, the question is would it be faster if I use VBA code instead just pure SQL query, I doubt it, but maybe I’m wrong.

Thank you.

 

[sonny]

I've seen many replies about this were everyone says sql is slower...

 

[Rich]

A stored query is faster than vba. Why don't you give some details of your structure etc

 

[mahmudich]

Hi Rich!

I didn’t risk to show the query before, because there is would be a big chance that nobody would read it. Anyway, I’ll try to explain what’s going on:
There are following tables:

tbl_Books
BookID (primary)
CategoryID
DiscPrice
Quantity

tbl_Categories
CategoryID (primary)
CategoryName

tbl_AgentWeeklyStockSale – (stores weekly sale of every agent excluding details)
AgentID (primary)
WeekID (primary)
WeeklyReportDetailsID
Totals

tbl_AgentWeeklySSDetails – (stores details of weekly sale of every agent)
WeeklyReportDetailsID (primary)
BookID
StockCurrent
New
Sold
Returned

The task is to select new collection of book titles for an agent, which he hasn’t had on his sale (“tblAgentWeeklyStock.Sold”) for the last 30 weeks. Quantity (tbl_Books.Quanitity) has to be > 0, Sale > 1 (“tblAgentWeeklyStock.Sold”), also agent has not taken (“tblAgentWeeklyStock.New” field) any new quantities of the book for the last 10 weeks; In the current example for simplicity AgentID=7, and current WeekID=200435.

SELECT DISTINCT [tbl_Categories].[CategoryID], [tbl_Categories].[CategoryName], [tbl_Books].[BookName], [tbl_Books].[Quantity], [tbl_Books].[DiscPrice], [tbl_Books].[BookID]
FROM tbl_Categories INNER JOIN (tbl_Books LEFT JOIN tbl_AgentWeeklySSDetails ON [tbl_Books].[BookID]=[tbl_AgentWeeklySSDetails].[BookID]) ON
[tbl_Categories].[CategoryID]=[tbl_Books].[CategoryID]
WHERE tbl_Books.Quantity>0 AND tbl_Books.BookID NOT IN

(SELECT BookID FROM tbl_AgentWeeklySSDetails
WHERE (New > 1 AND WeeklyReportDetailsID IN
(SELECT WeeklyReportDetailsID FROM tbl_AgentWeeklyStockSale WHERE AgentID = 7 AND WeekID > 200435 - 10))
OR (Sold > 1 AND WeeklyReportDetailsID IN
(SELECT WeeklyReportDetailsID FROM tbl_AgentWeeklyStockSale WHERE AgentID = 7 AND WeekID > 200435 -30 )))


Any comments welcome. Thank you

 

[mahmudich]

Hi Pat,

Thank you for taking time, but I think a few things won’t make this query work correctly:
Firs of all: WeekID and AgentID belong to tbl_AgentWeeklyStockSale, which is not included in the query right now. I tried to include it and got the following error:

“The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the fist join and then include that query in your SQL statement.”

This is why I had subselects in my query.

The second thing the query you wrote will do only half of work it supposed to. Because it will select BookIDs agent actually had, and we want the BookIDs from tbl_Books he has NOT had (in tbl_AgentWeeklySSDetails) where Sold>0 etc.
So the idea would be like:
Get all the BookIDs from tbl_Books which are NOT equal to ones from the query you wrote.

Thank you

 

[mahmudich]

Hi Pat, did I understand you correctly:

The structure of the SQL query would be like:

SELECT DISTINCT BookID FROM query_1 LEFT JOIN query_2 ON query_1.ID = query_2.ID WHERE query_1.BookID <> query_2.BookID

Thank you

 

[mahmudich]

Hi again,

Can anyone point me where the error is? Let’s say I have two queries.

q1 gives me these two columns:
ID name
1 mouse
2 cat
3 dog
4 bird

q2 gives me the following columns:
ID name
2 cat
3 dog

What I need is to get all names from q1 that don’t exist in q2, so here is:

SELECT q1.ID, q1.name
FROM q1 LEFT JOIN q2 ON q1.ID = q2.ID
WHERE ([q1].[ID]<>[q2].[ID]);

…and the result I get is nothing.

 

[Rich]

Have you tried the Unmatched Query Wizard?

 

[mahmudich]

You're champion!
But still, my solution was pretty logical,.... oh well.

Thank you.

 

[Rich]

Is it faster now?

 

[mahmudich]

It does the job in flash!

P.S. But it disappointing when you try to make one query instead of 3 and it does a bad job.

 

[Rich]

It does the job in flash!

Well done, now you've earnt a shorter break

 

[mahmudich]

...you're right: the moral of the story is don't try to be smart ass - or you'll get yourself in trouble.

now I don't have a break at all