Complicated Filtering (1 Viewer)

Tallbloke

Registered User.
Local time
Today, 14:58
Joined
Aug 17, 2006
Messages
66
This is a bit complicated so please stick with me!

I have a form that I enter Supplier information into, in this case suppliers could be a hotel, or an activity site for example. It's just a table with supplier information.

Because certain suppliers can provide services to multiple towns I have 36 check boxes that can be ticked to show which towns the supplier can be used for. SO for example...

Supplier A could have 3 locations selected using tick boxes.
Supplier B could just have 1 location selected.
etc etc

However, when we get a booking we enter the customer information onto a form. I select their location from a drop down and get this drop down to put a tick in "same named" hidden tick box on our hotel details page. I then go to the hotel details page to select their hotel.

As it stands I have a drop down that pulls in ALL the hotels from our supplier form but I would like it to pull in JUST the hotels that are relevant. I.e. the tick boxes on the 2 forms match up.

So...

Customer is visiting London.
The Hotel form has a tick in the London Box
I want the dropdown on this form to show all the Hotels from the Suppliers table that have a tick in the London Box

I'm not sure if that makes sense or not ;)
 

boblarson

Smeghead
Local time
Today, 07:58
Joined
Jan 12, 2001
Messages
32,059
From your description, your design is not normalized and should be fixed. You currently have 36 fields in your table (one for each location), is that correct?

If I am wrong, please forgive me, but it really sounds like you are selecting fields instead of adding records for each location. And, if that is the case, we need to fix your table structure before moving further.
 

Tallbloke

Registered User.
Local time
Today, 14:58
Joined
Aug 17, 2006
Messages
66
Sorry...

I have a supplier table. There are 1000's of entries in this. We work in 36 locations but a supplier is only relevant to 36 locations and this can be different from their actual address.

I had to find a way of showing which locations a supplier is relevant to, hence the tickboxes. Any supplier can be relevant to any number of these. If a supplier was only relevant to one location this would be simple... i drop down per supplier ;)

However, in this case I am looking at hotels which tend to only be relevant to the town they are in, hence just the one tick.

So, in my drop down on my customer entry I want to be able to select a hotel where the tick boxes match.

Does that make more sense?
 

Tallbloke

Registered User.
Local time
Today, 14:58
Joined
Aug 17, 2006
Messages
66
Code:
SELECT Suppllier.SupName, Suppllier.Address1, Suppllier.Address2, Suppllier.Address3, Suppllier.Address4, Suppllier.County, Suppllier.Postcode, Suppllier.Tel, Suppllier.Fax, Suppllier.Email, Suppllier.Website, Suppllier.NewcastleTick, Suppllier.Reference
FROM Suppllier
WHERE (((Suppllier.NewcastleTick)=Yes) AND ((Suppllier.Reference)=1))
ORDER BY Suppllier.SupName;

Actually, is there a way to put this line in via VB...

WHERE (((Suppllier.NewcastleTick)=Yes)

Depending on the tick box?

So changing it to

WHERE (((Suppllier.LeedsTick)=Yes) for example?
 

boblarson

Smeghead
Local time
Today, 07:58
Joined
Jan 12, 2001
Messages
32,059
Can you post a screen shot or two here (upload here, not to a file sharing site as those are blocked) so I can see what it is you currently have because it isn't too clear still.
 

Tallbloke

Registered User.
Local time
Today, 14:58
Joined
Aug 17, 2006
Messages
66
Hotel-Form.jpg

Supplier.jpg

I can filter the combo manually to show the information I want for this specific example... all hotels in newcasltle (data set is simplified) where the NewcastleTick box is yes.

What I need it the Query to tie in with the relevant tick box on the hotel form.

The Query would have all 36 fields with the "Yes" Crtieria on the field that matches the hotel form?

So... if the Hotel Form has a tick in NewcastleTick check box then the query should should have Yes in the field with the same name from Suppliers.
 

Tallbloke

Registered User.
Local time
Today, 14:58
Joined
Aug 17, 2006
Messages
66
The more I look at this the more I think I just need a way to change the where clause using VBA.

Current SQL
Code:
SELECT Suppllier.SupName, Suppllier.Address1, Suppllier.Address2, Suppllier.Address3, Suppllier.Address4, Suppllier.County, Suppllier.Postcode, Suppllier.Tel, Suppllier.Fax, Suppllier.Email, Suppllier.[Hotel?], Suppllier.Website, Suppllier.NewcastleTick
FROM Suppllier
WHERE (((Suppllier.[Hotel?])=Yes) AND ((Suppllier.NewcastleTick)=Yes))
ORDER BY Suppllier.SupName;

Is there a way to change ((Suppllier.NewcastleTick)=Yes))

to ((Suppllier.LeedsTick)=Yes)) if the Leeds box is ticked on the hotel form.
 

