gantt report?

[upeenarce]

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?

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

 

[JHB]

Could you provide some sample data, and how you want the results looks like, (picture)?

3 days is not so long, a "cheese" take the time a cheese takes, but of course if no progress has so ....

 

[upeenarce]

it feels like forever now though.

my query SQL code is:

SELECT Accommodations.PitchNumber, [PitchNumber] & " - " & [type2] AS Expr1, [Arrival] & "-" AS Expr3, Bookings.Departure, [Name] & " - " & [CustomerID2] AS Expr2, IIf([expr3]="-","-",[arrival]) AS arr, IIf([expr3]="-","-",[departure]) AS dep, 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;

im basically trying to do a version of duane hookoms crew rotation schedule.

access.hookom.net/Images/GanttSchedule.jpg&sa=X&ei=yvjNUJeUPOXL0AX0jYCwBg&ved=0CAkQ8wc&usg=AFQjCNF4ZjMBNFk_LK6s0zzNoEgQN7IvFg

I have altered the join type in the query so it shows all pitch numbers even if they are empty, but the code in the actual "on Detail Part" doesn't like an empty value on the report. so itried checking for them but nothing worked. so i again changed the query to fill empty slots with a dash "-" and then tried to change the code to filter them out. but nothing seems to work. what am i missing?