Report with no Record Source

[NISMOJim]

I asked this question as a "part 2" to another question in a different post, but wanted to ask it here so others may reap the benefits of your wisdom.



Is it possible for me to have a report without a Record Source? I was hoping to leave that blank, and for each textbox on a report I would use the expression builder for the Control Source (=[qryTrng1]![EmployeeName]). I have several unrelated queries, and the boss wants a list of employee names on one report, one column for each employee that has completed the training that is the column header. When I tried it this way, all I get is "#Name?" in each textbox.

I'm sure this may not be the most logical way to go about this, but can it be done this way? What am I missing to make the list of names not show up?

Thank you for any and all help.

[June7]

Can't reference a query object in an expression that way. Can use domain aggregate functions (DLookup, DSum, DAvg, etc) to pull data from table or query.

Or maybe you need a CROSSTAB query. However, basing a report on CROSSTAB query is tricky.

[NISMOJim]

After a few changes, here is what I have, in more detail...

A table, tblTraining, that contains all training data for all employees.
a query, qryRank, with 2 fields from tblTraining, EmpName and Course. I added a third field with the following in the Field Name...

Rank: IIf([Course]="A1 Skills",1,IIf([Course]="A2+ Skills",2,IIf([Course]="A2 Skills",3,IIf([Course]="B1+ Skills",4,IIf([Course]="B1 Skills",5,IIf([Course]="B2 Skills ",6))))))

This will assign a number to the levels of training that the employee has had. A1, the highest rank, will be assigned a 1, B2, the lowest rank, will be assigned a 6.

Another query, qryRankList, based on qryRank, has 2 fields, EmpName, and with the Totals button, I'm finding the minimum of the Rank field.
This way, on the impending report, if an employee has had B2, B1, B1+ and A2 training, they will only show up in the A2 column. This is working as planned.

Now, on to the report. Since the report has no Record Source, I was hoping that each textbox on the report could have a Control Source like so...

=DLookUp("[EmpName]","qryRankList","[Rank]='6'")

I have tried this expression with & without the equal sign, [Rank] and [MinOfRank], and with & without single quotes around the 6, but either get #Name? or #Error in the textbox. What am I doing wrong?

[NISMOJim]

I just tried this again with a Control Source of

=DLookUp("[FullName]","qryRankList","[MinOfRank]=6")

and the first name in the query with a rank of 6 displayed on the report. Unfortunately, I wanted everyone with a Rank of 6 to display, but this may give some insight into what is going wrong.

[June7]

The Rank expression appears to have an extra space in "B2 Skills "

The DLookup syntax looks good to me. The DLookup should return the first EmpName it encounters that meets the criteria. Is that what you want?

If you want to provide db for analysis, follow instructions at bottom of my post.

[NISMOJim]

I need to return all of the records that meet the criteria. One column (textbox) would show all A1 employee names, the next column all A2 names, etc. Do I need to be going about this in a whole different way?

[June7]

Then you need to bind the report to query. Options are:

1. CROSSTAB - http://allenbrowne.com/ser-67.html

2. emulate a CROSSTAB - http://www.datapigtechnologies.com/f.../crosstab.html

[NISMOJim]

I don't think I've ever done a crosstab query before. After reading about it & experimenting a little, it looks like I need to have columns and rows instead of just columns, not quite what I was thinking. So I tried the second option, emulating a crosstab using IIf statements to pull the names into columns. This works great, except for the empty spaces in each column where the names (in alphabetic order) are in the other columns. How can I get rid of the blanks? I would imagine this would be done in the report, (or maybe not?) maybe with a conditional formatting that says for each field, if the record is blank then display the next record?

[June7]

I am not sure why you have blank fields.

I don't know your db well enough to advise further.

[NISMOJim]

Permits and Training Database.accdb.zip
rptRankList is what I am working on. Hope this attachment works!

[June7]

There must be a row for each person. Each person name will display under 1 column.

VBA code could concatenate the names into a single field under each column and the result would be a single record.

What is wrong with having the blanks?

[NISMOJim]

If this were working the way I hoped, the list would be printed out on a regular basis & distributed to the managers. A one page list would be much easier to look through rather than what may one day be 5 or so pages due to all of the blank spaces. The concatenation with VBA sounds like that may be the way to go if it will eliminate the blanks. Unfortunately, I don't know where to start with that either.

[June7]

Will you have that many employees?

Review http://allenbrowne.com/func-concat.html

However, I don't think it will really reduce the report length by much.

Also, considering the effort involved in implementing the code and the fact that the code can be slow, probably not worth it.

[NISMOJim]

Sorry, I couldn't wrap my head around that, but it's ok, I got it to work!
I created a form with a list box for each training level I wanted to capture. In each list box, I build an sql in the Row Source to select the name where the rank equals the correct one for the column. I set it up to print on 8 1/2 x 14 paper, and stretched the boxes to the bottom of the page so there would be no scrollbars. Viola! Exactly what I wanted. Thank you for hanging in with me on this thread!