Creating fields in a query programmatically...?

[pietlinden]

Maybe I'm just not getting it... (might have something to do with it's
being 3AM?)

I am trying to write a function that will receive the name of a
summary query (does frequency counts), e.g.

SELECT Patient.ECOG, Count(Patient.PatientID) AS Frequency
FROM Patient
GROUP BY Patient.ECOG
ORDER BY Patient.ECOG;

and add a field for "Relative Frequency" (Frequency/PopulationSize)

SELECT Patient.ECOG, Count(Patient.PatientID) AS Frequency, Format$
(Count(Patient.PatientID)/DCount("[PatientID]","Patient"),"00.0%") AS
RelFreq
FROM Patient
GROUP BY Patient.ECOG
ORDER BY Patient.ECOG;

Okay, stupid question... I know you can append fields to a *table*
just fine... But can I do the same with a querydef? Does anyone have
a really simple example with formatting an expression as percent?

I was hoping for a result set like this:

ECOG Frequency RelFreq
1 4 40.0%
2 6 60.0%

I'm probably making this waaaay harder than it has to be... I don't
want to do it manually, because all query results are being sent to
Word as part of a report. (It's going into Word because someone is
going to add comments to the results, so it has to be editable.)

If this is clear as mud, sorry! If you need clarification (or to
point out a bonehead mistake), please don't hesitate to say so!

Thanks!
Pieter

 

[Stefan Hoffmann]

hi Piet,

Okay, stupid question... I know you can append fields to a *table*
just fine... But can I do the same with a querydef? Does anyone have
a really simple example with formatting an expression as percent?

You can't. The only thing you can do, is to rewrite the entire query by
assigning it a new SQL statement:

Dim qd As DAO.QueryDef

Set qd = CurrentDb.QueryDefs.Item("yourQuery")
qd.SQL = "new SQL statment including new field"
Set qd = Nothing


mfG
--> stefan <--

 

[pietlinden]

hi Piet,



You can't. The only thing you can do, is to rewrite the entire query by
assigning it a new SQL statement:

   Dim qd As DAO.QueryDef

   Set qd = CurrentDb.QueryDefs.Item("yourQuery")
   qd.SQL = "new SQL statment including new field"
   Set qd = Nothing

mfG
--> stefan <--

Bummer... that's what I was afraid of. = (
Well, I can stop trying to do it that way then! Thanks!