Yes/No field change to a data field

[steve i]

Greetings;
I have created a call log database. 90% of the fields within the data table are yes/no questions for which the employee either selects (for yes) or leaves blank for No. The other two fields are employee name and date. Issue; now I need to change the "yes" data to counts to allow me to create reports and other calculations. I have tried in queries by adding the one field of yes / no group on it and adding it a second time and counting it...but when i do this for the 6 yes /no fields in the same query, the results dont make sense. I cant make seperate queries as I can join the queries together for the report.
Any thoughts?

Thank you,
Steve

[Rabbit]

Greetings;
I have created a call log database. 90% of the fields within the data table are yes/no questions for which the employee either selects (for yes) or leaves blank for No. The other two fields are employee name and date. Issue; now I need to change the "yes" data to counts to allow me to create reports and other calculations. I have tried in queries by adding the one field of yes / no group on it and adding it a second time and counting it...but when i do this for the 6 yes /no fields in the same query, the results dont make sense. I cant make seperate queries as I can join the queries together for the report.
Any thoughts?

Thank you,
Steve

You can't do this for more than one question at a time. The grouping won't work correctly as you've already seen. You'll have to make separate queries and then combine the queries or use DCount instead on the report.

[Jim Doherty]

Greetings;
I have created a call log database. 90% of the fields within the data table are yes/no questions for which the employee either selects (for yes) or leaves blank for No. The other two fields are employee name and date. Issue; now I need to change the "yes" data to counts to allow me to create reports and other calculations. I have tried in queries by adding the one field of yes / no group on it and adding it a second time and counting it...but when i do this for the 6 yes /no fields in the same query, the results dont make sense. I cant make seperate queries as I can join the queries together for the report.
Any thoughts?

Thank you,
Steve

Steve
If I am reading you correctly I am assuming you have a single table which consists of lets say RecordID (an autonumber), and then the employee name as a field and then the date of the record as a field and then any number of other fields in the same row that are Yes/No fields (tickboxes).

If you have validation in place to make sure the correct employee goes into the row each time (ie a dropdown or some other mechanism) and you simply wish to COUNT every event of a YES value (TRUE) in each column over a given number of rows to be listed in row (records) between two date parameters then the following SQL Statement might help you. As a sample it relies explicitly on a table called tblYourTableNam e and ANOTHER table called tblDateCriteria

tblDateCriteria is JOINED with tblYourTableNam e in whats is known as a CROSS JOIN (the trick is that tblDateCriteria should only ever be populated by you with one row of data and that should be two dates (Date from and a Date to)

(The trick is that this one row of data essentially provides the criteria to 'feed' the query with its date parameters where you are COUNTing the number of YESes that exist and GROUPING by the employee name for records existing between the date from and date to parameters. This will give you a matrix display of x number of records with columns taboot. If you save the SQL below as a single query you ONLY have one query not multiples of queries?

The do not know your field names per se however the following SQL works..... provided you create the tables as I have mentioned and throw some sample data in you will see how the query essentially uses DERIVED (sub query) counts for each column. You may notice the word SURVEY within the SQL? this is called ALIASING and it is merely aliasing the table tblYourTableNam e as SURVEY.

This query syntax (which if you ponder on it in the query design window for a moment) you will see that it eradicates the need to use the DCOUNT function which (if you have attached tables in a split database) is slower versus SQL

SELECT DISTINCT Survey.Employee Name,
(SELECT Count(*) FROM tblYourTableNam e , tblDateCriteria AS DateCriteria
WHERE (((Survey.Surve yDate) Between [DateCriteria].[SurveyDateCrite riaFrom] And [DateCriteria].[SurveyDateCrite riaTo])) AND EmployeeName=Su rvey.EmployeeNa me and Field1=true) AS YesCount1, (SELECT Count(*) FROM tblYourTableNam e , tblDateCriteria AS DateCriteria
WHERE (((Survey.Surve yDate) Between [DateCriteria].[SurveyDateCrite riaFrom] And [DateCriteria].[SurveyDateCrite riaTo])) AND EmployeeName=Su rvey.EmployeeNa me and Field2=true) AS YesCount2, (SELECT Count(*) FROM tblYourTableNam e , tblDateCriteria AS DateCriteria
WHERE (((Survey.Surve yDate) Between [DateCriteria].[SurveyDateCrite riaFrom] And [DateCriteria].[SurveyDateCrite riaTo])) AND EmployeeName=Su rvey.EmployeeNa me and Field3=true) AS YesCount3
FROM tblYourTableNam e AS Survey, tblDateCriteria AS DateCriteria
WHERE (((Survey.Surve yDate) Between [DateCriteria].[SurveyDateCrite riaFrom] And [DateCriteria].[SurveyDateCrite riaTo]))
ORDER BY Survey.Employee Name;

Regards

Jim

[steve i]

Jim;
Thank you for the detailed explanation.... question I will need to use a date criteria how do I set that up....do i need to create a query prior to running the sql you provided? Then I need to display the information on a report...

Thank you,
Steve

[Jim Doherty]

Jim;
Thank you for the detailed explanation.... question I will need to use a date criteria how do I set that up....do i need to create a query prior to running the sql you provided? Then I need to display the information on a report...

Thank you,
Steve

1) Create a table called tblDateCriteria consisting of two fields
first field called 'DateFrom' and the second field called 'DateTo'
both of these obviously with datetime format

2) You can if you wish set a composite primary key for this table it is not essential though (ie select the two fields in table design ensure they are both selected and hit the primary key tool on the menubar. both fields will acquire the Access yellow key denoting them both as part of a composite primary key.

3) Why are we doing this in 2) above? it merely ensures you put a value in both fields for the row record save to succeed thats all you can do it other ways if you wish but for the purposes of this this is the quickest method thats all.

