gantt reports

[chrisgilbert1]

Hi all,

Ok so im well under way with building my database. But now i seem to have hit a brick wall and i dont know where to go.

The only condition that my database really has to meet is that i must be able to print out a visual report of my rooms that have a bar and label to show when they are occupied. after doing lots of research i think its called a gantt chart.

I have found the example database by duane hookom. The crew rotation schedule seems to be exactly (almost) what im after.

I have been playing around with it now for 3 days, and i just cant get it to work.

I need to be able to see 1 month at a time. i need to see all rooms even if they are not occuppied. Sounds easy?

Well trying this out i found that if my data runs from one month to the next (which it does) it runs into an error. also if the unit isn't occupied it doesn't show it at all.

i managed to get the empty rooms to show but in doing so it splits all the rest so every single booking has a new reference to that room.

Can anyone help with this? I dont mind if i have to do it in excell or anything, but i just have to have that report or my database is a right off?

i have attached a zipped copy of my database. the report is called rep_chart

Thanks in advance (i know i rambled on a bit but, thats how much it stressed me out)

 

[dhookom]

You need a form with a combo box to select the month/year for the report. The value of this control is used in the report's record source query to filter the records. In addition, the records with a start date earlier than the month must be changed using IIf() to the first day of the month. The same must be done with the end date and last day of the month.

Getting all rooms to show involves an outer join query that would include all of the records from your rooms table.

Duane
Hook'D on Access
MS Access MVP

 

[chrisgilbert1]

hi duane

thanks for this (sorry for emailing you directly over this one, but i would never have found this site otherwise)

I think its the query part of it that is causing me problems. As i can make it show the entire list of rooms in the query. But when i do i have errors generated from the start and end dates being empty. I have then changed the code to check for empty cells, but for some reason the rooms no longer match up. so i end up with to lines for the same room and another strange one not all the empty rooms are displayed still, only the first one in the list.

any ideas?

 

[dhookom]

Chris,
I'm not sure how much time I have to look at your issue since I am traveling later today through Friday. If you post some significant information, someone else might be able to build the query.
Provide at least:
[ul]
[li]SQL view of query[/li]
[li]Significant table and field names with relationships[/li]
[li]Form and combo box name with sample values like #12/1/2012#, #1/1/2013# etc. Keep in mind queries require m/d/y format[/li]
[/ul]


Duane
Hook'D on Access
MS Access MVP

 

[chrisgilbert1]

ahhh ok, no worries. i have posted a zipped version of the database for now. Ill continue to try it and see how i get on, ill let you know.

chris

 

[chrisgilbert1]

hi, just thought id post a quick update.

I have managed to get the report to show the empty units. and they are being put in the right places.

But now i need to work on making it only show the current month (selected month). I think ill need to do this from the query itself rather than the report. and thenm create a form afterwards to smarten it up.

i have posted my current query sql code below. Does anyone know how i can make it only show booking arrivals and departures (so if any part of there stay falls within the selected month) and still show me the units that are empty for the selected period?

SELECT Accommodations.PitchNumber, [type2] & " - " & [pitchnumber] AS Pitchname, [Arrival] & "-" AS Expr1, Bookings.Departure, Customers.CustomerID, Customers.Name, [name] & " - " & [CustomerID] AS FamNo, IIf([Expr1]="-","-",[arrival]) AS arr, IIf([Expr1]="-","-",[departure]) AS dep, IIf([Expr1]="-","NO BOOKINGS",[FamNo]) AS FN, Bookings.Arrival
FROM UnitTypes INNER JOIN (Customers RIGHT JOIN (Accommodations LEFT JOIN Bookings ON Accommodations.AccomID = Bookings.Unit) ON Customers.CustomerID = Bookings.CustomerID2) ON UnitTypes.ID = Accommodations.Type
ORDER BY [type2] & " - " & [pitchnumber];