Comparing two arrays and detecting differences

[skidude2000]

I am having some trouble checking a table. Here is my situation:

I have a database desinged to keep inventory of computers in my building. I have two tables withing this db called tblGroupAcct and tblAcctLink. tblGroupAcct contains all of the group accounts and their info; tblAcctLink contains a record for each instance of a group account on a computer in building. When I delete a computer from the database or remove it from a group account, the record in tblAcctLink linking that computer to a group from tblGroupAcct will get deleted.

Now, here's my issue:

When the last instance of a group in tblAcctLink is deleted, I need the db to prompt the user to remove the entire group account from tblGroupAcct. I have a form that allows the deletion of computers or the removal of a group, and on save of the changes (either delete the computer or remove the group), I want the db to check to see if there is a record of a group account in tblGroupAcct that has a corresponding link to a record in tblAcctLink. If there is a corresponding record, then ignore it. If there is no corresponding record, then prompt the user to delet the group from tblGroupAcct. It must loop through to check every group in tblGroupAcct before closing the forms.

What seems to be my solution is assigning all groups from tblGroupAcct to an array and assigning all records in tblAcctLink to another array. If I could then compare the arrays, and have the db tell me which parts of array A do not exist in array B, that would be great. But I'm not sure how to do that.

Please HELP!! I have tried many different ways of implementing this in VBA, and none have worked. I've been on this issue since 7:30 A.M. and it's almost 2:00 P.M.:banghead: Also, if my idea of comparing arrays does not make sense, I would appreciate any suggestions on how to fix my issue.

If you need clarification, please ask.

 

[mdlueck]

I read your post... I am not certain that loop code is required.

Assuming you have used ID numbers to link/associate the records, you could simply use that known ID to check for existence of related records. So, delete the record, then next do a SELECT for records matching that ID that the deleted record had. If none are found, then pop your orphan record alert.

So, I am suggesting to leverage the SQL language rather than interpretive VBA loop code dealing with memory arrays.

 

[skidude2000]

Ok, that makes sense. I am still confused about one thing though: multiple group accounts on one machine. Let's say that a computer in the database has two group accounts on it. By deleting the computer, its associated records in tblAcctLink will be deleted, and I will have to check for both accounts to see if either of them were the last instance. Does that make sense? So if I just had to check for one group account orphan, it would be fairly easy, but I have to check for multiple group orphan accounts. Is there any way you could perhaps exemplify through basic code based on the information I have given you? Also, I may not respond again today as I must leave soon.

Thanks.

 

[mdlueck]

I would think that you are describing this scenerio:

Products <--> ProductPartLink <---> Parts

Where multiple Product may exist, and multiple Parts also, and multiple Products may be associated with the same Part.

For this DB schema:
1) The Products and Parts tables are keyed by an ID number
2) The ProductParLink table has at least two columns, The ProductID / PartID columns. QtyPer is a logical column for that table as well.

So if you put that type of schema in place, you could easily SELECT from the ProductPartLink table where ProductID = x, or PartID = y, and find out if other records are using the common Product or Part.

 

[skidude2000]

Ok, I see what you're saying. And that is pretty much the exact setup I have going. I tried using the SQL SELECT like you suggested. I have it finding the orphaned groups in tblGroupAcct, in my case, and it seems to work well. Thanks for your help!

Cheers

 

[mdlueck]

I am glad to have been of assistance, skidude2000.