Need to add fields to crosstab query

[ajarrell]

I have created a crosstab query that I will use in a subform and a subreport. However, Access won't let me add the fields to the query that I need to link the subform to the form. Is it possible to add fields to a crosstab query?

Thank you in advance!

 

[CJ_London]

The easy answer is of course you can, but it may be that you are adding them in the wrong way. Also, you do know that the only way a cross tab can be a recordsource to a form is if the columns are predefined?

i.e. your sql would look something like

Transform Sum(myfield) as sumofmyfield
SELECT fld1, fld2
From myTable
Group By fld1, fld2
Pivot Fld3[COLOR=red] in ('Jan', 'Feb', 'Mar')[/COLOR]

 

[ajarrell]

The crosstab query and subform based on it work as expected. The field CC Cat ID (categories) are the row headings and the field SYards are the columns. These are square yard amounts for each month by category.

The main form is based on company entity. Each record in the square yards table is linked to the entity table by entity id. However, my crosstab query will only allow me to include the row headers field and the columns field along with sums. When I click on "Add existing fields", it shows only those fields. Consequently, there is no field to link the form and the subform.

I hope I am making sense. And thanks, again.

 

[CJ_London]

You aren't providing enough information for me to help.

my crosstab query will only allow me to include the row headers field and the columns field along with sums

The implication for this is that you are not bringing through the entityid into your crosstab - for whatever reason.

Suggest you supply details (field names/types) of your square yards table and the code for your crosstab

 

[ajarrell]

tblEntities:
ID Autonumber
EntityName Text
State Text
Promoter Text
Category Text

tblSYards:
Ent ID Number
SY Year Number
CCCat ID Number
SY Month Number
SYards Number


Crosstab query:
TRANSFORM Sum(tblSYards.SYards) AS SumOfSYards
SELECT tblSYards.[CC Cat ID], tblEntities.ID, Sum(tblSYards.SYards) AS [Total Of SYards]
FROM tblEntities INNER JOIN tblSYards ON tblEntities.ID=tblSYards.[Ent ID]
GROUP BY tblSYards.[Ent ID], tblSYards.[SY Year], tblSYards.[CC Cat ID], tblEntities.EntityName, tblEntities.ID, tblEntities.State, tblEntities.Promoter, tblEntities.Category
PIVOT tblSYards.[SY Month];

When I clicked on Add Existing Fields to my subform, only the row headings field and column field (with sum fields) were listed. I don't code SQL, but I went in and added tblEntities.ID to the Select portion of the query. The query works fine and includes the ID in a column of the crosstab query and in the subform. However, when I open the main form that includes the subform, I get this message:

You can't use a pass-through query or a non-fixed column crosstab query or a non-fixed column crosstab query as a record source for a subform or subreport. Before you bind the subform or subreport to a crosstab query set the query's ColumnHeadings property.

 

[CJ_London]

That error message is due to what I referred to in my first post - you need to include the bit in red (modified to your column headings) - you can do this in the query builder if you show properties, click on the top half of the query builder and complete the column headings property as below - you need to use the same form as per the values in your SY Month field

 

[ajarrell]

That did the trick. Thank you!