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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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)
  • How to do a crosstab in SQL
    ... Thus we could not easily write this query with static SQL; ... The crosstab used this code ... Key to using this query on your own data is to "pull out" the underlying ... DECLARE looper CURSOR FAST_FORWARD FOR select distinct txtLIType from ...
    (microsoft.public.sqlserver.server)
  • Re: Crosstab query with date problem
    ... I wouldn't have posted the suggestion if you couldn't use it in an Access ... You already said you're thinking of using a Crosstab - something ... the convert or date/time functions in an Access query. ... I expect your SQL will look something ...
    (microsoft.public.access.queries)
  • Re: Crosstab query with date problem
    ... John's expression can be used in a query. ... To see examples of crosstab reports, ... > Yeah but can I do it directly in my SQL query? ... >>> I am trying to do a report showing case files sent a certain number of ...
    (microsoft.public.access.queries)