Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to create an accommodation availability chart in Access?

Posted on 2014-10-06
6
Medium Priority
?
299 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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 750 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 49

Accepted Solution

by:
Dale Fye earned 750 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 40

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

569 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