Duplicating Data

[Lyne Savage]

I am a beginner...repeat, Beginner. I have several tables that I need to join
into a Query, in order to specify a certain Employee name as a filter. I want
all information from that certain employee from all related tables to be put
into a query. I have a one-to-many relationship specified for my tables. The
primary key for my main table is the employee name. Employee name then
appears on all other tables, not as primary key. When I put together the
tables I need into a query, all data is duplicated several times....help?

 

[Jeff Boyce]

Lyne

Correct. If you have a one-to-many relationship, each "one" would be
reasonably expected to have "many" records in your query.

Perhaps you are thinking that the query is the only way to display the data.
Take a look at reports, which will give you a way to group by data elements
without having them repeat.

By the way, I know two folks with the same name ... and others here know
three or more. How "safe" is it to use [EmployeeName] as a unique
identifier (i.e., Primary Key)? (hint: NOT!)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Lyne Savage]

I am building this for someone who has even less knowledge than me...and That
is the way they want it. We are a small company in a small town, names will
not be duplicated. This database will also be for confidential internal use
only.

I will try out the reports idea.

Lyne

Correct. If you have a one-to-many relationship, each "one" would be
reasonably expected to have "many" records in your query.

Perhaps you are thinking that the query is the only way to display the data.
Take a look at reports, which will give you a way to group by data elements
without having them repeat.

By the way, I know two folks with the same name ... and others here know
three or more. How "safe" is it to use [EmployeeName] as a unique
identifier (i.e., Primary Key)? (hint: NOT!)

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am a beginner...repeat, Beginner. I have several tables that I need to
join
into a Query, in order to specify a certain Employee name as a filter. I
want
all information from that certain employee from all related tables to be
put
into a query. I have a one-to-many relationship specified for my tables.
The
primary key for my main table is the employee name. Employee name then
appears on all other tables, not as primary key. When I put together the
tables I need into a query, all data is duplicated several times....help?

 

[John W. Vinson]

I am a beginner...repeat, Beginner. I have several tables that I need to join
into a Query, in order to specify a certain Employee name as a filter. I want
all information from that certain employee from all related tables to be put
into a query. I have a one-to-many relationship specified for my tables. The
primary key for my main table is the employee name. Employee name then
appears on all other tables, not as primary key. When I put together the
tables I need into a query, all data is duplicated several times....help?

That's probably EXACTLY WHAT IT SHOULD BE DOING. That's how queries work!

Let's step back a bit though. A Primary Key should meet three criteria: it
MUST be unique; it should be stable (not changing over time); and it should be
short, for efficiency. People's names fail all three of these! My name's John
W. Vinson; I once worked at a college with a Professor John W. Vinson.
Somebody used our names as an identifier once, and it took me two years of
fighting with the Internal Revenue Service to clear up the mess. You should
have a unique EmployeeID and use THAT as the primary key and foreign key; the
names should NOT be stored in any other table.

To the duplicates, though: let's say you have Employees related one to many to
(say) Dependents and to SecurityClearances. If you just create a Query joining
Employees to both tables, and Joe Schmoe has five dependents and six
clearences, you'll get all 30 possible combinations (since there is no
connection between little Sally Schmoe and her dad's Database Administration
clearance). That is correct behaviour for the database! If you want to
*display* the data, don't use one big master query to do so; instead consider
using a Form based on Employees, with two Subforms - one for each "many" side
table.

If you'ld care to post your actual table structure and an example of the data
you want to see someone may be able to give more specific help.

 

[Lyne Savage]

First of all, I know that it is hard for you IT geniuses not to look down on
people like me, but I just need help. If you can help me, please stay
respectful. I am a beginner.

I have 6 tables, one for each production line. Each table has all training
given and received for all employees on that line. What I need to be able to
pull up is all training that each employee has received. That is why I
thought queries might help me. One query per employee....Some employees
received training on several lines, so I imagine that relationships must play
a roll. Also, the data must be editable as employees receive training often.
ALSO, the person I am doing this for is not very familiar with Access, (yes,
even less than me) so I want to put this information into forms..and forms
are not a problem for me.

