How to query and display a list of items in a single text box.
I am currently working on an ID Badge project for my organization.
I have a number of table, the primary is the employee details, and a secondary table tbl_emp_endorsements which contaings all the departments each employee is trained to work in as well as any special skills they have been trained on.
I've enclosed a sample database to give an idea of what I"m working with.
My basic issue is that I can get single field data onto the badge, however when an employee has been authorized tyo work in multiple departments, however do I get this information to populate on one badge? to complicate matters, I have to do this twice as Skills are listed on another line.
Example
Endoresement Section of the badge
DEPARTMENTS
FIP REC SHP <----multiple department records by employee
SKILLS
A C D G J <------multiple skill records by employee
So esentially, I want the Badge grouped by employee with a detail section for Departments and one for Skills. Then I need all the data concantenated into a text box.
I hope I was able to explain my intentions well enough. Thanks for any input.
Badge-Sample.accdb
I have a number of table, the primary is the employee details, and a secondary table tbl_emp_endorsements which contaings all the departments each employee is trained to work in as well as any special skills they have been trained on.
I've enclosed a sample database to give an idea of what I"m working with.
My basic issue is that I can get single field data onto the badge, however when an employee has been authorized tyo work in multiple departments, however do I get this information to populate on one badge? to complicate matters, I have to do this twice as Skills are listed on another line.
Example
Endoresement Section of the badge
DEPARTMENTS
FIP REC SHP <----multiple department records by employee
SKILLS
A C D G J <------multiple skill records by employee
So esentially, I want the Badge grouped by employee with a detail section for Departments and one for Skills. Then I need all the data concantenated into a text box.
I hope I was able to explain my intentions well enough. Thanks for any input.
Badge-Sample.accdb
You are also using oddly named Keys...
in tblEndoresments, the key is "skill"?
In the Customer table the Pk should be CustomerID
In the Order table the PK should be OrderID
In the Products table the PK should be ProductID
In the BT6reDs243Sw782 table the PK should be BT6reDs243Sw782ID
....Make sense...?
in tblEndoresments, the key is "skill"?
In the Customer table the Pk should be CustomerID
In the Order table the PK should be OrderID
In the Products table the PK should be ProductID
In the BT6reDs243Sw782 table the PK should be BT6reDs243Sw782ID
....Make sense...?
...and you really need not store the Department in the Employes table, ...
If one Emp can have many Departments, then you need a "Junction" table...
If one Emp can have many Departments, then you need a "Junction" table...
unable to download your app due to corporate policies.
However, what you are looking for is the ability to concatenate data from a one-to-many relationship into a single result.
To do this, you would create a query and then a recordset based upon that query. Then loop through the recordset, building a string that contains the values you are looking for. Something like:
Dim strSQL as string
Dim rs as DAO.Recordset
Dim strDept as string
strSQL = "SELECT tblEmpDepts.Dept FROM tblEmployees INNER JOIN tblEmpDepts " _
& "ON tblEmployees.EmpID = tblEmpDepts.EmpID " _
& "WHERE tblEmployees.EmpID = 25"
set rs = currentdb.openrecordset(st
While not rs.eof
strDept = strDept & "," & rs!Dept
rs.movenext
Wend
strDept = mid(strDept, 2)
rs.close
set rs = nothing
Then, in order to concatenate several items for display in a single multi-line textbox you would do something like:
me.txtDeptAndSkills = "Departments:" & vbcrlf & strDepartments & vbcrlf & vbcrlf _
& "Skills:" & vbcrlf & strSkills
However, what you are looking for is the ability to concatenate data from a one-to-many relationship into a single result.
To do this, you would create a query and then a recordset based upon that query. Then loop through the recordset, building a string that contains the values you are looking for. Something like:
Dim strSQL as string
Dim rs as DAO.Recordset
Dim strDept as string
strSQL = "SELECT tblEmpDepts.Dept FROM tblEmployees INNER JOIN tblEmpDepts " _
& "ON tblEmployees.EmpID = tblEmpDepts.EmpID " _
& "WHERE tblEmployees.EmpID = 25"
set rs = currentdb.openrecordset(st
While not rs.eof
strDept = strDept & "," & rs!Dept
rs.movenext
Wend
strDept = mid(strDept, 2)
rs.close
set rs = nothing
Then, in order to concatenate several items for display in a single multi-line textbox you would do something like:
me.txtDeptAndSkills = "Departments:" & vbcrlf & strDepartments & vbcrlf & vbcrlf _
& "Skills:" & vbcrlf & strSkills
ASKER
Thanks everyone.
I see a number of good suggestions here. I will be looking at them all. I pulled this data from another database so I might have disturbed some of the relationships. Let me look at it all and see what I come up with.
Thanks again!
I see a number of good suggestions here. I will be looking at them all. I pulled this data from another database so I might have disturbed some of the relationships. Let me look at it all and see what I come up with.
Thanks again!
...On your basic "Design"...
Here is a sample DB.
In the Report, I left the Departments and Skills in "Tabular" format, just so that you can see that if the "design" is correct then this is fairly straightforward...
I will tweak a sample up with the "concatenation a bit later...
Access--report-Employee-Badges-M.mdb
Here is a sample DB.
In the Report, I left the Departments and Skills in "Tabular" format, just so that you can see that if the "design" is correct then this is fairly straightforward...
I will tweak a sample up with the "concatenation a bit later...
Access--report-Employee-Badges-M.mdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Over achiever!
Have a nice weekend, Jeff!
Dale
Have a nice weekend, Jeff!
Dale
Lol
Happy Gesticulating...
Jeff
Happy Gesticulating...
Jeff
ASKER
Haha...you guys are great.
Thank you so much for your work Jeff. The examples will help me tremendously. As I'm not "clasically" trained, my databases are pretty much fly by night. I'm certain I am at a loss for some of the simpler pricipals in DB design.
I'll look to apply the structures detailed.
Have a great weekend.
Michael
Thank you so much for your work Jeff. The examples will help me tremendously. As I'm not "clasically" trained, my databases are pretty much fly by night. I'm certain I am at a loss for some of the simpler pricipals in DB design.
I'll look to apply the structures detailed.
Have a great weekend.
Michael
Than as I stated, you might want to get *really* comfortable with the basic database theories...
Normalizarion
Keys
Relationships
...ect
*Before* you ever start worrying about custom reports...
As I stated, if your DB was originally normalized, this would all be easy...
See this book as a start:
http://www.amazon.com/Database-Design-Mere-Mortals-Relational/dp/0201752840/ref=dp_ob_title_bk
Keep us posted on your progress...
JeffCoachman
Normalizarion
Keys
Relationships
...ect
*Before* you ever start worrying about custom reports...
As I stated, if your DB was originally normalized, this would all be easy...
See this book as a start:
http://www.amazon.com/Database-Design-Mere-Mortals-Relational/dp/0201752840/ref=dp_ob_title_bk
Keep us posted on your progress...
JeffCoachman
As I try to link the appropriate fields in the relationship screen, I get referential integrity errors and "Indeterminate" Relationship types.
So this need to be fixed first...