Unanswered: Urgent help needed - plz deadline is v.soon
Sorry for being so childish at the start.
Towards the end, I attended a crash course of basic MySQL and MS Access. I revamped the queries, following the ideas of JoeG, of drawing out and illustrating what I wanted everything to do. This made it much simpler for me to just translate it into MySQL, with the help of my knowledge gained from the course.
I have now finished the database, and although I have not used your query examples, I thank you very much for showing me how they work, as it has taught me a lot about the workings of MySQL and MS Access.
Originally posted by Viking-ex
Could you please help me as this needs to be done by Wednesday preferably. It would be easier if you could download the database and integrate any changes then upload it again - as i'm a big beginner to Access.
So uh... how is that not us doing your homework for you?
For starters, I don't know that you necessarily need to keep a stock table, unless that's part of the requirement your instructor is giving you. Another way to do it would be to track the overall stock along with the film name and id, then derive the current stock from overall stock - rentals. I'd give you bonus points.
I looked at your sample. I can understand your frustration with the deadline coming in. At times like this it's good to stop and take a time out.
Have you drawn out this design on paper - like a flow chart of how everything works. The only reason I ask this is I have been watching your posts on this project for a while now and I still don't understand how your rental process works. The first time I created a couple of samples you were back online posting the same questions again and I felt like I had missed the mark entirely on your question.
This is not a criticism here... I want to help.
Maybe you could explain (once more) how the film rental process works. Now I am not saying to explain what the SQL code should say. That's the last 10% of your project. The more important part is to write out the rules that control how the program should work - that's where 90% of the design time should be spent (and where these issues get prevented from showing up at the last hour.)
I've been through this over and over again with clients in the real world and every time I've gotten a clear-cut list of how-it-should-work down before starting to code the program, the programming part always went without a hitch.
Here's an example of what I mean:
John Doe wants to rent a film.
He chooses the film, Rocky.
The program checks to see if you have that film as a selection.
If you are tracking the barcoded serial #s on film cassettes: You now mark that particular film box as rented-out. There is no stock to worry about since you merely have to tally up all the remaining films for that title that do not have the rented-out column tagged.
If you are not tracking the barcoded serial #s on film cassettes (in other words you just care that you have 5 of any one film title): You just decrease the stock for that film title by one at rental time. When the film is returned you increase it by one.
As far as film names vs film ids. I know you want to use the film name to count up films of the same name. But I would still use the film id - but for a different purpose. See if this makes sense...
Barcode serial #453333
Barcode serial #453442
The names are the same so counting by name would lead you to think you have two of the same name when in actuality the film id #200 represents Rocky I and id #252 represents Rocky II.
I wouldn't use film names because someone might spell a film name, It's a Wonderful Life or Its a Wonderful Life (without the apostrophe) which would count as separate films when they are really the same.
I'm leaving the office and heading home. I'll try downloading your database and check out the queries to give you some ideas.
Your description helped.
I'll get back to you later with some feedback.
Also, JoeG gave you GREAT advice about making certain you could describe the business process accurately before trying to code it. It can't be said often enough -- this is the most important part of automating business operations. If you can't write it down, you can't code it. SImple as that.
I also use it as a double-check with my clients. One of the worst problems once you start programming - especially if you don't have a plan - is "scope creep." People start slipping things in (even you) and the project gets bigger and bigger and bigger. And guess who's on the hot seat for not getting it done on time? (YOU GOT IT!!!)
I write up the procedures and double-check them with clients. Then they sign off on the plan. Any additions or changes in functionality are documented separately as changes to the project.
The best part - that gives me the chance to say (if it's true) "That'll cost you $x.xx more and it'll push back the delivery date to MM/DD/YYYY." They can then decide whether to add the features and pay me or just be happy with the original plan!
If you're not a consultant, it's still a really valuable way to manage your projects and your managers. Implement the same procedures with your boss's blessing and you'll keep out of trouble.
Back in from out of town. Glad you're helping Danny. I haven't had time to look at his latest post since my return.
School teaches more than simple programming. It also prepares one for life and determines to set good habits for us once we get into industry.
With my clients I've found many times that reading back to them what I think they want (based on the design meeting) will:
1. Confirm we are all on the same page.
2. Ensure I build a good paper design first.
3. Discover that I didn't understand what they really wanted.
4. Discover that they didn't understand what they really wanted.
5. Help them realize that the price they want for the feature set is not realistic or out of their budget (in which case we can modify the feature set, ask for more money, or abandon the project entirely).
I'd rather lose a 1-2 hour initial free consultation than 50 hours of grief and dissatisfaction where the target keeps moving and then have to fight to get paid at the end,
I'm actually really new to Access myself, and therefore probably understand how to do less than yourself Viking, but I'm wondering if you have had any progress youself.
Perhaps I misread what you said earlier about the goals of what you want your queiries to acocmplish, but the way you are trying to organize your tables and their linkings seems a bit tangled.
Maybe you'll be able to find an easier solution if you look at how everything is mapped out on paper, per Magam and JoeG's suggestions. If it's a simple matter of reorganization you might see where things are tangled up and be able to write the queries yourself.
If not and you are still stuck at least you will have had some additional practice, which I would imagine would help out with your next project (I'm assuming there will be more projects).
I think the issue here is nobody wants to totally rewrite your school project. I think you need to figure this one out so you benefit from accomplishing it on your own.
It's a fine line on the forum. Some people just need a push in the right direction. Others are lost and confused and need some help. Sometimes we are real close to getting something to work and just need that one missing parameter or syntax written correctly.
And then there are others on this forum who just want someone else to do their work for them for nothing. I do NOT believe you fit into this last category - which is why everyone wants to help you.
I know you've been on this project for weeks now and I've seen your progress. Don't give up on this. Look at the samples you've had in the past and maybe reconsider your database design and trace each step to figure out where the problem is.
You know how to get totals using the count or dcount function. You know how to update records using update queries. You know how to append records using append queries. I think you have all the pieces you need.
But to take your sample and completely redesign it, fix it and show you all the query code, at least for me, may be a disservice to you. Anyway that's my own humble opinion.
But I would relook at your design. And, if it helps you any, I would go to the relationships window, click on and delete all the relationship join lines, and skip that part for now. The relationships may be confusing things since you may be causing restrictions and automatic joins to tables that shouldn't be joined.
Make the process work. Then fine tune the enforcement of the design afterwards. (This is why having a design on paper would probably show you where the problems are located.)
How comfortable are you with me writing VB code for this?
Here's my aim: I will create the update query for you which will ask you the member name, video name and it will automatically add a new record the rentalTBL and update the stockTBL...