No valid fields can be found when using a crosstab query for a listbox in Access

Question

When I create a crosstab query and run it, I get the results that I'm looking for in a datasheet view. However, when I try to add a listbox to my form and then use that crosstab query, I get a pop-up every time "No valid fields can be found in [crosstab_query_name]. Please select a new source."

Here is my SQL:

PARAMETERS Forms![Home]![cmbChallenge].[Value] Short;

TRANSFORM Sum(-1*(1-(Round(b.WEIGHT/a.WEIGHT,4)))) AS SumOfWEIGHT

SELECT a.NAME

FROM (SELECT p.F_NAME & " " & p.L_NAME AS NAME, w.WEIGHT, p.ID FROM Participant AS p INNER JOIN Weight AS w ON p.ID = w.PART_ID WHERE (((w.INITIAL_WEIGH_IN)=True) And ((w.CHAL_ID)=Forms![Home]![cmbChallenge].Value))) AS a INNER JOIN (SELECT Weight.PART_ID, Weight.CHAL_ID, Weight.WEIGHT, Weight.WEIGH_IN_DATE FROM Weight WHERE (((Weight.INITIAL_WEIGH_IN)=False AND Weight.CHAL_ID = Forms![Home]![cmbChallenge].Value))) AS b ON a.ID = b.PART_ID

GROUP BY a.NAME

PIVOT b.WEIGH_IN_DATE;

Answer 1

That's not correct, @Scott. Here is a listbox bound to a crosstab.

Answer 2

That's not correct, @Scott. Here is a listbox bound to a crosstab.

I found the sql is not a parameter query from below image

PARAMETERS Forms![Home]![cmbChallenge].[Value] Short;

This is a parameter query.

I guess they are different at this point.

Answer 3

As Tom’s reply shows, he has filled the Column Headings property of the crosstab. There are a few uses of a crosstab that require this.

Answer 4

My bad

Answer 5

You can't use an aggregate query as a Rowsource for a listbox. There is no unique value to select.