Reccomendations.

[DanJames]

I have a table for clients and a field preffered days , with data like "mon, tues, thurs" for example and I have a table for cleaners and a field working days, with data like "thurs, wed, fri" For example. would like the query to display Cleaners that have 1 or more of the same day as the clients to be next to each other in the records. So for example, if john liked "mon, tues, fri" and mary worked on "fri, tues" I'd like them to be next to each other, obviously if another cleaner worked "wed, thurs, sat" then I'd like them to be next to John as they have more days not equal to each other. If youo get what i mean? Any Ideas? Thanks, Dan.

 

[dbDamo]

Show us how your table is structured and provide an example of how you would like your query to be displayed. It will be easier to see what you have and what you are trying to achieve.

 

[DanJames]

Heres a basic Access file Of what I'd Like.. Theres 2 tables for clients and cleaners. One query showing client names, preffered days ( what days theyd like a cleaner), cleaner names and Working days ( what days the Cleaner is working at the moment). So really its if there a day the client wants that the cleaner is free - put them next to eachother. S therll be a table of records showing A Client name and next to it the reccomended Cleaner Name and next to that the day reccomended?

View attachment QueryHelp.mdb

Thanks Alot, Dan.

 

[dbDamo]

Sorry for not getting back sooner, have been busy.

I have to say that I was hoping that when I looked at your Database you weren't storing the preferred days/working days in a single field, which was what your OP suggested, but you are.

I would have created a field for each day of the week and used a Yes/No Data Type. This would have made data entry and querying much simpler.

You may want to revisit your table design...

 

[DanJames]

Ahh right and then what about the criteria, so if Mon = True and Tues = True and Wed = True then show cleaner with Mon= False or/And Tues = False or/And Wed = False something like that isi?

 

[DanJames]

Right I have changed the design to Mon, Tues, Wed, Thurs, Fri, Sat with Yes/No boxes. Now I need to say on a querie: If clientnames perffered = Mon And Cleanernames Day Working = Not Mon Then Reccomend. If you get what I mean.. So I need a list of client names next to cleaner names that do not have the same days. Thanks Alot, Dan??

 

[DanJames]

Hi yes, im looking to make some sort of query that will reccomend you assign cleaners to clients, depending on what days the cleaner is available and what days the client preffers? Also after I have done this it would be good if it could depend on other things like star rating, address which I havent created yet. But does anyone know how I can do this on a query?

Thanks, Dan.

 

[gemma-the-husky]

the other thing you shouldnt have IS separate columns for each day - i dont think. I would also use vbconstants to indicate the days - i think vbsunday is 1, vbmonday is 2, and so on.


you should have normalised data - so

clients table (id name etc)
1 - jim smith
2 - fred bloogs

clients preferred days table (clientid, preferred day)
1 - 1 (ie sunday)
1 - 3
1 - 5
2 - 2
2- 3
2 - 4

-------------
now have a similar structure for your cleaners

cleaners table (id name etc)
1 - Mrs Smith
2 - Miss Jones

cleaners works table (cleanerid, day worked)
1 - 2
1 -3
1 - 4
2 - 1
2- 4
2 - 5

------------
now its still tricky, but now you can find more easily cleaners whose working days meet particular requirements of your clients, by matching the day numbers as appropriate

------------
a lot of apps show spreadsheet thinking of this nature - but this generally makes database development much harder than it ought to be, and much harder than when you have properly normalised data.

 

[DanJames]

Yes, However some people can have a few preffered days like.. monday, tuesday and wednesday. So thats why I used the check boxes for each day.

 

[gemma-the-husky]

yes - but having the preferred days as horizontal columns in a table makes it hard. it is non-normalised.

the way to do it is to try and store the preferred days vertically - in a list as it were - so ie, in a table