SQLTeam.com | Weblogs | Forums

Using a View in Access


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.



Try it as a SQL pass through query,