Re: Access the right program for the job?

On Apr 16, 8:46 am, "tina" <nos...@xxxxxxxxxxx> wrote:
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, seehttp://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" <seryozha.sundst...@xxxxxxxxx> wrote in message

news:1176696976.587468.178070@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



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- Hide quoted text -

- Show quoted text -

Wow! Thanks a lot! I did have most of what you suggest as far as
table names and such but I totally forgot that "Date" is not a valid
field name....i bet that (among other things) is what was screwing me
up.
Thanks again.

.

Relevant Pages

  • Re: Add Records
    ... One containing each customer, just once. ... The other table would have (apart from its own Primary Key) a number field ... Because the subform and mainform are linked, when you are on a customer's ... CustID (this is a number field. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Add Records
    ... One containing each customer, just once. ... The other table would have (apart from its own Primary Key) a number field ... Because the subform and mainform are linked, when you are on a customer's ... CustID (this is a number field. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Many to Many Relationships
    ... tblCustQuotes (one-to-many relationship with tblCustomers) ... QuoteID (primary key) ... other fields about the line item of the quote ... the above assumes that each quote belongs to one customer, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: subform issue
    ... Customer ID - autonumber, primary key ... CSitus (this is the city code field), ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Access the right program for the job?
    ... field as the primary key, and get rid of the PressID field. ... CustID (foreign key from tblCustomers) ... the advantage of listing your customer, parts, presses, and operations ...
    (comp.databases.ms-access)