SO, i need help with relationships and queries...put in layman's terms please.

 

[John W. Vinson]

First of all, I know that it is hard for you IT geniuses not to look down on
people like me, but I just need help. If you can help me, please stay
respectful. I am a beginner.

I apologize, Lyne. I'm just trying to keep you from making a bad mistake.

I have 6 tables, one for each production line.

And that's a mistake too, I fear! Storing data in tablenames is good
spreadsheet design but it's simply WRONG for relational databases. A much
better design would have *one* table with a field for the production line.

Each table has all training
given and received for all employees on that line.

With the properly normalized design, if an employee has been trained on five
lines she'd have five rows in the table, and a simple join query would show
her name and personal data repeated five times (one for each record) and the
five training rows.

The table would have fields like Employee (name, or I'd really suggest ID; you
can use an autonumber), ProductionLine (which line they were trained on),
TrainingDate, any other information about this employee's training on this
line.

It's *crucial* to get your table design right to start with; otherwise you'll
have to spend all your time working around it with complicated queries and
code.

What I need to be able to
pull up is all training that each employee has received. That is why I
thought queries might help me. One query per employee....Some employees
received training on several lines, so I imagine that relationships must play
a roll.

You certainly do NOT need one query per employee!! Instead consider a
"Parameter Query" - one of the most useful features of Access. Instead of a
criterion like

"Joe Schmoe"

use a criterion

[Enter employee name:]

Or, much better, use a little form frmCrit with a combo box cboEmployee
selecting all the employees and a criterion

[Forms]![frmCrit]![cboEmployee]

This will be easier for the user, as well as saving you from having to create
a new query whenever anyone changes jobs.

Also, the data must be editable as employees receive training often.

Of course.

ALSO, the person I am doing this for is not very familiar with Access, (yes,
even less than me) so I want to put this information into forms..and forms
are not a problem for me.

That's exactly what I'm suggesting: putting the data in Tables, using Queries
to manage the data, and editing and displaying it on Forms. You're on the
right track - keep going!

SO, i need help with relationships and queries...put in layman's terms please.

Try some of these resources, especially Crystal's tutorial:

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

 

[Jeff Boyce]

Lyne

One of the disadvantages of using Access with little experience is that it
looks way too much like a spreadsheet ... and it isn't one.

If you wish to make this easy (easier) for that someone, YOU will need to
become more conversant in Access ... not an easy learning curve!

Best of luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am building this for someone who has even less knowledge than me...and
That
is the way they want it. We are a small company in a small town, names
will
not be duplicated. This database will also be for confidential internal
use
only.

I will try out the reports idea.

Lyne

Correct. If you have a one-to-many relationship, each "one" would be
reasonably expected to have "many" records in your query.

Perhaps you are thinking that the query is the only way to display the
data.
Take a look at reports, which will give you a way to group by data
elements
without having them repeat.

By the way, I know two folks with the same name ... and others here know
three or more. How "safe" is it to use [EmployeeName] as a unique
identifier (i.e., Primary Key)? (hint: NOT!)

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am a beginner...repeat, Beginner. I have several tables that I need to
join
into a Query, in order to specify a certain Employee name as a filter.
I
want
all information from that certain employee from all related tables to
be
put
into a query. I have a one-to-many relationship specified for my
tables.
The
primary key for my main table is the employee name. Employee name then
appears on all other tables, not as primary key. When I put together
the
tables I need into a query, all data is duplicated several
times....help?

 

[Lyne Savage]

ok...so I changed my table structure, I now have only one... and I added a
new ID number as Primary key...so that's fine, and I will try a paramater
Query...no need to worry about relationships yet, yay!

