Force a form to show null values (Access 2000)

Topic

New Reply

Hi all,
I have a report that I submit daily, it’s about 14 pages long, and I do it in excel. I have a crosstab query in my database from which I derive all the information, and then I transfer it to excel. The reason right now that I’m *stuck* with excel is because the boss doesn’t want the pages to change as groups increase/decrease in size. My group has up to 8 possible products in it, depending on the location of the product (what the report is sorted by in the first place). I want to display all products at every location, even if there is none of it at that location. Help?

Reply | Quote

Replies

If the products are the columns in the crosstab query, you can force them all to be displayed by listing them in the Column Headers property of the query (separated by commas)

Reply | Quote

Hans,
I hope I didn’t mislead you, as I said Force a form in the subject where I should have said force a Report. I think we may have a small misunderstanding. I haven’t been able to access my database yet (it’s on a development computer, it’s not on the net…backwards, I know), so is the Column Headers property able to be listed on a report?

Reply | Quote

You can put the column headings into the crosstab query, then they appear in the report.

Open the query in design view, and you will colum headings in the properties box, when it displays the properties of the query.

Reply | Quote

This is pretty complex (to me at least), maybe it’s extremely simple and I’m reading too much into it. Is there a way for me to manually design the report and refer to specific values in the crosstab? How would I refer to that value from the query? I mean, I figure my Control Source for one specific item would look something like:
=SUM([qry]![qryLocbyCo]![location] & “BUFFALO”) AND ([qry]![qryLocbyCo]![PRODUCT] & “WIDGET”) AND ([qry]![qryLocbyCo]![RSTAT] & “ME”)

However I know I’m wrong…so any help would be appreciated

Reply | Quote

You are making it much more complex than it needs to be.

If you put column headings into a crosstab query, the column headings show up as field names when you go to design a report.

Then you just add them as you would any other fields.

Reply | Quote

I attach a demo of a simple crosstab report.

I have used the NZ function for the first two columns, but not for the other two.

I find that NZ converts the value to a string, so I then put a Val function around it to turn it back into a number.

Reply | Quote

John,
That example is good, however now maybe I can explain my problem. Take Penny Black and have her order nothing, no bread, no butter, etc… I still need her to show up on the report. Yes, my boss is

Reply | Quote

I attach another version of my example.

Penny Black has bought nothing, and I have added another product coffee. No one has bought coffee at all.

Yet both show up in the report.

Three things make this work:

* IN the query design foced teh query to show all people. Double click the join line and choose :All People”, then need to do the same with join from Peopleproducts to Products. The arrows need to run all the same way.

* Added coffee to the column headings

* Used NZ to replace nulls with zeros in the report.

Reply | Quote

John,
Beautiful solution, but you know…I always I have a wrench. All that data is on one table.

Reply | Quote

John’s report is based on a query that combines data from three tables, so please explain exactly what you mean.

Reply | Quote

Hans,
John’s query works because in his join he specifies that the query must show ALL values from one of the tables. I have one table, with all of those columns on it. I want my report to show each location on a separate page. On each page, I want every possible person listed. My query (As it stands now) has two column headers (Location, and Person), and the Row header is (Product)….hopefully that provides more insight.

Reply | Quote

I am sorry I don’t understand either.

If the people are present in the one table you have, and you don’t have a separate people table, how is the system supposed to know who are these people who haven’t done anything, and presumably the product that hasen’t been bought?

Reply | Quote

Don’t you mean that Location and Person are the Row Header fields and Product is the Column Header field? A crosstab query can have only one Column Header field.

Reply | Quote

Hans, yes I misspoke. Let me try this again:

Ok, please forget my people analogy. Let me try to rephrase this. All this info is in the same table, the basis for this table is tracking people… I have 4 possible companies. I have 7 possible locations. Some of these companies have people at every location, some are only at one. Each of these companies have employees with one of the following four employee codes: (temp) (man) (perm) (sec). These codes make up my column heading, and are how I count the number of people working for each company. (I keep totals of each employee code.) What I want to do is have a report with 7 pages (one for each location). On each page, I want every company listed, even if they have no employees at that location. Right now I have a crosstab query, with location and company as row headers, and the column header of employee codes. The query will only show each location as many times as there are different companies at it. I want the query to show every location 4 times (one time for each company.), with each company next to it (even if it has values of zero, meaning nobody from that company is at that location).

Reply | Quote

You need to create a query that returns all location-company combinations, then create a query based on this query and the crosstab query, with a left join on location and company. See the attached demo.

Reply | Quote

Well, I finally just broke down and created two more tables, one for location and one for company, and the query works great. So thank you.

Reply | Quote

The other thing you can do is use the NZ function to convert null values to zero.

Reply | Quote