Horrible Mapping Problem

[ShadesOfGrey]

Hi, new to the group, not to Access...but it's been awhile since I've
done much with it. I am creating a database that tracks student and
instructor attendance. I have two tables, one for student information
and one for the attendance records. I want to create a report that
takes the attendance record with fields Record Number, Student ID,
Date, and Type (student or instructor) and outputs it as a tabular
calendar showing "S" for Student and "I" for instructor (each person
can be either, but not both on the same day).

Example:

Student <name>
Year <year>

......1.2.3.4.5.6.7...31
Jan..S...S....I
Feb.......S...S
....
Dec....I..S...S

My problem is, how do I map a table such as:

<RecNo><Name><Date><Type>
RecNo1...Bob Robertson...12/1/2006...1
RecNo2...Bob Robertson...12/2/2006...2
RecNo3...Tim Franklin....12/1/2006...1
....
etc.

into the above tabular calendar report?

I would prefer that the calendar be a form or a report, but I'll settle
for a query.

Any help would be appreciated!

Stan

[Gord]

ShadesOfGrey wrote:

Hi, new to the group, not to Access...but it's been awhile since I've
done much with it. I am creating a database that tracks student and
instructor attendance. I have two tables, one for student information
and one for the attendance records. I want to create a report that
takes the attendance record with fields Record Number, Student ID,
Date, and Type (student or instructor) and outputs it as a tabular
calendar showing "S" for Student and "I" for instructor (each person
can be either, but not both on the same day).

Example:

Student <name>
Year <year>

.....1.2.3.4.5.6.7...31
Jan..S...S....I
Feb.......S...S
...
Dec....I..S...S

My problem is, how do I map a table such as:

<RecNo><Name><Date><Type>
RecNo1...Bob Robertson...12/1/2006...1
RecNo2...Bob Robertson...12/2/2006...2
RecNo3...Tim Franklin....12/1/2006...1
...
etc.

into the above tabular calendar report?

I would prefer that the calendar be a form or a report, but I'll settle
for a query.

Well, since the form or report will likely be based on a query, we'll
start with that. Create a new query. Cancel out of the "Show Table"
dialog. Switch to SQL view and paste in the following, replacing the
"SELECT;" that is already there. Adjust the table and column names to
match yours. It should give you something close to what you seek.

TRANSFORM First(IIf([Attendance_Type]=1,"S","I")) AS Type
SELECT Year([Attendance_Date]) AS [Year], Month([Attendance_Date]) AS
[Month], Students.Name
FROM Attendance INNER JOIN Students ON Attendance.Student_ID =
Students.Student_ID
GROUP BY Year([Attendance_Date]), Month([Attendance_Date]),
Students.Name
PIVOT Day([Attendance_Date]) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2 0,21,22,23,24,25,26,27,28,29,30,31);

[ShadesOfGrey]

Thank you for the tip, but I'm no SQL person. This is your query
written with my table and field names:

TRANSFORM First(IIf([Mem Type]=1,"S","I")) AS Type
SELECT [First Name] & " " & [Last Name] AS Name, Year([Date]) AS
[Year], Month([Date]) AS [Month]
FROM Attendance_tbl INNER JOIN Member_List_tbl ON Attendance_tbl.[ID
Number] = Member_List_tbl.[ID Number]
GROUP BY Year([Date]), Month([Date]), Name
PIVOT Day([Date]) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2 0,21,22,23,24,25,26,27,28,29,30,31);

However, it doesn't like my "Name" concatenation for some reason, even
though the exact same syntax is in another query's SQL statement (I
looked there for example syntax), which works perfectly (except for not
doing what I need).

Any thoughts?
Gord wrote:

ShadesOfGrey wrote:

Hi, new to the group, not to Access...but it's been awhile since I've
done much with it. I am creating a database that tracks student and
instructor attendance. I have two tables, one for student information
and one for the attendance records. I want to create a report that
takes the attendance record with fields Record Number, Student ID,
Date, and Type (student or instructor) and outputs it as a tabular
calendar showing "S" for Student and "I" for instructor (each person
can be either, but not both on the same day).

