Performing a query based on form entry (1 Viewer)

[llgreen]

Hi there!

I'm relatively new to Access, but I'm currently developing a database for a charity I volunteer with that supports disabled adults and children.

I have a table which contains the following fields:
-Date of birth
-Gender
-Conditions 1/2/3

As we frequently have to report statistics based on these three areas to our funders, I was wondering how I could develop a pop-up 'search-like' form where I could search for certain information: e.g. number of male and female service users between the age of 12 and 18 with autism; or a pie chart showing the distribution of conditions for all service users aged 25 and over.

I'd hope that a report of some sort could be generated from this information and shown in a form.

I understand this might be based on queries, but also, if possible, I'd like query information to be deleted after the user finishes their search.

If anybody could offer any advice or assistance, that would be much appreciated!

Thanks!

 

[Ranman256]

you really need 2 tables:
tPerson
-PersonID
-Date of birth
-Gender

and tCondition
-PersonID
-Condition

this way you can search on conditions.
make a base query on tPersons to calculate age, qsPersons:
select *,DateDiff("yyyy", pvBirth, Date) as AGE from tPersons

then use qsPersons to grab what you want, ie, females betweeen 12 and 18.
if you want to know conditions, make another query from qsPersons, say qsPersonCondions by joining tConditions to the qsPersons query.

 

[plog]

I suggest you pick one thing and work towards it--your initial post was kind of scattered on what exactly it is you wanted to accomplish. I also suggest that one thing be a report, not a chart--dynamic reports are intermediate- level stuff in Access, non-dynamic charts are just a little harder than that.

So, let's say you want a form where users can enter criteria and then click and button to open up a totals report. Here's the broad strokes:

Step 1: Build your query to generate the data you need. Criteria-wise, make it as generic as possible, but bring in/create the fields that will let you apply criteria (e.g gender, age, age band, etc.)

Step 2: Build your report upon that query. Because of the lack of criteria applied in the query, this report is going to show every record in its totals.

Step 3: Set up your form. Bring in inputs for every criteria you want the user to apply (gender, age, age band, etc.). If possible, make those drop downs. Add a button.

Step 4: Build the button's VBA code. When the user clicks the button you will write code to look at each criteria input on the form and build a criteria string that you can pass to the report when you open it using the DoCmnd.Open report method (google that).

So, get cracking and post back here what issues you have with any step.