4) Open the table put a date from value in and a date to value in (this will be your criteria) Close the table.

5) Create a form based on the table and save it as 'fdlgDateCriter ia'

6) Jig around with this form make it pretty or whatever, make it a dialog popup, centred on screen etc, whatever you wish the usual stuff. Put a close button on it blah blah blah... remove the record selector and navigation buttons and in the form design set the form property to NOT allow additions.

7) If you are with me thus far when you open the form you will see your date criteria in the fields but not be able to delete the ROW (no selectors and not be able to add anything either, which doesnt matter because you have access to the table. In effect you have a ONE row form record that will always be there so to speak.

9) Now with your main yes/no table thing which I have no idea of what the real field names are this end, create a query based on the table drag all fields to the grid, then add the tblDateCriteria table to the query window BUT DO NOT JOIN THEM leave them as they are (this is the cross join I spoke of) Save the query as 'qryTest' or something you can always rename it

10) Now if you look at the SQL statement I did.... the syntax must match the fieldnames. you either edit my SQL to suit your fieldnames OR if you quickly want to see the results as is, without affecting your data as is, then create a copy of your table and name it as tblYourTableNam e explicitly substitute your fieldnames in this table for mine and do with this table that which you did in 9) above.

11) Remember I have created the SQL query my end with no knowledge of your table name and fields, you obviously have more fields than I created in the query
so you'll have to build that by looking at my fieldnames and substituting it with yours.......I' m sure you get me.

12) When you succeed in replicating the query structure...... .when you then open the query you will see only rows returned where the survey date is between the dates provided by the cross joined table thats its.... so to speak. You don't need to do anything else.

13) Obviously when you need to edit the date criteria you will have the flexibility of being able to open the dialog form, edit the values close it and run the query again and so on. (No editing the query grid on this one you are in effect doing what is referred to in Access parlance as QBF query by form or a subtly different version of it)

14) Any Report you create can be based on the same query and obviously with it being so... the report will open exactly the same ....nothing more to add.... it gets it data from the underlying query the date parameters of which are passed by this CROSS JOIN method all part and parcel of the query itself rather unusual you might think!

You might want to look up the purpose of 'cross join' on the net. In this scenario we are putting it to a somewhat 'useful' purpose the logic in this case being (compare all rows in one table with all rows in the other table) the twist in actual fact here is to compare only ONE row in one table with hundreds or maybe thousands in the other and in so doing, restrict the return set by the values of the dates 'from and to' in one of the tables compared against the 'survey date' in the other you'll get the hang of it!!

I am a high speed typist Steve so don't worry about the length of this...but I can't read very fast hahahahahahahah aha

Hope this helps

Jim

[steve i]

Jim,
Thanks again....tomorr ow, Monday I will give it a try.

Steve

[steve i]

Jim;
Thanks again, it worked smooth...as I changed the SQL statement to match the 7 y/n fields I need...perfect! Until I added the sales order number if the sales = a yes....when I added that field "OrderNumbe r to the mix" and if multiple sales where made is uses the same count for each row instead of counting it only once... for example... The values are correct for each line, but since the order number is added, it duplicates the line each time the order number needs to be displayed. Any further help would very much appreciated

EmployeeName RMA Sales CustomerService Business ShippingRelated RMASaved SaleTaken ValueRMASaved ValueSaleTaken OrderNumber
Mark Yameen 6 1 3 0 0 2 1 10 3
Mark Yameen 6 1 3 0 0 2 1 10 3 11234
Mark Yameen 6 1 3 0 0 2 1 10 3 12345
Mark Yameen 6 1 3 0 0 2 1 10 3 56789

[steve i]

