Newbie query help (1 Viewer)

[ironvid]

Hi

I Have two table....

Table 1 (index)

Auto id
box
file_number
surname
first name
done

Table 2 ( archive)

Id
archive
file_number
surname
first name

Question 1

How can i query the table to give me a list of all box's that have ALL THE RECORDS IN THAT BOX in the archive table and an archive number higher than ZERO

THE LIST MUST BE OF BOXES THAT ALL RECORDS IN THAT BOX ARE IN THE ARCHIVE TABLE.

Question 2

How can i query the table to give me a list of all box's that have some records in the archive table but NOT ALL and NOT ZERO
-------------------------
i know the table stuctures a not very good but i cannot edit these as they are used by an app

the file number is unique

I have attached an example

thanks for any help

 

[dfitz]

I don't think that you can do what you want with a simple query. You will need to create a query to lits all items by box and use this to set up a recordset. You can then set up a loop to go through all records and identify whether all/none/some files are in archive. I would suggest that this should be used to populate a status field on the box record.
To do this needs some work in Visual Basic.
Hope this helps.
df