Lost in SQL statement world.

Q

Question Boy

Hello,

I can’t wrap my head around what I thought would be a simple query.

I have 4 tables

tbl_clients
clientid
firstname
lastname
…

tbl_instl
intlid
clientid
simple
…

tbl_servicing
servid
clientid
servtype
…

tbl_comp
compid
clientid
comptype
…

All the tables are on a 1-many relationship to the tbl_clients table.

Now I have a form where the user can enter 3 parameters, each should be used
to filter the return tbl_clients results. The user can select

simple (‘yes’/’no’/’show all’)
servtype (‘weekly’,’monthly’,’annual’,’show all’)
comptype (‘desktop’,’laptop’,’server’,’show all’)

Then press an execute button….

How can I build the SQL statement require for this situation. I started
building 1 per table… a real mess. Can it be done in one neat query?

Thank you!

QB
 
B

Bob Barrows

Question said:
Hello,

I can't wrap my head around what I thought would be a simple query.

I have 4 tables

tbl_clients
clientid
firstname
lastname
.

tbl_instl
intlid
clientid
simple
.

tbl_servicing
servid
clientid
servtype
.

tbl_comp
compid
clientid
comptype
.

All the tables are on a 1-many relationship to the tbl_clients table.

Now I have a form where the user can enter 3 parameters, each should
be used to filter the return tbl_clients results. The user can select

simple ('yes'/'no'/'show all')
servtype ('weekly','monthly','annual','show all')
comptype ('desktop','laptop','server','show all')

Then press an execute button..

How can I build the SQL statement require for this situation. I
started building 1 per table. a real mess. Can it be done in one
neat query?

Thank you!

QB
Click to expand...
Let's assume your form is called "SelectionForm" and the selection controls
are combo boxes with the names [cmbSimple], [cmbServtype] and [cmbComptype]

Your users may not appreciate the performance, but, create a new query and
add all 4 tables in. Click and drag to create the joins. Then switch to SQL
View to enter the following WHERE clause (before the semicolon! - the
semicolon has to be at the end of the statement ... or simply delete it:
it's not needed):

WHERE ([simple]= [SelectionForm]![cmbSimple] Or [SelectionForm]![cmbSimple]
= 'show all')
AND ([servtype]=[SelectionForm]![cmbServtype] Or
[SelectionForm]![cmbServtype]= 'show all')
AND ([Comptype]=[SelectionForm]![cmbComptype] Or
[SelectionForm]![cmbComptype]= 'show all')

If performance is an issue, then you will need to resort to dynamically
building the sql statement each time the button is pressed. Here are some
examples from RogersAccessLibraries:

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=368
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=367
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=366
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=365
 
Top