Query to pull only yes values? 2

[Asspin]

Hopefully this will make sense on a Friday, but here goes.
I have a database with a bunch of data housed as yes/no fields and checkboxes in the forms. I am wanting to create a report that basically lists out everything that has been checked as 'yes' for a each rep. I have my query setup to just pull the rep, but I am not sure how to just get the data that has been checked.
Any help would be great!

Here is the SQL for the query, figured that was easier to look at.

SELECT tData.sName, tData.Value1, tData.Value2, tData.Value3, tData.Value4, tData.Value5
FROM tData
WHERE (((tData.sName)=[Forms]![fReporting]![comChange]));

This is pulling the data for only the rep I want, but I am pretty lost from here!

Dan

 

[Remou]

Do you mean something like:

SELECT tData.sName, tData.Value1, tData.Value2, tData.Value3, tData.Value4, tData.Value5
FROM tData
WHERE tData.sName=[Forms]![fReporting]![comChange]
And (tData.Value1=True Or tData.Value2=True Or 
tData.Value3=True Or tData.Value4=True 
Or tData.Value5=True)

Generally fields with numbers in the names are a sign that you should have another table:

tblYesNoStuff
IdFromMainTable <- Foreign key
PropertyID <- Foreign key
PropertyValue <-Yes/No

tblProperties
ID
Property <- For example, Covers Eastern area

http://lessthandot.com

 

[Asspin]

Hmm, well I what I am actually trying to do, would basically be to create a report with the user name, then a list of all the items that are checked and a list of all the items that aren't checked. The problem is I don't know what I am doing. The numbered fields by the way, are just renamed to make it easy online. There is actually about 25 items that may or may not be checked for each user. This is what I am trying to get a list of.

Dan

 

[Remou]

It very much looks like you need to normalize your table. In the meantime, the best bet would seem to be a union query, for example:

SELECT ID, sName, "Value1" As PropName, Value1 As PropVal FROM tData
UNION ALL
SELECT ID, sName, "Value2" As PropName, Value2 As PropVal FROM tData
UNION ALL
SELECT ID, sName, "Value3" As PropName, Value3 As PropVal FROM tData
<and so on>
UNION ALL
SELECT ID, sName, "Value25" As PropName, Value25 As PropVal FROM tData

You can then build your report on this query and get something like:

[tt]
PropName Prop

SName : Al
------------------
Is Important Yes
Is Green No

SName : Bee
------------------
Is Important No
Is Green No[/tt]

http://lessthandot.com

 

[Asspin]

Ok, that looks like what I am trying to do, however I don't know what I am doing with SQL.

Here is what I actually have right now, I am not sure how to integrate in the UNION statement.

SELECT tData.sName, tData.sSupervisor, tData.sTopSkill, tData.sLocation, tData.bService, tData.bChange, tData.bPending, tData.bRequirements, tData.bUW, tData.bUS, tData.bVA, tData.bBilling, tData.bVB, tData.bDefault, tData.bOld, tData.bLeadership
FROM tData
WHERE (((tData.sName)=[Forms]![fReporting]![comChange]));

Dan

 

[lespaul]

which of those fields are your yes/no fields? those are the ones that need to be normalized....

So get all the fields you need plus ONE of the yes/no fields and then UNION to the NEXT of the yes/no fields:

SELECT All the Other FIelds, "FirstYesNo" as WhichOne, FirstYesNo From TableName WHERE FirstYesNo = True
UNION
SELECT all the other Fields, "SecondYesNo", SecondYesNo  FROM TableName WHERE SecondYesNo = True
UNION
SELECT all the other fields, "LastYesNo", LastYesNo FROM TableName WHERE LastYesNo = True

Leslie

Have you met Hardy Heron?

 

[Asspin]

Ok, that seems to be working, can I filter it with this? If so, how?

WHERE (((tData.sName)=[Forms]![fReporting]![comChange]))

Dan

 

[Remou]

You can wrap the union query as a sub query:

SELECT t.sName, t.AllOtherFields, t.PropName, t.PropVal 
FROM (
SELECT sName, AllOtherFields, "Value1" As PropName, Value1 As PropVal FROM tData
UNION ALL
SELECT sName, AllOtherFields, "Value2" As PropName, Value2 As PropVal FROM tData
UNION ALL
SELECT sName, AllOtherFields, "Value3" As PropName, Value3 As PropVal FROM tData
<and so on>
UNION ALL
SELECT sName, AllOtherFields, "Value25" As PropName, Value25 As PropVal FROM tData) t 
WHERE t.sName=[Forms]![fReporting]![comChange]

http://lessthandot.com

 

[Asspin]

Ok, so this is what I have going so far...

SELECT tData.sName, tData.Supervisor, tData.sTopSkill, tData.sLocation, "Service" as Skill, bService FROM tData WHERE bService = True
UNION SELECT tData.sName, tData.Supervisor, tData.sTopSkill, tData.sLocation, "Change", bChange FROM tData WHERE bChange = True
UNION SELECT tData.sName, tData.Supervisor, tData.sTopSkill, tData.sLocation, "Pending", bPending FROM tData WHERE bPending = True
UNION SELECT tData.sName, tData.Supervisor, tData.sTopSkill, tData.sLocation, "Requirements", bRequirements FROM tData WHERE bRequirements = True;

I am getting an extra column called bService, which I am not sure how to fix.

Remou I am getting an error when I try to setup the subquery. Something about a reserved word is in use.

Dan

 

[Remou]

The idea is to create a normalized table where you have a property name and value, so try:

SELECT t.sName, t.Supervisor, t.sTopSkill, t.sLocation, t.Skill, t.SkillValue 
FROM (
SELECT tData.sName, tData.Supervisor, tData.sTopSkill, tData.sLocation, "Service" as Skill, bService As SkillValue
FROM tData 
UNION SELECT tData.sName, tData.Supervisor, tData.sTopSkill, tData.sLocation, "Change" As Skill, bChange As SkillValue 
FROM tData
UNION SELECT tData.sName, tData.Supervisor, tData.sTopSkill, tData.sLocation, "Pending" As Skill, bPending As SkillValue 
FROM tData
UNION SELECT tData.sName, tData.Supervisor, tData.sTopSkill, tData.sLocation, "Requirements" As Skill, bRequirements As SkillValue 
FROM tData) t
WHERE t.SkillValue = True 
AND t.sName = [Forms]![fReporting]![comChange]

http://lessthandot.com

 

[Asspin]

Ahhhh yes, that did the trick! Awesome! Thanks for helping the SQL and database noob!

Dan