Solved

How to create an accommodation availability chart in Access?

Posted on 2014-10-06
6
209 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
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.

 

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 34

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

912 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now