Solved

How to create an accommodation availability chart in Access?

Posted on 2014-10-06
6
230 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 36

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

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