sub form

  • Thread starter Thread starter Spencer Hutton
  • Start date Start date
S

Spencer Hutton

i have a table with the fields : DATE, JOB #, MATERIAL, LABOR, TOTAL COST.
i am trying to make a form with date and job # as a header, and the
remaining items in a subform below. i will be ordering mutiple materials and
labor for a certain job. when i create a form like this i do not get as i
expected in the table. one record has the job number and date with no
labor/materials, and the next 2 records are labor/materials with no dates.
i am an access newbie. what do i need to do to get 3 records for the
material/labor order, each containing the same job # and date?
 
you need at least two tables. tblJobs, with date and job#, and
tblJobMaterials, with material and labor and the primary key field from
tblJobs. if total cost is a calculated value (from material + labor,
perhaps?) then it should not be stored in the table, but rather calculated
as needed for display in a form/report. link the two tables on the Jobs
primary key, and make sure it's a one-to-many relationship (one job may have
many materials).
also, recommend you don't name a field "Date". that is an Access reserved
word, and you'll have problems using the field in forms, etc because of
that. and, suggest you don't use a # sign in a field name. rule of thumb:
use only letters, numbers and the underscore ( _ ), and no spaces, in field
names and all object names. and i normally avoid using numbers, too.
see the following links for more info on table normalization. if you invest
the time now to understand how to build the table/relationship structure
correctly, it will repay you a thousand-fold later in time saved on building
the rest of the db and maintaining it. also suggest you take a good look
around the mvps.org website. excellent info and help by some of the best
Access minds around.

http://www.mvps.org/access/tencommandments.htm

below links originally posted by MVP John Vinson:

**********
You're using a relational database. Use it relationally! For some
online tutorials in doing so, here are a few refs:

ACC2002: Database Normalization Basics
http://support.microsoft.com/?id=283878

Database Normalization Tips by Luke Chung
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

Data Normalization Fundamentals by Luke Chung
http://www.fmsinc.com/tpapers/datanorm/index.html

324613 - Support WebCast: Database Normalization Basics
http://support.microsoft.com/?id=324613

*********

hth
 
Back
Top