Eliminating Repeats in Reports - New to Access

theformula

New member
Local time
Yesterday, 17:13
Joined
Nov 28, 2008
Messages
6
Hi folks. I am very new to Access, so please forgive me...I am learning :)

I am trying to manipulate a database that tracks government regulations, and their applicability to certain things around the building. For example, fire exits. Fire exits is assidned a number within the database. There is a table that houses all of the government acts and regulations that are applicable to fire exits. We have buildings cross Canada, and so the regulations vary from province to province. So for fire exits, 3 different Acts that are applicable, with 10 different regulations underneath each Act, which are also specific to provinces (these are all stored as their own record within the table ie: 001, Fire Exits, Act, Regulation, Province - all one record.

I have a report that shows all of this information, that can be filtered by province. So lets say I pull a report on the province of ontario. It will give me the item ID (ie: 001 = Fire Exits) and it will show me all the acts that point to that ID. However, the problem is, in the database there are several regulations behind each act. So when I run the report, it will list a bunch of repeats that show the same info, even though I have not asked to see the regulations. Like this:

001 - Fire Exits Ontario Fire Act
001 - Fire Exits Ontario Fire Act
001 - Fire Exits Ontario Fire Act
001 - Fire Exits Canada Fire Act
001 - Fire Exits Canada Fire Act

If I wanted to see each regulation, then this would be useful, I could add in the regulation field, and each line in the report would serve a purpose. But I only want to see the Act. So in this case, I want to get rid of all of those other duplicates.

I am sorry, I am very bad at explaining all of this...which comes back to my lack of Access knowledge. Hoping something can decypher what I am trying to say ;)

Thanks for your help.

Warren
 
In the SQL statement for the query for the report you could add Distinct after the SELECT
 
Thanks for the reply Keith.

When I added 'Distinct' to the query, and try to run the report, I get the following error:

Can't include Memo or OLE Object when you select unique values ([Data Question Details].TASK)

The last bit being a table, and a field within that table.
 
What data type is the task field? I looks like memo, can you change it to text? You should be able to as long as the values don't exceed 255 characters.
 
Some of the 'tasks' in the task field are more than 255 characters, and cannot be reduced any further. any ideas?

Thanks again Keith! appreciate your help.
 

Users who are viewing this thread

Back
Top Bottom