Thanks for the response

[Ted Clore]

I am trying to tie in some information into a single form for convenience.
I work in a job shop and it is important to track jobs by some criteria.

I need to know:

1) Customer, Part #, Quantities, descriptions

and then tie that into

2) Employee times and processes

And then tie that into

3) Vendors and costs of Outside Labor and Materials purchased.

Then I use some formula's to calculate, an hourly rate and a per piece cost
of the parts.

So far I have a table for, Employees, Customers, Vendors and Processes.
We track the job's by sequential job #'s and need to refer back to the job's
when someone orders from us again. So what I am trying to accomplish is a
way to look up a part number, and see the information about what went into
that part or look up a job number and see how we did on a particular job or
look up a customer and see our sales to a customer. Also, the owner wants
to look at the job's when the job is complete to see how the job did
according to the quotes that we bid on an hourly basis. Since we are a job
shop, and we make a wide variety of "stuff", the part #'s are not
necessarily our part #'s, and may only process through the shop one time.
But it is still vital for our process to look up a job and see the hourly
rate.

Also, Thanks for the tips.

Ted

 

[Vincent Johns]

I am trying to tie in some information into a single form for convenience.
I work in a job shop and it is important to track jobs by some criteria.

I need to know:

1) Customer, Part #, Quantities, descriptions

and then tie that into

2) Employee times and processes

And then tie that into

3) Vendors and costs of Outside Labor and Materials purchased.

Then I use some formula's to calculate, an hourly rate and a per piece cost
of the parts.

So far I have a table for, Employees, Customers, Vendors and Processes.

So you have three or four Tables so far, but don't be upset if you later
decide you need more. (Is "Vendors and Processes" one Table or two?)
Relational database systems are designed to be adaptable to some extent,
though it's still a good idea to be aware of what you want yours to do
before you put a lot of work into it.

Depending on what you want to do, you may decide to set up several
Forms, each with a different purpose. Putting 175 controls on one Form
probably gives you a very "busy" (cluttered) Form which is therefore
difficult to use. You might want to put just a few related data on each
of several Forms, so that a user can (for example) select the "New
Vendor" Form and enter the company name and contact information without
having to look at lots of irrelevant product-related fields.

We track the job's by sequential job #'s and need to refer back to the job's
when someone orders from us again. So what I am trying to accomplish is a
way to look up a part number, and see the information about what went into
that part ...

One good way to do that is to put the part numbers (and descriptions,
for the benefit of the human beings in your organization) into one Query
and the component information into a separate Query linked to the first
one via the part number. You could create a subdatasheet on the first
Query that invokes the second one. Later, if you wish, you could create
a Form for the [Parts] Query and, inside it, a subForm for the
[PartComponents] Query. (Subforms contain about the same information as
subdatasheets, but they look a little different, and the navigation
controls are different.)

or look up a job number and see how we did on a particular job or

You could use a [JobNumbers] Query and link a [Processes] subQuery to that.

look up a customer and see our sales to a customer.

You could define a [Customers] Query and link an [Orders] or [Sales]
subQuery to it.

Incidentally, I'm suggesting using Queries instead of Tables (which
would work about as well) because the Queries are more flexible (for
example, can contain calculated fields) and can be made read-only, and
you can delete a Query without erasing any data.

Also, the owner wants
to look at the job's when the job is complete to see how the job did
according to the quotes that we bid on an hourly basis.

I think I'd define just one [Jobs] Query here, showing summary
information. You'd need a subQuery only if you also needed to see a
bunch of component parts, such as a list of individual workers' charges
against a given job number. The [Jobs] Query could be filtered to show
you information on a selected subset of the jobs, such as only those for
a given customer, or only those finished in a given month.

Since we are a job
shop, and we make a wide variety of "stuff", the part #'s are not
necessarily our part #'s, and may only process through the shop one time.
But it is still vital for our process to look up a job and see the hourly
rate.

Your [PartNumber_Customer] field would need to be big enough to contain
any part # that a customer might feed you. But, having recorded that in
a Table, you can thereafter refer to it via that Table's primary key
(usually a 4-byte Autonumber field).