Query to choose from records (1 Viewer)

[ahti098]

Hi I have a table for employee documents including field doctype. Doctype is linked to another table Doctypes from which the corect document type an be selectes Passort or ID card.
Also a query based on that.
I can make the query to chose records based on criteria so that for example only Employee records with passport entries are chosen.
But how to make the query work so that if Passport data is absent then employee records with ID card are chosen.
Also if employee has both documents then only Passport data is cosen and not both.
Cant figure it out anyhow.

 

[jdraw]

Show us your table structures and a few sample records.

 

[ahti098]

Sorryy could not figure right now out a better way to atach
This nr 3 in DocType get me employee records with seamaspass but if he does not have seamanspass I would like to get rcords with Passport instead and if Passport is also missing then records with IDCard

 

[jdraw]

Where/How is passport identified/recorded?
Same info for IDCard?

 

[MajP]

Something like

Where DocType = 3 OR (DocType IS NULL AND Passport IS NOT NULL) OR (DocType IS NULL AND Passport IS NULL AND IDCard IS NOT NULL)

 

[ahti098]

I have DocExpiry table where Employee name comes form Employee table and DocType comes from DocType table. Therefore in the DocExpiry table the value is 3 from table DocType IDDocType what is the Primary key.
Problem is that doc type 3 what is Seamans passport cant be empty or 0 .In the table Doc Expiry employee can have a record for Seamans passport (and for IDCard and for Passport) or not. ANd if he does not have an entry to seamans passport then the next document should be selcted. Actually what I need from the Query is the document number to be put into a Report. So that if in the table Doc Expiry certain documnet and its expiry and number is not entered the next one should be selected for the report to display.
Hopefully I could convey my meaning

 

[MajP]

If I understand. You can have different documents in the Doc Expiry table. If they have a Seamans Passport then show that, if not show Passport, if not show ID Card.
I assume in the document table these are in order of precedent. Seamans is 3, Passport is some number greater than 3, and ID Card is some other number greater than Passport. If not I would add to that table a Precedence sort field.

If you do a Group by query on DocExpiryEmployee and MinDocType that will return first Seamans, if not Passport, if Not ID Card. Then use that in the above query. Link that to the employee table and another link to the Expiry Table. You probably want outer joins so if they have none of these IDs they still show in the report.

 

[ahti098]

thanks but seems that my knowlege is still lacking to fully understand what you meant. could you illustrate a little

 

[ahti098]

specially that part where I could get the results for "return first Seamans, if not Passport, if Not ID Card"