Forum Discussion
Sharepoint Linked Table - Help
In Sharepoint list, the only way to designate and enforce Referential Integrity on a relationship between two tables is the Lookup Field mechanism. In the screen shot, although the two fields are named the same, the data in them may or may not match up at all. I'd have to see the actual fields in SharePoint to be able to offer suggestions.
The problem with Lookup fields is that they often DISPLAY one value, but are bound to a different value, i.e. the Foreign Key. To the human eye just looking at the DISPLAYS, there is no way to know what the bound, underlying Foreign Key is.
Invest some time in learning how to designate Lookup fields in SharePoint with what they call "Data Integrity" or something similar. That's the closest you'll get to Referential Integrity.
The other query method you describe, with no relationship between tables, creates a Cartesian Product. Each record in the first table is returned along with each record in the second table. Hence thousands of pairs of fields in your result. There are niche cases where a Cartesian Product is useful, but not in a relational table.
You do not need to put the price for a mix type into the [Insitu Concrete] table UNLESS it differs from the standard price shown in the [Cost/M3] field in the [Concrete_Mix_Design_Costs] table.
This is because a query on the two tables can include fields from both, and that's how you'll display the price, and the total price as well. The Total price would be the [Cost/M3] * [Quantity], i.e. a Calculated field in the query.
I also assume that, because these are SharePoint lists, you had to use the Lookup field in table [Insitu Concrete] to designate the relationship between these two tables, as shown in the [Mix Type] field in [Insitu Concrete].
As an aside, I STRONGLY urge you to reconsider some of these naming choices.
Names like "Cost/M3" are especially dangerous because they include characters that can be ambiguous. In this case that is the use of the forward slash in the field name . That symbol is also the division operator for math. Having it in a name as well as using it for calculations can be a source of unnecessary complications for Access.
Depending on how long you've used this database, of course, the cost of correcting this design flaw may be greater than you want to invest. In the long run, though, it's a very good idea to do so. I recommend you consider correcting it now, rather than later.