just based on the information you posted, i'd probably have at least one
data table - possibly two - and four supporting (lookup) tables, as:
tblCustomers
CustID (primary key)
CustName
(if customers can be individuals or companies, i'd have CustFirst and
CustLast fields *instead of* CustName, and use the CustLast field for
company names.) include any other fields that describe a customer only; do
NOT include any fields that describe presses, operations, etc.
tblParts
PartID (pk)
PartNo (or PartName, as appropriate)
include any other fields that describe a part only, and nothing else; if a
part number/name is unique and never changes, you may want to use that field
as the primary key, and get rid of the PartID field.
tblPresses
PressID (pk)
PressNo (or PressName, as appropriate)
include any other fields that describe a press only, and nothing else; if a
press number/name is unique and never changes, you may want to use that
field as the primary key, and get rid of the PressID field.
tblOperations
OpID (pk)
OpName
include any other fields that describe an operation - but nothing specific
to a certain job, only data that applies to all instances of that operation.
tblJobs (or tblRuns, or whatever is appropriate)
JobID (pk)
CustID (foreign key from tblCustomers)
PartID (fk from tblParts)
PressID (fk from tblPresses)
JobDate (do *not* use just the word "Date" as a field name)
OpID (fk from tblOperations)
PartsCount
relationships are
tblCustomers.CustID 1:n tblJobs.CustID
tblParts.PartID 1:n tblJobs.PartID
tblPresses.PressID 1:n tblJobs.PressID
tblOperations.OpID 1:n tblJobs.OpID
the advantage of listing your customer, parts, presses, and operations in
supporting tables is that you can use those tables as the RowSources of
combo box controls in your data entry form(s). using combo box controls is
an easy way to make sure that the data entered in a field is always *valid*
data - no typos, etc. (note: do NOT use Lookup fields in tblJobs!!)
now, if you have recurring jobs that you run over and over again - a
specific operation on a specific part on a specific press for a specific
customer - with the only change being the date and count of a specific run,
then you might want to use two data tables, *instead of* the one described
above, as
tblJobs
JobID (pk)
CustID (foreign key from tblCustomers)
PartID (fk from tblParts)
PressID (fk from tblPresses)
OpID (fk from tblOperations)
tblJobRuns
RunID (pk)
JobID (fk from tblJobs)
RunDate
PartsCount
relationship is
tblJobs.JobID 1:n tblJobRuns.JobID
keep in mind that you know more about your company's process than you can
hope to tell us in this forum. i strongly recommend that you learn the
basics of relational design principles, so you can structure your
tables/relationships correctly. for more information, see
http://home.att.net/~california.db/tips.html#aTip1. also suggest you read
the rest of the tips on that page, which were written specifically for new
Access developers (newbies) (and yes, as soon as you begin creating a
database to house a process, you are a developer!)
hth
"seryozha" <se****************@gmail.comwrote in message
news:11**********************@n59g2000hsh.googlegr oups.com...
Hello, I'm wonder if a relational database is what i need based on the
data i will be entering.
Our company will have the following data entered:
Customer
Part
Press
Date
Operation
Count
Now, the customers,press,operation, and part will hardly every change.
There are about 15 customers, 20 presses and about 50 operations. The
parts go up into the thousands.
What we need to do is track the usage of a certain press given an
amount of time based on the number of counts.
We also need to see how many counts we did for a certain part or
customer.
I would like to use access since it can be made to be very user
friendly (the guys entering the data are NOT data entry people, they
are machine operaters (not computer....mechanical) and also to
organize and report on the data.
What im having trouble with is that it isnt exactly a relational
database since it is basically just one table.
The reason we cant really use Excel is because the guys entering the
data always change the standard of data...I.E.a part might be 646a but
the enterer might enter it 3 different ways (646-A 646A 646 a ) and we
need to standardize each entry.
Is access the program for this?
I have created a sample database but i am having trouble when to
comes to making queries....they are coming up empty!!
-Thanks for being my "mini" consultants
