Reports :: Attendance Sheet - How To Make Crosstab Query
Mar 15, 2013
I am making some attendance sheet on access as follows
Name !09:00!10:00!11:00!12:00!13:00!14:00!15:00
------!-----!------!-----!------!-----!------!
Test1 !Prsnt! ! ! ! ! !
test2 !Absnt! ! ! ! ! !
test3 !Vction! ! ! ! ! !
I have tables like
workersmaster(workerid,workername,joiningdate,stat us)
Workerdetail (Workerid,workername,entreetime,attendance)
timing(timing(0900to1500)
Now I wanted to make report as mentioned above, how can i do this?
View Replies
ADVERTISEMENT
Mar 26, 2013
I made a report with following crosstab query.
TRANSFORM Workersdetail.workername AS CountOfedate
SELECT Workersdetail.[attendance], Count(Workersdetail.[edate]) AS [Total Of edate]
FROM Workersdetail
GROUP BY Workersdetail.[Workername], Workersdetail.[attendance], Workersdetail.[workerhourenter]
PIVOT site+Cstr([workerhourenter])
I wanted to know that, is there any option through which my report gets autoupdate or refresh incase of addition in SITE field (as mentioned with PIVOT)?
View 1 Replies
View Related
Dec 18, 2005
Hi there thanks for all your help on the last problem got it working now. What I need now is 2 crosstab queries to become one, example below on how I want it to look:
Created By-----Total Of Date-----Total Of Date Taken-----Policy Name Type (eg Breakdown,Caravan going across counting Date and Date qouted in separate columns)
Here is the SQL for each crosstab query:
TRANSFORM Count([Date]) AS [The Value]
SELECT [Created By], Count([Date]) AS [Total Of Date]
FROM qryPresonalReport
GROUP BY [Created By]
PIVOT [Policy Name Type];
TRANSFORM Count(qryPresonalReport.[Date Taken]) AS [The Value]
SELECT qryPresonalReport.[Created By], Count(qryPresonalReport.[Date Taken]) AS [Total Of Date Taken]
FROM qryPresonalReport
GROUP BY qryPresonalReport.[Created By]
PIVOT qryPresonalReport.[Policy Name Type];
Cheers
Headintheclouds
View 1 Replies
View Related
Aug 24, 2007
I want to make a table from the results of a crosstab query and am struggling to find a way. Is this possible?
Any help gratefully received!
View 9 Replies
View Related
Mar 20, 2006
I am trying to make a class attendance form, where user chooses classType
ex:"English, French" then the program shows all of the students from tblStudents that are enregistered in that class. And some how make it so that user could select on or many users in the same class and press add button which will add the students into attendance table for the current class and the current day,
**Allso what I would like to do if possible after the form above works, is to if the student has been allready added for the current day, the check box becomes un editable with appropriable comment in the comments line for that student or for examples the student registered for 12 classes, the attendance keeps track of the classes and when the student tryed to go to 13'th class the check box is uneditable and in the comments line for that student metions the reases.
View 6 Replies
View Related
Sep 17, 2013
I am trying to make a crosstab query to filter my records from my table.
Here is the scenario.
I want to make a query that will return me my Rep ID, Rep Name, his Bonus and his GV-Q (another value) based on every month.
Now I make a crosstab query and here is the syntax.
Code:
TRANSFORM First([TBL Qualification Data India].[Bonus Rank]) AS [FirstOfBonus Rank]
SELECT [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name]
FROM [TBL Qualification Data India]
GROUP BY [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name]
PIVOT [TBL Qualification Data India].Period;
This resulted in a column for Rep Number, one column for Rep Name and columns for all the period of Bonus I am going to have., so there are basically 9 columns for this till this month for each month and bonus value shows as values for all these month (period) columns.
Now in this same syntax, I want to have my Rep GV-Q value as well as his bonus to show in the same query, I read and came to know that it's not possible to directly have two values or two column headings in a crosstab query, I must have to make a new crosstab query and then use a normal select query to display records from these two crosstab queries, so I went ahead and made a new similar but with one value field changed crosstab query and here is the syntax for that.
Code:
TRANSFORM First([TBL Qualification Data India].[GV-Q]) AS [FirstOfGV-Q]
SELECT [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name]
FROM [TBL Qualification Data India]
GROUP BY [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name]
PIVOT [TBL Qualification Data India].Period;
Now after this how to make a select query to show the data from these two queries.
I can make a normal query based on these two crosstab queries and manually add all fields and then I would have my result but then after every month I have to manually enter these two extra month details from both crosstab queries to my final query and that's not what I want.
Is there any method to do this by gathering data from these two queries into one and achieve the result I want or if there is any other approach to tackle this.
To explain my database and my need for output, I am attaching few pics to make things easier if I made some mistakes in explaining my problem. It's included in attached zip since I am not able to post images or links.
View 5 Replies
View Related
Apr 6, 2006
I have searched and searched for a thread before posting this, there are some discussions on related issues, but I could not find a solution. This is my problem:
I have a table with Incident_Type (a total of 6 selections from a combo-box), Incident_date, Incident_SubArea(25 selections from a combo-box), a check box for IsStaff_reported and all these are recorded by Incident_ID.
I have created a monthly report based on a parameter query that prompts for start and end dates using a custom form for incidents.
Now I need parameter (again start and end dates) cross-tab queries for number of incidents (i.e. count of Incident_ID) that are 1. IsStaff_reported, and 2. Not IsStaff_reported. to show up in a single report.
I dont know if that is too much to ask for or is there a simple way to accomplish this. Any help is appreciated.
View 1 Replies
View Related
Apr 19, 2015
I've got a self updating crosstab query, its essentially a monthly summary and every month a new column is added (one corresponding to the current month, i.e., next month the new column will be may, following that the new one will be june, etc)
I've designed a report to be based on this query and i tested it out by manually adding data for next month into a table, the query auto updated however the report remained the same (ended in april instead of adding a new column for may).
Just curious if there is a way to automatically add these new columns to the report every month or will i have to do so manually?
View 1 Replies
View Related
Jul 9, 2013
i have an excel data for assets and i have imported it into access 2010 but i want to be able to do the ffg;
1. want to be able to generate reports like how many computers does a particular branch have.
2. i want to be able to sum the no of each field heading per branch
View 1 Replies
View Related
Feb 9, 2015
I receive attendance data in Excel with two columns, StaffCode and DateTime, from a biometric machine.
Each staff member clocks in in the morning and clocks out in the evening.
Optionally, they also clock out for lunch and back in after lunch.
Each StaffCode may therefore have 2 or 4 entries per day of attendance.
Staff details (name and department) are specified in a related table.
I wish to design two reports in Access as follows:
1. Daily Attendance Report (Filtered by current date or custom parameter)
Department (1st Group level)
StaffName ClockedIn LunchOut LunchIn ClockedOut
2. Weekly/Monthly Attendance Report (filtered by current week/month or custom parameter)
Department (1st Group level)
Date (2nd Group level)
StaffName ClockedIn LunchOut LunchIn ClockedOut
View 1 Replies
View Related
Aug 29, 2013
I have a table which has the fields: Agentname,SaleDate,Branch,Sales, Percentage, Comission, Corrections, rent and Total
Total = Sales-comission+Corrections+rent
This table has the name of the agent that made the sale, the branch(location) where he made it, the date, the commission of how much he gets from the sale, Corrections which is various correction that need to be made like a refund and or bonuses, Rent which he pays and the total.
what i want to do is to get access to make me a sheet( a sales invoice) where i can see the sales for a specific agent for a specific branch. agents can work at multiple branches.
so lets say agent A on branch A sold 400 on 1/8, 300 on 2/8 and 500 on 5/8 i want to get a list of all the days from 1/8 till 31/8 and the records of 1/8, 2/8 and 5/8 automatically attached to the correct dates and the rest of the dates should have a value of 0.
View 4 Replies
View Related
Apr 3, 2014
I have an attendance database and I connect the time attendance machine db to my access db, what i am trying to do is to generate a report that shows the time in and time out for specific date. the type of attendance db is date/time.
Please see the attached screenshot db from attendance machine.
an also some time there is duplicate entry, I need to get the first and the last entry only for specific date.
View 4 Replies
View Related
Apr 11, 2008
Hey,
for some days i've a problem with an Access-Query and up to now i didn't succeed to find an answer, i'm not even sure if there is one.
So, how to explain my problem.
I'm trying to compile a Database for my small department to organize our computer-short-course
students (application, grades, attendance...)
And with the grades i have a problem as well as with the attendance. Up to now we are just using paper sheets for grades and attendance. So i just thought that it would be the easiest thing to create a similiar-looking form, drawing of planned Query-output-layout is attached.
The attendance should be set with a checkbox. New days are set with a small subform.
For the attendance i've this table-layout:
tblAttendance
aSID
aCID
aDate
aPresent
When a new day is set, every student gets a new record at the tblAttendance, which shall be compiled together for the later Attendance Form.
So now the big question comes, is it possible to create
a query that fits my needs (variable amount of days and students and the possibility to set my records)? It is important that i've a big sheet with all the days, students of a course
at the same time.
Any suggestions - i'm completly lost.
Thanks in advance,
protos
View 2 Replies
View Related
Sep 21, 2007
Hi all, I am utterly unsure if what I want to do is even possible:
I have two crosstab queries, qryRewCOCredit and qryWrapCOCredit which show the changeover (CO) times for the specified machine when they are NOT zero. (all zero entries don't show up).
There are many cases when there is a CO for the Rewinder on a specific day, but not for the Wrapper, and vice versa.
I want to make another crosstab query which performs a calculation. To keep it simple:
If (RewCOCredit>WrapCOCredit) Then
5-RewCOCredit
Else 'WrapCOCredit>RewCOCredit
5-WrapCOCredit
Please help!!!
View 2 Replies
View Related
Jun 7, 2004
I have 12 months' worth of data that the user wants displayed on a crosstab report. I have no problem creating the crosstab query or subsequent report. However, the user wants to be able to select their own 12 month time period. So, my crosstab is based upon a make table query, that will allow the user to enter parameters.
Now, my problem is that these 12 month column headers/data will change as the user enters different date ranges. How do I write the code that will allow me to pass these variables to the report? I'm using 97.
Thanks!
View 14 Replies
View Related
May 26, 2006
hi,
i'd like some initial help with how best to set-up my tables for a database i need to create. if anyone could give me a synopsis of what i need to do it would be great and then i can try and piece it together.
here are the requirements;
one student can attend many different courses.
each course runs for 10 weeks.
i'd like to be able to have forms to;
- add a student.
- assign classes to a student.
- record absence via a combo box for each student for each week
i'd also like to have a report mechanism that can record if a student is absent for 3 or more classes.
as i said, some pointers or examples would be great as i am new to this. i pressume i need;
a table for students
studentid
firstname
lastname
a table for the courses
courseid
coursename
a table for attendance
attendanceid
courseid
studentid
present/absent (combobox)
week
Am most stuck on the date part. the data may not be entered every week so i can't really use a date function. instead i need a combobox or something to be able to select the week.
cheers
mark
View 2 Replies
View Related
Nov 13, 2014
I have a crosstab Query as the source for my report, of course the issue is the column headings on the report. I have Purchasers as a row heading, Year as a row heading, Month/Year as a row heading, Meter as a column heading, PaidMCF as Value and, a total as a Row heading. My issue is feeding the column headings on my report with the meter names.
Purchaser 1 has 23 meters attached
Purchaser 2 has 1 meter attached
Purchaser 3 has 6 meters attached
Purchaser 4 has 2 meters attached
Purchaser 5 has 16 meters attached
Purchaser 6 has 11 meters attached.
View 4 Replies
View Related
Feb 22, 2015
I am playing with a report. Crosstab report works, but I would like to print check mark if value is "X". Other values are "A" and "E" and they need to remain as they are.
View 4 Replies
View Related
Mar 21, 2015
I've made a crosstab query and would like to use it to create a subreport. In the column headers I have names of courses. Courses can be added or removed. How can I make a crosstab report with dynamic columns?
PHP Code:
TRANSFORM Count(tblCourses.CourseName) AS CountOfCourseName
SELECT tblNmscStaff.NmscStaffFirstName, tblNmscStaff.NmscStaffLastName,
tblNmscStaff.PtOrFtNtl, tblNmscStaff.Ntl, tblNmscStaff.NmscID
FROM tblNmscStaff LEFT JOIN (tblCourses RIGHT JOIN [tblNmscStaff/CoursesPointer] ON
tblCourses.CourseID = [tblNmscStaff/CoursesPointer].CourseID) ON
tblNmscStaff.NmscStaffID = [tblNmscStaff/CoursesPointer].NmscStaffID
GROUP BY tblNmscStaff.NmscStaffFirstName, tblNmscStaff.NmscStaffLastName,
tblNmscStaff.PtOrFtNtl, tblNmscStaff.Ntl, tblNmscStaff.NmscID
PIVOT tblCourses.CourseName;
View 1 Replies
View Related
Nov 3, 2014
-if the result of a test is more than the normal value , i want up marked .
-if the result of a test is less than the normal value , i want down marked .
-if the result in the limits of the normal value , no marked.
View 5 Replies
View Related
Jan 24, 2012
What I am trying to do is EXPORT a query to an Excel Workbook which is read only and I need the data to land in a specific sheet in the excel workbook.
I have tried transferspreadsheet vbcode etc. but nothing works quite right and the fact the spreadsheet is Read Only does not work.
The Name of the Query is "Accrual"
The Name and location of the Excel Spreadsheet is "c:register.xls"
The Sheet inside the workbook is called "data".
I need the data to land in the "data" sheet over the top of the previous data and not create a new sheet or mess with the formulas that are linked to the "data" sheet.
View 2 Replies
View Related
Feb 27, 2015
What I am trying to do is populate the specific fields on excel sheet based on access query. so say I have 3 separate fields Name, Job, Salary on excel sheet (already designed excel sheet). and my access query has all 3 fields pulled up for all employees. Now if a person selects his "Name" on access form person should be able to see prepopulated excel sheet with his name, job and salary. I know I have to go recordset path.
View 1 Replies
View Related
Jul 5, 2006
I am using 97 and have been running into problems with tutorials from microsoft since they are in 2000 format. Its not a big difference so maybe I just cant figure it out...
Anyways, I wanted to make a list of the reports I had for the user to click on one and have it pop up. Anyone know how I can make this list?
My idea is to somehow create the names in the list box using a query or something and have them be links to the report somehow...
Please help...Thanks :)
View 14 Replies
View Related
Dec 24, 2014
I am selling some products, namely A to M. I record the orders of costumers in a table, named "orders". For example, if costumer "Jack" has ordered as the following:
Code:
ID costumer product quantity unitprice orderdate
12 jack A 4 10 25/11/2014
13 jack B 5 5 25/11/2014
14 jack C 1 4 25/11/2014
15 jack F 3 2 25/11/2014
16 Jack G 4 8 25/11/2014
17 Jack H 1 7 25/11/2014
Then I make a report based on the above table and issue that as the invoice to the costumer, who is jack here.
How can I make a unique invoice number, which automatically increases sequentially for each invoice? Do I need to add a new column to the table? I also need to be able to look for a specific invoice based on the number, or date.
View 11 Replies
View Related
Oct 26, 2011
I have oracle ODBC Dirver which easily connect and fetch data to a new excel sheet according to the query.
But I want to fetch data to a access table from a remote server database which will automatically update the data, through the query saved.
Excel is working good with automatic update option but can't fetch data to access table.
View 2 Replies
View Related
May 13, 2014
I am having a small issue with creating my report, that being I am actually trying to create two different reports in one. I first need to report how many of an item is located in each location.
Then under that I need to list the serialized items of each item in each location. I can run them both just fine separately in the detail section of the report but I can't seem to get them to both run at the same time. I would just put them together so it reads
Location
Part number
Serial number
Serial number
Serial number
But my boss told me that's too much detail in on place at one time for the customer.
Is it possible to add a second detail section to a report, or something that is equivalent and do the same thing?
View 5 Replies
View Related