Parameters...

[jeffcravener]

OK, first let me give you a description of my problem...

I recently started a new job that does not use access in the least...they use Excel for everything...

Now, different people want reports with basically the same information combined differently or layed out differently. What that means for me...keying in the same numbers in multiple excel sheets for half the morning.

I was telling them about Access and how I think we could punch in the statistics for each queue (i am a call manager at a call center) and then use queries to pull the information any way certain people want to see it...

Now, I have built all the tables...but I am having trouble getting the queries to work....I know you can prompt for a parameter, but how do you prompt for multiple parameters?

Like, say some people just want to view the statistics of one queue....then i can do a paramter to ask for one input...but what if one person wants 1, one wants the statisitcs on 3 combined, one wants statistics on 2 separate, but at the same time...see what I mean?

I also think I can set it up like this:
The report is generated from inforamtion the person enters into a form which pulls the information from a query and then populates the report...
Table-Query-Form-Report

Can that be done?

Any help would be much appreciated...thank you in advance!!

 

[rac2]

Give them codes to enter meaning "I'm not interested" for each field. The code would be a value that could not really exist for that field. For numerical fields it might be 0 or -1; for text fields it might be N/A.

Build queries like this

SELECT age, sex, phone
FROM bigTableInTheSky
WHERE
     (age = [Age:] OR [Age:] = -1)
     AND
     (sex = [Sex:] OR [Sex:] = "N/A")
     AND
     (phone = [Phone Number:] OR [Phone Number:] = "N/A")

Make the default values in your form equal to these "Don't Care" values.

The result is that if age is not entered the parameter will have the value -1; this will be true for all rows so the age will not affect which rows are selected. If a value is entered then only the rows with that age will be selected.

This approach works for getting a detail report; or for totals of a particular field for different groups. Your post sounds a bit like some people need summaries by area code, while others need summaries by time of day. I don't really know whether parameters can be used for the names of columns, my guess is they cannot. If this is the need then I think you are stuck with creating different reports for different summaries.