Example:

Student <name>
Year <year>

.....1.2.3.4.5.6.7...31
Jan..S...S....I
Feb.......S...S
...
Dec....I..S...S

My problem is, how do I map a table such as:

<RecNo><Name><Date><Type>
RecNo1...Bob Robertson...12/1/2006...1
RecNo2...Bob Robertson...12/2/2006...2
RecNo3...Tim Franklin....12/1/2006...1
...
etc.

into the above tabular calendar report?

I would prefer that the calendar be a form or a report, but I'll settle
for a query.

Well, since the form or report will likely be based on a query, we'll
start with that. Create a new query. Cancel out of the "Show Table"
dialog. Switch to SQL view and paste in the following, replacing the
"SELECT;" that is already there. Adjust the table and column names to
match yours. It should give you something close to what you seek.

TRANSFORM First(IIf([Attendance_Type]=1,"S","I")) AS Type
SELECT Year([Attendance_Date]) AS [Year], Month([Attendance_Date]) AS
[Month], Students.Name
FROM Attendance INNER JOIN Students ON Attendance.Student_ID =
Students.Student_ID
GROUP BY Year([Attendance_Date]), Month([Attendance_Date]),
Students.Name
PIVOT Day([Attendance_Date]) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2 0,21,22,23,24,25,26,27,28,29,30,31);

[ShadesOfGrey]

Immediately after posting, I saw a part of what I was doing
incorrectly.

I have [First Name] and [Last Name] as separate fields in
Member_List_tbl but I want the query to accept First&Last as a
parameter along with the year. So, if Year = [Enter Year] is 2006,
only Jan - Dec 2006 will output. And if Name = [Enter Name] is "FName
LName" only records for "FName LName" will appear.

This query ALMOST does what I need.

TRANSFORM First(IIf([Mem Type]=1,"S","I")) AS Type
SELECT Year([Date]) AS [Year], Month([Date]) AS [Month], Name AS Expr1
FROM Member_List_tbl INNER JOIN Attendance_tbl ON Member_List_tbl.[ID
Number] = Attendance_tbl.[ID Number]
GROUP BY Year([Date]), Month([Date]), Name
PIVOT Day([Date]) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2 0,21,22,23,24,25,26,27,28,29,30,31);


ShadesOfGrey wrote:

Thank you for the tip, but I'm no SQL person. This is your query
written with my table and field names:

TRANSFORM First(IIf([Mem Type]=1,"S","I")) AS Type
SELECT [First Name] & " " & [Last Name] AS Name, Year([Date]) AS
[Year], Month([Date]) AS [Month]
FROM Attendance_tbl INNER JOIN Member_List_tbl ON Attendance_tbl.[ID
Number] = Member_List_tbl.[ID Number]
GROUP BY Year([Date]), Month([Date]), Name
PIVOT Day([Date]) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2 0,21,22,23,24,25,26,27,28,29,30,31);

However, it doesn't like my "Name" concatenation for some reason, even
though the exact same syntax is in another query's SQL statement (I
looked there for example syntax), which works perfectly (except for not
doing what I need).

Any thoughts?
Gord wrote:

ShadesOfGrey wrote:

Hi, new to the group, not to Access...but it's been awhile since I've
done much with it. I am creating a database that tracks student and
instructor attendance. I have two tables, one for student information
and one for the attendance records. I want to create a report that
takes the attendance record with fields Record Number, Student ID,
Date, and Type (student or instructor) and outputs it as a tabular
calendar showing "S" for Student and "I" for instructor (each person
can be either, but not both on the same day).
>
Example:
>
Student <name>
Year <year>
>
.....1.2.3.4.5.6.7...31
Jan..S...S....I
Feb.......S...S
...
Dec....I..S...S
>
My problem is, how do I map a table such as:
>
<RecNo><Name><Date><Type>
RecNo1...Bob Robertson...12/1/2006...1
RecNo2...Bob Robertson...12/2/2006...2
RecNo3...Tim Franklin....12/1/2006...1
...
etc.
>
into the above tabular calendar report?
>
I would prefer that the calendar be a form or a report, but I'll settle
for a query.

