RAGEAngel9

I'm working a access thing for work and I can't figure out the correct way to get the data I need out of a table.
I have a table with a bunch of fields including year, yesNo1, yesNo2, yesNo3, and yesNo4. I want to count up all the occurances of each yesNo# in 2005 or later (obviously nothing later yet). I can't figure out how to do this. I can get the individual counts, but not a combination of them. I need the counts to put into a report.

I'm not that great with SQL and no very little about VB.
(I got stuck with this, because we are light on work this week )

Anyway suggestions?

Side question:
As I need to get something resembling a working knowledge of VB, what is a good resource. I find MS.com to be more of a pain, and googling hasn't given me anything comprehensive yet.

Thanks.

zen_tom

For VB, use excel, and the help documentation in Excel (It's got a FULL VB implementation and help files right there)

As for the SQL, you need to do what? Count how many yesses there are within a given year? What if you get more than one yes on any given record/line? Do you count the yessses, or the records? Part of learning and writing sql is stating clearly *exactly* what you want, once you can do that, the SQL kind of follows of its own accord.

If you're counting records, then you could try the following:

Select count(*) from table where
year = 2005 AND
(yesno1 = 'yes' OR yesno2 = 'yes' OR yesno3 = 'yes' OR yesno4 = 'yes')

if you're counting how many yesses there are, then you need to get 4 separate values (SQL primarily deals with records, so your queries have to act at the same level) and add them up - which might be done using a compound satement, or some other technique, but it really depends on your table's structure, and whether it contains a primary key/unique identifier field.

Do the yes/nos contain the words 'yes' and 'no' or the values 0 and 1. If it were the vaules, you could simply add them up i.e.

select sum(yesno1 + yesno2 + yesno3 + yesno4) from table
where year = 2005

Which would be the easiest way of doing it.

I've not tried any of these out properly, but they ought to work out fine.

Bamrak

if you are counting the rows it would look something like:

select a.record_1, b.record_2, etc
from
(select sum(column1) as record_1
from table XXX.XXX
where date >= 01/01/05) a,

(select sum(column2) as record_2
from table XXX.XXX
where date >= 01/01/05) b,

etc

This will give you one SQL output with all the fields totaled