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.
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
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 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 )
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
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)?
I now understand that when opening and saving crosstab queries Access (2010) runs that query to ascertain the column names. Unless you hard-code them. Running the query takes at least 20 minutes. I have hard-coded where I can, but one report takes arbitrary dates so I can't hard-code them.
I believe that turning off AutoCorrect might make a difference to whether the query runs - but I don't want to turn it off.
I have a sub form in DataSheet view and I would like to lock the design so that the User should not change the layout
Even when I set the property of the Datasheet “Allow Design Changes: Design View Only” users are able to unhide the hidden columns and they can also change the size of the column by dragging the column end line
Does any one know how to lock the design of datasheet ( I am using this sub form datasheet for data entry but do not want users to change the layout)
I'm going to make up names and values -- I'm interested in the structure.
Table ALPHA:
COLA DAT1 DAT2 DAT3 1 5 7 9 2 4 14 8
Table BETA:
COLA_IND DAT1 DAT2 DATN 1 a b c
Table CHARLIE:
COLA_IND DAT1 DAT2 2 d e
Table DELTA:
COLA_IND DAT1 DAT2 2 f g
Ok, the idea here is that the data in table ALPHA contains data with COLA a key such that selecting 2 would yield the data row "4, 14, 8."
Now, COLA_IND is a "COLA" key for table ALPHA (sorry, I can never remember which side is called the foreign key). So, from tables BETA, CHARLIE and DELTA, I can access any row in ALPHA based on the key "COLA_IND"
Here's the fun part. When I build my query, it wants to use an inner join on the keys from all these tables... In order words:
SELECT blah blah blah INNER JOIN blah ON (ALPHA.COLA=BETA.COLA_IND) AND (ALPHA.COLA=CHARLIE.COLA_IND) AND (ALPHA.COLA=DELTA.COLA_IND)
What I'm looking to do is expand BETA, CHARLIE and DELTA with the information from ALPHA based on the key COLA_IND. I don't think this is doing what I want.
I've got this problem and im 95% sure its going to need a query in order to achieve this answer im looking for.
I'm creating a Software Licensing Management db and its all working lovely. However my only problem remains is the graphical representation (text box within one of the forms) of howmany licenses are/aren't(doesnt matter if this number is a +/- number) available.
In order to achieve this answer I dont think you'l need the table structure of any of my tables other than these two:
However I would like to add a column to either of these tables named 'Availability' or something similar which will show the licenses available.
I've tried a number of Update/Append queries but all have failed. I want this Available running variable held within the table due to it not changing to much of the current db design as i baisically finished the project and they asked for it! any ideas of how to efficiently achieve this will be much appreciated!
I'm looking for a keyboard shortcut to expand the columns in "design view" of a query.
What I mean by this is rather than selecting all of the columns and double clicking to see the entire text, I'd like to be able to a shortcut.
The entire process as I see it involves 3 steps so I will need thesolution to the 3rd step.
(1) [ctrl+spacebar] to select initial column (2) [shift+arrows] to select all of the columns I need (3) [keyboard shortcut] will expand all of the columns "field" names to the size of the column heading
Alternatively, if you know of a shortcut that will expand the columns without having to select them first I'll take it!!
i found myself trying to write this outer join query and i realised that probably i need to improve my design rather than write the query! so here goes
i've created a form which a user can navigate through to look at various jobs which have been done (its a loft insulation database)
now one of the things the users need to do is to arbitarily choose jobs for which they are going to phone up the customer and check the work was completed satisfactorily for QA purposes
the table containing information about the jobs has a yes/no field "selectedForQA" which comes up as a checkbox on the form so they can just check the jobs they decide to check up on
i wrote a query which runs on click of a a command button "send selected to QA" which is just an append query sticking primary keys of the jobs table into another table TBL_QAList which has other fields 'contacted', 'satisfactory' and 'notes' for them to check when they have phoned the customer
of course i can't simply rely on the Append query because the jobs previouly loaded into QAList will still be checked and the append will try and append the same data twice cauzing a primary key violation
i don't doubt that it must be possible to only append those records which aren't already in the table (i believe its an outer join to return the records in neither of two tables? - i could then append the results of that query?) but anyhow the fact that i'd have to do something of that sort suggests to me my design is screwed up in the first place
i know that its bad to have the same data in two tables but all i would have replicated here would be the primary key and when i present the data to the user i use the table relationships to get them all the contact details etc that doesn't get copied across tables
maybe the better solution is to have no QAList table just the jobs table (with sentToQA field) and a QADone table - then the form the user sees can get its data from a query which finds those jobs for which Jobs.selectedForQA = True AND which are not in the QADone table and when they check the done box on their form the record gets added to QADone
have i just answered my own question or is there a yet better way of doing this? or was i on the right track in the first place?
I have 5 plants (when this was orginally created there was 1) I need to develop a report showing defect by suppliers. A user would select a supplier from a combo box, and using a query would show each reject number by supplier. I can get query to work, for only 1 plant as soon as I select another plant no records display. Any ideas
I have some tables that are set up as follows (these are not the actual names of the tables and fields, I'm using generic names so that I don't get a bunch of questions about the setup to begin with; I believe they are set up in the best possible way):
Table1 has a one to many relationship with Table2 Table1 has a one to many relationship with Table3 Table2 has a many to many relationship with Table3
Fields for Table1 Table1_ID Field1Table1
Fields for Table2 Table2_ID Table1_ID Field1Table2
Fields for Table3 Table3_ID Table1_ID Field1Table3 Field2Table3 Field3Table3
Fields for Table4 Table2_ID Table1_ID
Can anyone offer some advice on the best way to set up some forms for data entry with these tables? I think having Table1 be the recordsource for a parent form, having Table2 be the recordsource for one subform, and having Table3 be the recordsource of another subform would work for the one to many relationship between Table1 and Table2 and the one to many relationship between Table 1 and Table3. But I can't figure out how to then include the many to many relationship between Table2 and Table3.
I could not find Menu design in Access...Is it available during design time? When I searched for it I found AddMenuItem to programatically add menu... I vaguely remember seeing menu design in VB 3.0 7 years ago...I am looking for something similiar..
This probably the simplest and stupidest question..please excuse me for that.
Hi There, Im building a database that stores invoices and keeps a running total of all invoices accumulated per customer. Right now I have it so that on the form it Dlookup's a unit price and then divides it by the running total to get the total units accumulated as well as it displays the total money accumulated. The problem is, a few times in a year some unit prices will change. If a user updates this change then it will mess up the total unit count and the units to date will be wrong due to the new unit price. I'm wondering how i could overcome this problem. I was thinking along the lines of saving the total units accumulated in another table, reseting the entire form to zero, and some appending the old unit count to the reseted value... This probably sounds very confusing, but any help to just get me started would be greatly appreciated!!! thanks!
Hello everyone. Im relatively new to access, i've only been using it for a few weeks. There are certain aspects that I cannot get my head around.
Is anyone willing to let me email them what I have done so far and the criteria for the database I am making and advise me where I am going wrong and where I need to be heading to get it right?
Thanks very much. Any help will be very much appreciated.
This only shows the flow if the main category is "Floors", sub category "Joist", and then the choices under subcategory "Joist". There will be others for the other categories, subcategories, and choices.
Can someone help me or steer me in the right direction in setting up the database table/tables to accomplish this. Oh and BTW, there will be prices attached to each "Choice" under the subcategories.
I hope that this makes sense... any help would be much appreciated.
I am designing a db from an excel file that a volunteer org uses, i am the treasurer and I believe what they do in the spreadsheet would be easier in Access. I am familiar with tables, queries etc. Where I am getting stuck is the relationships and getting it to work properly. The database would need these tables based on the attached spreadsheet; Students, Tutors, Workshops, Events, Student Payments, Tutor Payments. NOTE: check the comments in each sheet in the spreadsheet for how the sheets work, but it is self explanatory. I think the db would need to be based on the students and from that the workshops and events that they attend, the workshops need to be linked to the tutors, where it gets tricky is that tutors can also attend events. Basically it would be a registration database, I've looked at the template from Microsoft.com called event management and it is sort of what needs to be done but not quite. Any suggestions would be greatly appreciated.
I need some basic help designing an environment where jobs can be assigned to personnel. Typically they'll have the same "job" every day, but if someone is absent then their job must be reassigned, distributed among the others.
The large assigment of job can be done automatically on a daily basis, and the reassignment could be manual. We're dealing with about 120 people, with 5 or so people absent at a time (potentially).
I'd like to track only whether or not the job has been completed. They'll have access to update their record.
I could do this fairly easy except for the fact that they'll have a daily assignment. How to I do this without generating a table without future dates? Or can I?
For some reason I just can't wrap my head around this design working with dates, assignments and completion.
I am trying to create a Preventive Maintenance Program for my facility,
I will create maintenance tasks: [ID],[Dept.],[Machine],[Task],[Frequency],[Completed Date],[Due Date]
Simple tasks,
Up front design help,
I will want the task to stay in the task table and the Due Date to be calculated combining the [completed date] & [Frequency]
Simple not a problem,
Here is my problem: I will need to create a task history table that will contain the Task details and the Completed date each time I complete the task; therefore my thought is that i will need to send the task detail to a history table each time it is complete, so I thought Append Query; but the problem is that it appends all of the records in the task table each time you run it including the tasks that have been appended on previous query runs.
any thoughts on how to capture the task details for a specific record and send them to a history table ?
