Help with Query

[duckers9]

There must be someone brighter than me...

I am trying to create a report which shows the full history of the student (as they can study more than 1 module), which includes the prospective number of credits, actual credits awarded to date along with invoicing details.

However sometimes a student may have more than 1 invoice raised per module as they may be funded by their employer.(I need to show all financial records).

However when I ask Access to calculate the total number of prospective credits or credits awarded it is calculating these incorrectly when there is more than 1 finance entry. For example John smith is undertaking a 10 credit module, so when he passes this his both is prospective credits and credits awarded should show 15. However because there are 2 finance entries for John for this module both totals are showing 30.

How can I get access to disregard some credits when there has been more than 1 invoice raised for the module.

Any help would be appreciated.

 

[Will_B]

The problem is that you are trying to accomplish two very different things in one query/report: TOTALING ALL student credits and also showing INDIVIDUAL invoice details. The two tasks are opposed to each other. As a result your credit totals are being multiplied by the number of invoices a student has (twice for two invoices, three times for three invoices, etc.). The approach you should take is to create two queries: the first sums all credits per student and module; the second would display individual invoice details for each student and module. In your report you would use the first query as the data source, but you'd then create a SUBREPORT inside that report that would use the second query, linking the parent report and subreport by BOTH of the following: StudentID and Module. Here is how Microsoft explains creating a subreport: http://office.microsoft.com/en-us/access/HP051878031033.aspx

 

[Will_B]

Sorry. The link I gave was to SUBFORMS (same principle, but not a report). Here is the subreport link: http://office.microsoft.com/en-us/access/HP051874091033.aspx

 

[duckers9]

Hi Will - Thanks for all your help - it took me a bit to fathom out what I had to do as I'm not used to database design - but it worked a treat on the test database! I knew that there would be someone who could help - save me going round and round and round in circles. Is there any way I can copy this report onto my pen drive and then paste it into the working database - the queries are already there... (to save me having to redo it again from scratch?) (Told you I was new to designing..

Thanks for your help again.

 

[Will_B]

You can export a report (or any database object for that matter) directly to another Access database so long as they are accessible on the same computer or network. Simply right-click on your report and select "Export", then select the Access database to which you want to copy it. "Export" simply makes a copy of the object, so it will still exist where you made it.

If your intended database is not on the same computer or network, you can't simply copy the report to your pen drive. You'll need to either copy the entire database or create an new, empty database and export the report to it --- then copy that database to your pen drive. After you put this database copy on the same network/computer as your destination, you'll be able to do the "export" I described earlier.

I'm glad to have been of help. It's nice to know that the many hours that I've spent "going around and around" have now benefited someone else.

 

[duckers9]

Hi Will - thanks for your advice - report is now on main user database and working fine!!!!

Would it be possible to use the same data in this report in form format. Currently students can study more than 1 module and these module details are stored in individual records on the existing form. However sometimes a student can progress from studying 8 modules at Diploma, then go on to do a Degree (another 8 modules) and then progress to MSc (another 8 or so). So as you can see there are a lot of modules to flick through. The old system we used was able to list the modules in a datasheet format for a particular programme and we were able to amend and add to this.

I've created a form using some of the data from the query, but when I run it the data does not report by student or by programme. It just gives me a student name and then EVERY students modules are below it.

Is it possible? (Advice would be greatly appreciated)

Thanks!

 

[Will_B]

On your form (parent form) you want to create a subform that links to the parent form on the Student_ID field. The principal is similar to a subreport. The parent form would display only student ID info (name, ID number, etc.) while the subform would display all modules for that particular student. How to create a subform and link it is explained here: http://office.microsoft.com/en-us/access/HP051878031033.aspx