That is in fact similar to what I did - I thought of it a little later on.
I did a string replacement on all the table names in the queries (40+
queries) with query names. I then created a few queries to replace those
tables.
I then added some parameters to give them options on the criteria/filter. It
seems to works fine in Access.
However, in Crystal reports when using these queries/views I get prompted
for a two extra parameters for some reason. (S.Site_cod and S.Patient_cod)
Extra parameter prompts in Crystal Reports as an ODBC connection.
--------------------------------------------------------
SELECT '1To3' AS Range,
SUM( IIF( (int_GoalImportance Between 1 And 3), 1, 0)) / Count(*) AS
PercentImportant,
SUM( IIF( (int_GoalConfidence Between 1 And 3), 1, 0)) / Count(*) AS
PercentConfident
FROM qryEncoded_HSFOHBPS_Serial AS S
WHERE ymd_VisitDate=
( SELECT Max( VisitDate )
FROM qrySitePatientVisit SPV
WHERE S.Site_cod = SPV.Site_cod
AND S.Patient_cod = SPV.Patient_cod
)
UNION
SELECT '4To6' AS Range,
SUM( IIF( (int_GoalImportance Between 4 And 6), 1, 0)) / Count(*) AS
PercentImportant,
SUM( IIF( (int_GoalConfidence Between 4 And 6), 1, 0)) / Count(*) AS
PercentConfident
FROM qryEncoded_HSFOHBPS_Serial AS S
WHERE ymd_VisitDate=
( SELECT Max( VisitDate )
FROM qrySitePatientVisit SPV
WHERE S.Site_cod = SPV.Site_cod
AND S.Patient_cod = SPV.Patient_cod
)
UNION SELECT '7Plus' AS Range,
SUM( IIF( (int_GoalImportance > 6), 1, 0)) / Count(*) AS PercentImportant,
SUM( IIF( (int_GoalConfidence > 6), 1, 0)) / Count(*) AS PercentConfident
FROM qryEncoded_HSFOHBPS_Serial AS S
WHERE ymd_VisitDate=
( SELECT Max( VisitDate )
FROM qrySitePatientVisit SPV
WHERE S.Site_cod = SPV.Site_cod
AND S.Patient_cod = SPV.Patient_cod
);
)!