Convert Output of Query result field to a single field

R

Ruth

I have an activity table that tracks employee activity such as hires,
transfers, terminations, etc. I have queries that 1) list the names of
the employees for each category for the report period and 2) count the
number of transactions for each type of activity. I would like to take
the results of the query that lists the employees for the activity
period and put the names into a single field. I would then take that
result and update the Report table field "TermNames" with that value.
Then, I could use that field value in the report I'm creating to list
the names of each employee for the report period. Is that possible to
do?

Current report format (in Excel) is:

Terminations (3): Jones, Smith, Brown
Transfers (2): Black, White
etc.

I can copy and modify code, but can't write it. :-/ Any suggestions?

Thanks in advance for any help.
 
L

Larry Daugherty

Hi Ruth,

Yes, it's possible to do what you want to do. Why do you want to do
it? It crams your data down into a tight jumble rather than breaking
it out and 'properly' displaying it. Why do you want it so tight?

First, the tools in Access assume that, since we've gone through all
of the machinations to whip the data into at least third normal form,
we'd like to keep it that way. Your expressed desire is to display it
as a flat file cluster in three instances: hires, transfers,
terminations.

Second, your (miss)use of relational database nomenclature and your
reference to an Excel model cause me to believe that you're early in
the transition to Access and that you have been doing your list
management in Excel.

Whether my assumptions are true or not, Welcome to the wonderful world
of Access! It can be a lot of fun and if you persevere you'll be able
to do some awesome things with it. That's the good feeling part of
it.

The down side is that Access is the most difficult of the MS Office
applications to learn. That learning curve is long and steep.
Relational database management systems are based on SET Theory from
mathematics. There is an awful lot of it that is simply
counterintuitive. You read things and just know that the author is a
fraud. Word and Excel are particularly easy to learn: You learn some
of the basics and can apply them immediately. In Access you have to
learn quite a bit before you can do anything useful. Word and Excel
have evolved from early word processors and spreadsheets and were user
level applications from the 'git go'. Under the hood they are both
very powerful but you're protected from the complexities unless you
need the real power. In Access you're exposed to things breaking and
going wrong from the very start.. In addition, the error messages
that get thrown up (pun intended) usually have nothing to do with the
issues at hand and nothing at all to do with what you're trying to
accomplish. I'm not trying to discourage you, far from it. I want
you to know that when things go wrong you should be patient with
yourself. It isn't because you are stupid! It's tough getting up to
speed with Access.

There are lots of rules to be learned and observed in the pursuit of
Access. Learn them and observe them. Plan to buy books appropriate
to your current level of understanding. Google the Access newsgroups
for recommended books. These newsgroups are excellent resources. For
newbies to Access I recommend both microsoft.public.access.tables
design and microsoft.public.access.gettingstarted. Lurk them daily
for an hour or so. Get to know the issues being posted and understand
the responses. Keep lurking those two groups until there are no more
surprises. Get so you can post some responses. Another great
resource is www.mvps.org/access Visit the site and poke around a
whole bunch. It is filled with Access lore and continually grows and
increases its value.

The heart and soul of any database application is the data design.
Analysis of your application will flush out the entities involved.
Entities in your application are captured in tables named for them.
Good candidate names for tables might be Person, Employee, Company,
Order, Manufacturer, etc. Not so good are things like Main, Master,
etc. It doesn't mater whether names are singular or plural. If your
data design is good you'll have an application you can enhance
indefinitely. If the data design is screwed up your application will
become increasingly difficult or impossible to maintain and enhance.

I know, I know. I haven't addressed your issues directly. I was
addressing some of the more important stuff first. :)

Using the standard tools provided by Access your sample data would
fall out something like:
==================================================

Terminations for the period ended November 20, 2005
Name: EmployeeID:
Jones, Rose 123456
Smith, Irwin 234234
Brown, Butch 345345

Total
terminations 3
----------------------------------------------------------------------
--------------------

Transfers out for the period ended November 20, 2005
Name: EmployeeID: Destination:
Black, Ruth 456456 Training Services
White, Harold 567567 Human Resources

Total
transfers out 3
----------------------------------------------------------------------
--------------------

Hires for the period ended November 20, 2005
Name: EmployeeID: Department:
Lincoln, Abraham 000111 Statesmanship
Eisenhower, Dwight 000199 Strategic Execution
Regan, Ronald 000299 World Affairs
Bush, George H.W. 000300 Statesmanship
Bush, George W. 000302 Operations

Total Hires 5
----------------------------------------------------------------------
--------------------

==================================================

Be aware that you have special Sorting and Grouping tools in the
Report Designer that supplement the filtering and sorting you may have
done in the underlying queries. There are also lines and graphic
elements that can be used in either mode to separate or emphasize
data.


to make it fall out like your example:
Terminations (3): Jones, Smith, Brown
Transfers (2): Black, White
Hires (5) Lincoln, Eisenhower, Regan, Bush,
Bush

would require writing three Static Function Procedures in a standard
module and then calling those procedures appropriately from the
Report's underlying query. Actually, since the three functions will
be almost identical, it will be possible to write one and to copy and
modify the first one :) It is not an Herculean effort to do that
but the application becomes more difficult to do and to later
maintain.

While my preferences are clear, I've tried to present a balanced
comparison.

If you are really, really sure you want it with the special
procedures, etc. post back with your table names, with their field
names and relationships. Someone, maybe even I, will pick up the
thread.

HTH
 
R

Ruth

Hi Larry,
I'm afraid that some of the informtion in my post misled you. I've used
Access for about ten years, and am a moderately skilled user-- I just
don't "do code." I am helping a client transition an Excel based report
(that was basically being used as a data entry template) to an
automated reporting system based in Access. My efforts to manipulate
the data stem from the client's desire to have the data available 1) in
the same format they're accustomed to seeing it; and 2) in a static
record. Results of queries reporting a particular period's results
would be written to a history table where they can be analyzed to show
trends and activity.

The report goes to senior management, and as I'm sure you know, with
those guys it's all about "short and sweet"-- which I why I wanted to
stay away from the space using the Sorting and Grouping tools would
require. My queries deliver the results as you showed in the examples
you created (thanks for going into such detail-- I know that took
time!). Now, I'd just like to take those names and put them into a
single cell. With that accomplished, I'll be able to recreate the
report to look like what they're currently getting in the Excel-based
report, and hold in the history table the names of the people with
activity for that period.

Names of objects are as follows:
Query: QryListNew
Based on: TblHCActivity
Contains fields: ReptDate; Type; Name

Records returned restricted by: A parameter value for Date and criteria
of "New Position" for Type.

If you can help me with that one, I can figure out the rest.

Thanks!
 
L

Larry Daugherty

Hi Ruth,

I don't think I can help you. We seem to be poles apart in our
thinking. Maybe someone here will pick up the thread. If not you may
want to post a new thread with the same issue & I'll stay out of it.

Good luck!
 
R

Ruth

Hi Larry,
Well, thanks anyway. The data source is from their mainframe HR system
(output to Excel) and it doesn't support Fname, Lname, etc. Their
current report lists employees by last name, so I think I'm just going
to have them enter the last name data in a field for that report date.
Thanks for trying though! I appreciate your attempt.

Ruth
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top