Crosstab query using dates

[guinness]

I'll probably have a hundred questions following on from this one. What I'm hoping is that someone has maybe tried this before and has an example that I could reverse engineer.

I want to create a cosstab query with dates for the next 12 months accross the top as columns and employee names down the side as rows. I then want to populate with data showing what each person is programed to do under the dates. For instance trainer 1 is delivering training from the 23/06-30/06 then designing a course from 02/07-10/07. that type of thing.

Am I being too ambitious? It would also be helpful if I could represent an activity using a colour i.e. yellow for delivery, green for design etc.

Any pointers or examples greatly recieved. Also do I need to create a table with all the dates? Is there a quick way of doing this?

Cheers

Kev

 

[CazB]

You might be better having the dates as the rows and the employees as the columns.... just think about how many columns you would need to represent just 3 months of dates!

I have a form in my database that might do what you're after, but it all depend on the structure of your current database and how good you are with coding as to whether it would be any use to you or not

I'll see if I can strip it out and show an example... bear with me!

 

[guinness]

Thanks Caz

I wanted the dates as columns as I intend to have the column width tiny and have a scrollbar along the bottom. Provided I get it to open at the present date then I hope to see the activities of about 30 folk for about a quarter at a time.

I hope that makes sense. I do it with a spreadsheet just now but am hoping that by using Access I can peerform calculations and produce some reports on the scheduling for my team

 

[CazB]

Ah ok, the solution I'm using won't work for you then.

Having said that, you will need to bear in mind a potential problem with a cross tab for the dates along the top: it doesn't like to show headings that don't have any 'data' against them - so unless you have something on every day, there may be dates missing in your scheduling....

Good luck!

 

[plog]

Yes, you are being overly ambitious. The most columns a query can have is 256. So, a full year is out of the question. Also, so is a report with just week days (~261 week days per year). I haven't done the math, but if you kick out holidays (depending on how liberal you are in counting holidays), you might get there, but its going to be close and it might not work for all years (e.g. Christmas on a Saturday).

So, your best bet may be to spit out the data from Access to an Excel file and just pivot from there.

 

[guinness]

Thanks Plog. It's frustrating when you know what you want to do but lack the skills to do it. I think you are right and I may be being overly ambitious.

My next thinking would be to use a form for input that would update a master table and then have the master table update an excel spreadsheet. The thing is I would like them to be able to view the spreadsheet wiwthin a form and have the spreadsheet update as soon as new data is entered. Does that sound possible?

 

[plog]

Not with anything I've done. I don't think you can embed a spreadsheet in a form. Why not just give them a 6 month or 3 month forecast instead of a year?

 

[guinness]

Thanks Plog.

I'm still kind of working between this and excel. I'd rather use acces as it's more stable but excel seems more inclined to give me the kind of out put I want. Six months could work provided it could be a rolloing six months i.e in January have the default display between December and May, In February between January and June and so on. Also with a filter to change the dates if Necessary so you could look further back or further forward.

I've seen similar off the shelf solutions but I really wanted something bespoke.

Thanks for putting your mind to it though. Depending how I get on I may be back to this idea.