Access 2003 - Question

[Nadihaha]

Hi Guys, Hopefully this will be my last question for a while.

I currently have a software, hardware and employee database that for the
most part is working fabulously. It matches software to both people and
hardware and matches hardware to people. I have just one issue.

We should have a Standard Operating Environment (i.e. Software that is on
every single computer) What would be the best way of setting up the Standard
Operating Environment so that I can set my database to automatically enter
all of the sotware involved in the SOE onto the Hardware and Employee?

I want this so that when we get a new computer we can either printout a
report for the employee that the hardware is going to be given to detailing
all of the software that is going on to the computer. Or so that we can print
a report detailing what should be on each piece of Hardware, allowing us to
do an audit.

Does anybody have any ideas? Is there any way of grouping items, so that if
I select the group it automatically adds everything in the group?

Thanks So Much!!!

 

[NevilleT]

Hi
I would create a boolean field for SOE in your software list. You can then
run a query to pick all the SOE software from the table, and put it in a
recordset. You can then run an update query to create a record for each
piece of software for the new person based on the recordset. Loop through
the recordset taking the software details, add the user details, then write
the record.

Neville Turbit
www.projectperfect.com.au

 

[John W. Vinson]

Hi Guys, Hopefully this will be my last question for a while.

I currently have a software, hardware and employee database that for the
most part is working fabulously. It matches software to both people and
hardware and matches hardware to people. I have just one issue.

We should have a Standard Operating Environment (i.e. Software that is on
every single computer) What would be the best way of setting up the Standard
Operating Environment so that I can set my database to automatically enter
all of the sotware involved in the SOE onto the Hardware and Employee?

I want this so that when we get a new computer we can either printout a
report for the employee that the hardware is going to be given to detailing
all of the software that is going on to the computer. Or so that we can print
a report detailing what should be on each piece of Hardware, allowing us to
do an audit.

Does anybody have any ideas? Is there any way of grouping items, so that if
I select the group it automatically adds everything in the group?

Thanks So Much!!!

Your software table should have a field indicating that the program is part of
the SOE (a Yes/No field would be fine, as Neville suggests). A simple way to
get it into the junction table would be an Append Query, selecting the
employee's ID from one table and the softwareID from the software table, using
the yes/no field to identify the records to be appended.

 

[Nadihaha]

Okay I have the append query running. But how do I limit it to only add the
new records? At the moment it adds them all again multiple times, I think I
did something wrong. Also how can I get it to run automatically? I can add an
event procedure after update but I can't for the life of me figure out how to
tell it to run my append query.

HELP!! Muchly appreciated!!

 

[John W. Vinson]

Okay I have the append query running. But how do I limit it to only add the
new records? At the moment it adds them all again multiple times, I think I
did something wrong. Also how can I get it to run automatically? I can add an
event procedure after update but I can't for the life of me figure out how to
tell it to run my append query.

Since I don't know what you did, it's a bit hard to tell what you did wrong!
Please open the append query in SQL view and post the SQL text here.

The best way to run a query is to use code like

Dim db As DAO.Database
On Error GoTo Proc_Error
Set db = CurrentDb
db.Execute "MyAppendQuery", dbFailOnError
<other code>
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox "Error " & Err.Number & " in MyProgram:" & vbCrLf & Err.Description
Resume Proc_Exit
End Sub

 

[Nadihaha]

Sure thing John (Learnt something just doing that!!)

INSERT INTO Employee_Software ( [Employee ID], [Software ID], Required )
SELECT DISTINCTROW Employee_Hardware.[Employee ID], Software.[Software ID],
Employee_Software.Required
FROM Employee_Hardware, Software INNER JOIN Employee_Software ON
Software.[Software ID] = Employee_Software.[Software ID]
WHERE (((Software.[Standard Operating Environment])=Yes) AND
((Employee_Hardware.[Requires SOE])=Yes));

Hmmm your looks nothing like mine. If I copy and paste yours into mine and
change the names do you think that would work?

 

[John W. Vinson]

