I have a bunch of student test scores. The test is scored out of 100. I want to COUNT the scores (ie I want to know how many kids scored 50 etc...). This is easy enough except the crosstab query will be very wide ie 100 columns.
I cant seem to reduce the size by grouping them into ranges (ie count the number of kids that scored between 50 and 60 in a cross tab. I've tried variations with select queries but without luck.
Maybe there is a way to code it.. and then show it on a report??
my issue is i have multilble text box in my form & based on change in one of this text box i need the code to compare between data in the form & table & returm Msg if it is not matching. attached screen FYI.
i look in the internet but i could not figuer out the VBA code since i do not know VBA. what comes to my mind to to use select case.
I have an access DB with games scores recorded in it. I want to get these values out into an ASP page. The basics are easy, but if a user has submitted more than 1 score, I want to extract just their highest result. This is my code:
Code: SELECT email, MAX(totalScore) AS highScores, playername, level01, level02, level03, level04, level05, lives, buildBonus FROM tbl_xmas2004 GROUP BY email
But Dreamweaver doesn't like this! Any thoughts? I have also tried creating Queries in Access and using these in Dreamweaver - however, Dreamweaver also refuses to use Joins? Or perhaps that's my code?
Code: "SELECT tbl_xmas2004.playername, tbl_xmas2004.email, tbl_xmas2004.level01, tbl_xmas2004.level02, tbl_xmas2004.level03, tbl_xmas2004.level04, tbl_xmas2004.level05, tbl_xmas2004.lives, tbl_xmas2004.buildBonus, 2004MaxJoin.MaxOftotalScore FROM tbl_xmas2004 INNER JOIN 2004MaxJoin ON tbl_xmas2004.email=2004MaxJoin.email"
I am trying to figure out how to count the number of Yes's and No's on one record to produce a score. There are 39 total questions and each question gives the user a choice of Yes, No, or N/A. Each record will need to have a score attached to it. I appreciate all the help I can get.
and my question is,how to calculate the student table's grade value? i want the result like below.. #studentID score Grade Sminth 88 A samantha 76 B Williamv 45 F martell 78 B
I have an issue sorting the results on my Access 2010 Report. Here are the details:
- Report Record Source = "Portfolio Ranking" - Portfolio Ranking is the name of a query - The query includes a calculated field called "Impact Score" - FYI: this is a web database (not sure if that limits what I can do)
I want to sort the report on the Impact Score column. However, when I view the query calculation for Impact Score, the "Sort" field says "(not sorted)" and it's greyed out, so I can't select Descending as desired.
I run various queries on a daily basis for multiple date ranges (previous day, week to date, month to date, and year to date). The below criteria allows me to get WTD numbers but it has a flaw. When I run the reports on Monday, this criteria gives me Sunday and Monday's data when I actually want the previous week's data. Does anyone know of an iif statement that basically, says, "If today is Monday, give me last week's data, else give me wtd of the current week?" Right now, I manually change the criteria on Monday morning to hard code the dates and then revert to the formula on Tuesdays but I need to have this completly automated because there are a large number of queries and I don't have time to change them all. Thanks for the help!
I received help on previous threads. Basically, I have built a query to group by course title and then select the number of students that were assigned a course during a specifc time frame. Is there a way to show multiple columns in the query. For example my query now shows, enrollment for March, I would like to column next to it to show enrollment for Feb and then another column for Jan.
This is my current sql view.
PHP Code: SELECT tblContent.Title, Count(*) AS [Students Assigned] FROM tblProfile INNER JOIN tblContent ON tblProfile.LoginName = tblContent.LoginName WHERE (((tblProfile.LastName)<>"Demo" And (tblProfile.LastName)<>"Care" And (tblProfile.LastName)<>"Robins") AND ((tblContent.Code) Like "NH*") AND ((tblProfile.Groups) Not Like "*Professional Development Center*" And (tblProfile.Groups)<>"All Users,Professional Development Center") AND ((tblProfile.Delete)<>Yes) AND ((tblContent.Type)="Learning Track")) GROUP BY tblContent.Title, tblContent.[Date Assigned] HAVING (((tblContent.[Date Assigned])>=#3/01/2004# And (tblContent.[Date Assigned])<=#3/31/2004#));
I have a table with employee names and there start time and stop time for their shift. There is also a start time and stop time which they cant work during their shift. So I have shiftstarttime and shiftstoptime and codestarttime and codestop time. I have to create a table with the employee names and generate a time called outboundtime. This outboundtime starts at a specified time (e.g. 9am) and runs for a specified time (60mins). This outboundtime has to be within the range of the employees shift and cant be within the range of the cant work time. If it was, then it would start after the codestoptime for that employee.
I have been trying "if then statements" but I can't seem to compare a range of time to another range of time. Only a specified time to a range which is making things confusing.
Am posting this message (also on reports forum) as i dont know whether the problem is at report or query level. Hope you can help.
Hello all, this is probably simple but im new to all this!
I am trying to produce reports that show details for only the past year, at present my reports are bringing up details of all the previous years also. Is there any way you can put conditions on reports ( or should it be in a query) that tells the report to only include dates i.e. today - 365 days. At present i have to change the dates every day both a start and end date. Is this possible?
I am attempting to create a parameter query to show a specific range of dates of service for my customers. Example: I want to see data from my table from Jan. 1st, 2006 to Feb. 15th, 2006 for any customers serviced in that time frame. In the query I have under my Date field, I have the following in the criteria field: Between [Input Start Date] And [Input End Date]. When I go to the while in the design mode of creating the query on the main menu under Query then to parameters and get my Query Parameters what should be put into the Parameter column and the data type columns and will this allow the user to input any start date and end date and get the information from the master table?
I also need to create a parameter query that will allow viewing of only each months table data. Example: Jan. 1st, 2006 through Jan. 31st, 2006.
I must be having a bad day, as I can't get something really simple to work. I have a query, that amoungst other things, I want to select all records between two dates. For instance, the table has a 'ScheduledDate' field, and I have set up two parameters in the query that prompt for 'From' and 'To' parameters.
In design view, I have tried the following in the 'criteria' box for the 'ScheduledDate' field;
>=[From] <=[To]
This doesn't work ~ can anyone point me in the right direction. I've searched the forum, perhaps not using the correct keywords?
I've read alot of good things on here regarding the DateDiff function and some other things that have helped me out finding the difference between two dates within the same entry on a table. I have a question regarding finding the difference in days between two entries within the same table.
Example:
test table:
Date ID # Name 1/18/07 1234567 Smith 1/20/07 1234567 Smith 1/23/07 1234567 Smith
Is there a way to find the difference in days between the lowest and highest dates? I want to know how many days are between the first entry and the last entry? In this cae it would be five days. I can do it if the days are in the same entry with the datediff function, but having trouble finding it in this situation. Any pointers?
(Thanks for all the good information on this site, BTW. I've seen a few posts similar to this, but it didn't really answer my question)
I have a form that is similar to a post card. The fields are linked to a master table that contains all the customer data such as dates of service, street address, PO Box, City, State and zip. What I need to do is have a method to pull into the post card form a range of dates, so the form would have only January customers in it. I have parameter query that pulls ranges but it comes up in table form. I need the range to come into the fields on the post card so I can then print just the month of January.
Do I need to create a button on the form for the ranges and if so how do I get it to pull just one month's customer data into the fields on the post card form or can this be done?
I am trying to put together a quick database for my HR team. I need to be able to pull multiple date ranges to show which employees are up for an annual, 6 month and 90 day review.
Is it even possible to put multiple date ranges in one query, or is this going to get complicated? I have no real database background, and know absolutely no SQL so I'm flyin by the seat of my pants here...
I am pulling some information, and I am needing a report to show the value "score" if the field is numeric, but if it is not numeric, retain it's original value. for instance if the field is 1234 it needs to show on the report as "score" but if it is MSP it needs to say MSP.
I am trying to import data from an Excel file to MS Access (2013).
The Excel sheet consists of 700 columns. A group of 7 columns (always same header) are to be implement in Access one among the other.
That means:
At first columns A-G, than columns H-N, than columns O-U
I am trying to solve this with a loop (as you can see in the code)
But, if I start the modul I get this error:
"Runtime error 1004. The method Worksheets for _Global object failed"
But the module works, of I start it a second time. The error message appears only at the first start.
Apart from that, the code works. Sometimes, the module imported empty rows into the Access table.
Code: Sub ExcelImport() Dim xlApp As Excel.Application Set xlApp = New Excel.Application xlApp.Workbooks.Open FileName:="C:Users...DesktopBeispiel.xlsx"
My goal is that the user can choose start date and end date use the calendar implement in the userform, and then the VBA will automatically select the data range from access and updated into the defined worksheet in Excel. I managed to connect to access, just don't know how to implement the selection with the two date(the start date and end date).
Code: Const strDb As String = "C:Documents and SettingsYuCMy DocumentsDatabase1.accdb" Dim rs As ADODB.Recordset Dim cn As ADODB.Connection Dim strdate As String Dim endate As String
How do I count the number of records with values within specified ranges?
My database contains a field with values ranging from say 1 to 1000. I need to write a query to show count the number of records with values in ranges I specify in the query.
I'm looking for output such as
<100 - 50 records 101 - 500 - 30 records 501 -1000 - 50 records
I have not found the solution to this looking elsewhere, but basically what I am trying to do is the following:
person date 1 1/1/2012 1 12/15/2012 2 6/12/2012 1 6/13/2012 2 6/14/2012 3 6/16/2012
I want a query which shows the count or records for which a person has a date within 30 days of each other. This is being queried from a large table in which records would may not be sequential by person. Thus in my example above, the third record for person 1 would be omitted as well as the record for person 3.
I want to be able to merge the two records together if the field chr matches between the two files plus if there is an overlap between the start number and end number from each file. For instance the first record from each file would match because the range from 1000 to 2000 of file 1 has numbers consisting of 500 numbers (1500-2000) that are also present and overlap in file 2 (1500-3000). I possibly cannot use < or > since the ranges from each file will vary to different degrees. Perhaps there is a between function that might work...
