I'd like to update the number of remaining aliquots (i.e. items) in a query based on the number of times its primary key appears in another tables in a relational field, i.e. value = number aliquots - count(# records its primary key appears). I thought I was on the right track but have hit a dead end and don't know where to go. Here is the query I have so far
Code:
SELECT Membranes.PrepName, Membranes.MembraneID, Membranes.DatePrepared, Membranes.Concentration, Plasmids.Protein, CellTypes.CellName, Membranes.AliquotVol, [Membranes]![AliquotsMade]-Count([Plates].[MebraneFK]=[MembraneID]) AS Expr1
FROM Plasmids INNER JOIN ((CellTypes INNER JOIN (Cells INNER JOIN Membranes ON Cells.CellID = Membranes.CellLine) ON CellTypes.CellTypeID = Cells.CellType) INNER JOIN Plates ON Membranes.MembraneID = Plates.MebraneFK) ON Plasmids.ID = Cells.Plasmid GOUP BY Membranes.PrepName, Membranes.MembraneID, Membranes.DatePrepared, Membranes.Concentration, Plasmids.Protein, CellTypes.CellName, Membranes.AliquotVol;