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?

You would be better off with recordsets in MS Access. Why two queries? — Fionnuala, Aug 30 '15 at 13:00
How would I accomplish this with recordsets? Is there a function like PHP's array_diff that would find the difference between two recordsets? Keep in mind that I'm trying to get a list of projects, _all_ of whose required items are on hand. — gr8dane, Aug 30 '15 at 19:55
@HansUp Wow!! I'm not completely sure I follow what that query is doing, but it seems to do the trick. Thanks. If you add it as an answer, I'll mark it as accepted. — gr8dane, Aug 31 '15 at 04:29
@HansUp Yeah, I noticed that, too. I've changed the title and removed most of the references to arrays in the question. Hope it's a better fit now. — gr8dane, Aug 31 '15 at 23:11

score 0 · Accepted Answer · answered Aug 31 '15 at 16:13

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.

Perfect!! I couldn't figure out how to make one SQL statement do all that. Thanks for the help. — gr8dane, Aug 31 '15 at 23:37

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

1 Answers1