July 27th, 2009, 04:21 PM
posted to microsoft.public.access.tablesdbdesign
|
|
|
Table/Forms Design Question
Bob Fox is not in your Excel example to see what you mean.
Steve
"Damian" wrote in message
...
I don't think I need TblForeman since Foreman field will not have name, it
only has a number. Like, Bob Fox(WorkerID) will have 2 Foreman(Crew ID).
Thats what my question is, how do I create my tables to represent this.
my TblCrew has 2 fields
CrewID
CrewName
This will only allor you to input the Crew name like Foreman or worken or
laborer. (these are the only 3) so should there be another CrewCount in
this
Tbl? or should the Count be somewhere else?
Look at this excell (
http://img21.imageshack.us/img21/6924/excellsample.png )
This is what I am trying to create.
But I am little confused on how to link them in a form and create Good,
Normalized Tables so there is no data repetition as I had in the
beginning.
"Steve" wrote:
You added more to the description of your database and now It seems you
need
to modify your tables again! Consider:
TblForeman
ForemanID
ForemanFName
ForemanLName
other foreman fields you might need
TblDailyJob
DailyJobID
DailyJobDate
DailyJobDescription
ForemanID
other job fields you might need
TblDailyJobWorker
DailyJobWorkerID
DailyJobID
WorkerID
Keep previous TblWorkerType and TblWorker. Delete TblCrew and
TblCrewWorker.
You can get the names in a "crew" by getting all the workers on a
specific
daily job.
Steve
"Damian" wrote in message
...
Great advice. I renamed the tables and I think You are right. It will
avoid
confusion.
So far I have this:
( http://img263.imageshack.us/img263/1...ellsample2.png )
Image for easier view and review.
What my goal is:
To create a form for a user which has Defined fields for WorkerType,
Underneeth Worker Name, and across CrewName all there because there are
only
handfull of guys for each WorkerType.
The User will input only a number of CrewName he/she had for that day..
So
if WorkenName had 1 Formen, 2 Workers and 4 Laborers under him/her. She
would
only enter 1 , 2 , 4 under her row for each CrewName. Then have fields
for
totaling each thing automatically. Is this all passible with a form or
do
I
have to do the totals as a querie and then create a summary report?
Am I making sense? The first excell image I think explains it very nice
what
I mean.
Thanks Guys for helping me with this.
"Steve" wrote:
Hello Damian,
Your tables are still incorrect. To start, how about for clarity
naming
the
people Workers. The naming a group of workers Crew.
TblWorkerType
WorkerTypeID
WorkerType
TblWorker
WorkerID
WorkerFName
WorkerLName
WorkerTypeID
TblCrew
CrewID
CrewName for identifying different crews
TblCrewWorker
CrewWorkerID
CrewID
WorkerID
You can count the types of workers in a crew in a totals query that
includes
all the above tables on the fly.
Steve
"Damian" wrote in message
...
Made a mistake. Is there an Edit button here? hmm
Correction on the Tables:
So Far I have 3 Tables:
Crews Neme
t_CrewID autoNumber PK
t_CrewFname first name
t_CrewLname last name
t_CrewType Carpenter, Surveyor, Laborers - Linked to Crews Type
Table
so
you can choose from drop down menu.
Crews Data
t_CrewDataID autoNumber PK
t_CrewDataType will be Foreman, Workman, Laborer
Crews Type
t_CrewDetailID autoNumber
t_CrewDetailType will be Carpenter, Surveyor, Laborman
Sorry
"Damian" wrote:
Great Help, thanks to all for info and great links. I'm starting to
get
the
Big Picture now.
The only thing I am confused is how will I buld a Form for the guys
to
fill
out.
Maybe I should explain what I want to accomplish here.
So Far I have 2 Tables:
Crews Neme
t_CrewID autoNumber PK
t_CrewFname first name
t_CrewLname last name
t_CrewType Workman - Foreman or Laborer Linked from Crews Data
Table
Crews Data
t_CrewsDataID autoNumber PK
t_CrewsDataType will be Foreman, Workman, Laborer
(Now the data Value that you mentioned 1 , 3 , 2 is the amount of
Foreman/Workman/Laborer each Crew had. This field is to be inserted
by
the
user. so should I still have a DataValue field in my Crews Data
table
when it
will be different for every t_CrewID and every T_CrewsID will have
all
t_CrewsDataTypes?)
I have an excell for this but I want to create a databaseto do
this. I
took
a screenshot of the excell so you can understand it and help me
better.
(Hope
is ok to post links here like these, if not I am sorry)
(http://img21.imageshack.us/img21/6924/excellsample.png)
Thanks Again
"John W. Vinson" wrote:
On Thu, 23 Jul 2009 13:36:01 -0700, Damian
wrote:
I am Trying to create Daily Reports for my company.
I created tables for Carpenters, Laborers, Surveyors,
Ironworkers
etc.
Each of these has fields *_Name, #ofForeman, #ofWorkers,
#ofLaborers.
ex.
"Carpenters Table
CarpentersName Foreman Worekman Laborer
Joe 2 1 5
Bill 1 5 2
"
"Laborers Table
LaborersName Foreman Worekman Laborer
Frank 1 1 0
"
And so on (you get the picture)
Now are those tables Normalized? I know I am repeating
Foreman,Workman
&
Laborer in each one of them but each of these tables has
different
Names and
different quantaties of people.
I'd change your design completely. Putting data (the type of
worker)
in
a
tablename or a fieldname is simply WRONG DESIGN.
Workers
WorkerID Primary Key, perhaps an autonumber
WorkerName (I'd actually use LastName and FirstName as
separate
fields
WorkerType e.g. Carpenter, Laborer, Surveyor, ...
WorkerData
WorkerID link to Workers
DataType e.g. "Foreman", "Laborer"
DataValue your 2, 1, 5 etc., whatever they mean
I created a form for Carpenters - A tabular form so I see All
names
for each
Carpenter in seperate box and next to them their
Foreman/Workman/Laborer.
I also included a total column at the end which adds up each
row. I
can edit
the fields each day and the total is changing. (thats good)
The problem is I have to create a Form for Each Table
seperately.
Can I put Table CArpenter/ Laborer/ etc.. into one form and be
able
to
view
it and edit like I can the single Carpenters Form? I cant seem
to
make
it
work.
Im reading all these access books and I cant find the answers I
am
looking
for anywhere.
I also created a Querie that sum up all Foreman for each Name
each
workman
for each name and so on. Plus a final total Column which sums up
everyone. It
works great, BUT when I try to make it show up on the Form I
cant.
I
can do
it as a footer but it does not automatically update when I
change
the
values.
I have to close it and come back in. Why is that?
I know I am asking a lot and I hope you guys can help me.
Thank you
Get your table design right first and then work on the forms.
--
John W. Vinson [MVP]
|