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.
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
Docmd.openReport "THE REPORT THAT READING THE CROSSTAB QUERY
hope this help
See clear as mud
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 MYLE YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
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?