How best to view data from several tables?
Posted on 2008-10-31
I am a beginner, trying to develop an Access 2007 (.adp) "project" as a front-end to Tables stored in a SQL Server 2005 Express database.
I have a "Stock Items" table, a "Customer History" table and a "Supplier History" table. These "History" tables list each time a customer or supplier was sold (or delivered) a quantity of each stock item. The "Stock Item Code" is used to link a "host" stock record to its own records in the "History" Tables.
The two "History" tables contain "address codes" that link to other "Customer Address" and "Supplier Address" tables respectively, from which I can get the full name and other details of the customer or supplier involved.
In my main "Stock Record" Form, I would like to click on a "History" page and see a list of sales and deliveries relating to the item, sorted in date order. This page would hopefully contain a "sub-form" pulling (and mixing) records from the "Customer History" and "Supplier History" tables.
I have tried to create a "View" in SQL Server upon which to base my "sub-form" but I can't get the "JOIN" clauses to coallate all the information I need. It seems to have trouble "going off in 2 directions" to read data from two separate "history" tables linked to the central "Stock Items" table.
I have tried combining the separate "Customer" and "Supplier" history tables into a single "mixed" history table, using an "AddrKind" field to distinguish between Customers and Suppliers. This has 2 major drawbacks - I (think I) lose my "cascading" relationship that allows "histories" to be removed if a Customer or Supplier is deleted from the main address tables, and in my sub-form ("continuous" or "datasheet" view), when I try to fill out a fuller "name" field (in the On_Current event procedure), the same address name appears in all lines. Storing the "full name" of the customers and suppliers in the "History" tables would get around this, but that seems a bit unwieldy.
I could also copy the Customer and Supplier histories into a "temporary" mixed history table in the "On_current" event and also read in the full names from the address tables. The contents of this "temporary" table would be removed when the item was finished with. But this also seems a bit cumbersome and might fall apart in a multi-user environment where more than one user might want to look at a stock item at the same time.
Can anyone suggest the best strategy I should adopt? I thought this type of thing would be fairly easy using Access & SQL Server, so I hope it is just me being a bit thick.
Many thanks. Colin.