I am using a view as the recordsource of a datasheet form, yet it displays the wrong data in one field.
Here is the code:
create view v_LubricantStockDateLastOrdered as select a.*, SupPartNumbers.DateLastOrdered from v_LubricantStock as a left join SupPartNumbers on a.MaterialCode = SupPartNumbers.MaterialCode;
the other view:
create view v_LubricantStock as select v_CombinedLocation.LocationName, Materials.MaterialDescription, Materials.MaterialCode, MaterialTypes.MatName, ContainerTypes.ContainerName, BinLocDetails.CurrentStock, BinLocDetails.LowStocking, BinLocDetails.HighStocking, Materials.Notes from v_CombinedLocation inner join (BinLocDetails inner join (MaterialTypes inner join (Materials inner join ContainerTypes on Materials.ContainerType = ContainerTypes.ContainTypeID) on MaterialTypes.MatTypeID = Materials.MaterialType) on BinLocDetails.MaterialCode = Materials.MaterialCode) on v_CombinedLocation.ID = BinLocDetails.BinID;
Here is the combined location view:
create view v_CombinedLocation as
select Bins.ID, CONCAT(StorageAreas.StorageDescription, '-', Aisles.AisleName, '-', Sections.SectionName, '-', Shelves.ShelfName) as Location from Shelves inner join (StorageAreas inner join (Sections inner join (Aisles inner join Bins on Aisles.ID = Bins.Aisle) on Sections.ID = Bins.Section) on StorageAreas.ID = Bins.StorageArea) on shelves.ID = Bins.Shelf;
when I look at the Lubricant Stock Date Last Ordered view in SSMS, i get the correct data for the Location Name field, which should have the same part number/material code in two different locations.
However, in Access, they all have the same location name.