[AccessD] Treeview and Listview Displays

Hi Terry:

To access the data that is in the same table you have to use two
relationships; once as a item and once as part of a group. You can not use
many to many but the following select statement will assemble both the child
and parent records.

select Inv.code, Inv.description, InvGroups.Code, InvItems.Code
from InvTable as InvItems, InvTable as InvGroups, Inventory as Inv
where inv.itemIndex = InvItems.itemIndex
  and inv.itemIndex = InvGroups.groupIndex

or you can use the UNION statement with a subquery

select Code, description, GrpCode, ItemCode from (
	(select Inv.code, Inv.description, InvGroups.GrpCode, "" ItemCode
	from InvTable as InvGroups, Inventory as Inv
	where inv.itemIndex = InvGroups.groupIndex)
	union all
	(select Inv.code, Inv.description, "" GrpCode, InvItems.Code
	from InvTable as InvItems, InvTable as InvGroups, Inventory as Inv
	where inv.itemIndex = InvItems.itemIndex)
) group by Code, description, GrpCode, ItemCode

This might not be absolutely correct, as I have no way to test it but
concepts are there. (You may have to use a throw away aggregate function.
like 'count(ItemCode)' to force the grouping, in the second code segment.)

HTH
Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of MACE, Terry
Sent: Sunday, October 19, 2003 2:50 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Treeview and Listview Displays


Can anyone tell me if either Treeview or Listview can be used to display
data in a many-to-many relationship.

I have a Bill Of Materials database where several parent parts may have the
same child part that I want to display.

Thanks

Terry Mace
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com