Generating Multiple Reports off Queries from 1 Table

[athyeh]

Hello All,

My need today is:

The ability to generate multiple reports off queries from 1 table.

I have a database that tracks production for several operators. I want to be able to generate separate reports from each operator regarding production. Each operator can be identified by: P-Number, Org Number, or Name.

I have written a query using, Query Design, and the "Or" statements and setup a report for Operator: 1 (P-Number), 113344 (Org Number), John (Name). But was wondering if there is a better/easier way to reproduce this for 30+ employees?

Thanks!

[pbaldy]

Sure, use a parameter query (point the query criteria to a form) or this:

BaldyWeb wherecondition

[athyeh]

I appreciate your response but don't know what you're talking about...

So instead of generating a report I would be creating another form?

[pbaldy]

No; your current query has a hard-coded criteria of "John". I'm saying to create a form where the user can enter or select the person, and point the query there. Then it runs for whoever is entered on the form. You don't need 30 queries and reports. Your criteria in the query would look like:

Forms!FormName.TextboxName

[June7]

Check out this tutorial demonstrating a parameterized query http://datapigtechnologies.com/flash...mtoreport.html

I prefer the VBA approach shown in Paul's link.

[athyeh]

I still don't understand what you're talking about.

I did try the parameter queries but after inputting [Enter Operator] into the Criteria column of the Operator field I was prompted to Enter Operator (which I did), and then was returned with an error message saying that the expression is typed incorrectly, or is too complex to be evaluated.


I am lost as far as the "wherecondition" option is. Would you be able to explain further? Please and thank you.

Thanks!

[June7]

Did you follow the tutorial for parameterized query that uses a control on form to enter filter criteria?

The "WHERE CONDITION" is an argument of the OpenReport (or OpenForm) method. This is specified in code (macro or VBA). Paul's link shows VBA code that refrences control on form as filter input.

If you know nothing about coding with macros or VBA, suggest you start with a review of: http://office.microsoft.com/en-us/ac...010341717.aspx

[athyeh]

I really appreciate your help and while I look over the article and the walkthrough video a thought occured to me.

For the time being, is there a way to simply have the report allocate a page(s) for each operator? So page 1's Header would be: John Doe and all the other fields from my table would be below it. Page 2's header would be: Jane Doe and the same fields (relevant to Jane) would be below it.

This seems like it would be a much easier quick-fix?

[June7]

This would be using report Grouping & Sorting features. Access Help has guidelines.

[athyeh]

I still don't understand what you're talking about.

I did try the parameter queries but after inputting [Enter Operator] into the Criteria column of the Operator field I was prompted to Enter Operator (which I did), and then was returned with an error message saying that the expression is typed incorrectly, or is too complex to be evaluated.
I am lost as far as the "wherecondition" option is. Would you be able to explain further? Please and thank you.

Thanks!

I've learned a little bit within the last few days and I have a question regarding: [Enter Operator] and parameter queries. For the prompt are you able to specify non-numeric data?

E.g.

I enter:

[Enter Operator] into criteria for "Operator" field.

When I am prompted to Enter Operator is there a way to have it so that I can type "Adam" instead of "1"?

[pbaldy]

You can enter any type of data you want, you would just need the criteria to be on a name field instead of an ID field. Most of us use forms, where you could have a combo with the operator names and ID's.

[athyeh]

Thank you for your reply.

So now I have a form that I am basing off a query. The form asks for operator name, start date and end date, and I want it to go into my maindb and pull information that is within the parameters.

Example:



Operator: John
Start Date: 01/13/2013
End Date: 02/13/2013

Pulls everything John did from 01/13/2013-02/13/2013

The Query it is based on looks like this:




Where Form1 is the Form where the user can set the criteria.

Problems:

1) I used the drop-down menu wizard to create the Operator combo-box but instead of showing "Operator" it is showing "ID" (I think this is a Primary Key issue but when I tried to change Primary keys got a whole bunch of error messages)

2) When I run the query without any criteria in Operator box it returns the info I want within the specified dates just fine. BUT when I add in the criteria for Operator seen above (so that I only pull John's stuff within the specified dates) it returns nothing.

Please help.

Thanks and have a happy 4th!

[June7]

Review this tutorial about multi-column combobox: http://datapigtechnologies.com/flash...combobox3.html

What is actually stored in the Operator field of tblMainDB? Is ID number or name text?

[athyeh]

Review this tutorial about multi-column combobox: http://datapigtechnologies.com/flash...combobox3.html

What is actually stored in the Operator field of tblMainDB? Is ID number or name text?

Name text.

[June7]

Then must use the name as search criteria, not ID value. Names can be poor unique identifiers. What if you have more than one John?