Drowning in queries - is there an easier way?

[BonnieG]

Just wanting to pick the collective brain of the forum and ask how I would be best off doing this...

I maintain a large database of employees. Each employee has a record (stored in "user" table) and any changes relating to any of their employment details are audited by recording a record under the "user_change" table.

I send weekly audit reports out to a number of system administrators and currently, I have about 15 separate queries set up.

Each of these queries has to be run once a week and the number of records returned from that query entered manually into a spreadsheet field. For example, how many members of staff changed their name this week. How many members of staff started employment this week, and so on.

I'm trying to make life easier for myself. What would be the best way to do this? Is there a way I can set up a single query with multiple "counts"? Or maybe a form which I can just load, key in some dates, and then display the relevant data?

Would I still need 15+ separate queries set up and stored as objects, or would I be able to incorporate it all into the one query / form?

Just looking for some advice on how others would do this sort of thing... the result I end up with is always simply a number. It's just figures I need, nothing complicated.

 

[CJ_London]

Easiest way would be a form or report - you could even automate it to update spreadsheets automatically.

combining queries into a form does not 'remove' the query although you can move it into the recordsource of a subform, combobox or listbox - one for each query and which is dependant on what data you want to display.

 

[BonnieG]

Thanks CJ... I've not done much with reports before. Do they work in a similar way to forms? Can you select variables in a report, e.g. if I need to change the date selection? I'd like something I can easily hand over to someone who has never gone "behind the scenes" in access before and is totally non techy so trying to figure out the most user friendly way really.

 

[CJ_London]

If you are not publishing the data as a report then I would use a form

In it's simplest form and you are just trying to get a single value from each query, the form would be unbound and have 15 listboxes set one row high and each with a rowsource of one of your queries.

In addition you would have one or more text controls where you enter a date or other value and you would set the rowsources to work off this value.

So lets say you have a form with a textbox called selDate

And you have a listbox called say NoOfChanges (doesn't really matter what it is called) with a rowsource of say

SELECT Count(ChangeType) 
FROM tblUserChanges 
WHERE Month(EventDate)=Month([COLOR=green][selDate][/COLOR]) AND Year(EventDate)=Year([COLOR=green][selDate][/COLOR])

Note the square brackets round selDate and the fact it is the same name as your text control.

When you first open the form, the listbox will display 0. Enter a date in the selDate control and hit f9 and it will recalculate to the value based on what you entered in selDate.

If you want to automate the calculation so it happens without hitting F9, you can put some code in the selDate after update event (the code is simply Me.Requery) but to trigger this you need to hit tab or click off the selDate control.