Help: create query

[Angelo]

Hello all,
I'm trying to create a query, but i've problem because i'm not an expert!

I have these two tables:

TBL_OF:
NAME|SURNAME|SEX|ID_CODE|BIRTH_DATE|STATE|CHOOSE|IDREG
----------------------------------------------------------------------------
------
NAME1, SURNAME1, M, 0001, 12/11/1980, MO, 04, 053321
NAME2, SURNAME2, M, 0002, 02/25/1989, MO, 03, 001245
NAME3, SURNAME3, M, 0003, 04/06/1974, MO, 04, 049328
NAME4, SURNAME4, M, 0004, 03/09/1988, MO, 04, 004348

TBL_APP:
NAME|SURNAME|SEX|ID_CODE|BIRTH_DATE|STATE|MODULE_NUM|ACTIVE
----------------------------------------------------------------------------
-----------------
NAME1, SURNAME1, M, 0001, 12/11/1980, MO, 00123, NO
NAME2, SURNAME2, M, 0002, 02/25/1989, MO, 00421, YES
NAME3, SURNAME3, M, 0003, 04/06/1974, MO, 00214, NO
NAME3, SURNAME3, M, 0003, 04/06/1974, MO, 02948, NO
NAME3, SURNAME3, M, 0003, 04/06/1974, MO, 00287, YES
NAME4, SURNAME4, M, 0004, 03/09/1988, MO, 00938, NO
NAME4, SURNAME4, M, 0004, 03/09/1988, MO, 00938, NO
NAME5, SURNAME5, M, 0005, 09/09/1998, MO, 00298, NO


and I want to create a query that return these value:

QUERY_RESULT:
NAME|SURNAME|SEX|ID_CODE|BIRTH_DATE|STATE|CHOOSE|STATE|IDREG|COUNT
----------------------------------------------------------------------------
-------------------------
NAME1, SURNAME1, M, 0001, 12/11/1980, MO, NO, 053321, 1
NAME2, SURNAME2, M, 0002, 02/25/1989, MO, YES, 001245, 1
NAME3, SURNAME3, M, 0003, 04/06/1974, MO, YES, 049328, 3
NAME4, SURNAME4, M, 0004, 03/09/1988, MO, NO, 004348, 1

Practically I want to get only the records in table TBL_OF that are present
in table TBL_APP via the ID_CODE, grouping the equal records in table
TBL_OF, if one of the record in that group have the STATE on YES must return
it in the query result.
For example:
- the record NAME3/SURNAME3/0003 is present tree times in TBL_APP, but I
want to get only one time where the value STATE is YES
- the record NAME4/SURNAME4/0004 is present two times in TBL_APP, but I
want to get only one time with the value of STATE ugual to NO

I don't know if I've explained the problem well, I hope, however, that
someone can help me.
best regards,
Angelo.

 

[A. Smart]

As far as creating the query, simply use the query wizard. Enter all fields
from both tables then view query in design view. Set up a direct relationship
between ID_Code in one table to the ID_Code in the other, and deselect the
fields that aren't wanted.
i.e you want
NAME|SURNAME|SEX|ID_CODE|BIRTH_DATE|STATE|CHOOSE|STATE|IDREG|COUNT

Now if i understand State is a check box then simple type True or False
under criteria, depending what you want to show.
If it is not a check box but is a text box then enter "Yes" or "No", again
depending on what you want returned.
Hope thats a start!