Q: Appending crosstab query having dynamic variables

From: MarkD (anonymous_at_discussions.microsoft.com)
Date: 04/29/04 

Date: Thu, 29 Apr 2004 13:40:08 -0700

Using Access 2000.

Hi,

I have a crosstab query that has a dynamic variable:

PARAMETERS [age] Short;
TRANSFORM Sum(IIf(DateDiff("m",[datefirstprrun],
[maxoftrans_dt])>=[age],1,0)) AS Expr1
SELECT [Q Raw].Region, [Q Raw].Name1, [age] AS MonthsOld
FROM [Q Raw]
GROUP BY [Q Raw].Region, [Q Raw].Name1, [age]
PIVOT DateDiff("m",#1/1/2003#,[datefirstprrun]);

[Q Raw] is just a query, and [age] is a variable ranging
from 1-24.

I want to run the above query 24 times, appending the
results into one giant historical table, changing the
[age] parameter from 1-24. I'd like to run this in code
and just have a loop run through 1-24. What I don't know
how to do is pass the variable to the query. I thought I
could just pass the whole query (something like "insert
into [big table] select [xtab query]") but don't really
know the syntax.

Hopefully that was clear. Any ideas?

In advnace, thanks.
-Mark

Relevant Pages

  • RE: IIf statement vs Select Case statement
    ... in a VBA function which the query calls. ... To create a function called AgeGroup go to the VBA window by pressing Ctrl+G ... parentheses so it accepts the age value. ... so in Access you can't count individual patients this ...
    (microsoft.public.access.queries)
  • Re: IIf statement vs Select Case statement
    ... To compute the age from the date of birth you can add a simple function like ... You can call this in a query once you've added it to a module in your ... get a distinct count of the patients. ... which would allow you to get the age groups by joining ...
    (microsoft.public.access.queries)
  • Re: formatting a calculated number field on a form
    ... I calculate 4 "ages": age at surgery, age at heart attack, age at ... This data could easily be tracked as a flat-file in Excel, ... >INSERT INTO Test (key_col, DoB, DoD) ... >Query object and save it manually: ...
    (microsoft.public.access.forms)
  • Re: next record
    ... If SSN is the primary key of Demographics, ... > have the age calculated. ... Is there some reason you can't send the results of a query to the Palm ... I don't know about exporting to a Palm Pilot, ...
    (microsoft.public.access.forms)
  • RE: Query return value is Null
    ... Age Gender Age ... Since you're using a Totals query (a query that uses ... be any display of data at all. ... Dim Rst As DAO.Recordset, strMsg As String ...
    (microsoft.public.access.queries)