Sevenair From United Kingdom, joined Feb 2001, 1728 posts, RR: 0 Posted (9 years 10 months 2 weeks 5 days 16 hours ago) and read 741 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
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
GKirk From UK - Scotland, joined Jun 2000, 24936 posts, RR: 56
Reply 2, posted (9 years 10 months 2 weeks 5 days 15 hours ago) and read 720 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!
Damirc From Slovenia, joined Feb 2004, 726 posts, RR: 7
Reply 12, posted (9 years 10 months 2 weeks 5 days 10 hours ago) and read 676 times:
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.
Joge From Finland, joined Feb 2000, 1444 posts, RR: 39
Reply 13, posted (9 years 10 months 2 weeks 5 days 9 hours ago) and read 672 times:
used cursors on MSSQL to walk through all records and manually added everything together
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.
Gordonsmall From UK - Scotland, joined Jun 2001, 2122 posts, RR: 21
Reply 14, posted (9 years 10 months 2 weeks 3 days 17 hours ago) and read 650 times:
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 ), 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.
Statistically, people who have had the most birthdays tend to live the longest.
Saint-Exupery From Switzerland, joined Mar 2001, 47 posts, RR: 0
Reply 15, posted (9 years 10 months 2 weeks 3 days 16 hours ago) and read 649 times:
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 From Slovenia, joined Feb 2004, 726 posts, RR: 7
Reply 16, posted (9 years 10 months 1 week 6 days 11 hours ago) and read 630 times:
... 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).