473,320 Members | 1,950 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Yes/No field change to a data field

16
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
Aug 22 '07 #1
14 2336
Rabbit
12,516 Expert Mod 8TB
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.
Aug 22 '07 #2
Jim Doherty
897 Expert 512MB
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 tblYourTableName and ANOTHER table called tblDateCriteria

tblDateCriteria is JOINED with tblYourTableName 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 tblYourTableName 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.EmployeeName,
(SELECT Count(*) FROM tblYourTableName , tblDateCriteria AS DateCriteria
WHERE (((Survey.SurveyDate) Between [DateCriteria].[SurveyDateCriteriaFrom] And [DateCriteria].[SurveyDateCriteriaTo])) AND EmployeeName=Survey.EmployeeName and Field1=true) AS YesCount1, (SELECT Count(*) FROM tblYourTableName , tblDateCriteria AS DateCriteria
WHERE (((Survey.SurveyDate) Between [DateCriteria].[SurveyDateCriteriaFrom] And [DateCriteria].[SurveyDateCriteriaTo])) AND EmployeeName=Survey.EmployeeName and Field2=true) AS YesCount2, (SELECT Count(*) FROM tblYourTableName , tblDateCriteria AS DateCriteria
WHERE (((Survey.SurveyDate) Between [DateCriteria].[SurveyDateCriteriaFrom] And [DateCriteria].[SurveyDateCriteriaTo])) AND EmployeeName=Survey.EmployeeName and Field3=true) AS YesCount3
FROM tblYourTableName AS Survey, tblDateCriteria AS DateCriteria
WHERE (((Survey.SurveyDate) Between [DateCriteria].[SurveyDateCriteriaFrom] And [DateCriteria].[SurveyDateCriteriaTo]))
ORDER BY Survey.EmployeeName;

Regards

Jim
Aug 23 '07 #3
steve i
16
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
Aug 25 '07 #4
Jim Doherty
897 Expert 512MB
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 'fdlgDateCriteria'

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 tblYourTableName 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 hahahahahahahahaha

Hope this helps

Jim
Aug 26 '07 #5
steve i
16
Jim,
Thanks again....tomorrow, Monday I will give it a try.

Steve
Aug 27 '07 #6
steve i
16
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 "OrderNumber 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
Aug 27 '07 #7
steve i
16
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?
Aug 27 '07 #8
Jim Doherty
897 Expert 512MB
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
Aug 27 '07 #9
steve i
16
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
Aug 28 '07 #10
steve i
16
Jim,
Below is the sql less the "order number" field.


SELECT DISTINCT Survey.[Log Id], Survey.EmployeeName, (SELECT Count(*) FROM tblYourTableName , tblDateCriteria AS DateCriteria WHERE (((Survey.SurveyDate) Between [DateCriteria].[SurveyDateCriteriaFrom] And [DateCriteria].[SurveyDateCriteriaTo])) AND EmployeeName=Survey.EmployeeName and RMA=true) AS RMA, (SELECT Count(*) FROM tblYourTableName , tblDateCriteria AS DateCriteria WHERE (((Survey.SurveyDate) Between [DateCriteria].[SurveyDateCriteriaFrom] And [DateCriteria].[SurveyDateCriteriaTo])) AND EmployeeName=Survey.EmployeeName and Sales=true) AS Sales, (SELECT Count(*) FROM tblYourTableName , tblDateCriteria AS DateCriteria WHERE (((Survey.SurveyDate) Between [DateCriteria].[SurveyDateCriteriaFrom] And [DateCriteria].[SurveyDateCriteriaTo])) AND EmployeeName=Survey.EmployeeName and CustomerService=true) AS CustomerService, (SELECT Count(*) FROM tblYourTableName , tblDateCriteria AS DateCriteria WHERE (((Survey.SurveyDate) Between [DateCriteria].[SurveyDateCriteriaFrom] And [DateCriteria].[SurveyDateCriteriaTo])) AND EmployeeName=Survey.EmployeeName and Business=true) AS Business, (SELECT Count(*) FROM tblYourTableName , tblDateCriteria AS DateCriteria WHERE (((Survey.SurveyDate) Between [DateCriteria].[SurveyDateCriteriaFrom] And [DateCriteria].[SurveyDateCriteriaTo])) AND EmployeeName=Survey.EmployeeName and ShippingRelated=true) AS ShippingRelated, (SELECT Count(*) FROM tblYourTableName , tblDateCriteria AS DateCriteria WHERE (((Survey.SurveyDate) Between [DateCriteria].[SurveyDateCriteriaFrom] And [DateCriteria].[SurveyDateCriteriaTo])) AND EmployeeName=Survey.EmployeeName and SaleTaken=true) AS SaleTaken, [SaleTaken]*3 AS ValueSaleTaken, [RMA]*5 AS ValueRMASaved, Survey.[Sales OrdeNumber], Survey.[RMASaved OrderNumber]
FROM tblYourTableName AS Survey, tblDateCriteria AS DateCriteria
WHERE (((Survey.SurveyDate) Between DateCriteria.SurveyDateCriteriaFrom And DateCriteria.SurveyDateCriteriaTo))
ORDER BY Survey.EmployeeName;
Aug 28 '07 #11
Jim Doherty
897 Expert 512MB
Jim,
Below is the sql less the "order number" field.


