Solved

How to create an accommodation availability chart in Access?

Posted on 2014-10-06
6
222 Views
Last Modified: 2014-10-07
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
0
Comment
Question by:Fritz Paul
6 Comments
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 40365592
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
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 250 total points
ID: 40365626
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40366551
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Fritz Paul
ID: 40367197
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
 
LVL 35

Expert Comment

by:PatHartman
ID: 40367257
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
 

Author Closing Comment

by:Fritz Paul
ID: 40367650
Thanks for the support.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question