access chart using a form !?!? 1

[gibblin]

Hopefully someone will be able to help with this.....

I'm creating a database where we can record holiday/sickness etc of staff. I've created a simple table & form which inputs their 'name', 'type' and 'date'. However i wanted to create a chart on a form, with the dates as columns and the names as the rows and the type of absence as the data on the chart.

i.e. D D1 D2 D3 D4 D5
name1 x y
name2 z y
name3 x y z

There are several problems, one is showing all the dates between 2 pre-determined points (not just the ones in the input table). Similarly showing the blank dates (when the employees aren't absent) and also showing all the employees as a list which would be variable (due to new staff, leavers etc).

I've seen a thread similar to this before thread702-564439, but am not able to access the site/database by billpower. But maybe someone has a solution anyway, if anyone can help i would be grateful.

 

[GingerR]

I have a table with all dates listed in it, which I use throughout all of my databases for reasons like this. It's easy to make in Excel and import into access. Your problem may be that you want to exlude weekends and holidays, etc. However you choose to do it, you'll have to create a date table with the dates you want to show. call it tblDates.

Then you'd make a query, with no join, using the new table tblDates and a table which has a unique list of employee names/ID's. the criteria put a reference to your date range (maybe taking them off of a form?). The result will be a list of every date/every employee combination.

Then you'd use THIS query in your final query, with joins on the Date field and EmpID fields, pointing towards the Date and EmpID fields in your table. The date and EmpID fields you'll bring into this final query will be from the new query you made above. So the final result will be every employee and date combination, regardless of if there is data or not for that particular combination.

Let us know how it goes!

g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[gibblin]

Thank u very much, it worked a treat. Thanks for your quick response too.

My only problem now is how to present this data as a chart, ideally on a form where the types of absence are colour coded. Or am i just over-complicating this! any ideas are most welcome!

 

[GingerR]

What have you tried so far? I suggest you try making a chart on your own, and if you need help, start a new post since your topic will be different than this one.

Have fun!


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244