Jim.
Second thought... If in the table of data I add a field called LogID (autonumber) which would make each row a seperate record...what would change in sql to allow each line to enter seperately in so that if I need to add an "Order Number" it along with each line entry will be seperate...Then in a second query I can group or sum the previous query?

[Jim Doherty]

Jim.
Second thought... If in the table of data I add a field called LogID (autonumber) which would make each row a seperate record...what would change in sql to allow each line to enter seperately in so that if I need to add an "Order Number" it along with each line entry will be seperate...Then in a second query I can group or sum the previous query?

Steve the SQL I provided was specific to your first post and answered that per se it counts all the Yes values for the EMPLOYEE specifically between criteria dates and answers your first post as you say ...perfect

Where it is going to fall down is where if you add anything else to the grid as a column to display AS IS it is going to want to count on and display that column/cell value EVEN IF it doesnt relate to the employee referenced in the row.....odd looking I know but logically truthful the SQL is doing as it is told but not giving us what we necessarily want to see.

Before I get lost on not knowing what you have there overall I think what you are going to have to do is outline your table name, fieldnames and datatypes to me so that I can replicate here what you have there structurally. Then you outline what you actually need to achieve in terms of queryability and then maybe we can build the queries taboot and post the SQL back and forth until it is right. That way we both have the same structure and the SQL will perform as we would expect...... on both machines

Problem here working blind is that one doesnt always know what YOU have there and what it is you are doing and trying to achieve.

Jim

[steve i]

Greetings Jim;
I failed to bring home a copy of the SQL statement I modified based on your original post, I will post it in the morning, in case if it is needed..
Anyway as requested here is some info on this project.
Background:
Provide a enduser online Call log that will capture various type calls received and have the ability to identify specific fileds that will be used for paying bonuses on.
There are 2 seperate database: 1 the Frontend Data DB which provides the enduser with a menu to open a form, then the call log. 2: the Data DB this db provides only the table of data and is linked to the first db. I wanted to set it up this way to allow for reporting as the directors want the ability to see how everyone is doing at any given time. DB 1 contains no reporting.

Database: Data DB
Table:
tLogData
Fields:
EmployeeName (lookup wizard on the tEmployeeName), Date (autodate for current date), Type Calls Received (yes / no) : RMA, Sales, CustomerService , Business, Shipping Related, Type Calls That Will Pay a Bonus (yes / no): RMASaved, SaleTaken
If RMA Saved, and Sale Taken are yes, then a text field called Order Number is populated.

Table:
temployee
Fields:
EmployeeName (text) first and last name combined

Query / SQL:
I modified your sql statement to support the fields in the first query, and I also added the following:
Two fields which are expressions: RMASaved*$5.00 and SaleTaken*$3.00

Reporting:

The reporting (from a form menu) will point to the SQL statement, prompt for a date (from and to), then group by employee their calls for X period, totaling the call types, totaling the RMASaved and SalesTaken which will the amount calculated, then last for each Save and Sale Taken, display the Order Number which will allow mngt to verify the employee's action.

Database: Frontend Data DB

Form:
A main menu (used to open a form of Call Log entry)
A Call Log Entry Form to allow for entry of data records id in tLogData
this form has a drop down list of employees for them to make a selection on.

Jim, thats the basics of the project...as noted, I will post the sql I modified in the morning.
Thanks again for all your great help...much appreciated.
Steve

[steve i]

Jim,
Below is the sql less the "order number" field.


