Forms - Displaying Multiple Records in one form

[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 Production line and was able to see at a glance which employee needed what kind of training.... All employees were on one page. 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. Is there a way to display several records at a time?

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..)

On Tue, 8 Sep 2009 10:22:01 -0700, Lyne Savage

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

 

[KARL DEWEY]

Use a query to feed form that is in Datasheet view.
--
Build a little, test a little.

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 Production line and was able to see at a glance which employee needed what kind of training.... All employees were on one page. 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. Is there a way to display several records at a time?

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..)

On Tue, 8 Sep 2009 10:22:01 -0700, Lyne Savage
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

Let me see if I'm misunderstanding.

I would say that you need (at least) two tables: a table of Employees, primary
key EmployeeID, field for lastname, firstname, other biographical data;

and a second table, Training, with fields TrainingID (autonumber primary key),
EmployeeID (long integer, related to Employees.EmployeeID), LineNo (which
production line), and other information about this employee's training on this
line, e.g. TraningDate, fields indicating the outcome of the training, etc.

If you have everything all in one table you've still got a ways to go!

 

[Lyne Savage]

which is why I had separate tables to begin with. I listened to one guy who
told me to combine...luckily I save dmy old structure.

I think I quit. I'll wait for the training. Out of my league.