Hi there,

I have two tables (EWO, EWOStatus) that are in one to many relationship. EWO stands for Enhanced Work Order. For each ewo record, there could be more than one ewo status record which has Pillar Responsible, Pillar Owner and Status fields.

I have created a printout report that displays some of the fields in the EWO table and EWO Status. On the printout report I need to display the Pillar Responsible field which in ewo status table.

Since ewo table is in a one to many relationship with ewo status table, I need to display the first row/record of the ewo status table for pillar responsible field for that particular ewo record

I tried: [tblEWOStatus].[pillarresponsible].Row(0) but this does not work.

Any help is greatly appreciated.

I have reposted this question and worded differently since I did not get any reponse for a couple of days now.

Thank you.

Have you tried a DLookup?

Thank you very much pbaldy.

Here is my DLookup function: =DLookUp("[PillarOwner]","tblEWOStatus","[EWOID] = Forms!frmEWO!EWOID")

As you know this function returns the first occurence of pillar owner which is okay for now.

But, I would like to dynamically change the report's pillar owner when a new record for ewo status is inserted. In other words, I don't always want to show the first occurence of ewo status record.

Could you please advise?

Thanks again for your help.

Well, first you said "I need to display the first row", now you're saying "I don't always want to show the first...". I'm confused, so perhaps a sample db with a specific description of what you want to show is in order.

Hi Paul,

Since I have a one to many relationship, there could be more than one ewo status record for each ewo record.

If there is only ewo status record for an ewo record, the DLookup function will obviously return that only record.

What if there are more than one record, the DLookup function returns a random ewo status record. How do fix that?

I alway want to show the last record if they are more than ewo status record for each ewo record.

Thanks a lot.