This one has kept me busy the past two days!

[weeze2]

I have to make a report that shows an overview of the holiday, other activities and absence in the depertment.
So far I have a report that has numbers 1 - 31(depending on the month) horizontally on top of the page. A red cross is made under a "day" represented by the numbers of the month to show that one has holiday.(I used VB for this)
I have a table for holiday that contains a record for every time a person has holiday-it contains the fiels: ID,perID(id from person table), From(date) and To(date).
If I(myself) have one week holiday here and then one week later( in other words: more than one record in the holiday table), I show up twice on the report. Thus my problem is that a person may show up as many times as he/she has holiday. There must be a way around this?????????

*********************************************************
HOW can i show the report that theer only apears one instance of a person on the page but with all the holiday?
*********************************************************
It should look like this:

1 2 3 4 5 6 7 8 9 10 11 12 13 14....
"my name" X X X X X X X
"next name" X X X


INSTEAD OF THIS:


1 2 3 4 5 6 7 8 9 10 11 12 13 14....
"my name" X X X
"my name" X X X X
"next name" X X X

*******************************************************

 

[mp9]

You should be able to produce the format you need using the Crosstab Query Wizard. Then create a report based on your query using a report wizard - this will maybe give you the required output?

 

[cmmrfrds]

On an equal join i.e. id1=id2, the select will return a record for each match in both tables. Depending on what you have in the select list, the DISTINCT clause can reduce the number of rows.
select distinct a, b from tablea inner join tableb on tablea.id = tableb.id

As long as a and b in the above select list are the same value in all rows then only 1 row will be returned in the results.