Solved

How best to view data from several tables?

Posted on 2008-10-31
8
247 Views
Last Modified: 2013-12-05
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.
0
Comment
Question by:colinasad
  • 5
  • 3
8 Comments
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 22850592
I would indeed combine your two tables, customers and suppliers, into one table, indicating what it is by a separate field. Cascade delete should still work as soon as there's foreign key. As for the the name, if it pertains to the parent record, then show it on the main form rather than on subform. Re, "customer address" and "supplier address", I would combine them accordingly; if customer/supplier name does not change from address to address, then it obviously belongs to the table CustomerSupplier rather than Address.

0
 

Author Comment

by:colinasad
ID: 22851298
Thanks for your suggestions, vadimrapp1

Since posting my original question I have discovered the "UNION ALL" expression that can be used in an SQL Server "view". This appears to allow me to execute more than one SQL expression (one for customer events and one for supplier events) in my "view" and combine the results.

My concern about the "cascade" implications of combining "customer" and "supplier" events in a single table is that my client uses old numeric codes for his customers and suppliers, so it would be possible to have a customer "00001" and a supplier "00001". I thought it would be easier to keep the tables updated automatically if they were separate.

Any further comments are welcomed.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 22851377
> so it would be possible to have a customer "00001" and a supplier "00001"

Make composite primary key:  id + AddrKind

As I said, you can still have separate views, and when it's better to work with customers and suppliers separately, use the view; but when you need combined view, work with the unified table.

0
 

Author Comment

by:colinasad
ID: 22856302
Thanks again vadimrapp1.

I was aware of the "composite" primary key feature but do not know (to be honest, haven't tried) how to establish a "cascadable" fireign key relationship with them.
if I combine a "C" + "00001" for a customer history record and "S" + "00001"  for a supplier history record, how does Access know to delete the "C" + "00001" entries when I delete customer "00001" from the main "Customer" address table and the "S" + "00001" entries if I delete a supplier with identifying code "00001"?

I apologise that you are havining to give me tuition on what are probably fairly basic Access features, but knowing this would certainly help me.

Regards.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 500 total points
ID: 22856477
table CustomersSuppliers
idtype
other data pertaining to the customer/supplier, such as name
table Orders
OrderId
CustomerSupplierId
CustomerSupplierType
ItemId
other data pertaining the transaction, such as date

table StockItems
Id
other data pertaining to stock item, such as name

Primary keys are in bold.

CustomerSupplierId and CustomerSupplierType in Orders is foreign key for Id and Type in CustomersSuppliers. ItemId in Orders is foreign key for StockItems.

Note that I separated customersSuppliers into a separate table, so information pertaining to the customer/supplier (name, address etc) does not repeat in every row of the History.

As for cascade deletions, I'd think that better idea would be exactly to prohibit any deletion from Orders, or from your current History table - exactly because historical financial data needs to be kept. But it's of course your business and your choice.
0
 

Author Comment

by:colinasad
ID: 22858788
Thanks again vadimrapp1,

I have tried to use your suggested strategy but am having a problem establishing a "foreign key" relationship between pairs of combined fields.

I have combined "AddrType" and "AddrCode" fields as a primary key in a mixed customers & suppliers "TBLAddress" table (ie "AddrType" = "C" or "S" for "customer" or "supplier", and "AddrCode" is the identifying code for a particular customer or supplier).

In my "TBLEventsHistory" table I have corresponding "HistAddrType" and "HistAddrCode" fields to identify the "owner" of the event (and a "HistUniqID" "identity" field is used as the primary key).

However, when I try to establish a "foreign key" relationship between the two tables, I get a message about a "Foreign Key Conflict" error in my "TBLAddress" table. I only have 6 records in my "TBLEventsHistory" table that are filled out with codes for existing customers and suppliers so I'm pretty sure the problem is "logical" rather than "data". I envisage that the sort of relationship I am trying to establish is between "AddrID + AddrCode" in my "TBLAddress" table and "HistAddrID + HistAddrCode" in my "TBLEventsHistory" table.

I am doing this in "Microsoft SQL Server Management Studio Express" where I maintain my SQL Server tables. In the "Design" view of my "TBLEventsHistory" table I use the "Foreign Key Relationships" dialogue box where I select my "TBLAddress" table as the "Primary key table", from which I select the "AddrType" and "AddrCode" fields. I specify my "TBLEventsHistory" table as the "Foreign key table", from which I then select the "HistAddrType" and "HistAddrCode" fields.

Am I doing this the correct way; just listing the pairs of fields beneath the table names? Do the fields I want to combine to form part of the relationship need to be "indexed" as well? I have tried doing this in my "TBLEventsHistory" table (they already form the "primary key" in my "TBLAddress" table) but that doesn't seem to make any difference.

I hope this is clear and that you can shed light on where I'm going wrong.
0
 

Author Comment

by:colinasad
ID: 22858850
I do aplogise, vadimrapp1,

It was a "data" problem and not a "logic" problem after all. Even though I had only 6 records in my "TBLEventsHistory" table, I had deleted one of the owning address records when I had been experimenting earlier today with separate customer and supplier address tables and separate customer and supplier events history tables. I had forgotten I had done that.

That's an hour of my life I won't get back again. I hope you didn't spend too much time on my previous posting (which was only 10 minutes earlier).

Regards. Colin.
0
 

Author Closing Comment

by:colinasad
ID: 31512048
Thanks vadimrapp.
You have revealed how multiple fields can be combined for foreign key relationships. Despite my initial "data" problems I have managed to get it working. Many thanks.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now