boblarson

Smeghead
Local time
Today, 07:58
Joined
Jan 12, 2001
Messages
32,059
The only reason this is complicated is that your table design is not normalized. Fix that and it becomes extremely easy to get what you want. Don't fix it and it will continue to be horrible to try to get meaningful data back out.

You design the table structure first, with good practices, and then design the form. It would appear that you designed the table to give you what you envisioned the form to be and that is the wrong way to go about it.
 

Tallbloke

Registered User.
Local time
Today, 14:58
Joined
Aug 17, 2006
Messages
66
I'm not sure how I would have done this differently.

I need a way for the user to instantly see what locations a supplier services but equally I need to do things with this data.

I can't use the address of the supplier as it often has no bearing on the locations they service.

Maybe I'm being dim, but I'm not sure what you mean by the table design not being normalised.
 

boblarson

Smeghead
Local time
Today, 07:58
Joined
Jan 12, 2001
Messages
32,059
I'm not sure how I would have done this differently.

I need a way for the user to instantly see what locations a supplier services but equally I need to do things with this data.

I can't use the address of the supplier as it often has no bearing on the locations they service.

Maybe I'm being dim, but I'm not sure what you mean by the table design not being normalised.

If your table has a field named NewCastleTick and LeedsTick, that means you don't have a normalized table structure. It should be

tblSuppliers
SupplierID
SupplierName
SupplierAddress
SupplierCity
SupplierRegion
SupplierPostCode

tblLocations
LocationID
LocationDescription

tblSuppliersLocations
SupplierLocationID - Autonumber (PK)
SupplierID - Long Integer (FK)
LocationID - Long Integer (FK)


And you then select the locations associated with the particular supplier. You can use some code and a temp table to allow for checkbox selection, but that will take a bit more code to do and won't be like it is on your form. If a location is added, it won't need changes but with your current setup it would need to have changes done to the form.

Anyway, that is how it really should be set up.
 

Tallbloke

Registered User.
Local time
Today, 14:58
Joined
Aug 17, 2006
Messages
66
If I am wrong, please forgive me, but it really sounds like you are selecting fields instead of adding records for each location. And, if that is the case, we need to fix your table structure before moving further.

But surely the problem remains... some records have multiple locations and some dont.
 

Tallbloke

Registered User.
Local time
Today, 14:58
Joined
Aug 17, 2006
Messages
66
If your table has a field named NewCastleTick and LeedsTick, that means you don't have a normalized table structure. It should be

tblSuppliers
SupplierID
SupplierName
SupplierAddress
SupplierCity
SupplierRegion
SupplierPostCode

tblLocations
LocationID
LocationDescription

tblSuppliersLocations
SupplierLocationID - Autonumber (PK)
SupplierID - Long Integer (FK)
LocationID - Long Integer (FK)


And you then select the locations associated with the particular supplier. You can use some code and a temp table to allow for checkbox selection, but that will take a bit more code to do and won't be like it is on your form. If a location is added, it won't need changes but with your current setup it would need to have changes done to the form.