SELECT DISTINCT Survey.[Log Id], Survey.Employee Name, (SELECT Count(*) FROM tblYourTableNam e , tblDateCriteria AS DateCriteria WHERE (((Survey.Surve yDate) Between [DateCriteria].[SurveyDateCrite riaFrom] And [DateCriteria].[SurveyDateCrite riaTo])) AND EmployeeName=Su rvey.EmployeeNa me and RMA=true) AS RMA, (SELECT Count(*) FROM tblYourTableNam e , tblDateCriteria AS DateCriteria WHERE (((Survey.Surve yDate) Between [DateCriteria].[SurveyDateCrite riaFrom] And [DateCriteria].[SurveyDateCrite riaTo])) AND EmployeeName=Su rvey.EmployeeNa me and Sales=true) AS Sales, (SELECT Count(*) FROM tblYourTableNam e , tblDateCriteria AS DateCriteria WHERE (((Survey.Surve yDate) Between [DateCriteria].[SurveyDateCrite riaFrom] And [DateCriteria].[SurveyDateCrite riaTo])) AND EmployeeName=Su rvey.EmployeeNa me and CustomerService =true) AS CustomerService , (SELECT Count(*) FROM tblYourTableNam e , tblDateCriteria AS DateCriteria WHERE (((Survey.Surve yDate) Between [DateCriteria].[SurveyDateCrite riaFrom] And [DateCriteria].[SurveyDateCrite riaTo])) AND EmployeeName=Su rvey.EmployeeNa me and Business=true) AS Business, (SELECT Count(*) FROM tblYourTableNam e , tblDateCriteria AS DateCriteria WHERE (((Survey.Surve yDate) Between [DateCriteria].[SurveyDateCrite riaFrom] And [DateCriteria].[SurveyDateCrite riaTo])) AND EmployeeName=Su rvey.EmployeeNa me and ShippingRelated =true) AS ShippingRelated , (SELECT Count(*) FROM tblYourTableNam e , tblDateCriteria AS DateCriteria WHERE (((Survey.Surve yDate) Between [DateCriteria].[SurveyDateCrite riaFrom] And [DateCriteria].[SurveyDateCrite riaTo])) AND EmployeeName=Su rvey.EmployeeNa me and SaleTaken=true) AS SaleTaken, [SaleTaken]*3 AS ValueSaleTaken, [RMA]*5 AS ValueRMASaved, Survey.[Sales OrdeNumber], Survey.[RMASaved OrderNumber]
FROM tblYourTableNam e AS Survey, tblDateCriteria AS DateCriteria
WHERE (((Survey.Surve yDate) Between DateCriteria.Su rveyDateCriteri aFrom And DateCriteria.Su rveyDateCriteri aTo))
ORDER BY Survey.Employee Name;

[Jim Doherty]

Jim,
Below is the sql less the "order number" field.


SELECT DISTINCT Survey.[Log Id], Survey.Employee Name, (SELECT Count(*) FROM tblYourTableNam e , tblDateCriteria AS DateCriteria WHERE (((Survey.Surve yDate) Between [DateCriteria].[SurveyDateCrite riaFrom] And [DateCriteria].[SurveyDateCrite riaTo])) AND EmployeeName=Su rvey.EmployeeNa me and RMA=true) AS RMA, (SELECT Count(*) FROM tblYourTableNam e , tblDateCriteria AS DateCriteria WHERE (((Survey.Surve yDate) Between [DateCriteria].[SurveyDateCrite riaFrom] And [DateCriteria].[SurveyDateCrite riaTo])) AND EmployeeName=Su rvey.EmployeeNa me and Sales=true) AS Sales, (SELECT Count(*) FROM tblYourTableNam e , tblDateCriteria AS DateCriteria WHERE (((Survey.Surve yDate) Between [DateCriteria].[SurveyDateCrite riaFrom] And [DateCriteria].[SurveyDateCrite riaTo])) AND EmployeeName=Su rvey.EmployeeNa me and CustomerService =true) AS CustomerService , (SELECT Count(*) FROM tblYourTableNam e , tblDateCriteria AS DateCriteria WHERE (((Survey.Surve yDate) Between [DateCriteria].[SurveyDateCrite riaFrom] And [DateCriteria].[SurveyDateCrite riaTo])) AND EmployeeName=Su rvey.EmployeeNa me and Business=true) AS Business, (SELECT Count(*) FROM tblYourTableNam e , tblDateCriteria AS DateCriteria WHERE (((Survey.Surve yDate) Between [DateCriteria].[SurveyDateCrite riaFrom] And [DateCriteria].[SurveyDateCrite riaTo])) AND EmployeeName=Su rvey.EmployeeNa me and ShippingRelated =true) AS ShippingRelated , (SELECT Count(*) FROM tblYourTableNam e , tblDateCriteria AS DateCriteria WHERE (((Survey.Surve yDate) Between [DateCriteria].[SurveyDateCrite riaFrom] And [DateCriteria].[SurveyDateCrite riaTo])) AND EmployeeName=Su rvey.EmployeeNa me and SaleTaken=true) AS SaleTaken, [SaleTaken]*3 AS ValueSaleTaken, [RMA]*5 AS ValueRMASaved, Survey.[Sales OrdeNumber], Survey.[RMASaved OrderNumber]
FROM tblYourTableNam e AS Survey, tblDateCriteria AS DateCriteria
WHERE (((Survey.Surve yDate) Between DateCriteria.Su rveyDateCriteri aFrom And DateCriteria.Su rveyDateCriteri aTo))
ORDER BY Survey.Employee Name;

Steve,

Check out your PM's if you haven't already theres one from me

Jim

[steve i]

Jim;
I'm not sure what PM means?

Steve

[Jim Doherty]

Jim;
I'm not sure what PM means?

Steve

Steve

Look on this page at the top right of browser window PM =private message

Jim

[Jim Doherty]

Jim;
I'm not sure what PM means?

Steve

Steve private message me please I have I have one database here to give you that I have done in some down time

Regards

Jim