If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
add yes/no's in access database
Because:
1) I was concerned about possible null values 2) I wanted to apply a keystroke tax for creating an un-normalized table structure ;-) -- Duane Hookom MS Access MVP -- "John Spencer" wrote in message ... Since these are all yes/no fields wouldn't Attended: Abs([Jan]+[Feb]+[Mar]+...) work just as well "Duane Hookom" wrote in message ... Looks like you are "committing spreadsheet". A normalized table would create one record per meeting attended so you could use a simple SUM or COUNT in a totals query. If you can't change your table structure, you can use something like: Attended: Abs(Nz([Jan],0))+Abs(Nz([Feb],0)) + etc for each month field -- Duane Hookom MS Access MVP -- "robo1a" wrote in message ... can't seem to get it to function the way I want it to.here's what I have, name followed by 9 months with yes/no box. I want to add the total months someone was present at a meeting. Thanks "NetworkTrade" wrote: hey there - I'll give you a real simply approach.... a. make a new query in Design view and just add the Name Field and the yes/no field b. select the greek sum icon from the tool bar (looks like greek E) this adds a "Total" row to your query builder 'GroupBy' is the default entry - leave this as-is for the Name field c. in the yes/no field click into right side of 'Groupby' and a drop-down will appear - - go ahead and change that to 'Count' then hit the red ! ......and the result will be the name of all and their count of Yes easy to scan down it to see whom is above your minimum criteria......hope this approach helps.... -- NTC "robo1a" wrote: I created a database that use's the check box(yes/no) my question is "How can I tabulte the yes and no's. example: show if 5 or more meetings were attended, for use in a query thanks |
#12
|
|||
|
|||
add yes/no's in access database
I'm sure it is possible, but what is your table structure? Is it something
like the following (bad table design)? MemberLastName MemberFirstName Meeting1YN Meeting2YN Meeting3YN .... Meeting9YN Or is it something else? If it is the way I show above, then the solution I posted earlier would be what you wanted. In a field in the query grid Field: NumberAttended: Abs([Meeting1YN] + [Meeting2YN] + [Meeting3YN] + .... + [Meeting9YN]) Criteria: =4 That assumes that Meeting1YN, etc are all YesNo (or boolean) fields. "robo1a" wrote in message ... Greetings John, I tried the formula you posted and recieved this pop up msg..calculation is to complex.try using variable...I'm just an old dog trying to learn a new trick, this is the first database I attempted to build. to say that I am confused is putting it mildly..Here is what I want it to do for me. I have a base of 85 members and we have 9 meetings a year.jan thru may. I would like to see who(last name) attended how many meetings. they need to attend at least 5 mtg to be eligble for a scholarship fund drawing.I would like to see ie: Blow,Joe 7. is this possible. Thanks for any assistance you can give. Is there a book on this subject that would help me? thanks again Rob M "John Spencer" wrote: Pardon me, but your simple approach won't work on yes/no (boolean) fields. This field type always has a value - either true or false. Since Count counts the number of times ANY value exists in a field, the count would the same as the number of rows in the group. To count true, you can use a formula Abs(Sum([YesNoField])) "NetworkTrade" wrote in message ... hey there - I'll give you a real simply approach.... a. make a new query in Design view and just add the Name Field and the yes/no field b. select the greek sum icon from the tool bar (looks like greek E) this adds a "Total" row to your query builder 'GroupBy' is the default entry - leave this as-is for the Name field c. in the yes/no field click into right side of 'Groupby' and a drop-down will appear - - go ahead and change that to 'Count' then hit the red ! ......and the result will be the name of all and their count of Yes easy to scan down it to see whom is above your minimum criteria......hope this approach helps.... -- NTC "robo1a" wrote: I created a database that use's the check box(yes/no) my question is "How can I tabulte the yes and no's. example: show if 5 or more meetings were attended, for use in a query thanks |
#13
|
|||
|
|||
add yes/no's in access database
John,
I have to Bad design I guess. lastname firstname sept oct nov dec jan feb mar apr may the months are all yes/no. what I would like is this: have a report that list the names and number of meetings attened such as: jacobs,henry 7. only interested in people that attended 5 or more meetings. I am grateful for your patients in this matter John. when I try the formulas you suggest it ask me for paramiters ,I'm lost. thanks again Rob m "John Spejanncer" wrote: I'm sure it is possible, but what is your table structure? Is it something like the following (bad table design)? MemberLastName MemberFirstName Meeting1YN Meeting2YN Meeting3YN .... Meeting9YN Or is it something else? If it is the way I show above, then the solution I posted earlier would be what you wanted. In a field in the query grid Field: NumberAttended: Abs([Meeting1YN] + [Meeting2YN] + [Meeting3YN] + .... + [Meeting9YN]) Criteria: =4 That assumes that Meeting1YN, etc are all YesNo (or boolean) fields. "robo1a" wrote in message ... Greetings John, I tried the formula you posted and recieved this pop up msg..calculation is to complex.try using variable...I'm just an old dog trying to learn a new trick, this is the first database I attempted to build. to say that I am confused is putting it mildly..Here is what I want it to do for me. I have a base of 85 members and we have 9 meetings a year.jan thru may. I would like to see who(last name) attended how many meetings. they need to attend at least 5 mtg to be eligble for a scholarship fund drawing.I would like to see ie: Blow,Joe 7. is this possible. Thanks for any assistance you can give. Is there a book on this subject that would help me? thanks again Rob M "John Spencer" wrote: Pardon me, but your simple approach won't work on yes/no (boolean) fields. This field type always has a value - either true or false. Since Count counts the number of times ANY value exists in a field, the count would the same as the number of rows in the group. To count true, you can use a formula Abs(Sum([YesNoField])) "NetworkTrade" wrote in message ... hey there - I'll give you a real simply approach.... a. make a new query in Design view and just add the Name Field and the yes/no field b. select the greek sum icon from the tool bar (looks like greek E) this adds a "Total" row to your query builder 'GroupBy' is the default entry - leave this as-is for the Name field c. in the yes/no field click into right side of 'Groupby' and a drop-down will appear - - go ahead and change that to 'Count' then hit the red ! ......and the result will be the name of all and their count of Yes easy to scan down it to see whom is above your minimum criteria......hope this approach helps.... -- NTC "robo1a" wrote: I created a database that use's the check box(yes/no) my question is "How can I tabulte the yes and no's. example: show if 5 or more meetings were attended, for use in a query thanks |
#14
|
|||
|
|||
add yes/no's in access database
Well, I can't see what you are doing, so this is tough to work out.
In the SQL view, I would expect to see SELECT LastName, FirstName, Abs([Sept] + [Oct] + [Nov] + [Dec] + [Jan] + [Feb] + [Mar] + [Apr] + [May]) As [MeetingsAttended] FROM [YourTableName] WHERE Abs([Sept] + [Oct] + [Nov] + [Dec] + [Jan] + [Feb] + [Mar] + [Apr] + [May]) 4 If you are being asked for parameters, that indicates that you have a field name or table name that Access doesn't understand - misspelling, different spelling, field not really in table, etc. What exactly is the parameter asking you for? Troubleshooting technique is to try building the query a bit at a time. Select LastName, FirstName FROM [YourTableName] If that works then add in Abs([Sept]) If that works then add in + [Oct] If that works then try adding the rest of the months. If that works try adding the condition in. robo1a wrote: John, I have to Bad design I guess. lastname firstname sept oct nov dec jan feb mar apr may the months are all yes/no. what I would like is this: have a report that list the names and number of meetings attened such as: jacobs,henry 7. only interested in people that attended 5 or more meetings. I am grateful for your patients in this matter John. when I try the formulas you suggest it ask me for paramiters ,I'm lost. thanks again Rob m "John Spejanncer" wrote: I'm sure it is possible, but what is your table structure? Is it something like the following (bad table design)? MemberLastName MemberFirstName Meeting1YN Meeting2YN Meeting3YN .... Meeting9YN Or is it something else? If it is the way I show above, then the solution I posted earlier would be what you wanted. In a field in the query grid Field: NumberAttended: Abs([Meeting1YN] + [Meeting2YN] + [Meeting3YN] + .... + [Meeting9YN]) Criteria: =4 That assumes that Meeting1YN, etc are all YesNo (or boolean) fields. "robo1a" wrote in message ... Greetings John, I tried the formula you posted and recieved this pop up msg..calculation is to complex.try using variable...I'm just an old dog trying to learn a new trick, this is the first database I attempted to build. to say that I am confused is putting it mildly..Here is what I want it to do for me. I have a base of 85 members and we have 9 meetings a year.jan thru may. I would like to see who(last name) attended how many meetings. they need to attend at least 5 mtg to be eligble for a scholarship fund drawing.I would like to see ie: Blow,Joe 7. is this possible. Thanks for any assistance you can give. Is there a book on this subject that would help me? thanks again Rob M "John Spencer" wrote: Pardon me, but your simple approach won't work on yes/no (boolean) fields. This field type always has a value - either true or false. Since Count counts the number of times ANY value exists in a field, the count would the same as the number of rows in the group. To count true, you can use a formula Abs(Sum([YesNoField])) "NetworkTrade" wrote in message ... hey there - I'll give you a real simply approach.... a. make a new query in Design view and just add the Name Field and the yes/no field b. select the greek sum icon from the tool bar (looks like greek E) this adds a "Total" row to your query builder 'GroupBy' is the default entry - leave this as-is for the Name field c. in the yes/no field click into right side of 'Groupby' and a drop-down will appear - - go ahead and change that to 'Count' then hit the red ! ......and the result will be the name of all and their count of Yes easy to scan down it to see whom is above your minimum criteria......hope this approach helps.... -- NTC "robo1a" wrote: I created a database that use's the check box(yes/no) my question is "How can I tabulte the yes and no's. example: show if 5 or more meetings were attended, for use in a query thanks |
#15
|
|||
|
|||
add yes/no's in access database
John, I did it, but I cheated.. I copied and pasted your sql
statement...works fine Thank You so much Rob m "John Spencer" wrote: Well, I can't see what you are doing, so this is tough to work out. In the SQL view, I would expect to see SELECT LastName, FirstName, Abs([Sept] + [Oct] + [Nov] + [Dec] + [Jan] + [Feb] + [Mar] + [Apr] + [May]) As [MeetingsAttended] FROM [YourTableName] WHERE Abs([Sept] + [Oct] + [Nov] + [Dec] + [Jan] + [Feb] + [Mar] + [Apr] + [May]) 4 If you are being asked for parameters, that indicates that you have a field name or table name that Access doesn't understand - misspelling, different spelling, field not really in table, etc. What exactly is the parameter asking you for? Troubleshooting technique is to try building the query a bit at a time. Select LastName, FirstName FROM [YourTableName] If that works then add in Abs([Sept]) If that works then add in + [Oct] If that works then try adding the rest of the months. If that works try adding the condition in. robo1a wrote: John, I have to Bad design I guess. lastname firstname sept oct nov dec jan feb mar apr may the months are all yes/no. what I would like is this: have a report that list the names and number of meetings attened such as: jacobs,henry 7. only interested in people that attended 5 or more meetings. I am grateful for your patients in this matter John. when I try the formulas you suggest it ask me for paramiters ,I'm lost. thanks again Rob m "John Spejanncer" wrote: I'm sure it is possible, but what is your table structure? Is it something like the following (bad table design)? MemberLastName MemberFirstName Meeting1YN Meeting2YN Meeting3YN .... Meeting9YN Or is it something else? If it is the way I show above, then the solution I posted earlier would be what you wanted. In a field in the query grid Field: NumberAttended: Abs([Meeting1YN] + [Meeting2YN] + [Meeting3YN] + .... + [Meeting9YN]) Criteria: =4 That assumes that Meeting1YN, etc are all YesNo (or boolean) fields. "robo1a" wrote in message ... Greetings John, I tried the formula you posted and recieved this pop up msg..calculation is to complex.try using variable...I'm just an old dog trying to learn a new trick, this is the first database I attempted to build. to say that I am confused is putting it mildly..Here is what I want it to do for me. I have a base of 85 members and we have 9 meetings a year.jan thru may. I would like to see who(last name) attended how many meetings. they need to attend at least 5 mtg to be eligble for a scholarship fund drawing.I would like to see ie: Blow,Joe 7. is this possible. Thanks for any assistance you can give. Is there a book on this subject that would help me? thanks again Rob M "John Spencer" wrote: Pardon me, but your simple approach won't work on yes/no (boolean) fields. This field type always has a value - either true or false. Since Count counts the number of times ANY value exists in a field, the count would the same as the number of rows in the group. To count true, you can use a formula Abs(Sum([YesNoField])) "NetworkTrade" wrote in message ... hey there - I'll give you a real simply approach.... a. make a new query in Design view and just add the Name Field and the yes/no field b. select the greek sum icon from the tool bar (looks like greek E) this adds a "Total" row to your query builder 'GroupBy' is the default entry - leave this as-is for the Name field c. in the yes/no field click into right side of 'Groupby' and a drop-down will appear - - go ahead and change that to 'Count' then hit the red ! ......and the result will be the name of all and their count of Yes easy to scan down it to see whom is above your minimum criteria......hope this approach helps.... -- NTC "robo1a" wrote: I created a database that use's the check box(yes/no) my question is "How can I tabulte the yes and no's. example: show if 5 or more meetings were attended, for use in a query thanks |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
What is the difference between 2002 and 2003? | Red Sonya | General Discussion | 2 | March 1st, 2005 05:10 AM |
Access 2000 -vs- Access 2003? | Mark | General Discussion | 5 | November 30th, 2004 06:36 AM |
Images in a database | Franz | General Discussion | 10 | October 7th, 2004 09:35 AM |
Access XP Compared to Access 2003 | Mardene Leahu | New Users | 1 | October 1st, 2004 05:11 AM |
Database Window Gone | DaveB | General Discussion | 2 | July 29th, 2004 12:24 AM |