Add mutiple records into a table

[Jenn]

Hi there,
I have posted this question before and have not gotten the correct response,
so I am going to try again. I am a beginner and I need to add several
records into a table.
We have a software table and I need to add several pieces of software for
each user, e.g. Windows XP, Ashampoo, etc (there are 13 different pieces of
software) into the user software table. I was wondering if there is an easy
way to do this without having to type each piece of software 300 times (there
are 300 employees). I know there is not suppose to be duplicate records in a
table, but this is what my Director wants and he won't budge. Any help would
be great.
Thanks

 

[Gina Whipp]

Jenn,

I do not see your previous posting so can't answer for the response you
got...

It sounds like you need a *joiner* table which would in effect Join Software
to Employee without storing duplicates in the Software table. However, it
is really hard to say without knowing the tables you already have. So can
you tell us what you have for tables now?

--
Gina Whipp
2010 Microsoft MVP (Access)

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

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

 

[John Spencer]

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

Assuming that you have a table of SoftwareTypes - one record for each specific
type of software and a table of users (one record per user) and a table that
joins the two - UsersSoftware.

INSERT INTO UsersSoftware (UserID, SoftwareID)
SELECT Users.UserID, SoftwareTypes.SoftwareID
FROM Users, SoftwareTypes

Before you do this add a compound unique index to the UsersSoftware table
based on the UserID and SoftwareID fields to keep from entering duplicates.

To create a multiple field unique index (Compound index)
--Open up the table in design mode
--Select View: Index from the menu
--Enter a name for the iIndex in first row under Index Name
--Select one field in the index under Field Name
--Set Unique to Yes
--Move down one line and select the next FieldName
(Do NOT skip rows, do NOT enter the index name again)
--Continue moving down and selecting fieldnames until all needed fields are
included.
--Close the index window and close and save the table

You can decide to keep the index or delete it depending on your requirements.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Steve]

It sounds like you want to record what software each employee has on his
computer. You need three tables:
TblEmployee
EmployeeID
Firstname
LastName
etc

TblSoftware
SoftwareID
SoftwareTitle

TblEmployeeSoftware
EmployeeSoftwareID
EmployeeID
SoftwareID

For data entry you need a form/subform. The main form needs to be based on
TblSoftware and the subform needs to be based on TblEmployeeSoftware. Set
the LinkMaster and Linkchild properties to EmployeeID. Make the subform a
continuous form. For each record in the subform you need a combobox whose
rowsource is TblSoftware and a hidden textbox for EmployeeID. You will be
able to create a list of software in the subform for any selected employee
in the main form. After data entry you will them be able to display the list
of software on the computer of any selected employee in the main form.
Further, on the main form you can enter new employees and you could create a
process on the subform to enter new software to TblSoftware. Initially, with
this setup, you only need to add the 13 software titles once.

Steve
(e-mail address removed)

 

[Dorian]

The way you do this is to have 3 tables:
Employee
Software
Employee-Software
You have all the employees and software products (just once each) in the
first 2 tables and then which software is used by which employee in the third
table.

Believe me your director does not care about the internal design of the
database, all he cares about is the data that is shown on forms or reports.

Once you have the database designed correctly, we can come up with a way to
load the data. In what form do you have the data (which software is used by
which employee)?

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Jenn]

I have the Employee and Software table setup. So in the Employee-Software do
I add every piece of software each user has into this table? This is the
table I was hoping to be able to merge the two tables together, so I didn't
have to type each piece of Software into the Employee-Software table?

 

[Jenn]

Hi Gina,

I have a Employee and Software Table.

Jenn,

I do not see your previous posting so can't answer for the response you
got...

It sounds like you need a *joiner* table which would in effect Join Software
to Employee without storing duplicates in the Software table. However, it
is really hard to say without knowing the tables you already have. So can
you tell us what you have for tables now?

--
Gina Whipp
2010 Microsoft MVP (Access)

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

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




.

 

[John W. Vinson]

I have the Employee and Software table setup. So in the Employee-Software do
I add every piece of software each user has into this table? This is the
table I was hoping to be able to merge the two tables together, so I didn't
have to type each piece of Software into the Employee-Software table?

You don't have to type ANYTHING.

This is a perfectly standard and common many to many relationship. The
Employee-Software table has two key fields: the EmployeeID, a link to the
Employees table identifying who has this piece of software; and a SoftwareID,
identifying which software they have. There might be other fields if there is
specific information about *this* employee's copy of *this* software (e.g.
date installed, individual license information, version if that's not part of
your Software table).

You could use a Form based on Employees, with a Subform based on
EmployeeSoftware; you could have a combo box on the subform to select Access
2007 or Word 2003 or Zork I from a dropdown, storing that software's ID.

A very analogous situation can be seen in many sample databases; for instance,
in the Northwind sample database, there is a table of Orders, a table of
Products, and a similar resolver table called OrderDetails; it has fields for
the OrderID and the ProductID. Check it out there.

If you are thinking that you need to open table datasheets and enter data
there... *don't*. Tables are for data storage, forms for interacting with the
data. Check out Crystal's video and the other resources on these sites:

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

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

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

 

[Gina Whipp]

Jenn,

I see John got you an answer... Good thing because I got a little tied up.
If you need any additional help just post back.

--
Gina Whipp
2010 Microsoft MVP (Access)

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

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