Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    parameter in crosstab query (A2002)

    I've got a crosstab query set up that displays consumers by age classification and gender. Now I've found out I need that information to be program specific. When I try to include a parameter allowing me to specify the program in the crosstab, the query won't run. Tried putting the parameter in a subquery...get the same error message when I try to run the crosstab.

    I don't want to have to create a crosstab query for each program. Anyone got any ideas?

    Elizabeth

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: parameter in crosstab query (A2002)

    It would help if you told us what error message you were getting.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: parameter in crosstab query (A2002)

    Here are some specifics...

    Here's the original crosstab:

    TRANSFORM Count(tblConsumers.ConsumerID) AS CountOfConsumerID
    SELECT tblConsumers.AgeRangeID, tlkpAgeRanges.Range, Count(tblConsumers.ConsumerID) AS Total
    FROM qsubDescriptorDates INNER JOIN (tlkpGender INNER JOIN (tlkpAgeRanges INNER JOIN tblConsumers ON (tlkpAgeRanges.AgeRangeID = tblConsumers.AgeRangeID) AND (tlkpAgeRanges.AgeRangeID = tblConsumers.AgeRangeID)) ON tlkpGender.GenderID = tblConsumers.GenderID) ON qsubDescriptorDates.ConsumerID = tblConsumers.ConsumerID
    GROUP BY tblConsumers.AgeRangeID, tlkpAgeRanges.Range
    ORDER BY tblConsumers.AgeRangeID
    PIVOT tlkpGender.Gender;

    Here's the original subquery:
    PARAMETERS [enter start date] DateTime, [enter end date] DateTime;
    SELECT DISTINCT tblConsProg.ConsumerID
    FROM tblConsumers INNER JOIN tblConsProg ON tblConsumers.ConsumerID = tblConsProg.ConsumerID
    WHERE (((tblConsProg.ExitDate) Between [enter start date] And [enter end date])) OR (((tblConsProg.CloseStaffDate) Between [enter start date] And [enter end date]));

    In addition to the date parameters in the subquery I tried to add a program parameter:
    PARAMETERS [enter start date] DateTime, [enter end date] DateTime;
    PARAMETERS [enter start date] DateTime, [enter end date] DateTime;
    SELECT DISTINCT tblConsProg.ConsumerID, tlkpProg.ProgramName
    FROM tblConsumers INNER JOIN (tblConsProg INNER JOIN tlkpProg ON tblConsProg.ProgramID = tlkpProg.ProgramID) ON tblConsumers.ConsumerID = tblConsProg.ConsumerID
    WHERE (((tlkpProg.ProgramName)=[enter program]) AND ((tblConsProg.ExitDate) Between [enter start date] And [enter end date])) OR (((tlkpProg.ProgramName)=[enter program]) AND ((tblConsProg.CloseStaffDate) Between [enter start date] And [enter end date]));

    When I tried to run it here's the error I received:
    The Microsoft Jet database engine does not recognize "[enter program]" as a valid field name or expression.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: parameter in crosstab query (A2002)

    Why don't you add [enter program] as a Parameter and see what happens.

    PARAMETERS [enter start date] DateTime, [enter end date] DateTime, [enter program] Text;

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: parameter in crosstab query (A2002)

    Eureka! You have found it!
    My reliance on the design grid and relative ignorance of SQL really hangs me up sometimes. Thanks for the bailout!

    Elizabeth

  6. #6
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: parameter in crosstab query (A2002)

    I

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: parameter in crosstab query (A2002)

    Change your WHERE clause to:

    WHERE (((tlkpProg.ProgramName) like [enter program] & "*") AND ((tblConsProg.ExitDate) Between [enter start date] And [enter end date])) OR (((tlkpProg.ProgramName) like [enter program] & "*") AND ((tblConsProg.CloseStaffDate) Between [enter start date] And [enter end date]));

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: parameter in crosstab query (A2002)

    If you have a crosstab query with parameters, the number of columns in the result will vary. Your report, however, has fixed columns. If one of the columns defined in the report doesn't occur in the result of the crosstab query, you will get the error you describe.

    There are two ways around this: fix the columns in the crosstab query, or make the columns in the report variable. I think that the first option is what you want, but it's up to you to decide.

    1. You can make the crosstab query return a fixed set of columns by setting the Column Headings property of the query. You can do this in the query design grid view - click in an empty part of the upper half of the query window, and enter the comma-separated list of column headings in the Properties window. You can also modify the SQL statement directly, like this:

    ...
    PIVOT tlkpWkHistory.WorkHistoryCode IN ("Code1","Code2","Code3");

    This would display only columns Code1, Code2 and Code3 in the result, regardless of whether they contain data (columns without data will contain null values). In your case, you could set the Column Headings to those you have in the report.

    2. You can create code to make a crosstab report dynamic - the column headings are filled when the report is opened. If you want to investigate this option, do a seach in this forum for "dynamic crosstab report".

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: parameter in crosstab query (A2002)

    <blockquote><hr>It seems that adding the third parameter has created instances when one of the columns (None Given) has data and sometimes it doesn

  10. #10
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: parameter in crosstab query (A2002)

    Much thanks. I decided to go with the first option.

    I'm in the process of making the changes now. I'm at one down with four more to go.

    Your information is wonderfully clear and precise; if only the help files could have been written as well!

    esw

  11. #11
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: parameter in crosstab query (A2002)

    Hi E

    If you want to try Hans suggestion # 2

    There is an excellent CrossTabReport.mdb demo at

    www.rogersaccesslibrary.com

    HTH

    John

  12. #12
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: parameter in crosstab query (A2002)

    What a treasure trove for a relative newbie! Lovin' it.

    Elizabeth

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •