|
microsoft access... for stupid people! (like me)
ok, here goes.... i have no idea how to use access professionally, but i'm trying to set up a database for my father.. he is selling stock!i have set up a purchase table, where he can enter the purchase details including the product id(key field)! in the products table i have a number in stock level. how would i get access to update this level everytime that specific id is input in the purchase table?
i really dont know if this makes sense
thanks
sarah
sarah mason
Tuesday, January 27, 2004
Maybe somebody more familiar with Access could comment, but most databases have "Triggers" that you could set to update the other table when values in your first table changes.
Chris Ormerod
Tuesday, January 27, 2004
There are a lot of tips at this site :
http://www.granite.ab.ca/accsmstr.htm
It's done by a MS Access MVP.
Kentasy
Tuesday, January 27, 2004
Data modeling is very tricky business, and to create this sort of app successfully would take a great deal of time & effort. There is an abundance of this sort of software. Just by a copy or a subscription to a package.
(I suppose you may simply be constructing the database to gain experience. If this is the case, before you dive in, please learn the fundamentals of database design. It's much easier to start off by learning good database habits rather than attempting to break bad habits in the future.)
anon
Tuesday, January 27, 2004
Good question Sarah. I'm not an Access expert myself and my advice may not work for you, but in databases such as SQL Server you would set up a "Trigger" that would automatically update the field in the Products table when a new record was inserted or deleted in the Purchase table.
Unfortunately I don't believe you can do this in Access. Instead you will probably have to use some VBA code. Let's say that your Dad enters a purchase from a form. This form has a button on it labeled "Enter Purchase". The code for the button inserts a new record into the Purchase table. What you want to do is add code to that routine to update the Stock Level field in the Products table.
If you're not at the level where you understand what I'm saying or you are not at the level where you are writing VBA code then you might try picking up a book on the subject.
Dave B.
Tuesday, January 27, 2004
thanx dave...... i just though i would do it in an update query... never thought about vba! i'll try that now
sarah
sarah mason
Tuesday, January 27, 2004
one tested approach one untested
tested:
in the function that creates the record in record 1 create the code that will update table 2
untested (this might work)
in access allow putting functions youve created as part of sql so put a function you want to use to update the code in the original query and have it execute the update code
ie
select fieA fieB myFunction(fieC) from tab1
where myFunction is the trigger code
The Artist Formerly Known as Prince
Tuesday, January 27, 2004
>> "Good question Sarah."
Honestly, this is an exceedingly bad question. The mere fact that one table relies on another table's data indicates that your data is redundant. If you insist on developing this app yourself, please pick up a book on database design and immediately got to the chapter on normalization.
anon
Tuesday, January 27, 2004
look, i came on for help, i dint come here so you can comment on my capabilities on access!
sarah mason
Tuesday, January 27, 2004
Sarah,
The main problem with the approach you are considering is that you are building into you database a fault point, a weakest link that is always waiting to be broken.
A better approach is to create you product table without the stock level, then use a query to calculate the stock level when you need it. This way you are not duplicating data and you can trust the result every time.
You may worry about the performance. Create indexes for the fields involved in the calculation so they don't cost very much.
Try to avoid using Access functions unless the calculation is very complex. Clean SQL will be faster and easier to move to other databases.
Ged Byrne
Tuesday, January 27, 2004
>> "look, i came on for help, i dint come here so you can comment on my capabilities on access!"
If a young Albert Einstein came to me as a world renowned child prodigy and asked me how he should formulate his cool new theory, I'd tell him to master the basics. I made no comment on your capabilities. You might have the potential to be the greatest DB designer the world has ever known, but I'll tell you this: You sure as heck won't actualize that potential unless and until you find out what the heck normalization is.
anon
Tuesday, January 27, 2004
Hey i really think you should give Sarah a break, she is asking for help and her question is met with severe criticism. Working in the database trade for so long i've come to know that helping people instead of this idle chit chat works best. You need to chill out and have nicer manners!
Josh Wilson
Tuesday, January 27, 2004
"The mere fact that one table relies on another table's data indicates that your data is redundant."
No, it could easily be a foreign key or abstract. For example transactions about a stock are neither redundant nor should they be contained in a table with the base element information.
If you are going to be critical, at least know what you are talking about.
AnonAnonAnon
Tuesday, January 27, 2004
"i have no idea how to use access professionally, but i'm trying to set up a database for my father.. he is selling stock!"
Learning Access is fine for you, but that's not your father's goal. I think your *father's* goal would be better served by buying him a copy of Quicken Deluxe (~$69) or Microsoft Money (~$39 after rebate) -- these products will also help with the capital gains taxes your father will owe after his sales. Who knows, the cost of the software may itself be tax-deductible.
Then you can improve your own skills -- worthwhile in itself, as the other posters suggest.
Dobie
Tuesday, January 27, 2004
I second the recommendations for Quicken or Microsoft Money. Why reinvent the wheel when there are perfectly good solutions already available?
An added advantage is that with Microsoft Money (and probably Quicken too) your dad could download stock purchase/sales data from many brokerages automatically, so he wouldn't need to enter all that data manually.
Robert Jacobson
Tuesday, January 27, 2004
Why do programmers and technical types insist that users go through this initiation process to get their work done with computers? Why can't a simple "do this and this and you're done" answer be sufficient? Someone always has to say "learn the theory of how the entire system works by reading these 500 pages and then you'll know which two buttons to click."
If they're not technical types, then they've got other shit to worry about. Why can't we just leave them be? It sounds too much like hazing to me.
H. Lally Singh
Tuesday, January 27, 2004
Ok, part 2:
Look what it's done to poor Sarah here. Users end up feeling 'stupid', inadequate, or in other ways unhappy because they didn't want to learn some dick programmer's thought process for how one should do this or that. It's terrible. People who have been using computers for 10-20 years say that they don't understand them. __What does that say about our field__???
A car, with the workings of the engine, the PCM, the thermodynamics, the static and dynamic stresses to all the parts, etc., is quite easily comparable in complexity to a modern user's PC. Yet most people don't feel the need to know what's going on in their car. And they feel confident driving it after 3-4 months behind the wheel.
H. Lally Singh
Tuesday, January 27, 2004
>> "Hey i really think you should give Sarah a break, she is asking for help and her question is met with severe criticism. Working in the database trade for so long i've come to know that helping people instead of this idle chit chat works best. You need to chill out and have nicer manners!"
Sarah, I'm sorry if I were brusque, but you don't need feel-good advice about VBA. The most efficient, rational thing for you to do, is get a book on database design. Believe me, I'm a grumpy old fart, and I know from painful experience that you'll save yourself a lot of time and anguish if you simply follow best engineering practices from the beginning.
A nice first book that worked for me was, "Data Modeling Essentials" by Graeme Simsion:
http://www.amazon.com/exec/obidos/tg/detail/-/1576108724/qid=1075249873//ref=sr_8_xs_ap_i3_xgl14/002-5303053-2643228?v=glance&s=books&n=507846
* * * * * * * * * * *
Sarah wrote:
>> "in the products table i have a number in stock level."
AnonAnonAnon wrote:
>> "No, it could easily be a foreign key or abstract. [...]
"If you are going to be critical, at least know what you are talking about."
AnonAnonAnon, I highly doubt that 'stock level' could be a foreign key.
anon
Tuesday, January 27, 2004
Has anyone noticed Sarah made most of the mistakes in grammar and punctuation Joel was writing about? I think you all got suck0rd
School Marm
Tuesday, January 27, 2004
H. Lally Singh, if Sarah is a user, then she should not be designing a database application - she should buy a product designed for the end user. If she is a developer, then she should use best engineering practices. Or, to use the terms you put forth in your inane, rambling analogy, drivers of automobiles are like users. Drivers do not need to understand "the PCM, the thermodynamics, the static and dynamic stresses to all the parts, etc." in exactly the same way that end-users need not understand the relational calculus.
However - and try to follow, genius - an engineer is like a developer. I sure as heck hope that the engineer who designed "the workings of the engine" had some concept of the intricacies of a car. I hope he looked back on the history of automotive design and decided not to dismiss the accumulated knowledge of the industry as simply "some dick" engineer's "thought process."
anon
Tuesday, January 27, 2004
Sarah, are you a child?
(Serious question).
Ali
Tuesday, January 27, 2004
Yummy! Flames!
Here I was thinking that Access was a user-level application. I mean, it's bundled with office, is designed for small to mid-level uses, and is GUI-centric. We've had databases for decades -- I think it's OK now to let users touch them.
Unless she's planning several hundred thousand entries in the database, then I think it's ok for her system to be inefficient -- so her computer can do the work instead of her.
As for misinterpretations of simple analogies: she's not designing the car, she's driving it. The dick who designed the car should've made the thing easier to drive. And if that isn't possible, then her engineer friends shouldn't give her shit for being human.
H. Lally Singh
Tuesday, January 27, 2004
>> "Here I was thinking that Access was a user-level application"
Well, you thought wrong. Access is for low-end application development, and for POWER-USERS. Big, big difference between power-users and end-users. Access isn't even bundled with Office; you have to upgrade to the small business edition or something.
>> "Unless she's planning several hundred thousand entries in the database, then I think it's ok for her system to be inefficient."
It's fine to be inefficient. Data modeling has nothing to do with efficiency; in fact, if efficiency were hugely important, you'd denormalize everything and try to live with the redundancy and resulting data anomalies. Nobody on this thread even mentioned system efficiency, so I have no idea what you're even talking about.
>> "As for misinterpretations of simple analogies: she's not designing the car, she's driving it."
Wrong. If she's using Access - and she is - then she's designing the car. If she, however, were using Quicken or Money, she'd be driving.
anon
Tuesday, January 27, 2004
Ouch. selective quote and response. Tisk tisk, that's playing dirty.
So you're saying that because she's using access, she's some power user or developer. So an end user can't make their own databases ?
Actually, don't answer -- I don't care. I'm off of this thread, and you're free to flame me to your heart's content.
H. Lally Singh
Tuesday, January 27, 2004
Sarah (if that's your real name), here is an actual *answer* to your question:
You need two tables. One contains "Products" and another contains "Purchases".
1) The Products table should have a ProductID field (use an Auto-number so access will automatically come up with new id numebrs for each new product entered), and have Product name, product number, description, etc. fields in this table. Make sure that if the products have a product number in the real world (ISBN, UPC, etc.), make that a DIFFERENT field then your 'ProductID' Autonumber field. This will save you lots of trouble in the future.
2) The Purchases table should have a PurchaseID field which is an autonumber, but also a ProductID field which is NOT an autonumber (just a 'Long' Number). This will contain the ProductID of the matching record in the Products table. Also have fields for Date purchased, quantity purchased, etc.
Now, when there is a purchase, add a record to the Purchases table (using an update query, or VBA -- however you want to handle it). If this is a purchase of a product that doesn't yet have a record in the Products table, you need to add a product record there before adding its purchase record to the Purchases table.
Finally, when you want to find out how many purchases have been made, have another query which counts the records of each product from the Purchases table. In the query builder, you will need to 'Show Totals' to get the Count function.
Good luck!
Jordan Lev
Tuesday, January 27, 2004
For what it's worth, Access (when fully installed) comes with some pre-built applications that, I believe, a wizard helps walk you through their creation. For example, in Office XP, go to the New... menu and you should get a side bar. Select General Templates and on the Databases tab is a whole set of applications. Now, maybe one of these fits your needs, but the value might be in seeing how MS tackled the same problems.
Good luck!
m
Tuesday, January 27, 2004
My, my what a lot of flouncing there is going on.
There's nothing inherently bad in a small record oriented database in having the current stock level as part of the base data of a stock record. Now you might argue its transactional and you should go query it every time (like summing all the stock movements), but in real life that's death.
Cos, except in particular circumstances I'm going to ignore, you need to know how much stock you have before you allocate it, issue it or whatever. This means locking something so that the universe doesn't change before you get done with your transaction. Relying upon a query to tell you the current state of a fixed variable such as stock level involves all sorts of other shenanigans that a small database engine just isn't going to cope with.
Also in real life (and this might shock some people), sometimes the amount of stock isn't actually the sum of the transactions. Its an entity in itself. Sometimes its not even a real number.
And then, usually at the end of the financial year, you do a stocktake and you end up with an entirely different number.
Now you can enter adjustment transactions which fix up the numbers but many systems allow a stocktake record to just update the current stock level.
But, as its unlikely there's going to be much multi-user contention going on here a simple record oriented system is fine.
Now as to whether this should be attempted or not given all the circumstances, I'd say probably not. Not because Sarah is a user and shouldn't roll her own solutions but because there's a cheap and cheerful alternative available that will do just fine.
Simon Lucy
Tuesday, January 27, 2004
>> "Now you might argue its transactional and you should go query it every time (like summing all the stock movements), but in real life that's death."
Umm, but it's *not* real life. It's a database that'll contain, probably, less than a couple thousand records. Far better that she learns the canonical database design skills, rather than implement a shortcut - a shortcut that, by the way, is very dangerous in all but the most experienced hands.
anon
Wednesday, January 28, 2004
Jeeze.
Simon Lucy
Wednesday, January 28, 2004
Whew.
anon
Wednesday, January 28, 2004
erm... yeah i'm 19 and i am trying to do my dad a favour... i go to college and study fashion design so db design is not something i do professionally....
i have normalised my data...(to the best of my ability)
there is a products table in my database....and the primary key is the product id... this is the foreign key in the purchases database.....i've figured out how to do it now but all i wanted was when the productid is entered in the purchase table to update the stock level in the products table.. then i could do a simple calculation using the re-order level and wow!!! a message should appear telling him to reorder stock!! wow!!! i'm quite proud of that.... all my own work... well...
thanx for your help, sorry for troubling you
sarah
sarah mason
Wednesday, January 28, 2004
How did you end up accomplishing that Sarah? (How did you solve your problem?)
Wednesday, January 28, 2004
"How did you end up accomplishing that Sarah? (How did you solve your problem?)"
Yeah... what happened? 888
anon
Monday, February 2, 2004
 Recent Topics
Fog Creek Home
| 
