Sorry, I've been looking at this so long I assumed everyone knows what
it does. I very much appreciate your help.
Member_tbl is:
[ID Number][Last Name][First Name][Rank ID][Date of Rank]
[ID Number] is a unique Autonumber for each member of our school and is
the key field. (I'm not using the [Rank ID][Date of Rank] fields
yet...I thought I'd tackle the hard query first.)
Attendance_tbl is created by user data entry. Every class, the student
or instructor looks up their name on a pull down, chooses whether they
are a student or the instructor, and adds a record to this table:
[Training Rec No][ID Number][Date][Mem Type]
Training Rec No ID Number Date Mem Type
28 1 1/1/2006 1
40 1 1/2/2006 1
59 1 1/3/2006 1
71 1 1/4/2006 1
83 1 1/5/2006 1
95 1 1/6/2006 1
[Training Rec No] is also an Autonumber and serves as key for the
table.
The query takes the Attendance_tbl and turns it sideways. The output
looks like:
[Year]...[Month]...[Name]...[Total]...[1]...[2]...[3]......[31]
2006...1...Jose Cuervo...30...S...I...S...S...S...etc.
2006...2...Jose Cuervo...12...""...S...S...""...I...etc.
....
2006...12...Jose Cuervo...14...S...""...""...I...etc.
Mem Type 1 is S for Student and 2 is I for Instructor. The [1], [2],
[3], etc. are days of the week.
The problem is that it is possible to enter twice on the same day by
accident, or if the member takes 2 classes in one day. I need the
query to ignore the duplicate Dates and only have "Total" equal to the
number of days attended (not the number of times an entry was made).
What I end up with, using my sample data, is 32 days in March and
December, for example.
Here is the query again after some other tweaks.
PARAMETERS [Forms]![Attendance_Entry_frm]![ID Number] IEEEDouble;
TRANSFORM First(IIf(Attendance_tbl![Mem Type]=1,"S","I")) AS Type
SELECT Year([Date]) AS [Year], Month([Date]) AS [Month], [First Name] &
" " & [Last Name] AS Name, Count(Attendance_tbl.[Date]) AS Total
FROM Member_List_tbl INNER JOIN Attendance_tbl ON Member_List_tbl.[ID
Number]=Attendance_tbl.[ID Number]
WHERE (((Member_List_tbl![ID Number])=Forms.Attendance_Entry_frm![ID
Number]) And ((DatePart("yyyy",[Date]))=Year(Date())))
GROUP BY Year([Date]), Month([Date]), [First Name] & " " & [Last Name]
PIVOT Day([Date]) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);
KARL said:
You had a problem with a crosstab query. So I responded to it.
You never posted your data so I do not know what you are talking about.
Post samples of your data and example of what you would like the results to
look like.
ShadesOfGrey said:
Using Count vs. Sum in your change gets me closer, but it's still
double-counting the days where there are two classes attended. I've
tried both DISTINCT and DISTINCTROW in the SELECT clause, but neither
eliminated the problem. Fixing this will also help my error-trapping.
It would ignore duplicates from people accidentally entering their
attendance twice.
I thought about creating a separate query of the source table with only
the Name and Date information, then setting that query to display
unique values. Then I could join this query with the Transform query
Thanks Karl, that's almost it...however, there are non-unique values in
the table. This is an attendance tracker for classes at a martial arts
school. We have morning and evening classes, but if you go to both, it
only counts as one training day (yeah, we're not too happy about that,
and it makes this harder, to boot). If you instruct it still counts as
a day, so as long as the dates are discrete, it doesn't matter if
you're an S or an I. Can you filter for uniques in this mess?
KARL DEWEY wrote:
Try this --
TRANSFORM First((IIf(Attendance_tbl![Mem Type]=1,"S","I"))) AS Type
SELECT Year([Date]) AS [Year], Month([Date]) AS [Month], [First Name] & " "
& [Last Name] AS Name, Sum(Attendance_tbl.[Mem Type]) AS Total
FROM Attendance_tbl INNER JOIN Member_List_tbl ON Attendance_tbl.[ID Number]
= Member_List_tbl.[ID Number]
WHERE (((Member_List_tbl.[Last Name])=[Enter last name]) AND
((Member_List_tbl.[First Name])=[Enter first name]) AND
((Year([Date]))=[Enter year]))
GROUP BY Year([Date]), Month([Date]), [First Name] & " " & [Last Name]
PIVOT Day([Date]) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);
:
First, let me say that this query isn't mine. Second, it does almost
everything I want it to do. What it outputs is a horizontal columnar
calendar from a vertical table with the structure:
<RecNo><ID Number><Date><Mem Type>
The output is:
<Year><Month><Name><1><2>...<31>
2006 1 [Name] S I ... S
2006 2 [Name] S S ... S
....
What I need is a column after <31> with the total number of "S" in the
<1>...<31> pivot. Here is the query. I intend to eventually pass the
name and year from a form, but this works right now.
PARAMETERS [Enter Name] Text ( 255 ), [Enter Year] IEEEDouble;
TRANSFORM First(IIf(Attendance_tbl![Mem Type]=1,"S","I")) AS Type
SELECT Year([Date]) AS [Year], Month([Date]) AS [Month], [First Name] &
" " & [Last Name] AS Name
FROM Member_List_tbl INNER JOIN Attendance_tbl ON Member_List_tbl.[ID
Number] = Attendance_tbl.[ID Number]
WHERE ((([First Name] & " " & [Last Name])=[Enter Name]) AND
((DatePart("yyyy",[Date]))=[Enter Year]))
GROUP BY Year([Date]), Month([Date]), [First Name] & " " & [Last Name]
PIVOT Day([Date]) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);
Does anyone have a suggestion?
