Generating a report using a dropdown

[punkin]

I have a relational database for training records.

I have the courses in one table and the attendees in another (plus quite a few other tables - this db is based loosely on the events database template from the microsoft website).

What I'd like to do is be able to select the list of attendees using the criteria of which course they are booked in for.

I need to be able to print out an attendance sheet for each course, also print out dietary requirements to send to the caterer, also the billings list to send to our accounts dept.

What I'd like to be able to do is when I select one of the reports, it have me select the course I want the report done for.

In the database for this year I have been "hacking" it slightly to get the results I want. I've got a new database for 2007's courses, in which I've ironed out a few of the glitches that I had to play with throughout this year. I would have quite happily spent the next month or so tweaking and learning queries etc BUT there is one major problem - that I'm just about to go on 7 months leave and my replacement isn't that knowledgeable on access (lets just say my procedure manual on the database consists of a lot of screen shots and very basic instructions) .

I'm at my wits end with regards to how to design the report and the underlying queries.

HELP!!!!
Thanks!
Sharon

 

[Rickster57]

reports

Did you try creating a combobox on a form that contains all the courses you mention? You use that value as the criteria in the query containing the fields of interest that you have in your report. In other words, all the people signed up for that course will show up in the report. Without seeing what you are doing, I am guessing at what you need exactly.

Hope this helps.

 

[punkin]

yes I've got a combo box on various forms that pull in that information, but I'll try this when I get to work in about 20 mins.

Don't know if I explained myself properly when I outlined my problem (I've done two 16 hour days - today isn't going to be any better & it is my last day) What I need to know is how to get the option of selecting which course when I run the report, the reports I've got now list every course in them - which is useless when I've got 200+ courses and 8,000 staff.

Yeh I know I've left it to the last minute (this is the only bit I'm having trouble with - exhaustion could have a large part to play with it), but I only knew about my husband being transferred 2 weeks ago, and in those 2 weeks I've had to streamline all my procedures for my job, write procedures for said job, set the courses for 2007, get the database created AND still do my job (4 more courses to run before mid-December).

 

[punkin]

I've been playing with it all day today (still can't get it to work), so I'm going to take a break from it and work it on Sunday when I've had a refreshing day of thinking about NOTHING except stripping down my project car.

As long as I've got it running by Tuesday, all will be fine. Have done searches on this site and printed out some threads so I can look over it on Sunday.

 

[gemma-the-husky]

what i would do is

1. create a query in design grid showing course and courseattendees and attendee tables, and join them together via couse links and attendee links - i assume you already have this or something similar

now if you want to see attendees for 1 course, all you need do is in the criteria row, of the courseid column, but a suitable test

eg if you use numeric course ids, put 12 say - this wil lnow show you just the attendees for course 12.

----------------------
now i think you want to do exactly this, but you want the criteria to be a variable

this next bit sounds long winded but it only takes a few moments - i've just gone through it carefully to show you what's going on.

so instead of putting the number in, you instead put a reference to your combo box, which makes it a variable value. You can do this with a direct form reference, but if you want to use idea from several forms, you are best using a variable. so in your combobox after update event (ie after you pick a course) put

gblCourseSelection = mycomboboxname - where gblcourseselection is a public variable

so now you need to create a module (or edit an existing one) and include
public gblCourseSelection as long (or string if you are using text ids)

now, in the criteria row of the courseid column you would like to be able to put

=gblCourseSelection, but you aren't allowed to use variable name directly - you have to use a function

so instead put the criteria

=courseselection() (you need the brackets)

and in your module have a function

function courseselection() as long (or as string)
either
courseselection = nz(gblcourseselection,0) - if its a number
or
courseselection = nz(gblcourseselection,"") - if its a string
end function

the nz's arent critical but protect you against trying to run the report with the variable value as null

now use the report designer to pick the fields you want in your form. and it should work ok.

you can equally use this technique to select a studentid, and see what courses they are on.