raymond See February 2016

Select items where count in another field matches (not updatable)

Here I am trying to get the record for my products where the # swab location in Main table matches the count of swab locations in swab Table and Users can checked off the Y/N to verify that the description of the locations are correct.

Here is the example of my 2 tables.

tblMainEquipment
Asset_ID   EquipmentName Num_SwapLocations Verified
234        Saijimon      2                 N
235        Pasquale      3                 N

tblMainSwapLocations
Asset_ID   Swap_location 
234        Particle Cannon
234        RailGun
235        Particle Cannon

I use the following query to count the number of records, i avoided using a having query to combine both tables since it is not updatable.

qryMainSwapLocationCount    
SELECT MSL.Asset_ID, Count(Asset_ID) AS [Count]
FROM tblMainSwapLocation AS MSL
GROUP BY MSL.Asset_ID;

This will give me the result of

qryMainSwapLocationCount
Asset_ID   count
234        2
234        1

I used the following as a record source for my form to allow users to verify the inputs.

SELECT MEQ.Asset_ID, MEQ.Equipment_Name,MEQ.Num_swapLocations MEQ.Verified
FROM tblMainEquipment AS MEQ, qryMainSwapLocationCount AS MSLC
WHERE (((MEQ.Asset_ID)=[MSLC].[Asset_ID]) AND ((MEQ.Num_SwapLocations)=[MSLC].[Count]);

This result would be

tblMainEquipment
Asset_ID   EquipmentName Num_SwapLocations Verified
234        Saijimon      2                 N

However this record set is not editable. Is there any reasons for this?

Answers


Matt Hall February 2016

I think you should put your table tblMainEquipment as your recordsource and bring all the fields from that on to your form:

enter image description here

Then insert an unbound textbox (perhaps close to your Num_SwapLocations field for easy comparison):

enter image description here

Then in this new textbox, put the following in the ControlSource:

=DCount("ASSET_ID","tblMainSwapLocations","ASSET_ID=" & [Asset_ID])

enter image description here

Then open your form and it should count the number of records in table tblMainSwapLocations that have the same Asset_ID as the record currently showing:

enter image description here

You'll then be able to update the Verified field in your tblMainEquipment table.

Post Status

Asked in February 2016
Viewed 2,955 times
Voted 4
Answered 1 times

Search




Leave an answer