Tables :: Creating Expression On A Report
Mar 4, 2013
I have a database of students that have taken tests. That database includes how many attempts it took them to pass. I have successfully generated reports that sort the exams by type, date, student, etc. I would like to also somehow insert a formula to show the pass rate of those exams on the top of the report. I know I can get into design view and build an expression, but how would I build an expression that would create a pass rate percentage based on whether or not a student passed an exam on the first attempt.
I have a DB with a column titled "Attempt" and the options "First, Second, Third, Fourth". I would like the expression to generate a percentage of students that have "First" in their box (as opposed to anything else) out of the total number of student records.
We need to track student pass rates in my school for a re-certification process.
View Replies
ADVERTISEMENT
May 18, 2006
Hi all,
I am a complete newbie so please beware/be patient.
What I am trying to do is to create a report from two table, using the wizard it starts complaining about the relationships between the two tables, though I only want to produce the table on one report.
Please help.
Thanks,
The Gurkentopf
View 1 Replies
View Related
Jun 2, 2006
Could you please help me out.
I am trying to develop a DB that is multi user. i ahve not split the data from the forms into separate db's as the users are in different locations and our network can be very slow.
part of the requirerment is to produce a drawing approval form, my current sticking point is that the method i am currently using to create the forms only allows one person at a time to use the process.
currently i have a command button that runs a query and then creates a table from the results. the table is then used as the data source for a mail merged form in word.
what i ultimatly need is a process which allows multiple users to generate these forms at the same time and have the ability to save the form (with no code behind it) as a word document (either RTF etc)
i am thinking that if i create a query on the fly in VB and then use the results to use in a report this would allow me to do the above.....
So my questions are:
1) is my assumption correct
2) If so, how do i do it as i have never done it before
3) If the above is not the best solution/ does not work... what will/is
View 4 Replies
View Related
Apr 8, 2006
I one of my tables, I have a date field (DateCompleted) with the format: mm/dd/yyyy
Now, I want to create a query which would create another field (DateExpected) by using the date in the above table and adding 5 months to the date.
The only issue is that for the new field in the query I want it just to have mm/yyyy format.
For example, if the original date in the table is 04/05/2006, after running the query, I would have a new field 09/2006.
Does anyone know the exact expression I would put in the query? Thanks. The expression would be put in the "build" area.
Thanks for your help!!
View 2 Replies
View Related
Jan 20, 2005
I created the following expression on a report, under the details, in a text box:
=nz(pathjbo50,0)/nz(pathjbo50,0)+nz(pathcol50,0)+nz(pathpp50,0)+nz( pathlm50,0)
what I want is to find out what percentage pathjbo50 is of the sum of the other 4 fields.
but I keep getting an "Error" message. How do I get this percentage?
Where is the best place to find literature on writting expressions?
View 2 Replies
View Related
Aug 10, 2012
I want a Text Box Query on my form to display the Status, Workshop, Time, Enrolled and Limit. The problem is these values come from two different tables and the Enrolled value comes from a single field that contains the different workshops.
What I mean is: In Table[Attendees] a row contains a customer's Number, First Name, Last Name, Workshop and Phone Number. The workshops vary for each customer so one row on the table could have John Doe attending Cover Letter Writing and the next row could have John Smith attending Resume Writing. What I want is to be able to count the different workshops within the Field[Workshop] and total them and then display the total in a Text Box Query. I have a Text Box Query set up displaying Status, Workshop, Time and Limit as these values all come from Table[Workshops].
So basically I need to Query to also display a result that is the Total for each workshop from Table[Attendees] and display the total for each workshop in a Query with data from Table[Workshops].
Here is a link to an Example Database [URL] ....
I'm trying to avoid putting things on different reports and the like because the people using this are basically computer illiterate and if they have to click a button (no matter how well labeled) they won't do it and the information might as well not exist.
And if there's a better way to do it, I'm all ears. The only thing is, I have to update these workshops month by month. Since they are dynamic, I want to avoid creating separate tables for each workshop.
View 14 Replies
View Related
Dec 18, 2014
I am using expression builder to specify a field in a report but it is acting more like a filter.So I have a report based on a query. However I want to add a field that is not in the query but is in a related table - called tblAgent.
So using expression builder I select the tblAgent in Expression Elements and then select the field from this table. This creates the expression =[Agent]![AgentAddress] however when I try to run the report it asks for a Agent parameter? Do I need to go back to reports 101?
View 1 Replies
View Related
Jun 12, 2014
I am trying to create an expression in a query to sum only the # of hours a student attended between two date fields. I do not want to use a parameter because each student has different start and midpoint dates and I need to see all of them in one list.These are the fields I'm using in the query:
Student Name
Start Date
Midpoint Date
Hours
It keeps giving me "0" or if I move the () around it says the correct syntax is [NOT]
Expr1: Sum([Hours]) between [Start Date] & [Midpoint]))
View 14 Replies
View Related
Oct 19, 2004
I'm trying to set a field in the heading of a report with a expression that gives me a percentage of personnel who have been trained against personnel who training is expired or is null. I'm gettin errors or it will show as-1. I'm new at this and having a hard time with working the expressions. Any help will be appriciated.
thank you
Del Re
View 3 Replies
View Related
Jun 27, 2005
Hi, I want to insert an Expression in a text box of a report using VB.
This is what I wrote in the code builder section of report header.
Me.totalBox.Value = strTotalExp
Me is the header section of the report.
totalBox is the textbox.
strTotalExp is the expression that I want to enter, it is a string.
How should I do this?
Thanks.
View 12 Replies
View Related
Jun 8, 2015
I have an Access application that print a report with 7 sub-reports in it. When I preview the report, it looks fine. When I print it directly to the printer, it looks fine. The problem is when I print from the preview, Access error with a "...too complex..." error and crashes the application.
View 8 Replies
View Related
Nov 4, 2013
I've now since moved everything into one table but i need formatting of my calculated field (I'm new to all this).
Below are the 3 expressions working independently of one another, I just need them combined in to one if possible. As I am getting an "invalid syntax" error or "too many arguments" when I try to get it working
PHP Code:
0.5*IIf([Type]="Offlist" And [APP_LINE_OFFA]>3,1,0)+0.2*IIf([Type]="Offlist" And [APP_LINE_OFFB]>2,1,0)+
IIf([Type]="Offlist",Abs([EP_DETAILS_OFF]*0.15+[REF_DETAILS_OFF]*0.15),0)Â
PHP Code:
IIf([Type]="Onlist",Abs([EP_DETAILS_ON]*0.15+[REF_DETAILS_ON]*0.2+[GEN_NOTE_ON]*0.2+[APP_LINE_ON]*0.25+[SPEC_INS_ON]*0.2),0)Â
PHP Code:
IIf([Type]="Aged Report", Abs([EP_DETAILS_AGED]*0.2+[REF_DETAILS_AGED]*0.2+[GEN_NOTES_AGED]*0.3+[SPEC_INS_AGED]*0.3),0)Â
View 4 Replies
View Related
Oct 19, 2004
Sorry about the simple question, but my brain is not functioning today. How do you create a report from a simple query?
View 2 Replies
View Related
Aug 23, 2004
Lets say I have table with the following information:
OrderNo. ProductId Count1 Count2
01 AA 2 0
01 AB 0 1
01 AC 1 0
01 AD 0 3
02 AD 0 2
02 AE 4 0
02 AB 0 1
.
.
.
A count in Count1 means 0 in Count2 and vice-versa
How do I create a report that looks like this:
OrdeeNo. SumCount1/SumCount2
01 (3) / (4)
02 (4) / (1)
.
.
---------------------------
Totals (7) / (5)
Thank you
View 1 Replies
View Related
May 17, 2007
AFternoon,
I have never used Access before and i have been asked to create a report that displays the cartridge name and the quantity. This part was easy. But, the problem i am having is that i have a field in the database called "DoNotOrder". If the cartidge has the box ticked in the field "DoNotOrder", I do not want it to be displyed in the report. I was wondering if anybody could give me a clue into writing a statement to do such a thing.
ANy help is much appreciated.
Thanks for your time
Ollie
View 1 Replies
View Related
Jul 28, 2014
I create e field with calculation expression and I need to round the results. I insert the following expression:
Round(IIf([A]="N";0;(IIf([B]>0;([B]-[C]-[D]-[E]-[F]+[G]);([H]-[C]-[D]-[E]-[F]+[G]))));2)
I have back the following error message: Could not find field 'Round'.
I do not understand why, because Round is not a field, but a function.
View 6 Replies
View Related
Dec 11, 2012
I created two tables, but i don't know what kind of relationship i should create.
In the first table, i would like to put all different tests (medical tests, such as EMG test, and so on), in the second table, i want to add the settingup for each test, say, recording site, stimulating site et.al, then i want to use one form to populate data into these two tables, what should i do?
View 6 Replies
View Related
Sep 6, 2004
I've been reading up quite intensively on using QueryDef's collection to try to pass a Parameter to a Query in order to then Open a Report. But can't seem to get it to work. I've been trying to write an SQL statement that the VB will accept in code but never got it to work. Depite copy/pasting the SQL from the Query Access creates. regardless.. I really need some help with this one. Thanks. Procedure follows. Get error that Report not found... The name is correct... I just want to pass the integer (index) from the ListBox into the Query then Open the Report... Thats all...
Private Sub previewReport_Click()
On Error GoTo Err_previewReport_Click
Dim intSelectMonth As Integer 'The Index of the Item Selected in Form List Box
Dim db As Database
Dim smReport As Report
Dim smQuery As QueryDef
Dim month As Parameter
Set db = CurrentDb
Set smQuery = QueryDef![GetReviewsMonthQuery]
smQuery.Parameters![rvMonth] = 9 'Using 9 as September just for Testing
Set smReport = Reports![Review Schedule by Month/Analyst]
DoCmd.OpenReport smReport, acPreview
Exit_previewReport_Click:
Exit Sub
Err_previewReport_Click:
MsgBox Err.Description
Resume Exit_previewReport_Click
End Sub
View 2 Replies
View Related
Dec 6, 2004
I have a form with a subform in it and I would like to print the contents of that form to a report. I am able to print now, using a "print" command button, but it only prints one record, just the one that is being displayed. I want to be able to print a report with all the records that are in the filtered result.
Also, when the press the "print" button I would like to give them an option "Print this record?" "Print All records?". depending on the input, it would print the report.
Can someone please help me with this? Much appreicated.
Thanks.
ronnie
View 1 Replies
View Related
Jul 18, 2005
Hello,
I can run a query but when I try to display this information on a report I get the following message:
The specified field “tblMachineData.TagName’ could refer to more than one table listed in the FROM clause of your SQL Statement.
Here is my SQL code from the quey.
SELECT tblMachineData.TagName, [tblMachineFloat Query].DateAndTime, tblMachineRunTag.TagName, tblMachineFloat.Val
FROM tblMachineRunTag INNER JOIN (tblMachineData INNER JOIN ([tblMachineFloat Query] INNER JOIN tblMachineFloat ON [tblMachineFloat Query].DateAndTime = tblMachineFloat.DateAndTime) ON tblMachineData.TagIndex = [tblMachineFloat Query].TagIndex) ON tblMachineRunTag.TagIndex = tblMachineFloat.TagIndex
WHERE (((tblMachineFloat.TagIndex)=0 Or (tblMachineFloat.TagIndex)=1 Or (tblMachineFloat.TagIndex)=3 Or (tblMachineFloat.TagIndex)=4))
ORDER BY [tblMachineFloat Query].DateAndTime DESC;
Any help is appreciated,
Bob
View 2 Replies
View Related
May 20, 2013
I am quite new to access 2007 but trying to create an IIF statement in a report but don't know where to start.
My report has lots of 'Label' fields with standard text based on a query for the fields components. I want one of my labels to only appear on the report for print IF a record field called 'Program' (which is from a list) has the word 'RHICHOP' at the beginning of it. If RHICHOP is not in the beginning of the Program then leave out the label text.
View 4 Replies
View Related
May 21, 2014
I have a table, it is fairly large but pretty simple.
It lists store numbers in the first column. Each column that follows is a SKU, and in the field it either has a 1 or a 0 to represent 1 if it is stocked in the store, and 0 if it is not.
Can I write a report that will show what each store has, and how do I do that? I basically want it to return the sku number (and its description) if it has a one in the box, but not if it doesn't.
View 5 Replies
View Related
Nov 9, 2014
I am quite new to MS access and started to develop a small database for my practice. I have made four tables that are for Patient details, their doctor detail, their problem and their treatment. I have successfully created form to enter the data.However I an unable to proceed, now I want to create a discharge letter which will be addressed to the doctor (based on the tables described above)
I want to make this in a way that if I enter the patient details i-e date of birth, ID and / or name a letter is created based on the tables above. I also want this letter to be saved for future use. how to design that letter and should it be a query or report.
View 6 Replies
View Related
Jun 9, 2015
I couldn't find a better way to organize my data for an ongoing history project and so am trying my hand at Access.
So, I've got an Access 2000 database built, with 3 main tables - have relationships set up and which look to be working fine (two of the tables each link to one field in the third, main, table).
What I want to do now, is to be able to have a combo box - whether on my existing form (or likely a separate one), that I can just pick the town I want, then the database would return all the records in the main table for that town, and put that into a report.
View 5 Replies
View Related
Oct 1, 2015
I have a form that allows users to input data into a field called "Checked out". I was wondering how I can create a report that shows the forms (I have about a couple thousand) only with the "checked out" field filled in.
View 7 Replies
View Related
Jul 31, 2014
Currently I have a calculated field in my table; however, under certain circumstances I need to change the value to another value which is not related to the calculation. I understand that the calculated fields are read only (why is that?), so I was wondering if there was any way to change values within a calculated column without actually changing the expression itself.
View 1 Replies
View Related
