query to populate child tables

hi all, can anyone help me?
I am a relative newbie to sql server and I am more familiar with
Enterprise Manager than QA. I have made many many access databases
though. I am making an asp.net application where by there are a set
number of users, about 80, each one logs in and manages information
within their department.
To get them started a manager has written 10 different hazards that
will apply to all of the departments, and he has written consequences
and controls for the hazards. Each department must have this
information as each will manage and deal with them differently
The hazard information is stored in a main 'hazards' table, and the
consequences and controls are stored in related tables linked by the
'hazardID' from the main table to a foreign key 'hazardID' in the
related tables
What i want to know is if there is a relatively simple way of using a
query to populate the 10 hazards to each department, and to also
include the related table links, i dont mind renaming the departments
names to match each hazard, but i do not want to have to relink the
related tables manually
If anyone can give me any advice to get me started i will be incredibly
grateful
thank you
Table information is below
Hazards
--
HazardID - identity key field
Hazard - varchar
Department - varchar
Consequences
--
ConsequenceID - identity key field
HazardID - FK
Consequence - varchar
Controls
--
ControlID - identity key field
HazardID - FK
Control - varchar
dwightThis is a multi-part message in MIME format.
--=_NextPart_000_00FF_01C6AABD.53A28AE0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
If I understand you correctly, you want to retreive the Hazard =Information and include the Consequences and Controls for each Hazard, =and you want to limit this by Department. But you also indicated that =there were 10 Hazards that were common to all Departments. If you want a =list of Hazards by Department, this may work:
SELECT h.HazardID
, h.Hazard
, cq.Consequence
, cn.Control
FROM Hazards h
JOIN Consequences cq
ON h.HazardID =3D cq.HazardID
JOIN Conrols cn
ON h.HazardID =3D cn.HazardID
WHERE h.Department =3D <Department>
-- Arnie Rowland
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
<dwightsmail@.gmail.com> wrote in message =news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com...
> > hi all, can anyone help me?
> > I am a relative newbie to sql server and I am more familiar with
> Enterprise Manager than QA. I have made many many access databases
> though. I am making an asp.net application where by there are a set
> number of users, about 80, each one logs in and manages information
> within their department.
> > To get them started a manager has written 10 different hazards that
> will apply to all of the departments, and he has written consequences
> and controls for the hazards. Each department must have this
> information as each will manage and deal with them differently
> > The hazard information is stored in a main 'hazards' table, and the
> consequences and controls are stored in related tables linked by the
> 'hazardID' from the main table to a foreign key 'hazardID' in the
> related tables
> > What i want to know is if there is a relatively simple way of using a
> query to populate the 10 hazards to each department, and to also
> include the related table links, i dont mind renaming the departments
> names to match each hazard, but i do not want to have to relink the
> related tables manually
> > If anyone can give me any advice to get me started i will be =incredibly
> grateful
> > thank you
> > > Table information is below
> > Hazards
> --
> HazardID - identity key field
> Hazard - varchar
> Department - varchar
> > Consequences
> --
> ConsequenceID - identity key field
> HazardID - FK
> Consequence - varchar
> > Controls
> --
> ControlID - identity key field
> HazardID - FK
> Control - varchar
> > > > dwight
>
--=_NextPart_000_00FF_01C6AABD.53A28AE0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

If I understand you correctly, you want =to retreive the Hazard Information and include the Consequences and Controls for =each Hazard, and you want to limit this by Department. But you also indicated =that there were 10 Hazards that were common to all Departments. If you want a =list of Hazards by Department, this may work:
SELECT h.HazardID , h.Hazard , cq.Consequence , =cn.ControlFROM Hazards h JOIN Consequences =cq ON h.HazardID =3D cq.HazardID JOIN Conrols cn ON h.HazardID =3D =cn.HazardIDWHERE h.Department =3D
-- Arnie RowlandMost good =judgment comes from experience. Most experience comes from bad judgment. =- Anonymous
wrote in message news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com=...> => hi all, can anyone help me?> > I am a relative newbie to sql =server and I am more familiar with> Enterprise Manager than QA. I have made =many many access databases> though. I am making an asp.net application =where by there are a set> number of users, about 80, each one logs in and =manages information> within their department.> > To get =them started a manager has written 10 different hazards that> will =apply to all of the departments, and he has written consequences> and =controls for the hazards. Each department must have this> information as each =will manage and deal with them differently> > The hazard =information is stored in a main 'hazards' table, and the> consequences and =controls are stored in related tables linked by the> 'hazardID' from the main =table to a foreign key 'hazardID' in the> related tables> > =What i want to know is if there is a relatively simple way of using a> =query to populate the 10 hazards to each department, and to also> include =the related table links, i dont mind renaming the departments> names =to match each hazard, but i do not want to have to relink the> related =tables manually> > If anyone can give me any advice to get me =started i will be incredibly> grateful> > thank you> => > Table information is below> > Hazards> --> HazardID - identity key field> Hazard - varchar> Department - varchar> > =Consequences> --> ConsequenceID - identity key =field> HazardID - FK> Consequence - varchar> > =Controls> --> ControlID - identity key field> HazardID - FK> Control - varchar> > > > dwight>

---

