>Can anyone help.
>First my background is C++/engineering, almost no database work. I've been asked to build a small app to track parts & associated data. The client is a small company happily using Visual Foxpro 6 & no desire (or finances) to upgrade. I work for a small company whose idea of training is to give you a job to do & occassionally access to the manuals (I've got the VFP6 manuals) - I'm pretty sure I've got some wrong ideas, but nobody to turn to for help.

You will get plenty of help here, whenever you need it.

>Initially the data required to be held per part is :
>
>PartNumber : alphanumeric
>Description : text description
>CurrentUse : logical
>PartType : Miniature, Single, Multi (need to be able to add further part types)
>DataCD : this is a alphanumeric reference to a manuacturers CD in their library
>Location : storage warehouse
>
>I thought to split this into 4 tables -
>Table 1 - parts - 1 record per part
>Table 2 - part types - 1 record per type (currently 3 records, but may expand)
>Table 3 - data cds - 1 record per CD (currently 21 records, but may expand)
>Table 4 - locations - 1 record per location (currently 10 records, but may expand)
>tables 2,3 & 4 would have numeric keys with matching fields in the main table, so each record in table 1 would be linked via the keys to a record in the other 3 tables.

On quick inspection, that looks like proper normalization.

>So far so good - I built the database & tables. The problems occur when I try to create a view linking all 4 tables - if I use the view wizard & try to link the tables, I get error messages saying tables can't be parents/children to multiple children/parents. If I create a new view, I can join the tables, but the query doesn't give the expected results - the join to the first linked table seems OK, but the other tables seem stuck to the first found record. I think I understand the differences between inner/outer joins, but just can't see where I'm going wrong. Have I created the right set of tables ? Should I even be using views ?
>
>I'm not expecting a detailed solution, but if anyone can give me some hints as to where I'm going wrong or pointers to other web sites where I might find the info, it would be appreciated.

First of all, I use views mainly to edit data, not to display it. For display, you can use a query, or just write the SQL statements.

Both a query and a view create a cursor (temporary table). The main difference is that a view has the capability of writing the data back to the original tables.

By writing your own SQL statements, you can do some things which are difficult in views. A view should still work in your case, though.

If I understood correctly, you want one record for each part, and then fetch the corresponding data from the other tables.

You can do this with left joins - or even inner joins, if there is no missing data. Let's assume that the possibility of missing data (in the parts table) exists. The following SQL statement should get the data, for read-only purposes, that is, for reporting purposes:

select Parts.*, PartType.TypeDescription, CD.CDDescription, Location.LocName;
  from Parts;
    left join PartType on Parts.PartType = PartType.PartType;
    left join CD on Parts.CD = CD.CD;
    left join Location on Parts.Location = Location.Location;
  into cursor TempReport

I am assuming that the primary key of each table has the same name as the table.

Or perhaps you want to post your current SQL statement, so we know more details of what is going on. You can obtain an SQL statement from a view.

HTH,

Hilmar.