How to create an accommodation availability chart in Access?

I am creating an accommodation rental database.
How can I include an availability chart like in the picture below?
Can it be done in Access?
Should I link to or embed Excel or Outlook?
I am using Access 2010.
Availability chart
Fritz PaulAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm not sure you could do that in Access, since there's a column limit of 255, which you would run into after a few "months" worth of data (and I'm sure you'd eventually need more than that).

You could embed an excel workbook into an Access form, but if you're going to do that, just use Excel.

What you really need is a grid, and Access doesn't really have anything like that. There are some 3rd party grids out there which claim to work with Access :

http://10tec.com/articles/ms-access-grid-control.aspx
http://www.bennet-tec.com/btProducts/TList/TList.htm

I've tried both, and they work pretty well. I've never actually created an application with them, since I just never needed that sort of functionality in an access app.
0
Dale FyeCommented:
Depends on the purpose of your chart.

If you are really just using it to determine whether you have a facility available during a specific period, then I would require the user to enter start and end dates and run a query which returns the accommodations that are available during that period.

It is actually relatively simple to generate that type of output in Excel, from within Access, so if you want to print that chart out, then, I would generate it from Access.

You could also create a form that is based on a CrossTab query which would allow you to do something quite similar to the chart depicted, within Access.  However, this is not trivial, and does bump into the 255 column limit that Scott mentions above.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
Just a note here.
(No points wanted)
Should I link to or embed Excel or Outlook?
At first glance, Outlook would appear to be a good choice because the exact "Gantt" look it would give you.
(Scheduling Assistant)
screen*However* it would be much too cumbersome to get this to work.
For example:
1. Each of your "Properties" would need to be separate Outlook "users"
2. You would not have any of the "Property Groupings" you are looking for
...etc

JeffCoachman
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Fritz PaulAuthor Commented:
Hi Jeff,
Yes that is precisely what I need.
It will surely not be trivial and I will need a lot of help.
In the mean time I suppose I will do the calculations in Access and export tables to Excel for the visual mapping. All I need is to show visually where accommodation is available and where not.

Hi Dale,
I think I will be using Excel as discussed in your second and third paragraphs.

Hi Scott,
Thanks, Interesting!
0
PatHartmanCommented:
It is not just tables that are limited to 255 columns.  Queries are also limited so your export to Excel will need to use OLE automation.  You won't be able to use TransferSpreadsheet or link to Excel.   Forms/Reports have a maximum width so you will run into that also and probably before you run into the column limit.

If you create the interface in Access (and you can), you need to use a sliding technique where only a period of data is visible at one time.  Eight months would be the maximum possible but you might limit it to two months so the controls are bigger.  You would have to scroll left and right one month or week at a time.

I should warn you that all that conditional formatting to colorize the controls will make the form sluggish and make it flicker.  So, the fact that you can do this in Access doesn't mean you should.
0
Fritz PaulAuthor Commented:
Thanks for the support.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.