Returning records only when all data is filled in other table

[Rinoaerith]

Hi, good evening!



I have a DB in access 2010 that I am working for my job that has the following tables:

tbl_ProjectInformation:
ProjectID ($)
Name
Date of initiation
Date of completion

tbl_Blocks:
ProjectID ($)
Blocks ($)
Date of initiation
Date of completion

tbl_Documents
ProjectID ($)
Blocks ($)
Documents ($)
Receiving date

($) is the primary key in each table.

So, the tbl_ProjectInformation is related to the tblBlocks by ProjectID and the tbl_Blocks is related to tbl_Documents by ProjectID and Blocks.
It means that every new entry on tbl_project will have multiple (yet already defined) entries in tbl_Blocks that will also have multiple (yet already defined also) entries in tbl_documents.

The tbl_documents has a field named "receiving date" in which I put the date in which I have received each document (it could takes months to receive all documents for a block).
When all documents for a specific block is received, I have to be warned.

My project will be only finished when all blocks are finished (it means that I have received all documents for each block and all blocks for each project).
So, I have also to be warned when all blocks for a specific project was received.

I would like to run a query that shows only the field "blocks" from tbl_blocks from which I have included all date in field "receiving date" in table tbl_documents. It means that if I have to receive 10 documents for that block and I received only 8, the block could not appear in the query, but when I receive all 10 documents, it has to appears in the query.

I dont know if I made myself clear, if you need I could include the DB here for someone to help me... I have googled it but honestly, I dont even know how to search this feature since I am very newbie in access...

Thank you in advance for your support

[June7]

You want to select records where there are only 10 records for each document?

[Rinoaerith]

Hi June 7!

In fact I want that the query include only the field "block" from tbl_blocks when all "receiving date" is filled for all documents in tbl_document.
Lets supose that I have one "block" in tbl_Blocks that has 10 related "documents" in tbl_documents. If the 10 documents has already a "receiving date" in tbl_documents the query should return the description of this "block".
Otherwise, if for this same example the "receiving date" is described only for 8 or 9 or other than the 10 documents, the query would not show the description of this "block"

I dont know if is clear... please let me know. I have to finish this by monday and this are making me crazy...

Thanks!

[June7]

Query object cannot be designed for dynamic selection of fields. A field is either in the query or it isn't.

Just to be clear, by 10 documents you mean 10 records?

[Rinoaerith]

Hi!

Yes, it is 10 records but they are already defined. It means that I have a project that is composed by blocks A, B, C (on every project) and block A is composed of document 1,2,3 (on every block A), block B is composed of document 4, 5, 6 (on every block B) and block C is composed of document 7, 8, 9 (on every block C). Every document regardless of the block from which it pertains has a field named "receiving date".

What I am trying to return is the name of the block (from tbl_blocks) only when all documents in the block has the field "receiving date" filled (from tbl_documents).

So, if the documents 1,2,3 has the receiving date filled (in tbl_documents) the query will show block A. Otherwise it will not show (if only 1,2 has receiving date for example)...

I am almost declaring lost game on this...

[June7]

Earlier comment still applies.

And then any form/report with textboxes bound to the omitted fields would error.

What you want could be done with VBA code that modifies design of objects. I don't recommend that but it is possible.

Also, criteria for whether or not to display the relevant block field is dependent on values in multiple records. More complication.

An expression in textbox could display alternate content if the receiving date is empty for any of the pertinent records. A DLookup() or DCount() could be used.

[Rinoaerith]

Hi June7!

I understood and I give up of this... I created a table to follow up instead. Thank you very much for your time!