Results 1 to 9 of 9
  1. #1
    pharmacologist is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    39

    Performing calculation on each record from select query based on another related table

    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;


  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You have a spelling mistake here
    GOUP BY

    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;

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Not seeing any UPDATE action.

    Any UPDATE action that involves aggregate query will fail.

    Your SQL shows GOUP BY instead of GROUP BY.

    Post sample raw data and desired result.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    pharmacologist is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    39
    Thanks for noticing the typo but I still am having trouble. Also sorry I shouldn't have said "update" as I'm not actually meaning to update a table, I just meant when I call the query it will recalculate the number remaining based on the number of times the key appears in the Plates table.

    I've been working at it a bit longer and here is where I'm at now. I've simplified everything just trying to get the most basic aspect down, that is, returning all the records in the table Membranes and adding a column that calculates the number remaining by subtracting the count of the column MembranesFK, where the field is equal to the primary key of the Membranes Primary key, from the initial total.

    So this works to give me the number of records that equal a primary key from table Membranes:

    Code:
    SELECT Count(Plates.MembraneFK) AS CountOfMembraneFK FROM Membranes INNER JOIN Plates ON Membranes.MembraneID = Plates.MembraneFK
    HAVING (([Plates].[MembraneFK]=15));
    This returns the correct number. I'm trying now to incorporate this as a calculation when I retrieve each record of the table Membranes. Here is where I'm at but it gives me an error.

    Code:
    SELECT Membranes.MembraneID, Count(Plates.MembraneFK) AS CountOfMembraneFK FROM Membranes INNER JOIN Plates ON Membranes.MembraneID = Plates.MembraneFK
    GROUP BY Membranes.MembraneID
    HAVING (([Plates].[MembraneFK]=[Membranes].[MembraneID]));
    It gives me the error "Your query does not include the specified expression '[Plates].[MebraneFK]=[Membranes].[MembraneID]' as part of an aggregate function

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Try WHERE instead of HAVING.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    pharmacologist is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    39
    Quote Originally Posted by June7 View Post
    Try WHERE instead of HAVING.
    Now it gives me the error "syntax error (missing operator) in query expression 'Membranes.MembraneIDWHERE (([Plates].[MembraneFK]=[Membranes].[MembraneID]))'

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Missing a space in front of WHERE. Is that actual case in your query or just a posting error?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    pharmacologist is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    39
    Posting error, seems to be deleting random characters when I'm copying and pasting, don't know why

  9. #9
    pharmacologist is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    39
    Got the solution from another forum:

    Code:
    SELECT m.MembraneID, m.AliquotsMade-c.plateCount AS Expr1FROM Membranes AS m LEFT JOIN (SELECT COUNT(MembraneFK) AS plateCount, 
                   MembraneFK
            FROM Plates
            WHERE MembraneFK IS NOT NULL
            GROUP BY MembraneFK
            )  AS c ON m.MembraneID = c.MembraneFK;

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 01-05-2018, 04:45 PM
  2. select team based on max record .. query
    By mabughazza in forum Queries
    Replies: 1
    Last Post: 11-02-2015, 10:19 AM
  3. Calculation based on Query Row or Record Location
    By Kaloyanides in forum Queries
    Replies: 2
    Last Post: 07-08-2015, 12:11 PM
  4. Replies: 5
    Last Post: 01-27-2014, 04:45 PM
  5. Replies: 7
    Last Post: 07-02-2012, 10:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums