p2p.wrox.com: Archives - access archive

access thread: Selecting records based on criteria

Message #1 by John Fejsa <John.Fejsa@h...> on Fri, 15 Jun 2001 16:12:24 +1000

I need to select records based on user selection. If the user selects one 

individual case manager I need to print a report for the selected case 

manager. If the user does not select any case managers I need to print all 

records. I would like to select required records with one query by using 

the iif function in the criteria, however, I was unsuccessful.



This is a simplified query I tried to use...



SELECT tblCaseManager.CaseManagerID, tblCaseManager.CaseManager, tblClients

.MRN

FROM (tblCaseManager INNER JOIN tblClients ON tblCaseManager.CaseManagerID 

=3D tblClients.CaseManagerID) INNER JOIN tblClientContacts ON tblClients.MR

N =3D tblClientContacts.MRN

GROUP BY tblCaseManager.CaseManagerID, tblCaseManager.CaseManager, 

tblClients.MRN

HAVING (((tblCaseManager.CaseManagerID)=3DIIf([Forms]![frmBetweenDates]![st

rSelectedEntity]=3D0,(tblCaseManager.CaseManagerID)>0,[Forms]![frmBetweenDa

tes]![strSelectedEntity])))

ORDER BY tblCaseManager.CaseManager;



Any suggestions will be greatly appreciated



Thanks

Message #2 by "John Ruff" <papparuff@c...> on Fri, 15 Jun 2001 03:08:12 -0700

Try this,



Dim strSQL as string



StrSQL=" SELECT tblCaseManager.CaseManagerID, tblCaseManager.CaseManager,

tblClients.MRN

FROM (tblCaseManager INNER JOIN tblClients ON tblCaseManager.CaseManagerID 

tblClients.CaseManagerID) INNER JOIN tblClientContacts ON tblClients.MRN 

tblClientContacts.MRN

GROUP BY tblCaseManager.CaseManagerID, tblCaseManager.CaseManager,

tblClients.MRN "



If  Me! StrSelectedEntity>0 then

StrSQL=strSQL & "tblCaseManager.CaseManagerID='" & Me!strSelectedEntity & "'

"

Endif



strSQL=strSQL & "ORDER BY tblCaseManager.CaseManager"



John Ruff - The Eternal Optimist :)

 -----Original Message-----

From: 	John Fejsa [mailto:John.Fejsa@h...]

Sent:	Thursday, June 14, 2001 11:12 PM

To:	Access

Subject:	[access] Selecting records based on criteria



I need to select records based on user selection. If the user selects one

individual case manager I need to print a report for the selected case

manager. If the user does not select any case managers I need to print all

records. I would like to select required records with one query by using the

iif function in the criteria, however, I was unsuccessful.



This is a simplified query I tried to use...



SELECT tblCaseManager.CaseManagerID, tblCaseManager.CaseManager,

tblClients.MRN

FROM (tblCaseManager INNER JOIN tblClients ON tblCaseManager.CaseManagerID 

tblClients.CaseManagerID) INNER JOIN tblClientContacts ON tblClients.MRN 

tblClientContacts.MRN

GROUP BY tblCaseManager.CaseManagerID, tblCaseManager.CaseManager,

tblClients.MRN

HAVING

(((tblCaseManager.CaseManagerID)=IIf([Forms]![frmBetweenDates]![strSelectedE

ntity]=0,(tblCaseManager.CaseManagerID)>0,[Forms]![frmBetweenDates]![strSele

ctedEntity])))

ORDER BY tblCaseManager.CaseManager;



Any suggestions will be greatly appreciated



Thanks

Message #3 by John Fejsa <John.Fejsa@h...> on Mon, 18 Jun 2001 09:54:54 +1000

I'm sorry, I should have clarify my request. I can easily select required 

records in VBA and set report RecordSource on the fly.  For example:



Private Sub Report_Open(Cancel As Integer)

Me.RecordSource =3D SetReportDatasource() 'Get report SQL statement

End Sub



Function SetReportDatasource()

'Compile report SQL statement

Dim strSQL As String



    strSQL =3D "SELECT tblCaseManager.CaseManagerID, tblCaseManager.CaseMan

ager, tblClients.MRN "

    strSQL =3D strSQL & "FROM (tblCaseManager "

    strSQL =3D strSQL & "INNER JOIN tblClients "

    strSQL =3D strSQL & "ON tblCaseManager.CaseManagerID =3D tblClients.Cas

eManagerID) "

    strSQL =3D strSQL & "INNER JOIN tblClientContacts ON tblClients.MRN 

=3D tblClientContacts.MRN "

    If Forms!frmBetweenDates!strSelectedEntity > 0 Then

        strSQL =3D strSQL & " WHERE tblCaseManager.CaseManagerID =3D " & 

Forms!frmBetweenDates!strSelectedEntity & " "

    End If

    strSQL =3D strSQL & " GROUP BY tblCaseManager.CaseManagerID, tblCaseMan

ager.CaseManager, tblClients.MRN "



    strSQL =3D strSQL & " ORDER BY tblCaseManager.CaseManager;"

   

    SetReportDatasource =3D strSQL



End Function





However, I would like to create and save the query in Design view. in 

order to speed up the execution. The report needs to generate detail 

report for about 500,000 records between selected dates with the above 

criteria. By using the compiled query I hope to improve the speed 

performance of the report. Hopefully that makes more sense.



Thanks





_____________________________________



John Fejsa

Systems Analyst/Computer Programmer

Hunter Centre for Health Advancement

Locked Bag 10

WALLSEND NSW 2287

Phone: (02) 49246 336 Fax: (02) 49246 209

________________________________________



>>> papparuff@c... 15/06/2001 20:08:12 >>>

Try this,



Dim strSQL as string



StrSQL=3D" SELECT tblCaseManager.CaseManagerID, tblCaseManager.CaseManager,



tblClients.MRN

FROM (tblCaseManager INNER JOIN tblClients ON tblCaseManager.CaseManagerID 

=3D

tblClients.CaseManagerID) INNER JOIN tblClientContacts ON tblClients.MRN 

=3D

tblClientContacts.MRN

GROUP BY tblCaseManager.CaseManagerID, tblCaseManager.CaseManager,

tblClients.MRN "



If  Me! StrSelectedEntity>0 then

StrSQL=3DstrSQL & "tblCaseManager.CaseManagerID=3D'" & Me!strSelectedEntity

 & "'

"

Endif



strSQL=3DstrSQL & "ORDER BY tblCaseManager.CaseManager"



John Ruff - The Eternal Optimist :)



 -----Original Message-----

From: 	John Fejsa [mailto:John.Fejsa@h...]

Sent:	Thursday, June 14, 2001 11:12 PM

To:	Access

Subject:	[access] Selecting records based on criteria



I need to select records based on user selection. If the user selects one

individual case manager I need to print a report for the selected case

manager. If the user does not select any case managers I need to print all

records. I would like to select required records with one query by using 

the

iif function in the criteria, however, I was unsuccessful.



This is a simplified query I tried to use...



SELECT tblCaseManager.CaseManagerID, tblCaseManager.CaseManager,

tblClients.MRN

FROM (tblCaseManager INNER JOIN tblClients ON tblCaseManager.CaseManagerID 

=3D

tblClients.CaseManagerID) INNER JOIN tblClientContacts ON tblClients.MRN 

=3D

tblClientContacts.MRN

GROUP BY tblCaseManager.CaseManagerID, tblCaseManager.CaseManager,

tblClients.MRN

HAVING

(((tblCaseManager.CaseManagerID)=3DIIf([Forms]![frmBetweenDates]![strSelect

edE

ntity]=3D0,(tblCaseManager.CaseManagerID)>0,[Forms]![frmBetweenDates]![strS

ele

ctedEntity])))

ORDER BY tblCaseManager.CaseManager;



Any suggestions will be greatly appreciated



Thanks

Message #4 by John Fejsa <John.Fejsa@h...> on Mon, 18 Jun 2001 14:49:14 +1000

I found the solution to my original problem:



IIf([Forms]![frmBetweenDates]![cmbSelectedEntity]>0,[Forms]![frmBetweenDate

s]![cmbSelectedEntity],[tblClients]![CaseManagerID])



If cmbSelectedEntity has been selected by user it will filter on the 

selection, otherwise, it will compare CaseManagerID with CaseManagerID, 

which will be alwas true. As a result ALL records will be printed.



However, I have a new problem now. Immediate if statement is veryyyy slow, 

much slower then VBA generated query. I will have to find another solution 

to speed up report printing procedure....





_____________________________________



John Fejsa

Systems Analyst/Computer Programmer

Hunter Centre for Health Advancement

Locked Bag 10

WALLSEND NSW 2287

Phone: (02) 49246 336 Fax: (02) 49246 209





>>> John.Fejsa@h... 18/06/2001 9:54:54 >>>

I'm sorry, I should have clarify my request. I can easily select required 

records in VBA and set report RecordSource on the fly.  For example:



Private Sub Report_Open(Cancel As Integer)

Me.RecordSource =3D SetReportDatasource() 'Get report SQL statement

End Sub



Function SetReportDatasource()

'Compile report SQL statement

Dim strSQL As String



    strSQL =3D "SELECT tblCaseManager.CaseManagerID, tblCaseManager.CaseMan

ager, tblClients.MRN "

    strSQL =3D strSQL & "FROM (tblCaseManager "

    strSQL =3D strSQL & "INNER JOIN tblClients "

    strSQL =3D strSQL & "ON tblCaseManager.CaseManagerID =3D tblClients.Cas

eManagerID) "

    strSQL =3D strSQL & "INNER JOIN tblClientContacts ON tblClients.MRN 

=3D tblClientContacts.MRN "

    If Forms!frmBetweenDates!strSelectedEntity > 0 Then

        strSQL =3D strSQL & " WHERE tblCaseManager.CaseManagerID =3D " & 

Forms!frmBetweenDates!strSelectedEntity & " "

    End If

    strSQL =3D strSQL & " GROUP BY tblCaseManager.CaseManagerID, tblCaseMan

