sevenair
Posts: 1496
Joined: Sun Feb 04, 2001 7:18 am

SQL Help!

Mon Nov 08, 2004 8:38 pm

Hey, im in the process of making a stock control system in MS Access, and need to make a query to altert me when the level of stock falls below the order point. I have tried the following statement:


SELECT Product.InStock, Product.ProductID, Product.Name, Product.Description, Product.OrderPoint
FROM Product
WHERE (((Product.InStock)<"Product.OrderPoint"));


Yet it comes up with a data type mismatch in the expression. I have checked the table (Product) and both of the feilds I am using are of variable type 'Number'. Ive looked in all my books, but I just can't work this out!

Is it just not possible to compare one field to another? Or should I be saying 'when InStock is less than '10'. I want a query which alerts me as to when items are below their order point, and thought all this would require is to compare the two fields. ANy help is much appreciated  Smile/happy/getting dizzy
 
Klaus
Posts: 20622
Joined: Wed Jul 11, 2001 7:41 am

RE: SQL Help!

Mon Nov 08, 2004 9:09 pm

Lose the quotes around Product.OrderPoint; As you have it, it tries to compare the value of Product.InStock with the string "Product.OrderPoint", not with the value of Product.OrderPoint.

You can also get rid of all but the outermost parentheses.

Good luck!
 
gkirk
Posts: 23347
Joined: Thu Jun 15, 2000 3:29 am

RE: SQL Help!

Mon Nov 08, 2004 9:22 pm

Was going to say virtually the same as Klaus.

So, your system warns you when the stock number falls below a certain number. I take it when it actually hits this number, it places an automatic order for that stock, or do you have to order it manually?
When you hear the noise of the Tartan Army Boys, we'll be coming down the road!
 
Klaus
Posts: 20622
Joined: Wed Jul 11, 2001 7:41 am

GKirk

Mon Nov 08, 2004 9:31 pm

GKirk: Was going to say virtually the same as Klaus.

I´m shocked and horrified!  Big thumbs up
How could that happen?  Wink/being sarcastic
 
gkirk
Posts: 23347
Joined: Thu Jun 15, 2000 3:29 am

RE: SQL Help!

Mon Nov 08, 2004 9:36 pm

Klaus, even I have done a wee bit of SQL at University  Wink/being sarcastic
When you hear the noise of the Tartan Army Boys, we'll be coming down the road!
 
Klaus
Posts: 20622
Joined: Wed Jul 11, 2001 7:41 am

GKirk

Mon Nov 08, 2004 9:41 pm

GKirk: Klaus, even I have done a wee bit of SQL at University

Don´t doubt your expertise... I´m just perplexed by this blatant demonstration of assent... Big grin
 
gkirk
Posts: 23347
Joined: Thu Jun 15, 2000 3:29 am

RE: SQL Help!

Mon Nov 08, 2004 9:42 pm

Actually, I dont know much, just enough to agree with you on this one  Big grin Will have to get the books back out  Wink/being sarcastic
When you hear the noise of the Tartan Army Boys, we'll be coming down the road!
 
Klaus
Posts: 20622
Joined: Wed Jul 11, 2001 7:41 am

GKirk

Mon Nov 08, 2004 9:46 pm

It just looks as if peace broke out across the north sea... It could get time again for the next round of Euro in Britain soon... Big grin
 
gkirk
Posts: 23347
Joined: Thu Jun 15, 2000 3:29 am

RE: SQL Help!

Mon Nov 08, 2004 9:50 pm

Wheesht Klaus you fool!
Dirty Diving German  Wink/being sarcastic
Can't have peace  Laugh out loud
When you hear the noise of the Tartan Army Boys, we'll be coming down the road!
 
Joge
Posts: 1386
Joined: Tue Feb 22, 2000 3:26 am

RE: SQL Help!

Mon Nov 08, 2004 9:50 pm

How about...

SELECT InStock, ProductID, Name, Description, OrderPoint
FROM Product
WHERE InStock < OrderPoint;


No need to use aliases in this case.

-Joge
Bula!
 
Klaus
Posts: 20622
Joined: Wed Jul 11, 2001 7:41 am

Joge

Mon Nov 08, 2004 10:06 pm

Joge: No need to use aliases in this case.

No aliases... just fully qualified column names. But you´re right; They´re not necessary in this simple case.
 
gordonsmall
Posts: 2106
Joined: Sun Jul 01, 2001 1:52 am

RE: SQL Help!