Well, since the form or report will likely be based on a query, we'll
start with that. Create a new query. Cancel out of the "Show Table"
dialog. Switch to SQL view and paste in the following, replacing the
"SELECT;" that is already there. Adjust the table and column names to
match yours. It should give you something close to what you seek.

TRANSFORM First(IIf([Attendance_Type]=1,"S","I")) AS Type
SELECT Year([Attendance_Date]) AS [Year], Month([Attendance_Date]) AS
[Month], Students.Name
FROM Attendance INNER JOIN Students ON Attendance.Student_ID =
Students.Student_ID
GROUP BY Year([Attendance_Date]), Month([Attendance_Date]),
Students.Name
PIVOT Day([Attendance_Date]) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2 0,21,22,23,24,25,26,27,28,29,30,31);

[Gord]

ShadesOfGrey wrote:

Thank you for the tip, but I'm no SQL person. This is your query
written with my table and field names:

TRANSFORM First(IIf([Mem Type]=1,"S","I")) AS Type
SELECT [First Name] & " " & [Last Name] AS Name, Year([Date]) AS
[Year], Month([Date]) AS [Month]
FROM Attendance_tbl INNER JOIN Member_List_tbl ON Attendance_tbl.[ID
Number] = Member_List_tbl.[ID Number]
GROUP BY Year([Date]), Month([Date]), Name
PIVOT Day([Date]) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2 0,21,22,23,24,25,26,27,28,29,30,31);

However, it doesn't like my "Name" concatenation for some reason, even
though the exact same syntax is in another query's SQL statement (I
looked there for example syntax), which works perfectly (except for not
doing what I need).

Any thoughts?

Try this:

TRANSFORM First(IIf([Mem Type]=1,"S","I")) AS Type
SELECT Year([Date]) AS [Year], Month([Date]) AS [Month], [First Name] &
" " & [Last Name] AS Name
FROM Attendance_tbl INNER JOIN Member_List_tbl ON Attendance_tbl.[ID
Number] = Member_List_tbl.[ID Number]
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,2 0,21,22,23,24,25,26,27,28,29,30,31);

Gord wrote:

ShadesOfGrey wrote:

Hi, new to the group, not to Access...but it's been awhile since I've
done much with it. I am creating a database that tracks student and
instructor attendance. I have two tables, one for student information
and one for the attendance records. I want to create a report that
takes the attendance record with fields Record Number, Student ID,
Date, and Type (student or instructor) and outputs it as a tabular
calendar showing "S" for Student and "I" for instructor (each person
can be either, but not both on the same day).
>
Example:
>
Student <name>
Year <year>
>
.....1.2.3.4.5.6.7...31
Jan..S...S....I
Feb.......S...S
...
Dec....I..S...S
>
My problem is, how do I map a table such as:
>
<RecNo><Name><Date><Type>
RecNo1...Bob Robertson...12/1/2006...1
RecNo2...Bob Robertson...12/2/2006...2
RecNo3...Tim Franklin....12/1/2006...1
...
etc.
>
into the above tabular calendar report?
>
I would prefer that the calendar be a form or a report, but I'll settle
for a query.

Well, since the form or report will likely be based on a query, we'll
start with that. Create a new query. Cancel out of the "Show Table"
dialog. Switch to SQL view and paste in the following, replacing the
"SELECT;" that is already there. Adjust the table and column names to
match yours. It should give you something close to what you seek.

