Avoid repetition in report

[psh]

Problem: A model has 'n' drawbacks and 'm' capabilities. There are three tables(models, capabilities and drawbacks).
Table description:
models (model_id(pk), model_name)
capabilities ([model_id, cap_id](pk),capability)
drawbacks ([model_id, draw_id](pk),drawback)
Relationship:
model-id of tableModels is related to model_id of tableCapabilities and tableDrawbacks.
Query: i am querying for a model's capabilities and drawbacks as follows:
SELECT models.model, capabilities.capability, drawbacks.drawback
FROM (models INNER JOIN capabilities ON models.model_id = capabilities.model_id) INNER JOIN drawbacks ON models.model_id = drawbacks.model_id

The query yilds correct result, though. That, is not my problem.
My problem is: How do I avoid repetition? Eg. Model "A" has a,b and c (i.e., 3) capabilities while having p,q,r,s and t (i.e., 5) drawbacks.
The above query yields for every capability all the fie drawbacks. Not to mention that the model name will also repeat. Thus I will end up with (3x5=)15 rows of information.
The information as displayed poses a challenge to the reader in order for him/her to arrive at a meaningful conclusion.

How do I avoid that repetion?

 

[jimmythegeek]

Use group headers and footers. Create a Model header, and underneath that, a capabilities header. Put your model field in the model header, and the capabilities field in the capabilities header. It will then look like the following:


Model 1
Capability 1
Drawback 1
Drawback 2
Drawback 3
etc.
Capability 2
Drawback 1
Drawback 2
etc.
Capability 3
etc.
Model 2

Of course you can put lines and make text bold and stuff to seperate headers and such.
Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[psh]

Jim,
Thanks for taking the time to reply my question. So far, I have been getting my repors the way you have described above. I want to eliminate the repetition in drawback, also.

Simply putI want the report to look like how a lay folk would write in a spreadsheet, say - Column1 models name, Column2 a list of its capabilities and Column3 a list of its drawbacks.

Does it take any programming skills to achieve this result?

psh

 

[jimmythegeek]

Are you talking about a Cross Tab query?

If the query behind the report laid out above is correct, you should not be getting any duplicate drawbacks.

Post the SQL from the query behind the report so we can take a glance at it. Jim Lunde
compugeeks@hotmail.com
Custom Application Development