Sure thing John (Learnt something just doing that!!)

INSERT INTO Employee_Software ( [Employee ID], [Software ID], Required )
SELECT DISTINCTROW Employee_Hardware.[Employee ID], Software.[Software ID],
Employee_Software.Required
FROM Employee_Hardware, Software INNER JOIN Employee_Software ON
Software.[Software ID] = Employee_Software.[Software ID]
WHERE (((Software.[Standard Operating Environment])=Yes) AND
((Employee_Hardware.[Requires SOE])=Yes));

Hmmm your looks nothing like mine. If I copy and paste yours into mine and
change the names do you think that would work?

What I posted is utterly different from what you posted because it's in a
different language! You posted the SQL to a query; I posted VBA code which can
be called to *execute* a query. There are many different ways to do so (and
most of them work), so if you're running your query OK there should be no
problem.

If you want your query to NOT append records which already exist for this
combination of EmployeeID and SoftwareID, you can do either or both of two
fixes: first, create a unique Index on the combination of the two fields. To
do so open Employee_Software in design view; select the Indexes tool; and
create a new index. Select some distinctive name in the left column, and
Employee ID in the right; in the next row on the right select Software ID.
Check the "Unique" checkbox. This will cause an error if you attempt to insert
two records for the same software for the same employee.

Your query as posted has a "Cartesian join" - it will match EVERY record in
Employee_Hardware up with EVERY record in Software. I'm not sure how you have
your relationships set up but this probably isn't how you want to do this! How
are you selecting which employee should be getting this data?

 

[Nadihaha]

Thanks for your patience John. I am a total novice at this so I have
absolutely no idea!!.

What I currently have is in the software table there is a check box flagging
the software as SOE.

Then I have a table with a form to enter data, where I select the staff
member from a drop-down combo box. Then I select the Hardware from a
Drop-down Combo Box and have a Checkbox flagging that the hardware needs the
SOE.

What I would ideally like to happen is that I can set up an event thingo so
that after the update happens the new records are appended onto 2 tables (but
I'm going to get one working first) The table I have called Employee_Software
(this links software to employee so I can print a report telling me which
software an employee needs on their PC) and a table called Hardware_Software
(linking hardware to software) I know it seems like duplication but at the
moment we have no way of auditing what is needed on the PC and what is
actually on there. The idea is that this will also help if the PC's get
switched around for some reason and we need to find out if the right software
is on the PC for the Employee.

I am looking for the simplest way to do this so any guidance is greatly
appreciated

Sure thing John (Learnt something just doing that!!)

INSERT INTO Employee_Software ( [Employee ID], [Software ID], Required )
SELECT DISTINCTROW Employee_Hardware.[Employee ID], Software.[Software ID],
Employee_Software.Required
FROM Employee_Hardware, Software INNER JOIN Employee_Software ON
Software.[Software ID] = Employee_Software.[Software ID]
WHERE (((Software.[Standard Operating Environment])=Yes) AND
((Employee_Hardware.[Requires SOE])=Yes));

Hmmm your looks nothing like mine. If I copy and paste yours into mine and
change the names do you think that would work?

What I posted is utterly different from what you posted because it's in a
different language! You posted the SQL to a query; I posted VBA code which can
be called to *execute* a query. There are many different ways to do so (and
most of them work), so if you're running your query OK there should be no
problem.

If you want your query to NOT append records which already exist for this
combination of EmployeeID and SoftwareID, you can do either or both of two
fixes: first, create a unique Index on the combination of the two fields. To
do so open Employee_Software in design view; select the Indexes tool; and
create a new index. Select some distinctive name in the left column, and
Employee ID in the right; in the next row on the right select Software ID.
Check the "Unique" checkbox. This will cause an error if you attempt to insert
two records for the same software for the same employee.

Your query as posted has a "Cartesian join" - it will match EVERY record in
Employee_Hardware up with EVERY record in Software. I'm not sure how you have
your relationships set up but this probably isn't how you want to do this! How
are you selecting which employee should be getting this data?