But while I have you, my next problem is the form. The person I am doing
this for had this in Excel. She had a tab for each line and was able to see
at a glance which employee needed what kind of training.... I have no idea
how to do the same thing in Access. Right now, If i make a form, it will give
me one employee at a time, one line at a time, one training session at a
time. Is there a way to get that overall view, with all information for a
line on one form? I've tried researching forms, subforms, grouping...really
tired of researching and not getting anywhere.

BTW, the company will be getting me trained in Access, but I am not very
patient! (They are taking a long time arranging it and it will be a few
weeks yet..)

First of all, I know that it is hard for you IT geniuses not to look down on
people like me, but I just need help. If you can help me, please stay
respectful. I am a beginner.

I apologize, Lyne. I'm just trying to keep you from making a bad mistake.

I have 6 tables, one for each production line.

And that's a mistake too, I fear! Storing data in tablenames is good
spreadsheet design but it's simply WRONG for relational databases. A much
better design would have *one* table with a field for the production line.

Each table has all training
given and received for all employees on that line.

With the properly normalized design, if an employee has been trained on five
lines she'd have five rows in the table, and a simple join query would show
her name and personal data repeated five times (one for each record) and the
five training rows.

The table would have fields like Employee (name, or I'd really suggest ID; you
can use an autonumber), ProductionLine (which line they were trained on),
TrainingDate, any other information about this employee's training on this
line.

It's *crucial* to get your table design right to start with; otherwise you'll
have to spend all your time working around it with complicated queries and
code.

What I need to be able to
pull up is all training that each employee has received. That is why I
thought queries might help me. One query per employee....Some employees
received training on several lines, so I imagine that relationships must play
a roll.

You certainly do NOT need one query per employee!! Instead consider a
"Parameter Query" - one of the most useful features of Access. Instead of a
criterion like

"Joe Schmoe"

use a criterion

[Enter employee name:]

Or, much better, use a little form frmCrit with a combo box cboEmployee
selecting all the employees and a criterion

[Forms]![frmCrit]![cboEmployee]

This will be easier for the user, as well as saving you from having to create
a new query whenever anyone changes jobs.

Also, the data must be editable as employees receive training often.

Of course.

ALSO, the person I am doing this for is not very familiar with Access, (yes,
even less than me) so I want to put this information into forms..and forms
are not a problem for me.

That's exactly what I'm suggesting: putting the data in Tables, using Queries
to manage the data, and editing and displaying it on Forms. You're on the
right track - keep going!

SO, i need help with relationships and queries...put in layman's terms please.

Try some of these resources, especially Crystal's tutorial:

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

 

[John W. Vinson]

ok...so I changed my table structure, I now have only one... and I added a
new ID number as Primary key...so that's fine, and I will try a paramater
Query...no need to worry about relationships yet, yay!

Well, actually you should: each Employee has zero, one or more Trainings, each
Training is applied to one and only one Employee. So you have a perfectly
classic one to many relationship.

But while I have you, my next problem is the form. The person I am doing
this for had this in Excel. She had a tab for each line and was able to see
at a glance which employee needed what kind of training.... I have no idea
how to do the same thing in Access. Right now, If i make a form, it will give
me one employee at a time, one line at a time, one training session at a
time. Is there a way to get that overall view, with all information for a
line on one form? I've tried researching forms, subforms, grouping...really
tired of researching and not getting anywhere.

A single Form (based on Employees) with a *continuous* Subform (set the
Default View property of the form to Continuous) will show the employee's data
with multiple rows for their trainings.

BTW, the company will be getting me trained in Access, but I am not very
patient! (They are taking a long time arranging it and it will be a few
weeks yet..)

The resources I posted have TONS of useful stuff, more than most Access
training courses I've seen; and there's a bunch of us here on the newsgroup
who are happy to answer (specific) questions. Heck, with your enthusiasm the
course instrctor may ask you to be a teaching assistant! <g>