Solved

How to query and display a list of items in a single text box.

Posted on 2012-03-30
12
277 Views
Last Modified: 2012-03-30
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
0
Comment
Question by:MCaliebe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
12 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37787883
First, you have inconsistent data in your tables...

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...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37787905
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...?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37787927
...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...
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37787943
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(strsql,,dbfailonerror)
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
0
 

Author Comment

by:MCaliebe
ID: 37787977
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!
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37788010
See fnConcat in the accepted response to this thread
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37788054
...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
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 37788184
Practically all done for you...
BadgesI am sure you can take this and adapt it for your uses
(Page Breaks, Columns, headings, ...etc)

;-)

JeffCoachman
Access--report-Employee-Badges-M.mdb
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37788211
Over achiever!

Have a nice weekend, Jeff!

Dale
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37788474
Lol

Happy Gesticulating...

Jeff
0
 

Author Comment

by:MCaliebe
ID: 37788673
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37788700
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
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question