TRANSFORM First(IIf([Attendance_Type]=1,"S","I")) AS Type
SELECT Year([Attendance_Date]) AS [Year], Month([Attendance_Date]) AS
[Month], Students.Name
FROM Attendance INNER JOIN Students ON Attendance.Student_ID =
Students.Student_ID
GROUP BY Year([Attendance_Date]), Month([Attendance_Date]),
Students.Name
PIVOT Day([Attendance_Date]) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2 0,21,22,23,24,25,26,27,28,29,30,31);

[ShadesOfGrey]

This modification to your query does the trick!

PARAMETERS [Enter Name] Text ( 255 ), [Enter Year] IEEEDouble;
TRANSFORM First(IIf([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,2 0,21,22,23,24,25,26,27,28,29,30,31);

Now to figure out how to pass the parameters from a form (my entry form
is supposed to pop up this query after it processes). It can't be too
hard. :)

Thanks a ton for your help!
Gord wrote:

ShadesOfGrey wrote:

Thank you for the tip, but I'm no SQL person. This is your query
written with my table and field names:

TRANSFORM First(IIf([Mem Type]=1,"S","I")) AS Type
SELECT [First Name] & " " & [Last Name] AS Name, Year([Date]) AS
[Year], Month([Date]) AS [Month]
FROM Attendance_tbl INNER JOIN Member_List_tbl ON Attendance_tbl.[ID
Number] = Member_List_tbl.[ID Number]
GROUP BY Year([Date]), Month([Date]), Name
PIVOT Day([Date]) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2 0,21,22,23,24,25,26,27,28,29,30,31);

However, it doesn't like my "Name" concatenation for some reason, even
though the exact same syntax is in another query's SQL statement (I
looked there for example syntax), which works perfectly (except for not
doing what I need).

Any thoughts?

Try this:

TRANSFORM First(IIf([Mem Type]=1,"S","I")) AS Type
SELECT Year([Date]) AS [Year], Month([Date]) AS [Month], [First Name] &
" " & [Last Name] AS Name
FROM Attendance_tbl INNER JOIN Member_List_tbl ON Attendance_tbl.[ID
Number] = Member_List_tbl.[ID Number]
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,2 0,21,22,23,24,25,26,27,28,29,30,31);

Gord wrote:

ShadesOfGrey wrote:
Hi, new to the group, not to Access...but it's been awhile since I've
done much with it. I am creating a database that tracks student and
instructor attendance. I have two tables, one for student information
and one for the attendance records. I want to create a report that
takes the attendance record with fields Record Number, Student ID,
Date, and Type (student or instructor) and outputs it as a tabular
calendar showing "S" for Student and "I" for instructor (each person
can be either, but not both on the same day).

Example:

Student <name>
Year <year>

.....1.2.3.4.5.6.7...31
Jan..S...S....I
Feb.......S...S
...
Dec....I..S...S

My problem is, how do I map a table such as:

<RecNo><Name><Date><Type>
RecNo1...Bob Robertson...12/1/2006...1
RecNo2...Bob Robertson...12/2/2006...2
RecNo3...Tim Franklin....12/1/2006...1
...
etc.

into the above tabular calendar report?

I would prefer that the calendar be a form or a report, but I'll settle
for a query.
>
Well, since the form or report will likely be based on a query, we'll
start with that. Create a new query. Cancel out of the "Show Table"
dialog. Switch to SQL view and paste in the following, replacing the
"SELECT;" that is already there. Adjust the table and column names to
match yours. It should give you something close to what you seek.
>
TRANSFORM First(IIf([Attendance_Type]=1,"S","I")) AS Type
SELECT Year([Attendance_Date]) AS [Year], Month([Attendance_Date]) AS
[Month], Students.Name
FROM Attendance INNER JOIN Students ON Attendance.Student_ID =
Students.Student_ID
GROUP BY Year([Attendance_Date]), Month([Attendance_Date]),
Students.Name
PIVOT Day([Attendance_Date]) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2 0,21,22,23,24,25,26,27,28,29,30,31);