Select Features - Print Custom Report

[fraser_lindsay]

Hello,

I am trying to get useful data out of my database now. One of which is the number of employees and their details based on specific criteria in a query.

At the moment I have a query for each single criteria and then a report for each query. The query and reports are identical except for a change in the category or criteria. This might mean I end up with quite a huge amount of each for my various permuations and I would like the ability to be more specific or go down to finer detail.

For example I can currently run a report which tells me how many employees and their details are at high risk of developing noise induced hearing loss. This report prints everyone.

What I would like to do is have the facility to be able to request the employees at medium risk of NIHL, or the number at high risk but in a specific location and so on.

I have dipped my toe into this on a form based system but could not get it to work and I need it in report format for output to hard copy.

It sounds like some sort of query on the fly - is this possible?


Can somebody point me in the right direction please?

 

[reclusivemonkey]

At the moment I have a query for each single criteria and then a report for each query. The query and reports are identical except for a change in the category or criteria. This might mean I end up with quite a huge amount of each for my various permuations and I would like the ability to be more specific or go down to finer detail.

No "might" about it! You've learnt exactly how NOT to create reports/queries. You should have ONE query, then you need to specify the relevant criteria when the query is run, either through a selection from a drop down list on a form, or through a message box. You then also need one report for this query, so that however large your criteria list grows, you don't need to create/alter queries and/or reports. You may want to create a different report/query if you need to alter the FORMAT of your report, or change the data which you are returning.

 

[Hayley Baxter]

I posted some samples in the thread below. The thread is a bit long winded but the samples might help give you a better idea of how this is done. The information entered in the form, displays the search results in the query. The report is then based on the query.

Here's the link anyway if you want to have a look at the samples

http://www.access-programmers.co.uk/forums/showthread.php?t=49098&highlight=search+form

Hay

 

[fraser_lindsay]

Yeah, well I had already accepted that, but wasn't entirley sure what the best method was to use. However, it is the only method I know at present and for the time being gives me the results I want.

I am under no illusion that this needs changed before I add more data.

I have been doing a more thorough search and it appears I need to get used to setting up the query by form technique.

Donations of code or examples will be greatly appreciated. I'll go and hunt some more.

 

[fraser_lindsay]

Thanks Hayley, I also found another post of yours. Once again providing some excellent assistance.

I think I will probably leave this until tomorrow. You're right, it's quite a large post and I need to get my head around it and then my own setup.

I may (and almost certainly will) be in touch again.

 

[reclusivemonkey]

One tip I learnt early on which really helped was sticking

[Enter Date]

Into the criteria part of queries. This way, you can design your query/report without having to worry about the form and how you will get your criteria just yet. The user is presented with an input box, and whatever you enclose in the brackets is placed in the box. When you get round to the form, its easy to replace the [Enter Date] with the item you are using to get your criteria (this will be on a form). Obviously an input box is not the best solution, but as its your DB, you should be fine with entering the criteria without validations/lookups.

Using the "Build" wizard helps with the correct syntax when linking to a form too.

 

[Hayley Baxter]

Hello Fraser

I was just thinking that I still have a copy of the original sample from Microsoft. I used this example to learn the Query By Form method. It gives better explanations in here as well as comments in the code. This is probably a better sample to learn from. There are also many other good samples of various topics in here.

Hope you find it useful.

EDIT: ok It's too big to post to the forum but you can download it here

Hay