A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Table/Forms Design Question



 
 
Thread Tools Display Modes
  #1  
Old July 23rd, 2009, 09:36 PM posted to microsoft.public.access.tablesdbdesign
Damian
external usenet poster
 
Posts: 141
Default Table/Forms Design Question

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


  #2  
Old July 23rd, 2009, 09:57 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Table/Forms Design Question

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


"Damian" wrote in message
...
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




  #3  
Old July 23rd, 2009, 10:04 PM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default Table/Forms Design Question

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/acc...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

"Damian" wrote in message
...
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




  #4  
Old July 23rd, 2009, 10:11 PM posted to microsoft.public.access.tablesdbdesign
Steve[_72_]
external usenet poster
 
Posts: 190
Default Table/Forms Design Question

It's not clear what #ofForeman, #ofWorkers and #ofLaborers with each name
means. Could you please explain.

Thanks,

Steve


"Damian" wrote in message
...
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




  #5  
Old July 24th, 2009, 04:53 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Table/Forms Design Question

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]
  #6  
Old July 24th, 2009, 04:11 PM posted to microsoft.public.access.tablesdbdesign
Damian
external usenet poster
 
Posts: 141
Default Table/Forms Design Question

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]

  #7  
Old July 24th, 2009, 04:26 PM posted to microsoft.public.access.tablesdbdesign
Damian
external usenet poster
 
Posts: 141
Default Table/Forms Design Question

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]

  #8  
Old July 24th, 2009, 05:49 PM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default Table/Forms Design Question

I am a little busy right now BUT if no one gets back to you by the time I
get UNbusy I will review your tables.

--
Gina Whipp

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

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

"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]



  #9  
Old July 24th, 2009, 07:44 PM posted to microsoft.public.access.tablesdbdesign
Steve[_72_]
external usenet poster
 
Posts: 190
Default Table/Forms Design Question

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

Steve


"Gina Whipp" wrote in message
...
I am a little busy right now BUT if no one gets back to you by the time I
get UNbusy I will review your tables.

--
Gina Whipp

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

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

"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]





  #10  
Old July 24th, 2009, 07:52 PM posted to microsoft.public.access.tablesdbdesign
Steve[_72_]
external usenet poster
 
Posts: 190
Default Table/Forms Design Question

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]



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:28 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.