Create One Report from one Query with multiple parameters

[trock12]

Hopefully the title makes sense. I have a query that uses 2 parameters - User, and a date range Start Date thru End Date.

When you run the query as a standalone, you would input the User's Name, then Start Date and End Date to get the desired information.
There are 14 users. I'm hoping to create 1 report, with a separate section for each user - all for the same variable of date range.

The goal is to just run the report, type in the date range, and have it populate a section for each user with the returned query information.

I'd have to think there is a simple solution to this that doesn't involve make a "Pre-Made" query for each user, and then entering in the date range 14 separate times to generate the report.

Any help would be appreciated!

 

[xenou]

Hi,
The user parameter comes out. You just run with the date range parameter only, and in the report group by (or order by) user.

 

[trock12]

I think I left out one key part -

The query uses a subquery. The user name parameter is actually looking for a Null record for each date within the specified range. So on the subquery parameter, I would enter "Smith", then on the parent query paremeter, I enter the date range (with a built in criteria of the user name being "Is Null") - so therefore it would return all dates where "Smith" is not present.

When I remove the parameter on the subquery, the report only returns all date values where No User had data, not just "Smith".

Is there are work-around?

 

[xenou]

You'll have to be more specific about how the data is related. Is it one table or two tables? What are the key fields you are querying? If more than one table, how are the tables related? Provide some sample data with two or three users and examples of these nulls.

 

[trock12]

The first query uses two tables.

tbl_Info_added &
tbl_username

Information is added to the tbl_Info_added via a form. Therefore there is a join between the two tables, where the user pulls their name from a dropdown from tbl_username.

The query (qry_info) contains all fields (including a date input) on the tbl_Info_added & one field (the username) from tbl_username. There is a parameter entered [Type User Name:] in that field on the query. And a secondary parameter to enter the date range.

The 2nd query (qry_MissingInfo), uses the qry_info query and a table called tbl_workday, which houses a massive list of all business days. This query only has two returned values - Workday (which is all rows from tbl_workday and only those records that are in the qry_info that match), and the User field from qry_info.

The Workday has the Between [Enter Start Range:] And [Enter End Range:] Parameter and the User parameter "Is Null" (which isn't displayed)

The purpose of this query is to see if "Jack Smith" filled out his form for each business day of the specified range. Any day where he didn't, would show up on this query.

Example:

Jack Smith in January completed his info every day but 3 days - the 5th, the 11th and the 27th.

When I run the qry_MissingInfo I type in Username: Jack Smith in the first parameter and then 1/1/2016 as Start Range and 1/31/2016 as End Range.

qry_MissingInfo returns just those three dates listed above and nothing else.

My goal is to have this information return for ALL users on a report, without having to specify each user and date range 14x (# of users).

If I "removed"

 

[xenou]

You probably have to rewrite your queries I think. But you can get that info from the tables for sure. I think this works (as one way of doing it):


first, you need all the users and dates (think of it as the "master list"). This is also where you can filter down your dates. This query will be used by the next one.
qry_workdays_users

select
	[workday], [username] 
from 
	tbl_user, tbl_workday
where
	[workday] between [Date1] and [Date2]

Then you need to join this to the tbl_info_added table and get the missing records (the ones that are not in the master list) (the two null checks are redundant but for emphasis - either of these fields being null means that the join on the two fields failed so there was no match for that user on that day).
qry_missing_users

select t1.[username], t1.[workday]
from 
	qry_workdays_users t1
	left join tbl_info_added t2
	on t1.[username] = t2.[username]
	and t1.[workday] = t2.[workday]
where
	t2.[username] is null
	and t2.[workday] is null

 

[trock12]

Thanks so much!

I have entered everything and T1 operates exactly as expected when running that as a standalone query. Now I'm gettin Mismatch in Expression error when running the 2nd query.

 

[xenou]

are the data types for username and workday the same in all the tables? What is the exact error message?

 

[trock12]

The DataType for Username in the User table is Text, while the DataType for the User in the Info_added table is Number (since its a calculated field pulled from a form dropdown).

The Actual error when running the 2nd query is "Type Mismatch in expression"

 

[xenou]

change:

on t1.[username] = t2.[username]

change to:

on t1.[username] = CStr(t2.[username])

In short, make them the same datatype (text).

Although it's a little confusing why you have text in one place and numbers in another. You may be doing something here on the form that I am not understanding correctly. You should strive to get the real value for the username in the form - the same thing as you would get if you went to the table. And by value I mean the primary key that identifies the record, be that a name or a number.