Table/Forms Design Question

D

Damian

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 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
 
J

Jeff Boyce

If each of the tables for each of these different categories of 'workers'
has different "fields", then fine.

Or, if every table is identical in structure EXCEPT for the worker category,
then you've committed spreadsheet on Access.

Yes, Access tables LOOK like a spreadsheet ... but Access is NOT a
spreadsheet. Access is optimized to work with well-normalized data. A
separate table for each type of worker is not well-normalized.

Before you proceed any further, take the time to learn about "relational"
and "normalization". It's something of a "pay now or pay later"
situation...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
G

Gina Whipp

Damian,

As Jeff said, you have commited speadsheet with Access. Therefore what you
want to do is going to prove very difficult. I would suggest you review the
following and REread those books you have...

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials
--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
J

John W. Vinson

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.
Click to expand...

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 said:
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
Click to expand...

Get your table design right first and then work on the forms.
 
D

Damian

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
 
D

Damian

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
 
G

Gina Whipp

Not stalking... not sure where you get that from. Here I am here now and I
see you provided the tables I would thank you but you would probably find a
way to twist that around...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Steve said:
You are too busy to answer the OP but not to busy to stalk me! Go
figure???

Steve
Click to expand...
 
G

Gina Whipp

Steve,

I do track your responses, along with others, but yours because you keep
offering fee-based services in a FREE newsgroup and I feel the OP has a
right to get their question answered for FREE in this FREE forum. I do not
interject false statements and certainly not where it concerns you. I am
not *stalking* you or anyone else, perhaps you should look up the word in
the dictionary.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
J

John... Visio MVP

stevie, you really have problems.

have you considered that if you were not harrassing the posters to these
newsgroups for unreasonable work at imodest prices that Gina andthe rest of
us would not be on your case?

John... Visio MVP
 
J

John... Visio MVP

Steve has a special dictionary. It appears that the letter "R" is missing
and he confuses Free for Fee. Actually, he is just confused.
John... VIsio MVP

PS: Thanks for STALKING the posters to these newsgroups and providing
helpful answers to their questions.
 
G

Gina Whipp

Okie dokie then... I see you have only snipped out the parts that make you
seem sane...

As for the recording you are doing... if you mean interjecting by letting
the OP know that these are FREE newsgroups where they can get FREE help by
all means keep recording...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
J

John... Visio MVP

stevie, you know as much about the law as you do Access. For your home work
tonight, look up the phrase "vexatious litigation"

John... Visio MVP
 
K

Keith Wilby

Steve said:
I am recording each time you interject anything destructive to one of my
posts.
Click to expand...

To what end, you sad, pathetic, creepy excuse for an individual?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need Database Design Advice 1
table level validation 1
How do I create a table on a new worksheet from other worksheet da 2
Table Design 5
Table Design...Did I make a mistake? 3
Database Design for Fill-In Forms 3
Table Design Question 3
DTB Design Question 1

Top