Geek asks and answers

Access LookUp table

I need to create a lookup table in Access, where all abbreviations are associated with the value, and if the abbreviation (in the main table) is zero, then I want to show "Unknown"

I got values ​​that work, but I can't show that zeros are displayed.

my lookup table is as follows:

REQUEST     REQUEST_TEXT
------------------------
A           Approve
D           Disapprove
NULL        N/A

but when I do a count on demand, it only shows the values ​​for A and D, although I know that there are some spaces.

what am I doing wrong?

+3
source share
1 answer

It will be easier if you change tblLookup.

REQUEST     REQUEST_TEXT
------------------------
A           Approve
D           Disapprove
U           Unknown

tblMain REQUEST Required = True = "U". , U REQUEST, A D.

, REQUEST, , , , .

SELECT m.REQUEST, l.REQUEST_TEXT
FROM tblMain AS m
    INNER JOIN tblLookup AS l
    ON l.REQUEST = m.REQUEST;

, , "X" REQUEST.

Edit: tblMain , , Nz() LEFT JOIN.

SELECT m.REQUEST, Nz(l.REQUEST_TEXT, "Unknown")
FROM tblMain AS m
    LEFT JOIN tblLookup AS l
    ON l.REQUEST = m.REQUEST;

, , ASP, Nz() . , IIf() Nz().

SELECT m.REQUEST, IIf(l.REQUEST_TEXT Is Null, "Unknown", l.REQUEST_TEXT)
FROM tblMain AS m
    LEFT JOIN tblLookup AS l
    ON l.REQUEST = m.REQUEST;

Edit2: Null. "", tblLookup, JOIN, Nz tblMain.REQUEST

SELECT m.id, m.request, l.request_text
FROM tblMain AS m
    INNER JOIN tblLookup AS l
    ON Nz(m.request,"U") = l.request;

tblLookup REQUEST Null REQUEST_TEXT = Unknown, , Nz JOIN. Nulls . .

+3

More articles:


All Articles