Anyway, that is how it really should be set up.

Well..as it happens I do have the first 2 tables.

I've just never come across the idea behind third table and would have no idea how to use it to hold and display the information I want it to.
 

boblarson

Smeghead
Local time
Today, 07:58
Joined
Jan 12, 2001
Messages
32,059
But surely the problem remains... some records have multiple locations and some dont.
No, the problem would not remain. The junction table stores the associated location with the supplier ID. So, if a supplier is associated with 3 locations, then it will have 3 records in that table. If they are associated with 1 location then it woiuld have 1 record in that table.

Then, you can pull multiple locations simply by using something like

LocationID In(1, 3, 9)
 

Tallbloke

Registered User.
Local time
Today, 14:58
Joined
Aug 17, 2006
Messages
66
Ok...I get the theory.

My one issue is this Database has kinda grown over time since 2005. I've been asked to make it do something specific and I have just somehow found a way. I'm aware the original table design is poor and the front end actually pulls in tables from 2 backends.

It's nasty mess of a thing that I have been asked to make more changes to. I can see an end in sight, the lifespan of the system is now only a year or so. Meddling with the underlying table structure seems a bit risky and time consuming at this stage so I'm not quite sure what to do for the best.
 

boblarson

Smeghead
Local time
Today, 07:58
Joined
Jan 12, 2001
Messages
32,059
The next best thing is to modify the query's WHERE clause using a QueryDef and using code from Access MVP Armen Stein. Unzip this file:
http://www.jstreettech.com/files/basJStreetSQLTools.zip
and copy the text into a new standard module and name it basJStreetSQL.

Then you can use code to build your where clause like:

Code:
Dim strWhere As String
Dim qdf As DAO.QueryDef
 
If Me.NewcastleTick Then
   strWhere = "[NewCastleTick] = True OR "
End If
 
If Me.LeedsTick Then
   strWhere = "[LeedsTick] = True OR "
End If
 
'...and so on for all 36
 
'and then at the end -
 
If Right(strWhere, 4) = " OR " Then
   strWhere = Left(strWhere, Len(strWhere) - 4)
End If
 
Set qdf = CurrentDb.QueryDefs("YourQueryNameHere")
 
qdf.SQL = ReplaceWhereClause(qdf.SQL, "WHERE " & strWhere)
 
qdf.Close
 
Set qdf = Nothing
 
Last edited:

Tallbloke

Registered User.
Local time
Today, 14:58
Joined
Aug 17, 2006
Messages
66
The next best thing is to modify the query's WHERE clause using a QueryDef and using code from Access MVP Armen Stein. Unzip this file:
http://www.jstreettech.com/files/basJStreetSQLTools.zip
and copy the text into a new standard module and name it basJStreetSQL.

Then you can use code to build your where clause like:

Code:
Dim strWhere As String
Dim qdf As DAO.QueryDef
 
If Me.NewcastleTick Then
   strWhere = "[NewCastleTick] = True OR "
End If
 
If Me.LeedsTick Then
   strWhere = "[LeedsTick] = True OR "
End If
 
'...and so on for all 36
 
'and then at the end -
 
If Right(strWhere, 4) = " OR " Then
   strWhere = Left(strWhere, Len(strWhere) - 4)
End If
 
Set qdf = CurrentDb.QueryDefs("YourQueryNameHere")
 
qdf.SQL = ReplaceWhereClause(qdf.SQL, "WHERE " & strWhere)
 
qdf.Close
 
Set qdf = Nothing

I've got this working, thanks! Just wondering how I would adjust your code so that I can add another parameter.

Code:
If Me.NewcastleTick Then
   strWhere = "[NewCastleTick] = True OR "
End If

I would like it to ALWAYS have this tacked on as well

Code:
AND ((Suppllier.Reference)=1))

I'll have a play myself and will update if I get it right :)
 

Users who are viewing this thread

Top Bottom