counting assemblies per year

[Wayne B. Stanley]

I have a database with records dating back to the year 2001
I want to know how many of each Assembly was tested per year
I have fields :
TestDate
AssemblyNo
I am new to access but was able to get the count to work with just one year
(if I query for that year first).
how do I get access to count for each year

[Douglas J. Steele]

SELECT Year(TestDate) As TestYear, AssemblyNo, Count(*)
FROM MyTable
GROUP BY Year(TestDate), AssemblyNo

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Wayne B. Stanley" <ws******@wilco xon.com> wrote in message
news:e2******** *************** ***@posting.goo gle.com...

I have a database with records dating back to the year 2001
I want to know how many of each Assembly was tested per year
I have fields :
TestDate
AssemblyNo
I am new to access but was able to get the count to work with just one year (if I query for that year first).
how do I get access to count for each year

[PC Datasheet]

Create a query based on the table that contains TestDate and Assemblyno. In the
first column of the query enter this expression:
YearOfTestDate: Year([TestDate])
Pull down AssemblyNo into the second column.

Click on the Sigma button on the toolbar at the top of the screen, (Looks like a
capital E). This makes your query a totals query. Go down under AssemblyNo and
use the dropdown list to change GroupBy to Count. When you run the totals query
you will get the total number of Assemblies for each year.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdata sheet.com
www.pcdatasheet.com
"Wayne B. Stanley" <ws******@wilco xon.com> wrote in message
news:e2******** *************** ***@posting.goo gle.com...

I have a database with records dating back to the year 2001
I want to know how many of each Assembly was tested per year
I have fields :
TestDate
AssemblyNo
I am new to access but was able to get the count to work with just one year
(if I query for that year first).
how do I get access to count for each year

[John Winterbottom]

"Wayne B. Stanley" <ws******@wilco xon.com> wrote in message
news:e2******** *************** ***@posting.goo gle.com...

I have a database with records dating back to the year 2001
I want to know how many of each Assembly was tested per year
I have fields :
TestDate
AssemblyNo
I am new to access but was able to get the count to work with just one year (if I query for that year first).
how do I get access to count for each year

Start by creating a table to hold the years:

create table calendar
(
yr int not null primary key,
yrStart datetime not null,
yrEnd datetime not null
)

Fill the table with all the years you will even need, e.g. for 2001

insert into calendar(yr, yrStart, yrEnd)
values (2001, #01/01/2001#, #01/01/2002#)

Then this should give what you're looking for:

select c.yr, count(a.Assembl yNo) as assemblies
from calendar as c
left outer join assemblies as a on c.yrStart <= a.TestDate
and a.TestDate < c.yrEnd
group by c.yr
order by c.yr

[Salad]

Wayne B. Stanley wrote:

I have a database with records dating back to the year 2001
I want to know how many of each Assembly was tested per year
I have fields :
TestDate
AssemblyNo
I am new to access but was able to get the count to work with just one year
(if I query for that year first).
how do I get access to count for each year

I'm going to assume not all of the items were tested. In this case I
would want to get a count of those tested. If a date is null, IsDate is
False = 0. If the date is filled in, IsDate is True = -1 and Abs
converts it to a positive.

I usually do something like this. I create an expression by dragging
TestDate to a column in the query builder.
WasTested : Abs(IsDate([TestDate]))

Now I drag down TestDate and get the Year/
YearTested : Year([TestDate])

I make this a Totals query and in the Totals row keep YearTested to
GroupBy and select Sum for WasTested.

You could also drag down TestDate to a third column and set the Total
row to Count to get the total number of records. Then you have a value
to get the total records and the total Tested and with those values you
can get the count of records not tested.