Query To Calculate Grade Using Lookup And Inner Joins
Sep 5, 2013
I have a database with:
2 tables, tblStudentMarks and tblGrades
2 Queries qryResults and qryStudents_Grades
1.qryResults:Calculates the TotalAverageMaths+English+Science)/3.
2.qryStudents_Grades:Calculates the students grades based on the TotalAverage by comparing the total average to the min & max marks on the tblGrades.
Grades are compared within a range specified in tblGrades with the following SQL wich is the query qryStudents_Grades:
Code:
SELECT SM.Student_ID, SM.Students, SM.TotalAverage, SM.Maths, SM.English, SM.Science, GD.Grade
FROM qryResults AS SM INNER JOIN tblGrades AS GD ON (SM.TotalAverage>=GD.MinMarks) AND (SM.TotalAverage<=GD.MaxMarks);
Question: i would like to also calculate the grades of the individual subjects in the same query (qryStudents_Grades), where the marks got by a student in the subject..say maths is compared to the (tblGrades) and grade is displayed in a colum say maths_grades for each student.
How can i do this for all subjects in one query. (qryStudents_Grades)?
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
helloi have had alot of help on these forums already and i thank those that have helped me thus far.but i am having some troubles doing a query.i am trying to get a students grade.but each assesment they do has a differant weighting.for each assesment they are given a percent for it say 80% which may have a weighting of 30.while another assesment they have they get 52% but it has a weighting of 60.and if they missed an assesment it doesnt affect there grade.so what i am asking how can i get there percentage at the end of all the assesment and then turn that into a grade like B+.to better show you what i mean here is the end report i have to make which shows how subjects have weighting.http://i18.photobucket.com/albums/b145/hi2chriz/1stPage.jpghttp://i18.photobucket.com/albums/b145/hi2chriz/2ndpage.jpghope i was clear
I have one table with employees and positions. Each position has a specific rate which is stored in another table (table2). Since the rates of the positions are changing, I want each time the employee get paid with the correct rate.
The problem is that I do not want to lose historic data since I want the rate to calculate the salary. I thought that the best way is to look up for the rate according to the position and the period that is valid.
I have attached herewith a small example with the two table that I want to use the relationship.
Simple problem, but my access and sql skills are very limited.
I have two tables. One containing a group of frequent customers with a column called 'member status'. Another table containing a group of non-customers. The addresses in both tables have been matched using group1 software.
I would like to create a query that shows me all the customers with 'member status' = 'A' and all the non-customers who live at the same address.
When i run the query, everybody comes up as 'member status'=A. I think this is because there is no 'member status' field in the non-customers table, and i have failed to make the appropriate join or parameters. Is there a way to design a query that will show 'member status' for those who have it, and will display a null for those who do not?
I'd like to create a query that shows all the suppliers that are in the Suppliers table as well as the last date of despatch (if any). Thus, this would be the maximum date that relate to that supplier ID. However, despatches that have a quantity of 0 should be excluded.
Currently I have the following, it's working fine but suppliers who doesn't have a corresponding despatch are not being listed. I need the report to list all the suppliers.
SELECT [Suppliers].[fldSupplierFullName], [Suppliers].[ID], Max([Despatches].[DespDate]) AS MaxOfDespDate FROM Despatches LEFT JOIN Suppliers ON [Despatches].[SupplierRef]=[Suppliers].[ID] WHERE ((([Despatches].[QtyLoaded])<>0)) GROUP BY [Suppliers].[fldSupplierFullName], [Suppliers].[ID];
Anyone know any issues that would stop me down grading an access 2007 database. Someone up converted it without me knowing but I want it to be usable for people on older versions.
I went to save as access 2003 and nothing happened. My initial thought was recompile the vba code and do a compact and repair. I did this and still nothing happens. I hit save as access 2003 and nothing happens no error messages or anything (HELPFUL).
I just restructured my DB and I was wondering if anyone can give me some advice on whether or not my joins/relations are correct. I left some joins/relations out because I wasn't sure what relation I should use.
Hi- I have three queries: Queries: Contain: qryPrimary - primary_code, 1Reasons, EnteredDateCount as "CountP" qrySecondary - 2ndary_code, 2Reasons, primary_ref, EnteredDateCount as "CountS" qryTertiary - tertiary_code, 3Reasons, EnteredDateCount as "CountT", 2ndary_ref
In another query to combine data for a report, I need to see: All the "1Reasons" and "CountP"; their corresponding Secondary "2Reasons", "CountS"; and finally the Tertiary "3Reasons", and "CountT" that correspond to the Secondary's.
Like a fool I thought it would be easy. I know it is probably a join problem, but I can't figure it out.
I am having a problem coming up with a design for reporting by grades within a golf recording system. I want to calculate the grade for each player within a query, based on their handicap.
tPlayer contains the fields pName & pHandicap. tGrades currently contains gGradeName, gMaxHandicap.
In a query I want to be able to list by Grades Grades could be A , 10 B , 15 C , 27
or A , 10 B , 14 C , 19 D , 32 or whatever. but the number, name and ranges of Grades may change.
While it may seem odd, I want to be able to change the Grades and requery on the same Players data.
I feel it should be simple but have not been able to come up with either the design or SQL that would enable me to do this cleanly.
Hope this makes sense - any help would be appreciated.
I am creating an access database where the results to an exam will be entered (multiple choice exam so nothing fancy), and in turn they will be graded. I have searched and the closest thing I have come to this is the survey DB with a field for each question. However since this is a exam rather than having "no right answer" this answer needs to be compared with a key. So my question is as follows:
What table structure is ideal for this? ( I have yet to find a way to compare rows )
I have created some queries that are joined on long text fields (80-120 characters). I can save the query in the graphic query design window. But when I reopen the window I get messages that the Joins have been deleted. I cannot find any references to this issue. Does anyone have experience or info on this?
I don't need a lecture about the use of long text fields in Query Joins. And yes I have used Search first.
My query references 2 related tables: one for persons (PERS) and one for telephone/fax numbers and email addresses (CONT, for Contacts). The relevant fields are:
The foreign key come_id refers to a table for contact methods (COME), either "Phone (Home)", "Phone (Work)", "Mobile", "Fax" or "Email".
Now i want to list all persons with their home phone number and email address, also if they don't have one. It seems to be impossible to get it ... I will explain what happens. Lets start simple: first list all persons with their home phone number (come_id = 1): SELECT PERS.pers_forename, PERS.pers_surname, CONT.cont_number AS Phone FROM PERS LEFT JOIN CONT ON PERS.pers_id = CONT.pers_id WHERE (((IIf(IsNull([come_id]),1,[come_id]))=1)); This works fine. The IIf expression is necessary since we are dealing with an outer join: not all persons have a home phone number. If we would simply put "WHERE come_id = 1" then the query produces only the persons that have a home phone number.
But now i also want to see the email address (come_id = 5): SELECT PERS.pers_forename, PERS.pers_surname, CONT.cont_number AS Phone, CONT_1.cont_number AS Email FROM CONT AS CONT_1 RIGHT JOIN (PERS LEFT JOIN CONT ON PERS.pers_id = CONT.pers_id) ON CONT_1.pers_id = PERS.pers_id WHERE (((IIf(IsNull([cont].[come_id]),1,[cont].[come_id]))=1) AND ((IIf(IsNull([cont_1].[come_id]),5,[cont_1].[come_id]))=5)); It seems perfectly logical: i added a second alias CONT_1 for the email address. Since this is also optional we have a second outer join, and the WHERE condition should also use an IIf expression. The result is not correct though: the resulting recordset shows only the persons that have both a home phone number and an email addres or neither! I have a lot of experience with SQL and queries, but i know i am not infallible. Nevertheless i am quite convinced that i should get all the persons: those that have a home phone number or an email address, or both or neither ... I hope that someone of you can explain this.
I am building a select query which is grabbing data from multiple tables with items being linked by a unique field "Certificate_ID".
I have created joins between table A and Table B, and Table A and Table C, linking both by Certificate_ID
All have the join property set to select all Records from table A, but only those from Table B and Table C where the joined fields are equal.
Table A has 5000 records. Am I correct to assume that my query should only return a max of 5000 records as well? When I select Certificate_ID from A and another column from B it only gives me the 5000 unique records. When I add in a column from Table C it is however returning something like 7500 records, with several being duplicates with the same data in every column.
Why it is choosing to duplicate records and give me more than I want. I am sure I am overlooking something simple.
I have a list of PC SN#s in tbl1; I have a list of other PC SN#s in tbl2 I want to display ALL records of tbl1, AND ALL records of tbl2, matching up the SN#s (where there is a match) - but I want to display all records whether they match or not.
My boss and I are in a jam. We have been using Access to run a reporting process, but one of our tables will exceed the maximum fields allotted this month. Our thought, dump the table into SQL Server and use the GUI interface provided in Access Projects.
Unfortunately, the query designer seems to have a few drawbacks. The one that effects us the most is in using UPDATE queries where more than one table is used to determine records to be updated. In attempting it, we get the message: "The designer does not graphically support the Optional FROM clause SQL construct".
Now I know we can manually create Update Queries, but we often need 1,000+ in a short period of time. Manually punching in all the fields involved and other code just isn't timely.
Question: Is there some alternative, service pack or anything else that would allow us to graphically create these Update Queries using Access Projects or even SQL Server 7.0? I would hate to have to scrap all the work we did over something that seems so minor.
I am trying to develop a query in MS Access 2010 to join two tables using three joins one of which is a (between) date range. The tables are contained in Access.
ABCPART links to XYZPART. ABCSERIAL links to XYZSERIAL. ABCDATE links to (between) XYZDATE1 and ZYZDATE2. [ABCTABLE] ABCORDER ABCPART
I need to execute a complex query. I can get the query to work if I break it into two queries, queryA and queryB, and then use queryB inside queryA. Note that queryB (combined query) produces the final results I want.
Here is the code for queryA --------------------------------- SELECT Table_Subgrantees.ID, Table_Subgrantees.SDPISubNumber, Table_Subgrantees.SDPIName1 AS SDPINameSubgrantee, Table_Subgrantees.SDPIName2 AS SDPIName2Subgrantee, Table_FormLogInfoSubgrantees2004.FormRequired AS FormRequiredSubgrantee, Table_FormLogInfoSubgrantees2004.FormRequiredComme nts AS RequiredCommentsSubgrantee FROM Table_Subgrantees INNER JOIN Table_FormLogInfoSubgrantees2004 ON Table_Subgrantees.SDPISubID = Table_FormLogInfoSubgrantees2004.SDPISubID;
Here is the code for queryB -------------------------------- SELECT Table_AreaAbbrs.AreaAbbr, Table_AreaAbbrs.AreaID, Table_Grantees.SDPINShort, Table_Grantees.SDPINumber, Table_FormLogInfoGrantees2004.FormRequired, Table_FormLogInfoGrantees2004.FormRequiredComments , Table_Grantees.SDPIName1, Table_Grantees.SDPIName2, QueryA.SDPISubNumber, QueryA.SDPINameSubgrantee, QueryA.SDPIName2Subgrantee, QueryA.FormRequiredSubgrantee FROM (Table_AreaAbbrs INNER JOIN (Table_Grantees LEFT JOIN QueryA ON Table_Grantees.ID = QueryA.ID) ON Table_AreaAbbrs.AreaID = Table_Grantees.AreaID) INNER JOIN Table_FormLogInfoGrantees2004 ON Table_Grantees.ID = Table_FormLogInfoGrantees2004.ID;
I need to execute this query from ADO inside my code so I would like to come up with a single query that will return the results I want. I have played around with it for quite a while and everything I come up with generates messages like: "Join expression not supported".
Can anyone help me write this query as a single query?
All retailers have at least one distributor BUT a retailer may or may not have ordered any products.
I have created my form but the query linked to the form is having some trouble. It is only selecting those records that have ordered products. For example, if I query a retailer name only and it does not have any ordered products, it will not display. Is there a problem with the table joins? The SQL for the query is displayed here:
I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?
I'm wrestling with the issues; in other threads, it became apparent that because I could not know ahead of time what I will need to know about a given entity, I will use a table to enumerate attributes that is applicable for a given entity.
However, the stumper is that what if an attribute should conform to a set list of values? Since they are dynamic, I would have problem predicting what I will need to be able to lookup, and am even don't know whether I will need a one-many lookup or many-many lookup.
I thought that generic lookup table with a table listing "classes" of lookup would allow me to have one big generic lookup table while using "classes" to act like virtual tables so I can then set the query to appropriate "class" to return just right set of values.
But as I thought about it, I ran into some issues which is pulling me toward the crazy idea that I should have freestanding tables, and use a field in tblAttribute to give me the table's name so I'd know which free-standing table it points to, and have the necessary key to lookup the values within that table.
Even though my gut instincts tell me that I shouldn't be going against the conventions of database design (who the frick goes around creating free-standing lookups?!?), I'm simply not sure how I can use a generic lookup table to hold all information.
For example, suppose I was given a list of values that has its own categories. Since the former design allows only for two level (lookup and lookupclass), where am I to insert that extra level?
Furthermore, I found myself needing a set of virtual keys to reference a certain "class" of lookups for report purposes. That means I need an extra field in my lookup table than I originally anticipates. What if I find myself needing one more field that just won't fit the generic lookup table?
So does anyone have suggestions on how we would create a placeholder for a lookup table that will be made just in time?
I am creating a database for a hyperthetical car hire company.
I have a field with a lookup/query. The data that this query searchs for is entered into my table/form already. Is there anyway of avoiding repeating this data twice?
I have three large source tables imported into my database. I have created queries to retrieve relevant values from fields in each source table which feeds into my form. Each field on my form that is connected to the relevant query is a lookup field. For example, one field called "Supplier_Name" another called "Supplier_Code" and a third called "Route_Number".
Needless to say each of my lookup fields are very long. I am trying to filter my search based upon the selection from the previous Lookup field. How I can filter a lookup field's value based upon the previous lookup field selection? Each Supplier has a code and assign route(s) and I have already established these relationships.
I have read the Microsoft article at http://support.microsoft.com/kb/q95918/ several times. However, I am looking for an expression I can place in the field name to calcluate the median of another field in the same query. Is this possible? If not, I don't understand how to call the function created in the Microsoft article.
Example (fields in the query) Expr1 Expr2:Median(Expr1)
