Sponsor Message:
Non Aviation Forum
My Starred Topics | Profile | New Topic | Forum Index | Help | Search 
SQL Help!  
User currently offlineSevenair From United Kingdom, joined Feb 2001, 1728 posts, RR: 0
Posted (9 years 8 months 3 weeks 5 days 13 hours ago) and read 728 times:

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


16 replies: All unread, jump to last
 
User currently offlineKlaus From Germany, joined Jul 2001, 21415 posts, RR: 54
Reply 1, posted (9 years 8 months 3 weeks 5 days 13 hours ago) and read 715 times:

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!


User currently offlineGKirk From UK - Scotland, joined Jun 2000, 24913 posts, RR: 56
Reply 2, posted (9 years 8 months 3 weeks 5 days 12 hours ago) and read 707 times:

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!
User currently offlineKlaus From Germany, joined Jul 2001, 21415 posts, RR: 54
Reply 3, posted (9 years 8 months 3 weeks 5 days 12 hours ago) and read 705 times:

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


User currently offlineGKirk From UK - Scotland, joined Jun 2000, 24913 posts, RR: 56
Reply 4, posted (9 years 8 months 3 weeks 5 days 12 hours ago) and read 701 times:

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!
User currently offlineKlaus From Germany, joined Jul 2001, 21415 posts, RR: 54
Reply 5, posted (9 years 8 months 3 weeks 5 days 12 hours ago) and read 699 times:

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


User currently offlineGKirk From UK - Scotland, joined Jun 2000, 24913 posts, RR: 56
Reply 6, posted (9 years 8 months 3 weeks 5 days 12 hours ago) and read 696 times:

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!
User currently offlineKlaus From Germany, joined Jul 2001, 21415 posts, RR: 54
Reply 7, posted (9 years 8 months 3 weeks 5 days 12 hours ago) and read 694 times:

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

User currently offlineGKirk From UK - Scotland, joined Jun 2000, 24913 posts, RR: 56
Reply 8, posted (9 years 8 months 3 weeks 5 days 12 hours ago) and read 692 times:

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!
User currently offlineJoge From Finland, joined Feb 2000, 1444 posts, RR: 40
Reply 9, posted (9 years 8 months 3 weeks 5 days 12 hours ago) and read 693 times:

How about...

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


No need to use aliases in this case.

-Joge



Bula!
User currently offlineKlaus From Germany, joined Jul 2001, 21415 posts, RR: 54
Reply 10, posted (9 years 8 months 3 weeks 5 days 12 hours ago) and read 689 times:

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.


User currently offlineGordonsmall From UK - Scotland, joined Jun 2001, 2100 posts, RR: 22
Reply 11, posted (9 years 8 months 3 weeks 5 days 11 hours ago) and read 680 times:

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.
User currently offlineDamirc From Slovenia, joined Feb 2004, 724 posts, RR: 7
Reply 12, posted (9 years 8 months 3 weeks 5 days 7 hours ago) and read 663 times:

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.


User currently offlineJoge From Finland, joined Feb 2000, 1444 posts, RR: 40
Reply 13, posted (9 years 8 months 3 weeks 5 days 7 hours ago) and read 659 times:

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!
User currently offlineGordonsmall From UK - Scotland, joined Jun 2001, 2100 posts, RR: 22
Reply 14, posted (9 years 8 months 3 weeks 3 days 15 hours ago) and read 637 times:

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.
User currently offlineSaint-Exupery From Switzerland, joined Mar 2001, 47 posts, RR: 0
Reply 15, posted (9 years 8 months 3 weeks 3 days 14 hours ago) and read 636 times:

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.


User currently offlineDamirc From Slovenia, joined Feb 2004, 724 posts, RR: 7
Reply 16, posted (9 years 8 months 2 weeks 6 days 9 hours ago) and read 617 times:

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]

Top Of Page
Forum Index

This topic is archived and can not be replied to any more.

Printer friendly format

Similar topics:More similar topics...
Help Finding Egyptian/Arabic DVDs posted Thu Dec 7 2006 19:47:58 by RJpieces
Song Help posted Mon Dec 4 2006 02:15:53 by JBLUA320
Help With Noise Cancelling Headphones... posted Tue Nov 28 2006 18:08:47 by Buck3y3nut
Vista Help posted Sun Nov 26 2006 16:50:59 by Walter747
Native Spanish Speakers: I Need Help posted Sun Nov 19 2006 05:48:43 by Tsaord
Help There Is A Bear In My Yard posted Fri Nov 17 2006 02:31:50 by Charger
I Need Help With Copyrights And Radio Broadcasts posted Thu Nov 16 2006 23:01:51 by FlyingNanook
Chelsea FC Ticket Help posted Tue Nov 14 2006 03:50:35 by Phoenixflyer
Computer Help Needed posted Sun Nov 12 2006 04:02:39 by VonRichtofen
German Poster Help, Please posted Fri Nov 10 2006 05:12:07 by Alitalia777