Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    67

    Question Unanswered: Grouping Fields in a record?

    Hi All,
    I have been trying to figure this one out, but no luck so far.

    I need to group several fields in a single record, so that I can plot them on a chart.
    What I have is a db to track audiograms, so the tblAudo has fields like R500, R1K, R2K...... and L500, L1K, L2K......etc.
    I would like to query this data, so that R500--->R8K of a single record is grouped AS "RightEar", and L500--->L8K is grouped AS "LeftEar"?

    I have tried the following, but it puts all fields into one field.

    SELECT BLL500 & BLL1K & BLL2K AS LeftEar, BLR500 & BLR1K & BLR2K AS RightEar, tblAudio.PatientID
    FROM tblAudio
    ORDER BY PatientID;

    Can this be done?, and What am I doing wrong?
    Thank you for any suggestions...Enviva

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Sorry don't understand what your are trying to achieve

    are you trying to string (concatanate) together 3 text values or 3 numeric values. using the & character does concatenate string columns in JET SQL.

    what is the require final output - could you instead do it in either a user defined function or in the final report / form

    user defined functions arn't a particular clever idea in queries - they can have a significant performance problem

    your function could take the form of
    Code:
    Public Function concatvalues(intval1 As Integer, intval2 As Integer, intval3 As Integer) As String
    concatvalues = Format(intval1, "000") & Format(intval2, "000") & Format(intval3, "000")
    End Function
    you may need to work out how you intend to handle null values.

  3. #3
    Join Date
    Feb 2004
    Posts
    67
    Healdem, Thanks for your quick response.
    I really don't want to concatenate the data I want to group the fields so that I have this.
    LEFTEAR
    ...5
    ...5
    ...10
    ....5
    ...10

    Here's what I have:
    a table for employees
    a table for audiograms
    The tables are related in 1-to-1 reslationship

    The fields in (tblAudio) are:
    AudioID, EmpID, L500,L1K,L2K,L3K,L6K,R500,R1K, R2K, R3K, R6K
    The data in the record is as follows:
    L500 = 5, L1K=10, L2K=10, L3K=5, L6K=5, R500=10, R1K=10, R2K=5, R3K=15, R6K=15
    I would like to print the report for this employee with a graph or chart that displays something like this?

    0---------------------------------
    5-----L-------------R----L------L-
    10----R------RL-----L-------------
    15-----------------------R------R-
    20--------------------------------
    25--------------------------------
    ------500----1K----2K----3K----6K

    I think I need to group L500...thru L8K as LeftEar and R500...thru R8K AS RightEar in order to chart the data.

    I am not sure where to start with this one, and I have been beating my head for the past several days with charts/pivot tables etc..

    I would greatly appreciate any help on this.
    Thanks again...Enviva

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    H'm on first thought a crosstab query with a bit of fiddling in the reports format might do the job, but I'm not convinced. If its going into a report the you could tinker with the display options.

    If I have the time today I'll have a look, anyone else any ideas?
    Last edited by healdem; 11-20-05 at 05:27.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Well, I've run out of time, this is my attempt at a solution. The form can be used as a sub form in your main form or as report. I'm sure that you will need to redesign the form to meet your requirements. On the face of iut it seems to work.

    ***health warning*** its a quick and dirty solution, it should work but it could adversely affect perfomrance.
    Attached Files Attached Files

  6. #6
    Join Date
    Feb 2004
    Posts
    67
    Healdem,

    Thank you for your hard work and time. I will definetly use what you have started. Maybe some day I will figure out how to put this all into a LINE Chart.

    Once again, My appreciation...ENVIVA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •