Breaking out data

[John Menken]

Hi,
I am using Access 2010 and I have a query question.
I have four fields, empID, CourseName, FirstName, and Lastname.
I would like to separate out the data to show the courses that each
person took.

Example:

6 Basics of ABC John Doe
6 Fundamentals of DEF John Doe 2
7 Skilled Writing Carol Adams 1
8 Basics of ABC Eric Morris
8 Fundamentals of DEF Eric Morris
8 Leadership 101 Eric Morris
8 New Manager Basics Eric Morris 4


Can I do this with the use of a Query?
Many thanks.

 

[John W. Vinson]

Hi,
I am using Access 2010 and I have a query question.
I have four fields, empID, CourseName, FirstName, and Lastname.
I would like to separate out the data to show the courses that each
person took.

Example:

6 Basics of ABC John Doe
6 Fundamentals of DEF John Doe 2
7 Skilled Writing Carol Adams 1
8 Basics of ABC Eric Morris
8 Fundamentals of DEF Eric Morris
8 Leadership 101 Eric Morris
8 New Manager Basics Eric Morris 4


Can I do this with the use of a Query?
Many thanks.

How would you like to see the result? What you posted does show the name and
the course; I take it that's not what you want.

If you want to see

John Doe Basics of ABC, Fundamentals of DEF

then you'll need a Query with the help of a little bit of VBA code; there's
sample code at http://www.mvps.org/access/modules/mdl0004.htm.

If instead you want a display like

John Doe
Basics of ABC
Fundamentals of DEF
Carol Adams
Skilled Writing
Eric Morris
<etc>

then your best bet would be to create a Report based on the table (or a query
of the table), using the report's Sorting and Grouping to group by EmpID; put
the empID and name in the group header and the course name in the detail
section.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[John Menken]

Thank you.
I should've been a little more precise in what I was describing.
I want to see a report that looks like what I show above with a number
after the last record of each group showing the number of courses each
person took.
John Doe took two courses, for example, and Eric Morris took four
courses, but Carol Adams only took one course.

6 Basics of ABC John Doe
6 Fundamentals of DEF John Doe [the number 2 would go here since Doe
took 2 courses]
7 Skilled Writing Carol Adams [the number 1 would go here since
Adams took 1 course]
8 Basics of ABC Eric Morris
8 Fundamentals of DEF Eric Morris
8 Leadership 101 Eric Morris
8 New Manager Basics Eric Morris [the number 4 would go here since
Morris took 4 courses]

Thank you.

 

[John W. Vinson]

Thank you.
I should've been a little more precise in what I was describing.
I want to see a report that looks like what I show above with a number
after the last record of each group showing the number of courses each
person took.
John Doe took two courses, for example, and Eric Morris took four
courses, but Carol Adams only took one course.

6 Basics of ABC John Doe
6 Fundamentals of DEF John Doe [the number 2 would go here since Doe
took 2 courses]
7 Skilled Writing Carol Adams [the number 1 would go here since
Adams took 1 course]
8 Basics of ABC Eric Morris
8 Fundamentals of DEF Eric Morris
8 Leadership 101 Eric Morris
8 New Manager Basics Eric Morris [the number 4 would go here since
Morris took 4 courses]

Thank you.

Use a calculated field

CoursesTook: DCount("*", "[yourtable]", "[EmpID] = " & [EmpID])

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com