Need to display value from another table if current value is null

[RCurtin]

There are 3 main tables

tblDrawingRegister - stores information about construction drawings
tblDrawing Revisions - each drawing in tblDrawingRegister has one or more revision or version
tblVendorDrawingList - this table has a list of drawings numbers that correspond to the drawings in tblDrawingRegister.

The drawing numbers from the vendor table as provided as an 'extra' as some people are more familiar with this numbering system.

Now the issue is that there is a one-to-many relationship between tblDrawingRegister and tblVendorDrawingList. In the screenshot I've attached there are 14 drawing numbers that correspond to one drawing number in tblDrawingRegister. (This is because the main supplier has packaged 14 drawings as one with just one drawing number). When this is the case I want to display the drawing title from the Vendor table. Otherwise if should just show the title from tblDrawingRegister.

I have tried using Dlookup to show the title from the vendor table when there are dublicates in the vendor table. It didn't work and I think this would be really slow. I've tried a few other things too but to no avail.

I really hope that this is clear. I've attached screenshots of the query too.

 

[KeithG]

you will need to use the IIF function in you query.

 

[RCurtin]

Thanks for the reply KeithG. I did try using IIf but was definitely making it more complicated than necessary. Got it working in the end:
DrawingTitle: IIf(IsNull([VDrawingTitle]=True),[DrawingName],[VDrawingTitle])

So if the vendor drawing title is null (i.e.) there is no corresponding vendor drawing number it shows the main drawing title.

 

[RCurtin]

Hi,
I've realised that there is another issue with this.

What I'm trying to do now is show the title from the main table if there is only one matching record in the vendor table. (The title in the main table tblDrawingRegister is the more official one and they can vary slightly).

Otherwise if there are several drawings in the vendor table for the one in the main table - it will go with the title in the vendor table.

Now I've just created a DLookup expression that will check if there is more than one matching record in the Vendor table:

Multiple vendor drawings: DLookUp("[MHINum]","[DuplicatesInVendorDrawingList]","[tblVendorDrawingList]![MHINum]='" & [tblDrawingsRegister]![DrawingNum] & "'")

It does it by looking for each drawing number in a query that shows dublicates in the vendor table. However this has made the query really really slow - there are 8000 records altogether and this will grow..

I'm sure there must be an easier way to do this??

 

[KeithG]

Have you tried using DCount insteat of your Dlookup expression. DCount will count the number of times a certian value is in a table.

 

[RCurtin]

Thanks again Keith. Didn't realise there was a DCount function. That will come in useful.

I just had another idea though. I could do a query to update the vendor table with the title from the main table where there is only one matching record.

I think that that will be the faster than having an iif with a Dcount function in it. The database will grow alot more so that would be the better option I think.

Thanks again for your replies though. I would be lost without this forum being the only IT/database person where I work.