Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Convert Output of Query result field to a single field

Discussion in 'Information Technology' started by Ruth, Nov 18, 2005.

  1. Ruth

    Ruth
    Expand Collapse
    Guest

    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.
     
  2. Loading...

    Similar Threads Forum Date
    Sikhconvert Joins Sikh Philosophy Network! New SPN'ers Aug 28, 2016
    Jehovah's Witnesses Are Out And Converting! Blogs Jul 31, 2016
    Islam Ishna Has Converted To Islam Interfaith Dialogues Apr 1, 2016
    A Christian Missionary tried to convert me. Blogs Oct 22, 2015
    "Convert's Corner" - Ideas for Retitle? Convert's Corner Oct 21, 2015

  3. Larry Daugherty

    Larry Daugherty
    Expand Collapse
    Guest

    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
    --
    -Larry-
    --

    "Ruth" <ruthhays@gmail.com> wrote in message
    news:1132249079.974993.291690@o13g2000cwo.googlegroups.com...
    > 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.
    >
     

Share This Page