Creating Report from Query **HELP**

[James M]

Hi,



I'm hoping someone will be able to help with a report design issue I've been having all day long. I've researched and researched and haven't overcome any hurdles!!

I have the following query setup which gives me the data I need:

SELECT DISTINCT Issues.User, Issues.[High Focus], Issues.Summary, [BAU Activity].[BAU Activity]
FROM Issues INNER JOIN [BAU Activity] ON Issues.User = [BAU Activity].User
WHERE (((Issues.[High Focus])="Yes"));



The query produces duplicates even though I've stipulated unique values only, I think it's something to do with the DISTINCT query, I don't think I'm being specific enough...

I've attached an example of the DB and the report I want to be able to produce.

I hope someone can assist

Cheers,
James

[June7]

I don't understand data relationships. What is BAU? What is purpose of this db?

There are no duplicate records in the query. Every record is unique. The DISTINCT qualifier has no impact. The query is behaving correctly. The only linking is on userID. Every record for each userID in each table must join to every record with the same userID in the other table.

[James M]

Hi June,

Thanks for explaining the behaviors of the DISTINCT query, I now understand. My next question is how to remove the duplicate lines from the Summary column after execution of the query.

[June7]

I might be able to help if you would answer the two questions I posed.

There are no duplicate records in the query. The values in the Summary field are repeated, but the records are unique.

You need to provide info about the data. The relationships as set up don't make sense to me.

[James M]

The 'Issues' table is a repository for all issues raised by the user.

The 'BAU Activity' Table is everyday tasks that the user performs.

What I want to show in a table, like the excel doco I uploaded is a view of how long the Issues that are set to 'High Focus' have been assigned to the user in months and also to show the BAU activities the user performs.

I'm open to redesigning how the data is displayed.

Thanks for your help!!

James

[June7]

How do Issues relate to Activities? Are these activities conducted in response to dealing with issues? Do you want to document what activities were done to handle each issue?

If there is no connection between Activities and Issues then cannot include in the same query.

Use subreports to display the different data on one report.

If activities should be related to issues, data structure is wrong.

[James M]

Hi June,

There's no relation between Issues and Activities.

The subreport option is exactly what I need, i'll give that a go and let you know the results.

Thank you very much for responding so quick and helping me out, fingers crossed I come back with good news!!