Counting multiple y/n fields from multiple tables.

  • Thread starter Thread starter Philip Klaerner
  • Start date Start date
P

Philip Klaerner

OK i am trying to count 3 tables that have y/n (each y/n are named the same in each table) in each of the tables. I am trying to get a sum total from the 3 tables for the "NO".

My access table run like this.
t1= prime key (date)
t2= slave key (start date)
t3= slave key (date entered)
t4= slave key (date entered)
I am running standard one to many relationships between each primary and slave.

I want to get the grand total of all the no's in t2, t3, and t4

I have tried IIF, Select, and the wizard with no luck can some one help with the correct equation and or code. I need this to complete my final sub report. Thank for you time on this matter please help.

Phil

EggHeadCafe - Software Developer Portal of Choice
Review: XSelerator XSL Editor/Debugger
http://www.eggheadcafe.com/tutorial...d5-7432dfb4d64d/review-xselerator-xsl-ed.aspx
 
OK i am trying to count 3 tables that have y/n (each y/n are named the same in each table) in each of the tables. I am trying to get a sum total from the 3 tables for the "NO".

My access table run like this.
t1= prime key (date)
t2= slave key (start date)
t3= slave key (date entered)
t4= slave key (date entered)
I am running standard one to many relationships between each primary and slave.

I want to get the grand total of all the no's in t2, t3, and t4

I have tried IIF, Select, and the wizard with no luck can some one help with the correct equation and or code. I need this to complete my final sub report. Thank for you time on this matter please help.

Phil

EggHeadCafe - Software Developer Portal of Choice
Review: XSelerator XSL Editor/Debugger
http://www.eggheadcafe.com/tutorial...d5-7432dfb4d64d/review-xselerator-xsl-ed.aspx
Click to expand...

A UNION query would seem to be the desired result... but it's not at all clear
from your example what field contains the Yes/No values. Your example
specifies that these are dates.

Where are the yes/no fields? How are the tables related?

Do note that a Yes/No field is stored with -1 for Yes, 0 for No; so

=Sum(1 + [yesnofield])

in a Totals query will return the number of NO values.
 
its a medical tracking database it is a one to many relationship using t1 date field as unique identifier. Each child table are set up similarly with demographics (tracking different injuries and illnesses, and pregnancies) and all have the same y/n field I am trying to get a grand total on. Below I will explaing the tables.

T1 = master table the "1" in one to many relationship, date patient was seen

t2 is the illness table and is one of the may in the one to many relationship.

t2 is set up like this
d= demographics, y/n= stats i am tracking, dates, period of time I am tracking

d1, d2, d3, d4, start date (relation ship to master table), end date, y/n1 (one I am trying to track on all three tables. there are more y/n in this table but y/n1 is the one I want to track)

t3 is set up like this
date entered (many relationship to master table), d1, d2, d3, d4, date1, date2, y/n1 ( one I am trying to track)

t4 is set up like this
date entered (many relationship to master table), d1, d2, d3, d4, date1, date2, date3, y/n1 (one I am trying to track)

let me know if this helps out

Phil



John W. Vinson wrote:

A UNION query would seem to be the desired result...
13-Oct-09

A UNION query would seem to be the desired result... but it is not at all clea
from your example what field contains the Yes/No values. Your exampl
specifies that these are dates

Where are the yes/no fields? How are the tables related

Do note that a Yes/No field is stored with -1 for Yes, 0 for No; s

=Sum(1 + [yesnofield]

in a Totals query will return the number of NO values
-

John W. Vinson [MVP]

EggHeadCafe - Software Developer Portal of Choice
Bspline Curves in c#
http://www.eggheadcafe.com/tutorial...5d-8bb9-19ccac9133ab/bspline-curves-in-c.aspx
 
its a medical tracking database it is a one to many relationship using t1 date field as unique identifier. Each child table are set up similarly with demographics (tracking different injuries and illnesses, and pregnancies) and all have the same y/n field I am trying to get a grand total on. Below I will explaing the tables.
Click to expand...

If you have one table for injuries, another table for illnesses, and a third
table for pregnancies you're violating proper relational design principles.

If you have one yes/no field for this characteristic of an injury, another
yes/no field for another characteristic, etc. you're "committing spreadsheet"
- an even worse violation!

What are some of these yes/no fields? What are the d1, d2, d3 and d4 - they
sound like repeating fields?
T1 = master table the "1" in one to many relationship, date patient was seen

t2 is the illness table and is one of the may in the one to many relationship.

t2 is set up like this
d= demographics, y/n= stats i am tracking, dates, period of time I am tracking

d1, d2, d3, d4, start date (relation ship to master table), end date, y/n1 (one I am trying to track on all three tables. there are more y/n in this table but y/n1 is the one I want to track)

t3 is set up like this
date entered (many relationship to master table), d1, d2, d3, d4, date1, date2, y/n1 ( one I am trying to track)

t4 is set up like this
date entered (many relationship to master table), d1, d2, d3, d4, date1, date2, date3, y/n1 (one I am trying to track)

let me know if this helps out
Click to expand...

That said: you can create a query on (say) t3. Select just the start date and
y/n1 fields. Change it to a Totals query; Group By [Start Date] and in a
vacant Field cell type

-Sum([Y/N1])

to count the Yes answers. Yes is stored as -1, No as 0; so if you Sum the
yes/no field and there are six yesses, the sum will be -6. The minus sign
before it will make it a +6.

You can then Join this totals query to your main table. Repeat for the other
tables.
 
Back
Top