Mon Nov 08, 2004 11:10 pm

I'm questioning why you would want to do it with an SQL statement anyway - do that in a production environment and you're likely to get your testicles bitten off by the database admin.

Calculations in SQL are not a good idea, very cycle intensive, use a script in the native environment language instead, in your case VBA in access.

Regards,
Gordon.
Statistically, people who have had the most birthdays tend to live the longest.
 
damirc
Posts: 726
Joined: Fri Feb 13, 2004 8:43 am

RE: SQL Help!

Tue Nov 09, 2004 2:40 am

Gordonsmall, eh?

Access != SQL (it uses SQL syntax, but it's a single user, single instance database). The DBA wouldn't bite anyone's testicles off for running such a query. It's simple, and any RDBMS will sweep through it. What do you suggest then? Selecting everything and then using logic on the application level? Might as well use a flat text file then.

Reminds me of a former coworker who instead of SELECT SUM(blah) ... used cursors on MSSQL to walk through all records and manually added everything together. No need to say he didn't last very long.

D.
 
Joge
Posts: 1386
Joined: Tue Feb 22, 2000 3:26 am

RE: SQL Help!

Tue Nov 09, 2004 2:55 am

used cursors on MSSQL to walk through all records and manually added everything together

OMG!  Laugh out loud

Damirc is right, however. In case you want to use a (real) DB for an application, you should go on with an RDBMS, like MySQL or InterBase. They are both free and good for a start at least. Newest MySQL supports views too. If you want to pay some £££ for the MSSQL or Oracle you'll also get stored procedures and some other benefits.  Smile

-Joge
Bula!
 
gordonsmall
Posts: 2106
Joined: Sun Jul 01, 2001 1:52 am

RE: SQL Help!

Wed Nov 10, 2004 6:56 pm

Damirc,

Firstly, read my post properly before you spout bullshit about access in my direction!

Secondly, how about you post some benchmark figures on your claimed "sweeping" through anything. SQL level mathematics and comparisons are all well and good on a database with only a few thousand records, but a database with millions, or in the case of a project I've spent the last few months working on with over 17 billion records occupying 170 terrabytes of storage, using SQL for the comparisons would have brought the whole thing crashing down. The only logical solution was to bring the data client side before performing any straight mathematical comparisons, and even then the database servers (all 780 of them) were struggling to cope.

No DBA in his right mind is going to let you perform a recursive and/or mathematical calculation using an SQL statement on a large scale database of more than a few million records where the statement is executed on a continual basis (unless he's got more processing power at his disposal than Nasa of course  Smile/happy/getting dizzy), when you can simply use a select on the required subset of data, bring it client side and perform the logic there using at least half the processor cycles on the server and better balance the load. Admittedly it's a pain in the arse to do for the programmer concerned, and network traffic increases by a massive factor but sadly we all live in the real world, not the fluffy bunny scenario that academics seem to think we live in so, as I would say to any member of my team who tried to argue the toss over something as trivial as a few hundred extra lines of code, tough shit.

Regards,
Gordon.
Statistically, people who have had the most birthdays tend to live the longest.
 
Saint-Exupery
Posts: 47
Joined: Tue Mar 13, 2001 10:37 pm

RE: SQL Help!

Wed Nov 10, 2004 8:08 pm

Gordon
I can't see another way to solve the problem as to use the where-clause as sevenair tried to define. First of all, this comparison is the only selection criteria. Do you want to load all data inside your 170 terrabites of memory you have available on your workstation and then compare?
Second point is, as I understand, the comparison occurs between two fields in the same record. Not that bad performance-wise as if he would create a recursive comparison. And I can't see a mathematical function either.
 
damirc
Posts: 726
Joined: Fri Feb 13, 2004 8:43 am

RE: SQL Help!

Mon Nov 15, 2004 12:50 am

Gordon...

... we are talking about Access - which does suggest that he doesn't plan to keep a Database in it that is in the range of Terabytes, but rather (if so) Megabytes ...

And about such a simple query bringing down your SQL cluster. If such a simple SQL query brings down your SQL server cluster - then I'm afraid to say, you have either something misconfigured or have never heard about indexing. Some of my recent SQL related work included queries, that were quite a bit more complex and didn't run a simple SQL server (dual Xeon) with an 800 Megabyte database into the ground (while it was still serving users and adding new records).

D.

[Edited 2004-11-14 16:55:07]

Who is online

Users browsing this forum: afterburner, Baidu [Spider], zanl188 and 12 guests