Query problem 1

[Domino2]

I have two tables, related. I have two listboxes. The first listbox I want to list primary records that have cild records existing in the second related table.

In other words, Listbox 1 will show Company A, with it's primary key, and the second listbox will show related records to the selected company A.

My listbox rowsource for list 1 is:
SELECT DISTINCT PARTNERS.ID1, PARTNERS.Partner, ACTIVITIES.[Task Description]
FROM PARTNERS INNER JOIN ACTIVITIES ON PARTNERS.ID1 = ACTIVITIES.ID1
WHERE (((ACTIVITIES.[Task Description])>""))
ORDER BY PARTNERS.Partner;

However it shows the company (Partner) as many times in list 1 as there are records in list 2, instead of just once.

Hope thats not confusing. So eg Lisbox 1 shows one company, Listbox 2 shows 6 related records to that company.

Many thanks

 

[kjv1611]

If you just want to show the Company (Partner), then leave out Activities in the SELECT list.

So:

SELECT p.ID1, p.Partner
FROM PARTNERS p INNER JOIN ACTIVITIES a ON p.ID1 = a.ID1
WHERE (((LEN(a.[Task Description]))>0))
GROUP BY p.Partner
ORDER BY p.Partner;

Also, I'd use Group by rather than DISTINCT - at least I tend to get better results that way - or else I've just gotten used to it.

I also changed your WHERE clause. Still not sure it's correct. Really depends upon the data. If it doesn't work correctly, you may need to change it to:

WHERE (((a.[Task Description]) IS NULL))

You may have some quirks in the code as well.

Also, are you wanting each list to reflect the changes in the other list? If so, you will likely need some VBA code in the _onchange event of each list that will change the rowsource of the other.

 

[Domino2]

Thanks, however when I run the query I get:

You tried to execute a query that does not include the specified expression 'ID1' as part of an aggregate function

Thanks

 

[PHV]

I have two listboxes
So, what is the actual SQL code of each RowSource ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Domino2]

Thanks. My two list boxes work okay at present. The first listbox shows a complete list of Partners in a table. On clicking a Partner in the list, the second list shows related reports to each partner. However there are far fewer partners with related reports, and I was trying to make the first list only show partners that had related reports in list 2 if that makes sense.

SELECT DISTINCT PARTNERS.ID1, PARTNERS.Partner
FROM PARTNERS
ORDER BY PARTNERS.Partner;


SELECT ACTIVITIES.ID1, ACTIVITIES.ID3, ACTIVITIES.[Task Description]
FROM ACTIVITIES
WHERE (((ACTIVITIES.ID1)=[Forms]![TabbedForm].[Form].[List51]));

 

[PHV]

So, the RowSource of the 1st ListBox could be something like this:

SELECT DISTINCT PARTNERS.ID1, PARTNERS.Partner
FROM PARTNERS INNER JOIN ACTIVITIES ON PARTNERS.ID1 = ACTIVITIES.ID1
ORDER BY PARTNERS.Partner

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Domino2]

Bingo!! That's it, perfect, exactly what I was looking form. Many thanks

 

[kjv1611]

So, you didn't need to handle for any Null Descriptions? I guess you have none?

Glad you got it sorted out.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Domino2]

Thanks, I was trying to get only records in the child table with reports, and get the partner (Parent) just mentioned once in the first listbox. In simpler description, listbox one is filled with prents. listbox two the children belonging to the selected parent. Thanks for trying to fix it.