Duplicates in spreadsheet style report (1 Viewer)

[Fasopus]

I have a spreadsheet style report that acts as a calendar. It works fine and retreives all the data I want (vacation days taken) for each person, however if somone takes more than one vacation day in a week (the report only shows the data for a selected week) they will show up on two rows. I know this is because each vacation day taken counts as a seperate record in the table, but how do I stop the duplicate rows from occuring?

 

[Fasopus]

So I've discovered the HideDuplicates property, however when I enable it it doesnt work at all

EDIT: I mean the property does nothing, the report looks exactly the same

 

[vbaInet]

Look into using the DISTINCT clause in the sql of your report's query.

http://www.techonthenet.com/sql/distinct.php

 

[Fasopus]

Thanks a ton for the help! How exactly would this work with selecting multiple fields, for example: only one field is distinct, the other fields can all contain duplicates

 

[vbaInet]

Could you give me an example of the records displayed and your desired output?

 

[Fasopus]

Sure, First Ill post my SQL:

SELECT Vacation.Login, Vacation.Type, Vacation.VDate, Vacation.Memo
FROM (Vacation INNER JOIN [Warehouse Info] ON Vacation.Login = [Warehouse Info].Alias) INNER JOIN [Alias Fullname] ON Vacation.Login = [Alias Fullname].Alias
WHERE (((Vacation.VDate)>=[Forms]![Vacation Details]![Text19] And (Vacation.VDate)<=([Forms]![Vacation Details]![Text19]+6)))
GROUP BY Vacation.Type, Vacation.VDate, Vacation.Memo, Vacation.Login;


So the vacation table has the Login, Type, VDate and Memo fields in it. What i'm looking to do is retrieve the unique logins with the rest of the data in the record. Basically every other field can contain duplicates but since the data is for another person it doesnt matter

 

[Fasopus]

After some further consideration, I think I should be able to make it work with DLookups and other functions if I can just get the Login field to be unique, even without the type field inside of the query


EDIT: Aha! After tinkering with my Query and using some DLookups I got it working exactly as intended, thanks again for the help!

 

[vbaInet]

Ok, notice something in your query. Since you're not using any of the fields from [Warehouse Info] table, get rid of it. That's what's causing the duplicates. Your query should now be:

SELECT Login, Type, VDate, Memo
FROM Vacation 
WHERE ((VDate>=[Forms]![Vacation Details]![Text19] And VDate<=[Forms]![Vacation Details]![Text19]+6))
GROUP BY Type, VDate, Memo, Login;

Also, change these two field names Type and Memo to something else because they are resserved for Access and VBA. You will encounter problems in the future if you don't.