Forum Discussion
Sharepoint Linked Table - Help
Hi, I have an older sharepoint list that collects concrete deliveries to site (Insitu Concrete), i have a new Sharepoint list that that shows the price for each mix type (Concrete_Mix_Design_Costs).
I cannot run a query or use DLookup succesfully to add the price to each delivery.
Above are the tables i want to put the price against each delivery based on Mix Type.
Any help would be much appreciated, I am a builder not a computer person but have muddled my way to building a database that runs our procurement etc, but despite hours of effort i am going nowhere with this. Thanks
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.
Thank you for your response and assistance, see below comments.
I have tried to run a query between the two tables but no joy. The [Mix Type] column in the insitu concrete table is a 'choice column' in Sharepoint, as this List was developed before I introduced the [Concrete_Mix_Design_Costs] table. I was hoping that as the two field are named the same and have matching information i could create a relationship between them, but I am unable to do so.When I add the [Concrete_Mix_Design_Costs] table into the query and add the [Cost/M3] field i get zero results if I create a link between [Cost/M3] in both tables.
If I remove this link the Access query give me a a result that has a each price from the [Concrete_Mix_Design_Costs] table multiplied by each record, therefore 3000+ results instead of 365.
I get your point regarding the naming of the Cost Field and will see if i can change this going forward. I hope i have explained my issue a bit clearer. Thanks
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.George
Ok got you regarding the lookup fields to create the relationship, I should have asked the question earlier would have saved me a lot of messing.
Going forward I will change the choice field to a lookup field it will work better for me done this way as it will keep the source Data better controlled.
I will do as you suggest and have a look at the mechanics behind linked Sharepoint lists.
Thanks again for your help.
Henry
