Need pointed in right direction please (1 Viewer)

janeyd

Registered User.
Local time
Today, 02:35
Joined
Jun 24, 2011
Messages
27
Hi
I work in a small company warehousing yarns ready to be woven into fabric. I have created a simple stock database, and can enter customer orders with an estimate of all different yarns required for each fabric. (not an exact science unfortunately) So i have queries/reports of total yarn in stock, total yarn required and total free yarn.
The yarn is sent off-site to be made into fabric and I would like to be able to create a form that has a drop down box to chose a job no and then populates fields customer, fabric, yarn, warp colour, kg, weft colour kg but then have new boxes with actual yarn sent (we always send more than estimate) and date. Was wondering how best to do this part. I would also like to run an update query so that once I have entered the date here I can generate other reports to show stock off-site and total stock on-site - and also if there is a date not to show the job no in drop down box.
There is a lot more I want to do after this - ie once fabric is woven, update stock to take yarn off completely, and then a form to add any yarn returns back on stock (against the order they were sent for so I can monitor how good the initial estimate was) - but hopefully would be able to work this out...
Thank you
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 02:35
Joined
Jul 9, 2003
Messages
16,245
Pick a simple thing, something that will will save you time/make your job easier. Implement that first.
 

plog

Banishment Pending
Local time
Yesterday, 21:35
Joined
May 11, 2011
Messages
11,613
I would also like to run an update query so that once I have entered the date here I can generate other reports to show stock off-site and total stock on-site

UPDATE queries are usually hacks used to get around a poor table structure. There's really no need to move data from one table to the other and you shouldn't store values you can determine in other manners. So I fear you don't have a good table structure.

Can you post yours? Set up the relationship tool in Access then take a screenshot and post.
 

janeyd

Registered User.
Local time
Today, 02:35
Joined
Jun 24, 2011
Messages
27
Thank you both. I have gone back to basics and am building what I need one step at a time - seems obvious but couldn't see wood for trees at first!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 21:35
Joined
Feb 28, 2001
Messages
27,001
janeyd- I'll give you a couple of bits of advice in the form of rules to help you in this crucial phase. Because failures in designs come back to haunt you forever.

Old Programmer's Rule #1: If you can't do it on paper, you can't do it in Access.

This means that you must have a table design and a set of predefined actions (or transactions or transformations) as guidelines such that, if you only had accounting analysis pads and a pencil, you could still define what you wanted to do. Access is just a tool. It won't make a thing on its own. You have to be like the designer and the technician. Or, to use a furniture analogy, you have be both the maker of blueprints and the carpenter who implements them. Access is like a power tool. All it does is help you make things faster. And if you have a faulty design, it helps you make mistakes faster. So lay it out on paper before you implement it. There is no substitute for forethought.

Old Programmer's Rule #2: Access won't tell you anything you didn't tell it first (or tell it HOW to tell you first.)

Access cannot create data from nothing. So if you want to have X in any report, you must assure that either a source of X exists to allow you to pass it through to a report, or you have to define a formula for X based on other items. This often means that you will have to define your desired outputs first and then check each item you wanted to see so that you can verify you have a way to get that item. In effect, work backwards from results to assure availability.

Which is why, based on those two rules, you have to know your problem forwards (rule #1) and backwards (rule #2) to make it work. But what's that old saying that I can't claim as my own? "An ounce of prevention is worth a pound of cure." Well, the source of that prevention is to spend time at the design stage.
 

Users who are viewing this thread

Top Bottom