Search the Community

Hey all, I hope this is the right place for this - if not move it and slap me! All,I have this formula (inherited from a manager who has left the school).=IF(N$3>$H$3,0,($L8*$E$3+IF($I8>0,-30,0)-O8)*IF(SUM($J8:$K8)>0,0,1)) Basically it is working out money owed to the school by students doing extra curricular music lessons. Some students are eligible for free lessons (if they have free school meals or if the lesson is part of their GCSE course etc) all the others have to pay a fixed amount per lesson. They get a discount if they are learning a set pair of instruments. The formula works for everyone who has to pay. If any student is in the FSM or GCSE group then as long as the amount owed is greater than £0.00 then the formula works. If the value owed is less than £0.00 (for example they've paid but have since moved to a FSM or GCSE group) then the amount owed always shows as 0,00 and not - - the sheet should display the credit amount as a red negative value. Example values for a student in the FSM group are :N$3 - this is a date $H$3- also a date $L8 - is the number of instruments being learned - this is at 1 for all students - I assume this means that if they are learning a set pair that doesn't count as multiple instruments because of the discount. $E$3 - the current charge for a single lesson $I8 - this is set to one if the student is entitled to a discount as they are learning a set pair O8 - value paid which is updated manually by the finance staff $J8 - is set to 1 if student is entitled to free school meals$K8 - is set to 1 is the instrument is being learnt as part of the student's GCSE course It seems to me that the problem is that the multiplication operation at the end of the formula is screwing it as it seems to multiply something by 0 if certain conditions are met or by 1 if they are not. Multiplying a negative with a positive number will always give a negative but multiplying with zero will always give zero... I think that's what's breaking it but I struggled to figure out how to fix it so credit amounts show as negative and not as zero until I self-bodged what i think is a very clumsy "solution" as per below: I created two hidden columns. I moved the original formula in column P and removed the final multiplication statement: - *IF(SUM($J8:$K8)>0,0,1) - to column Q and hid the column. Added a second formula in column R and hid the column: - =IF((COUNT(J6:K6)>0),0-O6,Q6) - which looks at whether the student is due free lessons or not by checking for numbers in the relevant columns The amount owed column (P) now has this - =IF(ABS(R7)-O7<=0,R7,ABS(R7)). This makes sure the amount owed is shown in the correct sign (either negative or positive) - if the school is owed the amount is positive, if the student is owed the amount is shown as negative and in red. It works but it seems to me to be very clumsy I'm sure there is a way of doing this without resorting to using up two extra columns of data. If anyone has any suggestions please feel free to embarrass me! Treat me kindly though as I am very new to this formula lark and am learning on the job..... Literally. Thanks in advance....

Hi all, I have been passed over an Access database that needs some modifications. It is a relatively simple database, and most of the modifications I have done. However, I need to now dump some of the data out into a word document, which I can partially do, however I don't have all the code required. I was hoping there might be some Access gurus on here that might be able to lend a hand / point me in the right direction I am aiming to: The database stored 'bookings' and has multiple tables with lookup data that all are matched in the bookings table. Ie. Students table, Tutors table, Rooms table and subjects table. I need to present all the bookings for a particular tutor or student in a grid 'timetable' view. I have created the timetable template in Word and I have inserted bookmarks in each cell. I currently have some VBA code that will dump a particular field into a particular bookmark area The Issue: There are three parts that I need some assistance with. 1. I need to be able to select a tutor's name (from a listbox preferably) and it only give me the bookings for that tutor. I can try and pull data from a query, however as it stands i've only managed to get it to prompt me for manual input of a name rather than look at a listbox and set that name as the criteria. 2. Only IDs are stored in the bookings table, the data I want to pull out is the actual friendly names, which are stored in the seperate tables. 3. I need to input the bookings into the correct cell in the table based on the timeslot that is selected. Ie. If the booking is put against timeslot: "Monday A Session 1", then it needs to go into cell with bookmark 'mon1_session1_subjectname'. I can do a lost of if else statements, but it's how do I get all the data together to take from? The database is attached if you wish to have a look: LFC LSA_Lauren Monks.zip At the moment I have none of the above functionality set up, and a few bits are broken (work in progress), but more so that you can have a better understanding of the database. Thanks in advance for any assistance! I haven't done any Access work for a few years, and I'm a bit rusty!

Hey! Could anyone recommend some sort of script or application that will enable me to generate a list of usernames which are pulled from active directory. I require an ability to extract both the first name and surname as well as the associated login name (possibly even password). The format we use would be much like the following example: Stephen Walden = 'stephenw' Your help would be much appreciated! Steve Holmfirth J I & N School

Hi everyone, Just started a new post at a school, looking to dig around some of the data held in SIMS to help senior level leadership. Is it possible to run reports or query the SIMS database directly without interfacing with the SIMS GUI? Right now my current workflow is to query the database using Reports -> Run Report and attaching a macro enabled excel file to manipulate data. I'm sure there's a more useful way to be doing this? I've seen some posts relating to CommandReporter, could anyone point me in the direction of some documentation for this? Thanks for your help!

I'm back for more Access help - hopefully someone out there will answer. I have a database which contains a list of hotel guests and the gifts they have claimed over a certain amount of time. I now want to print a report on the 28th of each month to see what each member has claimed in that month - for example Marth 29th - April 28th and so on. I have designed the report for it to print but am having trouble with the query that calculates this information. I've tried putting this: Between Date() And DateAdd("m",-1,Date()) into the query criteria but cannot get it to show the results. Am I missing something? There should be at least one guest who has claimed gifts in the past month. Any help appreciated, Gareth

Basically i have setup my Deployment Share with 2 seperate task sequences one for Windows 7 Enterprise 32bit and one for 64bit both also have their own driver folder x86 and x64 respectively which they point to Bascially this is the only manual process (apart from name and OU which i want to keep manual) which i want to automate What i am trying to do is basically get MDT to query the amount of RAM that the machine currently has from the BIOS etc and point it to either 32bit if 3.5GB RAM or lower / 64bit task sequence if 4GB +?? Thanks Jack

Good Morning, I was hoping someone could help me out - I have the task of creating a system for our transport manager and would like to email query results, thing I dont know is what VBA coding i need in order to do this ...... Any suggestions would be great, need anymore information feel free to ask? thanks

I am not really looking for the usual user management features that exist in some / many pieces of software that tap into MS AD. What I want to do is be able to produce reports detailing the user account information, namely username and password. In the past I have used an excel file and merged it to word to produce a little wallet sized card with all the appropriate network and email account information on it. The teachers have found this very useful as I can quickly produce a class set and when thirty kids walk in the ICT we can get them logged in as quick as possible. I kept on top of for a couple of schools (only visiting 1 day a week) and worked well when you used this as the central account db and just exported to the various system for instance, AD and bgfl email and some school software applications. Now I am looking for something that will query AD and produce the same soft of reports (8 wallet sized network accounts) and something that will be as flexible as excel / mail merge and allow me to add other bits of info!! Has anyone used anything like this before? Thanks in advance - not long now!!

Does anyone know if it is possible to run an AD query that lists all users in the domain who are members of more than 1 global security group? If so, do you have a suggestion in how that string might look like, or already have a similar string that you want to share?