SELECT DISTINCT Survey.[Log Id], Survey.EmployeeName, (SELECT Count(*) FROM tblYourTableName , tblDateCriteria AS DateCriteria WHERE (((Survey.SurveyDate) Between [DateCriteria].[SurveyDateCriteriaFrom] And [DateCriteria].[SurveyDateCriteriaTo])) AND EmployeeName=Survey.EmployeeName and RMA=true) AS RMA, (SELECT Count(*) FROM tblYourTableName , tblDateCriteria AS DateCriteria WHERE (((Survey.SurveyDate) Between [DateCriteria].[SurveyDateCriteriaFrom] And [DateCriteria].[SurveyDateCriteriaTo])) AND EmployeeName=Survey.EmployeeName and Sales=true) AS Sales, (SELECT Count(*) FROM tblYourTableName , tblDateCriteria AS DateCriteria WHERE (((Survey.SurveyDate) Between [DateCriteria].[SurveyDateCriteriaFrom] And [DateCriteria].[SurveyDateCriteriaTo])) AND EmployeeName=Survey.EmployeeName and CustomerService=true) AS CustomerService, (SELECT Count(*) FROM tblYourTableName , tblDateCriteria AS DateCriteria WHERE (((Survey.SurveyDate) Between [DateCriteria].[SurveyDateCriteriaFrom] And [DateCriteria].[SurveyDateCriteriaTo])) AND EmployeeName=Survey.EmployeeName and Business=true) AS Business, (SELECT Count(*) FROM tblYourTableName , tblDateCriteria AS DateCriteria WHERE (((Survey.SurveyDate) Between [DateCriteria].[SurveyDateCriteriaFrom] And [DateCriteria].[SurveyDateCriteriaTo])) AND EmployeeName=Survey.EmployeeName and ShippingRelated=true) AS ShippingRelated, (SELECT Count(*) FROM tblYourTableName , tblDateCriteria AS DateCriteria WHERE (((Survey.SurveyDate) Between [DateCriteria].[SurveyDateCriteriaFrom] And [DateCriteria].[SurveyDateCriteriaTo])) AND EmployeeName=Survey.EmployeeName and SaleTaken=true) AS SaleTaken, [SaleTaken]*3 AS ValueSaleTaken, [RMA]*5 AS ValueRMASaved, Survey.[Sales OrdeNumber], Survey.[RMASaved OrderNumber]
FROM tblYourTableName AS Survey, tblDateCriteria AS DateCriteria
WHERE (((Survey.SurveyDate) Between DateCriteria.SurveyDateCriteriaFrom And DateCriteria.SurveyDateCriteriaTo))
ORDER BY Survey.EmployeeName;
Steve,

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

Jim
Aug 28 '07 #12
steve i
16
Jim;
I'm not sure what PM means?

Steve
Aug 29 '07 #13
Jim Doherty
897 Expert 512MB
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
Aug 29 '07 #14
Jim Doherty
897 Expert 512MB
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
Aug 29 '07 #15

Sign in to post your reply or Sign up for a free account.

Similar topics

13
by: Peter | last post by:
Can anyone tell me how to change the data type of a field in a table created with a make table query? The field is a binary and must be changed to text. alternately does anyone know how to specify...
7
by: Amy | last post by:
I'm trying to add an autoincrementing id to a table based on an existing field Name, but Name has duplicated records. How can I do that in ACCESS? Thanks. Amy
4
by: N. Graves | last post by:
Hello... thank you for your time. I have a form that has a List box of equipotent records and a sub form that will show the data of the equipment select from the list box. Is it possible to...
4
by: Mal | last post by:
I have an ACC 2000 database that has a strange behaviour I have a small table, with just a few fields... My report has very simple grouping and sorting, no code bar a NODATA event. I have a...
4
by: Josh | last post by:
Hi, I am trying to write a function in a module in MS Access 2000 that will change the data type of a field called 'Start' in table 'bo_cpm_CS01ALL'. Here is the code that I have done so far...
4
by: Javier Gomez | last post by:
I have a query field which is a non editable field because is a function's result. Due to I need to edit the information, Can you please suggest me any solution for this problem?? (No matter...
5
by: HS1 | last post by:
Hello I have a datagrid to show data for a database table using "seclect * from tablename" The datagrid works OK. However, I want to change the name of the fields in the database to other...
2
by: Brett | last post by:
My database has 2 tables: Table1 & Table2. If a field is not null on a record in table2, then the not null fields in table1 that correspond to the records in table1 needs to be updated to match the...
5
by: Alan T | last post by:
Currently our SQL Server 2000 database table field is using char to store as boolean, ie. "T" or "F". Now we change the field from char to bit field. I am not sure how it has impact on the C#...
17
by: The Frog | last post by:
Hello everyone, I am working on an application that can build database objects in MS Access from text files. I suppose you could call it a backup and restore type routine. Accessing the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.