Determining Difference Between Items On-Hand and Items Required per Project in Access 2003

Question

I'm usually a PHP programmer, but I'm currently working on a project in MS Access 2003 and I'm a complete VBA newbie. I'm trying to do something that I could easily do in PHP but I have no idea how to do it in Access. The facts are as follows:

Tables and relevant fields:
tblItems: item_id, on_hand
tblProjects: project_id
tblProjectItems: project_id, item_id

Goal: Determine which projects I could potentially do, given the items on-hand.

I need to find a way to compare each project's required items against the items on-hand to determine if there are any items missing. If not, add the project to the list of potential projects. In PHP I would compare an array of on-hand items with an array of project items required, using the array_diff function; if no difference, add project_id to an array of potential projects.

For example, if...

$arrItemsOnHand = 1,3,4,5,6,8,10,11,15  
$arrProjects[1] = 1,10  
$arrProjects[2] = 8,9,12  
$arrProjects[3] = 7,13  
$arrProjects[4] = 1,3  
$arrProjects[5] = 2,14  
$arrProjects[6] = 2,5,8,10,11,15  
$arrProjects[7] = 2,4,5,6,8,10,11,15  

...the result should be:

$arrPotentialProjects = 1,4

Is there any way to do this in Access?

Answer 1

Consider a single query to reach your goal: "Determine which projects I could potentially do, given the items on-hand."

SELECT
    pi.project_id,
    Count(pi.item_id) AS NumberOfItems,
    Sum(IIf(i.on_hand='yes', 1, 0)) AS NumberOnHand
FROM
    tblProjectItems AS pi
    INNER JOIN tblItems AS i
    ON pi.item_id = i.item_id
GROUP BY pi.project_id
HAVING Count(pi.item_id) = Sum(IIf(i.on_hand='yes', 1, 0));

That query computes the number of required items for each project and the number of those items which are on hand.

When those two numbers don't match, that means at least one of the required items for that project is not on hand.

So the HAVING clause excludes those rows from the query result set, leaving only rows where the two numbers match --- those are the projects for which all required items are on hand.

I realize my description was not great. (Sorry.) I think it should make more sense if you run the query both with and without the HAVING clause ... and then read the description again.

Anyhow, if that query gives you what you need, I don't think you need VBA array handling for this. And if you can use that query as your form's RecordSource or as the RowSource for a list or combo box, you may not need VBA at all.