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><D ate><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
5  1327 
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><D ate><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.Stud ent_ID =
Students.Studen t_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,20,21,22,23 ,24,25,26,27,28 ,29,30,31);
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,20,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><D ate><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.Stud ent_ID =
Students.Studen t_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,20,21,22,23 ,24,25,26,27,28 ,29,30,31);
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,20,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,20,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><D ate><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.Stud ent_ID =
Students.Studen t_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,20,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,20,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,20,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><D ate><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.Stud ent_ID =
Students.Studen t_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,20,21,22,23 ,24,25,26,27,28 ,29,30,31);
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("yyy y",[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);
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,20,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,20,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><D ate><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.Stud ent_ID =
Students.Studen t_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,20,21,22,23 ,24,25,26,27,28 ,29,30,31);
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Ian Turner |
last post by:
Hi,
In an attempt to get every request for a particular virtual directory to go
through ASP.NET I've been trying to add a wildcard application mapping. The
scenario is that any url for that VD, whether it resolves to an actual file
or not, can be intercepted by an HttpModule and ultimately redirected (using
RewritePath) to a resource of my choosing.
I was hoping that just adding the wildcard mapping to the IIS VD would work.
But it...
|
by: Danny Miller |
last post by:
Hi there,
I'm facing a problem that is driving me nuts. The scenario is as follows:
1) Create an empty directory structure e.g. C:\Dev1\TestWebApp
2) Map a virtual directory e.g. TestWebApp to this new directory
3) Under C:\Dev1 create a blank visual studio solution and add a new web
project called TestWebApp (which will be created in C:\Dev1\TestWebApp and
|
by: Michael Tissington |
last post by:
I'm trying to add some extensions to IIS on the properties, home directory,
config screen.
I must be missing something because the OK button is always disabled ...
Any ideas please ?
--
Michael Tissington
http://www.oaklodge.com
|
by: none |
last post by:
Hi,
I'm trying to establish table mappings, and I've hit a snag.
At the point to where I try to fill the schema (DB_adapter.FillSchema),
I get an exception, and the message is as follows:
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Invalid column name 'Unique_ref'.
Invalid column name 'ID_string'.
Invalid column name 'Sequence'.
|
by: Thomas |
last post by:
in .net 1.1 we successfully use a HttpModule to catch 404 / 403.1 html
errors.
after migrating to .net 2.0, this modules is broken in a very, very strange
way.
we have defined a wildcard applicatio mapping to the .net 2.0 isapi dll:
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_isapi.dll
with "check if file exists" disabled.
the HttpModule "RedirectMissing" is called upon every request to the
| | |
by: =?Utf-8?B?RGF2ZQ==?= |
last post by:
I am actually trying to get the UNC path of the main module of a process
running from a mapped drive, and I am trying to do this from a service. The
ProcessModule class only provides the full path with the mapped drive letter,
but the drive letter refers to the drive mapping for the user that started
the process, and cannot be resolved to a UNC path from a service running
under LocalSystem.
Presumably, therefore, I need to get the drive...
|
by: marcosalvadeo |
last post by:
I have a problem.
I created a dataset in visual studio 2005 which use tables of SQL
Server Mobile.
When I create insert query in the relative TableAdapter, the dataset
designer maps the type numeric of SQL Server Mobile as "Object" and
not "Decimal".
The result of this is that when the debug starts there's an exception
because the mapping "Object" and "Numeric" is wrong.
To solve this problem I must change manually, in the dataset...
|
by: alan |
last post by:
Hello world, I'm wondering if it's possible to implement some sort of
class/object that can perform mapping from class types to strings?
I will know the class type at compile time, like so:
const char *s = string_mapper<thetype>();
However I do not know the string to be associated with the type at
compile time, and will need a way to set up the mapping, to be created
at run time, possibly like so:
void foo(char*...
|
by: tenxian |
last post by:
Could you put up with the horrible PHP code?
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
