Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2008

    Unanswered: Can I use a select query within a crosstab to display a bookings calendar?


    I'm sorry if this is a stupid question. I'm away from my copy of Access and can't check if it works.

    What I'd like to know is this: I've just started working with crosstab queries. The information I've found so far says that each cell in the query performs a calculation using the row value and the column value as parameters. Rather than a calculation, can each cell contain a Select query which searches a table of the database using the row heading and column heading as its criteria?

    Here's the situation: I'm helping a friend build a database for a cattery. The client wants an at-a-glance view of which pens are in use on each day over the upcoming week and which cats are in them. Ideally what would be good is a datasheet with the dates of the upcoming week as column headings, and the numbers of the pens (1-12) as row headings. I'd like each cell of the crosstab to run a Select query which would be something like:

    SELECT CatName FROM Bookings (the relevant table) WHERE Bookings.PenNo = (that row's reference) AND Bookings.StartDate <= (the date at the head of that column) < Bookings.EndDate

    So each cell of the crosstab would display the name of the cat occupying that pen on that date.

    Does this sound like it might be possible? And is it possible to do it without using VBA code? I'd be prepared to try it but my friend is code-phobic.

    Any help much appreciated! Thank you.

  2. #2
    Join Date
    Feb 2004
    New Zealand
    Provided Answers: 10
    one of the hardest thing to do with Crosstab queries work with the coloum name IE in reports forms ....
    also crosstab queries don't like reading other query that have where statement in them
    bugger what do we do.

    my work a run to a lot og thinking

    1 work out what you want as the colum header tobe (P1 P2 ... ) make the Query up
    2 know make a Make Table Query base on the above qurey
    3 KNOW do the crosstab query but in the feild Properties of the Coloum you can make the coloum heading P1,p2,p3,p4,p5,...... which is good as if there is on data the coloums still there and the report form don't error.

    4 how do run all together


    Docmd.setwarning False ' turn off warning
    Docmd.openQuery "THE MAKE TABLE ONE"
    Docmd.setwarning Ture ' turn back on

    hope this help

    See clear as mud

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008

  3. #3
    Join Date
    Apr 2008
    Thanks Myle! I didn't realise crosstabs didn't like Where statements. That explains a few things.

    I've now got the crosstab to display the cats' ID numbers cross-referenced by pen number and the date their stay begins. What I'd really like is for it to display the cat's name, but I can't seem to make it display the contents of a text field rather than a numerical value. Is there a simple way to do this?


  4. #4
    Join Date
    Nov 2007
    Adelaide, South Australia
    Yeap. You just need to use EXPRESSION in the Total row of the Value column.

    Use an expression like CatName: [CatsName] in the Field row.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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