Creating a Query

In order to obtain the correct fields for a subform, you must create a query that generates those fields.

You will need to select the video isbn in order to link the fields, as well as the aisle and shelf.  You will also need to generate a count of videos that have a status equal to "in stock."  I used only the Video and Video Copy tables.  Be sure to use the group by clause. 

SELECT [Video Title].[ISBN], [Video Title].[Aisle], [Video Title].[Shelf], Count([Video ID].[Video_ID]) AS [Num in Stock]
     FROM [Video Title], [Video ID]
          WHERE [Video Title].[ISBN]=[Video ID].[ISBN] And [Video ID].[Status]="In Stock"
               GROUP BY [Video Title].[ISBN], [Video Title].[Aisle], [Video Title].[Shelf];

Test your query carefully before basing a subform on it to be sure it lists data only for videos that are in stock.































































SELECT Video.isbn, Video.V_Aisle, Video.V_Shelf, Count(Video_Copy.VC_ID) AS [Num in Stock]
    FROM Video, Video_Copy 
        WHERE Video.ISBN = Video_Copy.ISBN AND 
                     Video_Copy.VC_Status="in Stock"
              GROUP BY Video.ISBN, Video.V_Aisle, Video.V_Shelf;