Hi

I want to create an Access (2003) table for my youth club's summer trips. I need to print off an information sheet for each club member, telling them which trip they are attending, what they need to bring, when to meet, etc. Each member's sheet will be different, depending on which trips they'd like to attend.

To me, it sounds like a simple task...but then I tried to create it! I can create a form for one person using just one table but if I want make a print-off for a second person I need to go through a lot of hassle in reformatting a 'report' and, indeed, in creating a 'report' for each individual member. I want to be able to print-off the results for each club member with just a wee click of the mouse. Is this possible? Tell me how!

Thanks!

Colin

Hello Colin,

Yes it is possible. You can do this in the query itself. Instead of specifying a persons name, such as Bill Gates, type in something like club.member instead. Access will think this is a field in a table that does not exist (because of the dot), and when you run the query/report, it will request for you to manually enter a value for it. Then you can type in the name of the person you want to view the query/report for.

This is a cheap and very quick way of getting the results that you want. Access is actually capable of doing absolutely anything, because it is a database development tool. There are so many other ways of doing this, such as creating a dropdown list from within a form, but there is where things start to get a little complex.

Hi MadMonkey

Thanks for your help but I'm afraid I'm a little unsure about what to do. I've created a query with "club.member" as a field, which makes the program ask me to enter a parameter for it each time it runs. However, all this does is give a list of all club trips, with the specified parameter on display in its own column. I want it to only display the club trips that, say, "Julie" wants to attend.

It may be that your solution actually does work but I'm too dumb to understand, so let's do this in the most basic fashion possible:

Imagine my table has the following fields:

Event
Date
Start time
Meeting point
Julie
Angela
Steven
Robert

(where Julie, etc are club members and their fields are yes/no boxes)

What do I do next to print off a report that shows which events each club member is attending? (Surely there is some 'if' statement involved somewhere?)

No, if you need to select by name, then you must first need some method of selecting or entering that name before you get to using VB. If you intend to use VB, an create a more complex method of selecting your members, then I would suggest hiring a developer for this work (such as Live Person). Databases can often become time consuming, so try not to over complicate things. At my workplace, we have a developer whose sole job is to develop and maintain just two Access databases.

I may have mis-understood you in any case.

What I meant in the query, was to use it as a value (under "Criteria") instead of a table name. The name would automatically change from club.member to [club].[member]. (demontrated in the attachment). This is more of a cheat, so that you could input the value you want into the query, without the need to do any programming.

You seem to have quite an odd setup there. Everytime you need to add/remove a member, you have to alter the design of the database. How many members do you have? What is the purpose of these fields, and what does the criteria of the fields need to be in order to view the report?

Attached Images

untitled.JPG (17.6 KB, 4 views)

Yes, I think that the database set-up is a little odd too! It's just for the summer so there's no long-term issues at stake. There are bout 40 members. I initially had fields named "member1", "member2", etc, in which I would enter the names of each person attending the trip. However, I didn't know how to use a query to pull out an individual's records using this method, but I managed to do it with the yes/no boxes previously mentioned (with the attendant problems).

I'm not going to use VB, but I did mention the IF statement with an assumption that Access would make it quite easy to use such a thing for the average user.

Ideally I would like to use the database for the following purposes:

* to print off a sheet specific to each member, informing them about the nature of each club trip

* to do the same for each staff member who is working on the trip

* to print off a sheet specific to each trip, telling me who is attending it

I don't care how the data is entered, as long as I can get this info out of it!

I can't check this morning whether or not your suggestion (the attachment) works for the table I've created, but I'll manage to have a look in about 5 hours.

Cheers for your help so far, and here's hoping we can master this damn table! (or the summer programme is cancelled!)

Thanks for the detailed info there cwebs,

So basically, if you wanted to view Robert in that report, then you would put a "Yes" under the criteria in his query?

If so, then it's definately over complicated IMO. The method I mentioned will still work, but it would work slightly differently. You could put Select.Robert instead of Yes under criteria, and do the same for all of your members. Or instead of using the dot, you can encapsulate it in square brackets, for example [Do you want to select Robert?] Then when it comes to run the report or query, then it will run through each members names, and then you can type in "Yes" for the member you want to see in that report. (demontrated in the attachment). Make sense?

It would also work if you had just one field called "member" with each members name listed below it.

Attached Images

untitled.JPG (7.2 KB, 3 views)