ager.CaseManager, tblClients.MRN "



    strSQL =3D strSQL & " ORDER BY tblCaseManager.CaseManager;"

   

    SetReportDatasource =3D strSQL



End Function





However, I would like to create and save the query in Design view. in 

order to speed up the execution. The report needs to generate detail 

report for about 500,000 records between selected dates with the above 

criteria. By using the compiled query I hope to improve the speed 

performance of the report. Hopefully that makes more sense.



Thanks

Message #5 by "John Ruff" <papparuff@c...> on Sun, 17 Jun 2001 21:20:13 -0700

Checkout CreateQueryDefs in the help files.





John Ruff - The Eternal Optimist :)

 -----Original Message-----

From: 	John Fejsa [mailto:John.Fejsa@h...]

Sent:	Sunday, June 17, 2001 4:55 PM

To:	Access

Subject:	[access] RE: Selecting records based on criteria



I'm sorry, I should have clarify my request. I can easily select required

records in VBA and set report RecordSource on the fly.  For example:



Private Sub Report_Open(Cancel As Integer)

Me.RecordSource = SetReportDatasource() 'Get report SQL statement

End Sub



Function SetReportDatasource()

'Compile report SQL statement

Dim strSQL As String



    strSQL = "SELECT tblCaseManager.CaseManagerID,

tblCaseManager.CaseManager, tblClients.MRN "

    strSQL = strSQL & "FROM (tblCaseManager "

    strSQL = strSQL & "INNER JOIN tblClients "

    strSQL = strSQL & "ON tblCaseManager.CaseManagerID 

tblClients.CaseManagerID) "

    strSQL = strSQL & "INNER JOIN tblClientContacts ON tblClients.MRN 

tblClientContacts.MRN "

    If Forms!frmBetweenDates!strSelectedEntity > 0 Then

        strSQL = strSQL & " WHERE tblCaseManager.CaseManagerID = " &

Forms!frmBetweenDates!strSelectedEntity & " "

    End If

    strSQL = strSQL & " GROUP BY tblCaseManager.CaseManagerID,

tblCaseManager.CaseManager, tblClients.MRN "



    strSQL = strSQL & " ORDER BY tblCaseManager.CaseManager;"



    SetReportDatasource = strSQL



End Function





However, I would like to create and save the query in Design view. in order

to speed up the execution. The report needs to generate detail report for

about 500,000 records between selected dates with the above criteria. By

using the compiled query I hope to improve the speed performance of the

report. Hopefully that makes more sense.



Thanks





_____________________________________



John Fejsa

Systems Analyst/Computer Programmer

Hunter Centre for Health Advancement

Locked Bag 10

WALLSEND NSW 2287

Phone: (02) 49246 336 Fax: (02) 49246 209

________________________________________



>>> papparuff@c... 15/06/2001 20:08:12 >>>

Try this,



Dim strSQL as string



StrSQL=" SELECT tblCaseManager.CaseManagerID, tblCaseManager.CaseManager,

tblClients.MRN

FROM (tblCaseManager INNER JOIN tblClients ON tblCaseManager.CaseManagerID 

tblClients.CaseManagerID) INNER JOIN tblClientContacts ON tblClients.MRN 

tblClientContacts.MRN

GROUP BY tblCaseManager.CaseManagerID, tblCaseManager.CaseManager,

tblClients.MRN "



If  Me! StrSelectedEntity>0 then

StrSQL=strSQL & "tblCaseManager.CaseManagerID='" & Me!strSelectedEntity & "'

"

Endif



strSQL=strSQL & "ORDER BY tblCaseManager.CaseManager"



John Ruff - The Eternal Optimist :)



 -----Original Message-----

From: 	John Fejsa [mailto:John.Fejsa@h...]

Sent:	Thursday, June 14, 2001 11:12 PM

To:	Access

Subject:	[access] Selecting records based on criteria



I need to select records based on user selection. If the user selects one

individual case manager I need to print a report for the selected case

manager. If the user does not select any case managers I need to print all

records. I would like to select required records with one query by using the

iif function in the criteria, however, I was unsuccessful.



This is a simplified query I tried to use...



SELECT tblCaseManager.CaseManagerID, tblCaseManager.CaseManager,

tblClients.MRN

FROM (tblCaseManager INNER JOIN tblClients ON tblCaseManager.CaseManagerID 

tblClients.CaseManagerID) INNER JOIN tblClientContacts ON tblClients.MRN 

tblClientContacts.MRN

GROUP BY tblCaseManager.CaseManagerID, tblCaseManager.CaseManager,

tblClients.MRN

HAVING

(((tblCaseManager.CaseManagerID)=IIf([Forms]![frmBetweenDates]![strSelectedE

ntity]=0,(tblCaseManager.CaseManagerID)>0,[Forms]![frmBetweenDates]![strSele

ctedEntity])))

ORDER BY tblCaseManager.CaseManager;



Any suggestions will be greatly appreciated



Thanks

Return to Index

P2P Archives