Query display question.

[amy3000]

I'm doing a database for our county's Medical Reserve Corps. I have a query made up of 2 tables. The first table has person ID information and some other information. One field asks if the individual has a military obligation in the case of an emergency. A second table has a list of other possible committments like being on staff at a hospital, red cross, etc. The query ends up listing the military obligation for as many entries there are for other committments. Eg,

Name Military_Obligation? Other_Committments
Jane Doe Yes Red Cross
Jane Doe Yes Hospital/Clinic

Now, I would prefer just to add "Military Obligation" to the list of all committments so it doesn't get listed more than once. However, the people I'm doing this for want the fields to match a database the State is doing as closely as possible in the case the State actually makes something useable to the Counties for retrieving data.

So, is there any way to have the query just put in a blank the second time the same data would occur. That would look kind of like a report. I just want it to be more readable to the users. I don't want to do a report because they want to do some querying themselves. Eg,can the query look like this. Thanks.

Jane Doe Yes Red Cross
Jane Doe Hospital/Clinic

 

[Arafel]

Hi,

I don't think so as I have experienced this problem previously. I think it goes beyond the limits of the query function as the item will be repeated for how ever many items are listed for Jane Doe.

If you were desperate I suppose you could move the 'military obligation field to table 2 and only enter Yes once for each person, but that does not strike me as a tidy way to deal with Access tables.

Maybe someone else will have an idea.... will be interested if they do!

Arafel

 

[dhookom]

Amy3000,
Do you have some exact table and field names? Also, is the military obligation field text or numeric or yes/no?

I believe this could be done but it would require that you have some prescribed sort order of the Other_Commitments table.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[amy3000]

I used the State's table names. The table with the military question is:
registry_person_tbl.

There are actually 2 fields. The first is a text yes/no called HAVE_OBLIGATION. The second is a text field to describe the obligation MILITARY_DETAIL.

The other table is:
registry_obligation_tbl.

There are also two fields in this table, apart from the primary and foreign key. OBLIGATION_TYPE is a list of possible obligations. OBLIGATION_DETAIL1 is a text field to further describe or to describe the selection of "other".

 

[dhookom]

If there are 2 fields in registry_person_tbl, how is this related to any other table? I don't even see a field that resembles a name or person id.

How about you try again with some table and field names with actual records and how you would expect them to appear in a query?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[amy3000]

Oops, sorry. I don't think I quite understood the question. The registry_person_tbl has about 30 fields but I believe this is the relevent information:

registry_person_table
PERSON_ID
HAVE_OBLIGATION (this refers to military obligation)
MILITARY_DETAIL

registry_obligation_tbl
OBLIGATION_ID
OBLIGATION_TYPE
OBLIGATION_DETAIL
PERSON_ID

 

[dhookom]

This method uses a subquery:

SELECT Registry_Person_table.Person_ID, 
IIf([Have_Obligation]=True And 
[Obligation_Type]=(Select Top 1 Obligation_Type 
   FROM Registry_Obligation_tbl r
   WHERE r.Person_ID = Registry_Obligation_tbl.Person_ID 
   ORDER BY Obligation_Type),"Yes","") AS MilitaryObligation, 
Registry_Obligation_tbl.Obligation_Type, Registry_Obligation_tbl.Obligation_Detail
FROM Registry_Obligation_tbl INNER JOIN Registry_Person_table ON Registry_Obligation_tbl.Person_ID = Registry_Person_table.Person_ID;

Duane
MS Access MVP
Find out how to get great answers faq219-2884.