Lookup table IDs as Columns

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Dave44000
    New Member
    • Oct 2013
    • 15

    Lookup table IDs as Columns

    I'm trying to mail merge a single record in MS Word. That works. The problem is that single record or row in Access can't be created via SQL alone. I can see how to use vba to get from where I'm at to where I'm going, but I don't want to use vba.

    It seems to me that since combo boxes are so normal, writing this query should be normal as well.

    Code:
    PATIENT    PATIENT TELEPHONE      TELEPHONE TYPE
    PatientID  PatientTelephoneID     TelephoneTypeID
               TelephoneNumber        Description
               TelephoneType
               PatientID
    
    TelephoneTypeID Description
    1               Office
    2               Home
    3               Fax
    4               Cell
    I wrote an insert query for each type, so I have four queries using the following SQL:

    Code:
    INSERT INTO PrintPatientTelephones ( PatientTelephoneID, PatientID, TelephoneTypeID, Office )
    SELECT PatientTelephone.PatientTelephoneID, PatientTelephone.PatientID, PatientTelephone.PatientTelephoneType, PatientTelephone.TelephoneNumber
    FROM Patient INNER JOIN PatientTelephone ON Patient.PatientID = PatientTelephone.PatientID
    GROUP BY PatientTelephone.PatientTelephoneID, PatientTelephone.PatientID, PatientTelephone.PatientTelephoneType, PatientTelephone.TelephoneNumber, PatientTelephone.PatientTelephoneType
    HAVING (((PatientTelephone.PatientID)=[Forms]![frmProcessPatientReferral].[txt67]) AND ((PatientTelephone.[B]PatientTelephoneType)=2[/B]))
    ORDER BY PatientTelephone.PatientID;
    I change the bolded ID number, as appropriate for each query.

    This generates several rows. The telphone number appears below the appropriate telephone type column heading.


    I need to generate a single row for each patient, instead of several rows.
  • zmbd
    Recognized Expert Moderator Expert
    • Mar 2012
    • 5501

    #2
    You've complete lost me here.... and that's a rare thing.

    If a single record mail-merges correctly, then all should. It should not matter what the query it self looks like.

    I do not understand what you are attempting with an insert query...

    However, if you want your table that looks like this:

    Code:
    [pk][fk][field][info]
    1,   2,   a,    info1
    2,   2,   b,    info2
    etc...
    to look like this
    Code:
    [fk][info1][info2]
    1    x      y 
    2    a      b
    within Access then the only easy way might be a crosstab query.

    Comment

    • Dave44000
      New Member
      • Oct 2013
      • 15

      #3
      I think you understand what I need given your last two examples. A crosstab query would still need some vba. I'll look at it again. Thanks.

      Comment

      • zmbd
        Recognized Expert Moderator Expert
        • Mar 2012
        • 5501

        #4
        Save you some time and grief with CTQ take a read thru:
        •Crosstab query techniques - row totals, zeros, parameters, column headings All versions (allenbrowne.co m)
        This information has kept me sane (ok, almost sane) when trying to work with these. The wizard is "OK" for setting the simple CTQ, and is often where I start; however, it lacks a great deal as you'll find reading AB site. There are other sites and examples out there too.

        If you get stuck, post back with the SQL for your query (please format it using the [CODE/] button) and one of us should be along shortly to help. Rabbit is one of the masters of SQL (IMHO) and has done things I've not thought possible - all without VBA!

        Comment

        • Dave44000
          New Member
          • Oct 2013
          • 15

          #5
          Thanks for the crosstab mention and reference. I had tried one, but didn't do a good job on it. I looked at the article you referenced. Then I Googled it and found, http://www.youtube.com/watch?v=k_uLJK3mT2o, which did exactly what I needed.

          It couldn't have been as hard as what I was doing or what I had tried earlier. And, yes, no vba. Great!

          Comment

          • zmbd
            Recognized Expert Moderator Expert
            • Mar 2012
            • 5501

            #6
            Hurray!
            Glad this got you on the correct track.
            It would be helpful to have the SQL available for those of us that can not get at the youtube site...

            Comment

            • Dave44000
              New Member
              • Oct 2013
              • 15

              #7
              So here is my SQL

              Code:
              TRANSFORM First(PatientTelephone.TelephoneNumber) AS FirstOfTelephoneNumber
              SELECT PatientTelephone.PatientID
              FROM PatientTelephone
              GROUP BY PatientTelephone.PatientID
              ORDER BY PatientTelephone.PatientID
              PIVOT PatientTelephone.PatientTelephoneType;
              Skip using the wizard. Write the usual query then convert it to a crosstab query select the row field and column fields in Design View; Change you data field to First instead of Group By; and you're done.

              It does exactly what I needed. This after three weeks of trying this and that.
              Last edited by zmbd; Dec 5 '13, 11:28 PM. Reason: [z{no edit{THANK YOU for posting this back and the supporting comment!!! BOL!}]

              Comment

              Working...