Challenge 1

[JBinQLD]

You have a table as follows:

FullName: String
LikesDogs: Bool
LikeCats: Bool

You run an animal sanctuary and need to make a query to lists everyone apart from those that DO like dogs but DO NOT like cats. Eg, everything apart from LikesDogs=TRUE And LikesCats=FALSE.

How is this possible? I want this to be an exclusion criteria rather than a series of:

LikesDogs=FALSE And LikesCats=False
LikesDogs=FALSE and LikesCats=True
LikesDogs=TRUE and LikesCats=True

...as criteria

I've solved this now and although I'm ashamed at how simple the solution was, it took me an embarrassing amount of time to come up with it

 

[jamaarneen]

Hi JBinQLD ,

You should maybe try the following code in your where clause:

(Not (LikesDogs=TRUE AND LikesCats=FALSE))

Hope this help. pls let me know
Ja

 

[JBinQLD]

Imagine the query in the query builder. Which column would you put that on?!

 

[fneily]

Nothing to do with how wonderful a riddle solver someone is. Your table is not normalized. Violates first normal form.
1) Fullname not broken down into atomic parts
2) Table within a table - dogs, cats. If it looks like a category, animals, then it could be a table.

Table should look like:
tblAnimalLikes Multi-field primary key on ClientID and Animal.
ClientID
FirstName
LastName
Animal

 

[jamaarneen]

JBinQLD,

It dosn't really matter in which column - LikesDogs or
LikeCats. You can help yourself by using the builder tool (right click)

by the way, what fneily wrote about normalising your database, is very true!

Ja

 

[PHV]

WHERE LikesDogs=False OR LikesCats=True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

If this is an animal sanctuary would you have other animals: snakes, birds, rabbits? Would you be worried about likes and dislikes of other animals. Then I would think to add to where AceMan is going.

tblClients
ClientID
FirstName
LastName

tblAnimals
animalID
maybeOther animal fields

jncTblClient_AnimalPreferences
clientID_fk
animalID_fk
preferenceType (like, dislike, unknown)


So this way client1
likes snakes
dislikes dogs
likes horses
unknown cats
and client 2
dislikes snakes
likes dogs
likes horses
likes cats

Then you have a many to many looking something like

1 1 likes
1 2 dislikes
1 3 likes
1 4 unknown
2 1 dislikes
2 2 likes
2 3 likes
2 4 likes

 

[JoeAtWork]

Imagine the query in the query builder. Which column would you put that on?!

Why would I limit myself to the Query Builder?

First I figure out the selection criteria (I probaby would have come up with a NOT clause as posted by jamarneen), and if I can't easily stick it in the Query Builder, just press the SQL button and type it in.

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[PHV]

in the query builder[tt]
field LikesDogs LikesCats
criteria False
or True
[/tt]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hey, JBinQLD.

Too much DRAMA!

How about a reply to all the good folks above who took you up on your "challenge". You had some boolean logic answers and some data normalization suggestions.

And a ...
[blue]
Thank Tek-Tip Contributor
for this valuable post!
[/blue]
a little Purple Star would be very appropriate, when you have a "valuable post" type of reply, as is customary, here at Tek-Tips.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JBinQLD]

Good point mate, so long ago I'd forgoten. 'tas been ackowledged now. Too easy, no drama. Sorry for delay folks.