Re: Using Columns in a Crosstab Query as fields in another query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

You could:
1) add a field to your table of shows to create a column heading for your
crosstab. Each year you would change the values in this field.
or
2) use PIVOT "yr" & Val(Format(Date(),"yy") - Val(Right(tex_ShowID,2))

--
Duane Hookom
MS Access MVP
--

"Amy E. Baggott" <AmyEBaggott@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:34983573-219C-4604-B933-F2F6F54D18FC@xxxxxxxxxxxxxxxx
> Actually, that column heading comes from the value of a show ID field.
> Here's the SQL for the crosstab query:
>
> TRANSFORM Avg(qrySqFtHistory.SqFt) AS AvgOfSqFt
> SELECT qrySqFtHistory.exh_masid, qrySqFtHistory.[Company Name],
> Avg(qrySqFtHistory.SqFt) AS [Total Of SqFt]
> FROM qrySqFtHistory
> GROUP BY qrySqFtHistory.exh_masid, qrySqFtHistory.[Company Name]
> ORDER BY qrySqFtHistory.tex_showid DESC
> PIVOT qrySqFtHistory.tex_showid;
>
> And here's the SQL for the bonus points query:
>
> SELECT qrySqFtHistory_Crosstab.exh_masid, qrySqFtHistory_Crosstab.IBSX01,
> qrySqFtHistory_Crosstab.IBX02, qrySqFtHistory_Crosstab.IBX03,
> qrySqFtHistory_Crosstab.IBX04, qrySqFtHistory_Crosstab.IBX05,
> IIf(IsNull([IBX02]) Or IsNull([IBX03]) Or IsNull([IBX04]) Or
> IsNull([IBX05])
> Or [IBX02]>=[IBSX01],0,IIf([IBX03]>=[IBX02] And [IBX04]>=[IBX03] And
> [IBX05]>=[IBX04],4,0)) AS BonusPoints
> FROM qrySqFtHistory_Crosstab;
>
> I tried using the Column Headings property to simply give the columns
> different headings, but then no data at all showed up. Am I going about
> the
> whole thing all wrong?
> --
> Amy E. Baggott
>
> "I''m going crazy and I''m taking all of you with me!" -- Linda Grayson
>
>
> "Duane Hookom" wrote:
>
>> Assuming IBX02 is generated as a column heading in a crosstab,
>> re-evaluate
>> how you create the column heading expression. I think you can replace the
>> "02","03" etc with a number that is relative to the current year.
>>
>> If you need more help, reply back the with SQL view of your crosstab.
>>
>> --
>> Duane Hookom
>> MS Access MVP
>> --
>>
>> "Amy E. Baggott" <AmyEBaggott@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:CB0A9CCE-5A76-439A-9A47-C552D093EBE2@xxxxxxxxxxxxxxxx
>> > The expression for the bonus points is
>> >
>> > BonusPoints: IIf(IsNull([IBX02]) Or IsNull([IBX03]) Or IsNull([IBX04])
>> > Or
>> > IsNull([IBX05]) Or [IBX02]>=[IBSX01],0,IIf([IBX03]>=[IBX02] And
>> > [IBX04]>=[IBX03] And [IBX05]>=[IBX04],4,0))
>> >
>> > However, the field names for next year will change to IBX02, IBX03,
>> > IBX04,
>> > IBX05, and IBX06. What I'd like to do is somehow set it up so that the
>> > query
>> > doesn't have to be rewritten every year to change the field names
>> > because
>> > the
>> > PITA factor there gets to be huge, plus I want to set this up so that
>> > other
>> > people can run the query besides me.
>> > --
>> > Amy E. Baggott
>> >
>> > "I''m going crazy and I''m taking all of you with me!" -- Linda Grayson
>> >
>> >
>> > "Duane Hookom" wrote:
>> >
>> >> I'm not exactly sure what you want but consider the Orders and
>> >> Customers
>> >> tables in Northwind. This SQL will sum the Freight for the most recent
>> >> 5
>> >> orders for each customer:
>> >>
>> >> SELECT Customers.CustomerID, Customers.CompanyName,
>> >> Sum(Orders.Freight) AS SumOfFreight
>> >> FROM Customers INNER JOIN
>> >> Orders ON Customers.CustomerID = Orders.CustomerID
>> >> WHERE OrderID In (SELECT TOP 5 OrderID
>> >> FROM ORDERS O
>> >> WHERE O.CustomerID=Orders.CustomerID
>> >> ORDER BY OrderDate Desc)
>> >> GROUP BY Customers.CustomerID, CompanyName;
>> >>
>> >>
>> >> --
>> >> Duane Hookom
>> >> MS Access MVP
>> >> --
>> >>
>> >> "Amy E. Baggott" <AmyEBaggott@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
>> >> message
>> >> news:2EBBA24C-A7CF-445C-9D08-A3D4EAAF721E@xxxxxxxxxxxxxxxx
>> >> >I have a crosstab query that lists the exhibitors with their square
>> >> >footage
>> >> > from each show they've been in. I have another query that
>> >> > calculates a
>> >> > point
>> >> > bonus for them based on their square footage in the last five shows.
>> >> > However, this requires me to rewrite the bonus query every year, as
>> >> > the
>> >> > field
>> >> > names will change each year. Is there any way to set this up so I
>> >> > don't
>> >> > have
>> >> > to rewrite the fields (particularly a calculated field) every year?
>> >> > I'm
>> >> > afraid I may forget, and I know that if I'm not here, nobody here
>> >> > would
>> >> > know
>> >> > how to do it.
>> >> > --
>> >> > Amy E. Baggott
>> >> >
>> >> > "I''m going crazy and I''m taking all of you with me!" -- Linda
>> >> > Grayson
>> >>
>> >>
>> >>
>>
>>
>>


.

Relevant Pages

  • Re: Crosstab Query Question
    ... Don't think I ever tried using multiple Column Heading source fields in one ... > crosstab query for each column heading or are you limited to just one? ... >> You do not want an IN list in the PIVOT clause. ...
    (microsoft.public.access.queries)
  • Re: Crosstab Filter Form
    ... Now you have the form set up, close the recordset, and build the crosstab ... Include the column heading names string in the PIVOT clause (just in case ... multi-user scenario.]) Assign this crosstab query string to the RecordSource ...
    (microsoft.public.access.formscoding)
  • Re: Cross Tab Queries
    ... Crosstab: Row Heading Row Heading ... Crosstab: Column Heading ... above query: ...
    (microsoft.public.access.queries)
  • Re: Using Columns in a Crosstab Query as fields in another query
    ... Assuming IBX02 is generated as a column heading in a crosstab, ... how you create the column heading expression. ... reply back the with SQL view of your crosstab. ... > people can run the query besides me. ...
    (microsoft.public.access.queries)
  • Re: Percent calculation based on cross-tab query
    ... I created a new query which drew upon ... Allen Browne - Microsoft MVP. ... A crosstab can have only one Column Heading field, ...
    (microsoft.public.access.queries)