sub-query or two queries needed? (1 Viewer)

[mcalex]

Hi all

I have a couple of linked tables and I'm trying to run a query to put data on a form.

The tables are: Topic(ID, Subject, Detail, etc); Solution(ID, SolnNum, ShortDescrip) which both have 1-many joins with TopicSolution (ID, TopicID, SolutionID). A Topic can only have none, one or two Solutions.

How do I create one query that shows Topic.Subject, Topic.Detail, Topic.etc, Solution a, Solution b (with blanks if either don't exist)? Or do I have to run a second query to find Solutions from TopicSolution using the TopicID found in the original query (which performs a key-word search of Topic.Subject)?

I tried a CT query, but it made too many columns - since there are a large range of Solutions - and I don't know how to make the CrossTab look at the Solutions as generic 'Solution a' and 'Solution b'.

I'm currently going with the second query idea, as I don't know sql well enough to do it in one. Unfortunately, I think I'm gonna have to try and invisibly join two ListBoxes to put the data in one 'table' widget.

Am I making sense? i'm sure this can be done a bit more elegantly than my current attempt. Whether with a sub-query or as a cross tab, or anything less kludgy.

thanks for any assistance or pointers
mcalex

 

[Galaxiom]

I would do it with a subform.
A query on Topics as the Record Source of the main form.

Create another query to join Solutions and and TopicSolution on TopicID. Order the solutions by some suitable field.
Use this query as Record Source for a subform with Master Child Link Fields set as ID.
Set the subform to Continuous Form or DataSheet.

This doesn't designate a solution as A or B but will display as many as are available in the order you entered in the subform Record Source query.

This would be preferable as the system won't be limited to two solutions and the controls will only appear on the subform for the number of solutions available.

 

[mcalex]

Hi Galaxiom

Thanks for that, but can I make the subform appear to be part of the 'row' of data that the query returns. Ie the resultant view should look like:

Subject        |Detail                    |LogDate  |Solution     |Solution         |
--------------------------------------------------------------------------------------
No noise       |No sound coming from unit |12/3/2010|Adj volume   |                 |
No display     |No display on panel       |15/3/2010|Power on     | Inc. brightness |

I think I see what you're saying, though I'm not great with subforms. The two solutions limit is a business rule that (I've been told) won't ever change. Does your way allow me to display the data in for each topic in one row? Giving it a shot, in any case.

cheers
mcalex

 

[Galaxiom]

Subforms can be made to look like they are part of the mainform by turning off all extraneous borders, selectors etc in the subform properties. However they can't directly make it look like you need.

But I think this will work.

Create two queries on an inner join between the Solutions and TopicSolutions tables. Group By the TopicID and select Max(SolutionID) in one and and Min(SolutionID) in the second.

Sometimes you have to drop the other fields and just work on th IDs to get the Group By to do what you want. You can reconnect these other fields later.

Then outer join the Topics table (show all Records from Topics) to both these sets of results.

Then select the solution A and solution B from the max and min fields in their respective tables. Then join back the lost fields if you did have to drop them earlier to get the Group By working properly.

 

[mcalex]

woot! nearly there

only problem now is that when there's only one Solution, it is returned in both the min and max part of the queries - resulting in the following

Subject        |Detail                    |LogDate  |Solution     |Solution         |
--------------------------------------------------------------------------------------
No noise       |No sound coming from unit |12/3/2010|Adj volume   |Adj volume       |
No display     |No display on panel       |15/3/2010|Power on     |Inc. brightness  |

will an IIF statement in the query {select ... iif(minofsid = maxofsid, "", minofsid) ... } work?

 

[Galaxiom]

Sounds like a reasonable idea to me.