Damian,
Okay, first of all THANK YOU for the other Excel spreadsheet, that helped
clear some things up. However, I see things which may require some changes,
like by Description you really mean Activity, those two words mean two
different things which is why I originally suggested putting it in tblJobs.
As simple as the Excel spreadsheet looks it IS complicated. I also see you
are trying to design this database like a flat file (Excel) and you are
having a hard time see that Access is not a flat file format. I am unsure
how I am going to get you to NOT think in flat file format and stop trying
to deisign tables to hold data as you see it in the reports/forms but I'll
plugging away.
Questions...
1. Suppose you wanted to look-up historical Daily Reports where do you?
2. Subcontractors are really Firms, okay then what are the Crews and what
are Trade?
3. Am to understand the Equipment has nothing to do with the Trades? That
Equipment is assinged to the Job?
4. Is the bottom the Detail of the Top Part?
5. If the Job is the ONE then what fields are the many?
I think we need to start from the beginning. Tables are beign designed with
new information being revealed and that never works. AND are there anymore
spreadsheets? Here is an example of how to start...
One Side
Job
Weather
Date (Going to need this is you want historical)
Many Side
Crew
Subcontractors (Firms)
Area (Is this the area worked on?)
Equipment
Shift
Date (Going to need this is you want historical)
How does the above appear? Oh, erase the image of the reports from your
mind! :-)
--
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
...
Hi Gena,
Lets start with weather. I only need this field per day. ex. It was sunny
on
monday so the weather would be Sunny. All Jobs are together in one daily
report so I do not need weather for each Job.
Let me show you the excel sheet for the dailt report. I know I can't think
like in excel but maybe you get a better idea of what i am trying to
create.
( http://img9.imageshack.us/img9/2928/summaryreport.png )
Here you can see how the Description is connected with
(Area)-(Subcontractor
withTrades(TblCrewTypes)) and (Equipment). Now Subcontractors are names of
Firms. They do not have First and Last name, so I dont know how they would
fit into TblCrew. In this excell Trades(which are CrewTypes) are together
with Subcontractors, I want that seperate in access and it probably makes
more sense.
TblJobs is just to see how many Foreman-Workers & Laborers the MAIN GUY
from
each TblCrews had. Also Subcontractors have a number of Foreman and
Workers
assigned to it. Subcontractor is a Firm so no First/last name and we dont
care what the names of Foremans or workers are just how many each
subcontractor had.
Thats why in the Summary we dont need to see the names of Crew, just the
CrewType they belong to. BUT we need totals for the TblJobs(maybe its a
bad
name for it, it only counts the # of F+W+L). Maybe we should call TblJob
something else like TblNumberOfFWL (FWL = Foreman-Workers-Laborers)
This report is so simple its complicated lol.
This excell is simple and not good.
I Know Access can do this much better and probably will look and act
totally
different then the excel, which I want.
I just cant stop looking and thinking like the excel sheet, I know that,
but
I cant stop lol.
Am I making any sense with all this? Do you see the Relationship between
TblSubcontractor, TblArea and TblEquipment.?
ex. AREA(SouthControlBuildong) Subcontractor((Skanska)or(Carpenter))
Activity/Description(Cleaning out the aeration tank) Equipment(pulley)
Thank You
"Gina Whipp" wrote:
Answers in-line...
--
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
...
Here are the new tables and relationship screenshot.
( http://img32.imageshack.us/img32/377/relationships2.png )
I left weather out since it has no connection to anything. I just want
to
use it later as a Headder on a form or report.
Are you looking to Weather as in, "Rained Today" OR Weather the day of
the
Job? How many Jobs are going to be put in here? If you are going to
show
the Weather the day the Job is being done then you need to include
weather
in tblJobDetail (I explain why you want that later on.)
Also the jDiscription is your tblJobs I took it out because the only
description that will be entered is for Subcontractors. Also I linked
Tbl
Subcontractors to Tbl Job Detail Summary because it will be using 2
fields
from Tbl Jobs (Foreman and Workers).
Explain how you are using Description in the Subcontractors table. Also,
why do you need a seperate table for Subcontractors? Why not add them to
Crew and set the Crew Type as Subscontractors? You really don't want
seperate tables for each Crew member type. That would be like having a
seperate table for Administrative Assistants, Manager, CEO, etc... You
want
one table that you can specify Crew member type.
I know that I should not be thinking about Forms and Reports but I want
to
see the Big Picture and Understand how to set up tables and
Relationships.
I
learned that TblDetail is used as a pass-through table right? so thats
why I
dont think I need to link weather because its only going to be used
once
per
daily report to show what the weather was like today.
tblJobDetail is not a pass-through table, it is exactly what it says.
There
will be ONE Job but many Crew memeber attached to that Job. There will
be
ONE Job but several pieces of equipment will be attached to that Job.
Making any sense?
As for Forms and Reports... they are just Windows allowing you see
Outside
world (the data). Setting up tables has nothing to do with how the
tables
look, the same way the Window in your home can't control what you see
outside, just that you can see outside. Make sense?
Think One-to-Many... One natural Mom (Job) per child ----- and a Mom
can
have ten children (Job Detail)
The data is not private. I can send you thw whole excel sheet if you
like.
The guys where using that for a while but it failed miserably since it
cant
be accessed by more that one user at a time.
I think we might be okay so won't need the Excel spreadsheet right now.
More tidbits...
1. Get rid of the spacing in your table and field names (if there are
any),
it will only make extra work for you later on.
2. Explain the whole Equipment scenario to me... I'm not sure I
understand
why Area is in it and how you use it except to assign Equipment to a Job.
--
Gina Whipp
SNIPPED