How to avoid repetition?

[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?

 

[RickSpr]

I assume what you want is the model name, followed by a list of its capabilities and another list of its drawbacks. You should do this with a main form (model name) and two subforms (capabilities and drawbacks).

If you join the three tables in a single query, you're asking for every possible combination of capability and drawback for each model name. That's just the way relational joins work.

The form solution will separately query the two child tables and display the results in separate lists. You don't get combinations because the subforms query their record sources separately, using the link field (model id) as a search criterion. Rick Sprague

 

[psh]

Thanks for the reply. I knew I could count on you folks out there.

I have a follow up question. I would like to be able to do what you have suggested in a report, rather than a form. How do I do it in a report, and can you please walk me through? Thanks ahead.

psh

 

[LarryDeLaruelle]

PSH:

Have you tried the Sorting and Grouping option (under view)?

Set Model for Grouping, enable Group Header and put the Model in the Group Header Band; put the capabilities and drawbacks in the Detail Band .

Larry De Laruelle
larry1de@yahoo.com

 

[psh]

Thanks for the reply Larry.

I tried it. It works, but only partly. Upon three level of grouping - model, capability and drawback, I still get the drawbacks repeated for every one of the capability. Ofcourse, the repetition in Model and Capability is no longer there.

I wonder if there is some kind of programming required to achieve the result I am looking for. I am a beginner and will take a very long time to do it, and I don't have that kind of time. If any of you are good at programming, and can help me, I will very much appreicate your help.

psh

 

[RickSpr]

You don't need any programming. You just need a main report with the model name, and two tiny reports to include on the main report as subreports. Each of the reports uses one of your tables as a Record Source. You set up the model id as your link field for each child. It's basic stuff, harder to describe than it is to do. You could even use a report wizard to set up one subreport, then use what it did as a guide to setting up the other. Rick Sprague

 

[psh]

Thanks Rick.

The technique you suggested works, but is a little cumbersome. At this time it is the better option I have and I have decided to go with it.

Thanks again.