--=_NextPart_000_00FF_01C6AABD.53A28AE0--|||Hi Arnie
Thank you for the response
I find it hard to explain things like this :)
What I want to do is to 'append' the hazards, i will manually then go
through them and change the departments so that they are unique for
each department to log in
When I append them I want them to take the new HazardID to the related
tables
So to start with, I have 10 hazards with related consequences and
controls
For an example, I want to repeat those 10 hazards 10 times so that i
can assign them to 10 different departments
When I append the 10 hazards once each for each department I want the
controls and consequences to also append to their tables and for the
HazardID link the tables to the related tables
I know this is repeating data, but it is only to initialise the system
for the users and they will then go on to manage them differently
Does that make sense now?
thank you so very much for your assistance
Arnie Rowland wrote:
> If I understand you correctly, you want to retreive the Hazard Information and include the Consequences and Controls for each Hazard, and you want to limit this by Department. But you also indicated that there were 10 Hazards that were common to all Departments. If you want a list of Hazards by Department, this may work:
>
> SELECT
> h.HazardID
> , h.Hazard
> , cq.Consequence
> , cn.Control
> FROM Hazards h
> JOIN Consequences cq
> ON h.HazardID = cq.HazardID
> JOIN Conrols cn
> ON h.HazardID = cn.HazardID
> WHERE h.Department = <Department>
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <dwightsmail@.gmail.com> wrote in message news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com...
> >
> > hi all, can anyone help me?
> >
> > I am a relative newbie to sql server and I am more familiar with
> > Enterprise Manager than QA. I have made many many access databases
> > though. I am making an asp.net application where by there are a set
> > number of users, about 80, each one logs in and manages information
> > within their department.
> >
> > To get them started a manager has written 10 different hazards that
> > will apply to all of the departments, and he has written consequences
> > and controls for the hazards. Each department must have this
> > information as each will manage and deal with them differently
> >
> > The hazard information is stored in a main 'hazards' table, and the
> > consequences and controls are stored in related tables linked by the
> > 'hazardID' from the main table to a foreign key 'hazardID' in the
> > related tables
> >
> > What i want to know is if there is a relatively simple way of using a
> > query to populate the 10 hazards to each department, and to also
> > include the related table links, i dont mind renaming the departments
> > names to match each hazard, but i do not want to have to relink the
> > related tables manually
> >
> > If anyone can give me any advice to get me started i will be incredibly
> > grateful
> >
> > thank you
> >
> >
> > Table information is below
> >
> > Hazards
> > --
> > HazardID - identity key field
> > Hazard - varchar
> > Department - varchar
> >
> > Consequences
> > --
> > ConsequenceID - identity key field
> > HazardID - FK
> > Consequence - varchar
> >
> > Controls
> > --
> > ControlID - identity key field
> > HazardID - FK
> > Control - varchar
> >
> >
> >
> > dwight
> >
> --=_NextPart_000_00FF_01C6AABD.53A28AE0
> Content-Type: text/html; charset=iso-8859-1
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 4045
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> &

---

>
>
>
>
>
> If I understand you correctly, you want to retreive
> the Hazard Information and include the Consequences and Controls for each
> Hazard, and you want to limit this by Department. But you also indicated that
> there were 10 Hazards that were common to all Departments. If you want a list of
> Hazards by Department, this may work:
>
> SELECT
> h.HazardID ,
> h.Hazard , cq.Consequence , cn.ControlFROM
> Hazards h JOIN Consequences cq
> ON h.HazardID = cq.HazardID JOIN Conrols
> cn ON h.HazardID = cn.HazardIDWHERE
> h.Department =
> -- Arnie RowlandMost good judgment
> comes from experience. Most experience comes from bad judgment. -
> Anonymous
>
>
>< href="http://links.10026.com/?link=mailto:dwightsmail@.gmail.com"> size=2>dwightsmail@.gmail.com> wrote in
> message href="http://links.10026.com/?link=news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com"> face=Arial
> size=2>news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com face=Arial size=2>...> > hi all,
> can anyone help me?> > I am a relative newbie to sql server and I
> am more familiar with> Enterprise Manager than QA. I have made many many
> access databases> though. I am making an asp.net application where by
> there are a set> number of users, about 80, each one logs in and manages
> information> within their department.> > To get them
> started a manager has written 10 different hazards that> will apply to
> all of the departments, and he has written consequences> and controls for
> the hazards. Each department must have this> information as each will
> manage and deal with them differently> > The hazard information is
> stored in a main 'hazards' table, and the> consequences and controls are
> stored in related tables linked by the> 'hazardID' from the main table to
> a foreign key 'hazardID' in the> related tables> > What i
> want to know is if there is a relatively simple way of using a> query to
> populate the 10 hazards to each department, and to also> include the
> related table links, i dont mind renaming the departments> names to match
> each hazard, but i do not want to have to relink the> related tables
> manually> > If anyone can give me any advice to get me started i
> will be incredibly> grateful> > thank you> >
> > Table information is below> > Hazards>
> --> HazardID - identity key field> Hazard -
> varchar> Department - varchar> > Consequences>
> --> ConsequenceID - identity key field>
> HazardID - FK> Consequence - varchar> > Controls>
> --> ControlID - identity key field> HazardID -
> FK> Control - varchar> > > >
> dwight>

---

> --=_NextPart_000_00FF_01C6AABD.53A28AE0--