How to group queries by users

[Pari]

Hi all,

I have approximately 40 odd queries on my Access database and as this number is increasing on a daily basisi, I was hoping there was a better way to easiliy retrieve queiries relating to particular sections or people.

I am aware you can group queries but for some reason the main view of Access still displays all the queiries. It would be really helpful if this could only display the queiries in the group selected.

If it helps, I am currently making use of Access 2003

Thank you in advance for your help.

Regards. Pari.

 

[boblarson]

Quick question - why are they growing daily? What are they and why do you keep adding to them? (just wondering if we can find a reusable solution for you so you don't need to have multiple queries with just different criteria).

 

[Pari]

Hello Bob,

The reason I have more stats added all the time is due to providing stats for over 100 users. As there are also time when I am asked to verifiy stats that I have provided previously, it is extreamly helpful to have access to the exact same query for this purpose.

Hope that helps. Please let me know if you need any other clarification Bob.

Regards. Pari.

 

[boblarson]

I would highly suggest that you create a form for your criteria and then we can use the same query for multiple people simply by selecting who it is on your form you want the data for. Creating tons of queries can be a nightmare which will only get worse over time. You need a "reusable" solution where you can have a single query for a certain set of data (you can have more queries but they should not be pulling the same exact thing with only criteria differences) and then you can pass the criteria to it.

For example, check out this sample of mine (this is a report but we can work at some things to help you get it if you need queries or if you want to export the data to Excel or such). The sample shows a GENERIC report with a GENERIC query underlying it and we just pass the WHERE clause to it using the form, depending on what we've picked.

 

[Pari]

Bob,

That is really neat and resolves the matter. Although would it also be possible to show the results of the query in a table format, instead of a report?

Regards. Pari.

 

[boblarson]

Yes, we could do it so that you could have it in a "table" type format. It would probably be easiest if you built a FORM for each base query and set it to display as DATASHEET. Then we can use the code to open the form and pass the WHERE criteria to it and it will open with that criteria applied and look just like if you had a query open.

 

[Pari]

OK Bob,

Let me take a look at how to make a form. Would it be possible to display certain queries in the form if a particular user is selected?

Regards. Pari.

 

[boblarson]

OK Bob,

Let me take a look at how to make a form. Would it be possible to display certain queries in the form if a particular user is selected?

Regards. Pari.

Almost anything is POSSIBLE - it just depends on what we're working with as to how much we have to code in order to make it as reusable as possible. When you say "display certain queries in the form if a particular user is selected" it sounds like we have a little disconnect. What you wouild do is to have a form built for each query that has different fields. If you have a query that you just want the data for one user and then a different user or a different selection of dates, or by user and date, etc. you can use that single form for that one type of query and pass it the criteria when you open it.

For a second query which has different fields then you would make a form for it but you can call it and limit the data to whomever and whenever you wanted from the same input form.

We'll try to work you through this together and there are a lot of other people here on the forum which can help as well.

 

[rheeyum]

Anyone who knows how I can filter a query based on the active user logged in? I am not using a workgroup so the the CurrentUser() does not work??? I am using the Login Database from the database repository first page