By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,795 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

counting assemblies per year

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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******@wilcoxon.com> wrote in message
news:e2**************************@posting.google.c om...
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

Nov 13 '05 #2

P: n/a
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******@pcdatasheet.com
www.pcdatasheet.com
"Wayne B. Stanley" <ws******@wilcoxon.com> wrote in message
news:e2**************************@posting.google.c om...
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

Nov 13 '05 #3

P: n/a
"Wayne B. Stanley" <ws******@wilcoxon.com> wrote in message
news:e2**************************@posting.google.c om...
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.AssemblyNo) 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







Nov 13 '05 #4

P: n/a
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.

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.