sql query on access database
- Thread starter trumanrus
- Start date
I'm not stalking you, I swear 
I've answered this question in another forum but I thought I'd add some additional info. I've encountered this same situation myself. For example, I wrote an app that interacted with a point-of-sale system that had an Access database for recent data that contained a Customer table and a Purchase table. There was also a database for archived data, but only the purchases were archived. There are a few ways to create the desired relationship in code once the tables have been retrieved separately.
You can create a new, combined table yourself and populate it yourself. This is probably more trouble than it is worth, unless you need to display the combined table in a DataGrid.
Use a DataSet and create your own DataRelation between the two tables. This is probably not useful for data that is not going to be updated.
For a one-to-many relationship, you can use a HashTable or SortedList. The keys would be the primary key values or the row indices from the parent tabel, while the values would be ArrayLists containing either the primary key values or the row indices from the child table. You can implement this method with two For loops and it would take about ten lines of code.
Use a DataView to filter the child table based on the foreign key. Every table has a DataView associated with it via the DefaultView property. You can dynamically set the RowFilter property of the DataView to filter out the rows that don't match the desired foreign key.
Use the Select method of the DataTable to retrieve only the rows that have a particular foreign key. This method is similar to using a DataView but returns an array of matching DataRows instead of simply hiding those that don't match.
I've answered this question in another forum but I thought I'd add some additional info. I've encountered this same situation myself. For example, I wrote an app that interacted with a point-of-sale system that had an Access database for recent data that contained a Customer table and a Purchase table. There was also a database for archived data, but only the purchases were archived. There are a few ways to create the desired relationship in code once the tables have been retrieved separately.You can create a new, combined table yourself and populate it yourself. This is probably more trouble than it is worth, unless you need to display the combined table in a DataGrid.
Use a DataSet and create your own DataRelation between the two tables. This is probably not useful for data that is not going to be updated.
For a one-to-many relationship, you can use a HashTable or SortedList. The keys would be the primary key values or the row indices from the parent tabel, while the values would be ArrayLists containing either the primary key values or the row indices from the child table. You can implement this method with two For loops and it would take about ten lines of code.
Use a DataView to filter the child table based on the foreign key. Every table has a DataView associated with it via the DefaultView property. You can dynamically set the RowFilter property of the DataView to filter out the rows that don't match the desired foreign key.
Use the Select method of the DataTable to retrieve only the rows that have a particular foreign key. This method is similar to using a DataView but returns an array of matching DataRows instead of simply hiding those that don't match.
Similar threads
