Lookup table IDs as Columns

[Dave44000]

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.

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:

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.PatientTelephoneType)=2))
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]

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:

[pk][fk][field][info]
1,   2,   a,    info1
2,   2,   b,    info2
etc...

to look like this

[fk][info1][info2]
1    x      y 
2    a      b

within Access then the only easy way might be a crosstab query.

[Dave44000]

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.

[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!

[Dave44000]

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!

[zmbd]

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

[Dave44000]

So here is my SQL

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.