Report not showing results for a particular scenario

[west]

In the test db included the report works fine if every Rep (representative or User/Admin) has records assigned. But, for example, if I go to the table and re-assign the first record to another user/admin (pick list in the table field [Rep]), then `User01’ will have no record assigned at all. Then an error message appears: The Microsoft Office Access database engine does not recognize 'User01' as a valid field name or expression. And no report is produced.


I wanted to try solving it with the info on thread searched by: "cross tab query reports", (13th result) but to be honest I really don’t know how to implement.

*note, I can't link yet.

Any light is appreciated.

-JC

 

[plog]

Pretty straight forward. You have a control in your report that wants the field User01 from qryTblMain_crosstab. Your crosstabs fields are determined by the values in the Rep field of TableMain. If you have no value in the Rep column for a user, its not going to exist in your cross tab as a field and consequently your report won't be able to find it.

My advice is to force all your user values that you want to appear in your report, to appear in the cross-tab. This requires using a table that has all the user values. Do you have it? If so, you bring it into your query, left join from it to tblMain (qryTblMain serves no purpose, get rid of it) and then use the field from your Users table with the Rep value. This will force all your users to have fields in your cross tab.

Actually, my first advice is to not layout your report as such. Cross-tabs are a pain (as evidenced by this and other issues). You should layout your report with data going vertically. You can use groupings to bind various values together. Or barring that, spit out the data and do a proper pivot in Excel.

 

[west]

I think is solved now.

In the Column Heading property of the crosstab query I wrote the names of the columns I want shown even without data;

"Admin01";"Admin02";"User01";"User02";"User03"

Now it is working. There might be a better solution, but right now this one complies.

-JC