By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,469 Members | 2,402 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,469 IT Pros & Developers. It's quick & easy.

Lookup table IDs as Columns

P: 15
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.

Expand|Select|Wrap|Line Numbers
  1. PATIENT    PATIENT TELEPHONE      TELEPHONE TYPE
  2. PatientID  PatientTelephoneID     TelephoneTypeID
  3.            TelephoneNumber        Description
  4.            TelephoneType
  5.            PatientID
  6.  
  7. TelephoneTypeID Description
  8. 1               Office
  9. 2               Home
  10. 3               Fax
  11. 4               Cell
  12.  
  13.  
I wrote an insert query for each type, so I have four queries using the following SQL:

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO PrintPatientTelephones ( PatientTelephoneID, PatientID, TelephoneTypeID, Office )
  2. SELECT PatientTelephone.PatientTelephoneID, PatientTelephone.PatientID, PatientTelephone.PatientTelephoneType, PatientTelephone.TelephoneNumber
  3. FROM Patient INNER JOIN PatientTelephone ON Patient.PatientID = PatientTelephone.PatientID
  4. GROUP BY PatientTelephone.PatientTelephoneID, PatientTelephone.PatientID, PatientTelephone.PatientTelephoneType, PatientTelephone.TelephoneNumber, PatientTelephone.PatientTelephoneType
  5. HAVING (((PatientTelephone.PatientID)=[Forms]![frmProcessPatientReferral].[txt67]) AND ((PatientTelephone.PatientTelephoneType)=2))
  6. 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.
Dec 4 '13 #1

✓ answered by zmbd

Save you some time and grief with CTQ take a read thru:
•Crosstab query techniques - row totals, zeros, parameters, column headings All versions (allenbrowne.com)
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!

Share this Question
Share on Google+
6 Replies


zmbd
Expert Mod 5K+
P: 5,287
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:

Expand|Select|Wrap|Line Numbers
  1. [pk][fk][field][info]
  2. 1,   2,   a,    info1
  3. 2,   2,   b,    info2
  4. etc...
to look like this
Expand|Select|Wrap|Line Numbers
  1. [fk][info1][info2]
  2. 1    x      y 
  3. 2    a      b
within Access then the only easy way might be a crosstab query.
Dec 4 '13 #2

P: 15
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.
Dec 5 '13 #3

zmbd
Expert Mod 5K+
P: 5,287
Save you some time and grief with CTQ take a read thru:
•Crosstab query techniques - row totals, zeros, parameters, column headings All versions (allenbrowne.com)
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!
Dec 5 '13 #4

P: 15
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!
Dec 5 '13 #5

zmbd
Expert Mod 5K+
P: 5,287
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...
Dec 5 '13 #6

P: 15
So here is my SQL

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM First(PatientTelephone.TelephoneNumber) AS FirstOfTelephoneNumber
  2. SELECT PatientTelephone.PatientID
  3. FROM PatientTelephone
  4. GROUP BY PatientTelephone.PatientID
  5. ORDER BY PatientTelephone.PatientID
  6. PIVOT PatientTelephone.PatientTelephoneType;
  7.  
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.
Dec 5 '13 #7

Post your reply

Sign in to post your reply or Sign up for a free account.