Insert foreign key with and without Null value

Question

I have a relational database in MS Access which contains 4 tables: dummyTable, Table1, Table2, Table3. dummyTable holds the foreign keys of others.

Now, for inserting, I am using the query:

INSERT INTO dummyTable (Col1, Col2, Col3, FK_Table1ID, FK_Table2ID, FK_Table3ID)
    SELECT '25' AS Expr1, 'test2' AS Expr2, 2 AS Exp3, Table1.ID, Table2.ID, Table3.ID
    FROM Table1, Table2, Table3
    WHERE (((Table1.dummyName) = 'Germany') 
            AND ((Table2.dummyName) = 'Berlin') 
            AND ((Table3.dummyName) = 'dummyStreet'));

Which is basically, selecting other columns value and insert the foreign keys in my main table (dummyTable). This is working.

But the problem is here some of the values are optional. Let's say I must not have Table2.dummyName. If I try ((Table2.dummyName)='') Or ((Table2.dummyName)=Null). It doesn't insert anything / 0 row inserted in precise but runs successfully.

My query actually connected with VB.Net at the backend so I want to send DBNull.Value in my parameter. I have tried also only the selecting query:

SELECT '25' AS Expr1, 'test2' AS Expr2, 2 AS Exp3, Table1.ID, Table2.ID, Table3.ID, Table4.ID
FROM Table1, Table2, Table3
WHERE (((Table1.dummyName)='Germany') AND ((Table2.dummyName)=Null) AND ((Table3.dummyName)='dummyStreet'));

And it produces no rows. So I tried:

.....(upper part is the same)
WHERE (((Table1.dummyName)='Germany') AND (IIF(((Table2.dummyName)=Null), Table2.ID = Null, ((Table2.dummyName)='Berlin')) AND ((Table3.dummyName)='dummyStreet'));

But again, no row inserted in case of Null, but works When (IIF(((Table2.dummyName)='Berlin'), Table2.ID = Null, ((Table2.dummyName)='Berlin'))

Can anyone please help me with it? I am really stuck here. Is it possible by using IIF or Switch, or I am completely in the wrong direction?

Answer 1

Well, it turns out you can. Whether it's best or not. The code:

INSERT INTO dummyTable (Col1, Col2, Col3, FK_Table1ID, FK_Table2ID, FK_Table3ID)
    SELECT Top 1 '25' AS Expr1, 'test2' AS Expr2, 2 AS Exp3, 
      IIf(((Select t1.ID From Table1 t1 Where Exists (SELECT t1.ID FROM Table1 t1 WHERE 
       t1.dummyName = 'Germany') AND t1.dummyName = 'Germany') Is Null),t1.ID=Null,(Select 
       t1.ID From Table1 t1 Where t1.dummyName = 'Germany')) AS TableOne,
      IIf(((Select t2.ID From Table2 t2 Where Exists (SELECT t2.ID FROM Table2 t2 WHERE 
       t2.dummyName = 'Berlin') AND t2.dummyName = 'Berlin') Is Null),t2.ID=Null,(Select 
       t2.ID From Table2 t2 Where t2.dummyName = 'Berlin')) AS TableTwo,
      IIf(((Select t3.ID From Table3 t3 Where Exists (SELECT t3.ID FROM Table3 t3 WHERE 
       t3.dummyName = 'dummyStreet') AND t3.dummyName = 'dummyStreet') Is 
       Null),t3.ID=Null,(Select t3.ID From Table3 t3 Where t3.dummyName = 'dummyStreet')) 
       AS TableThree
    FROM Table1 As t1, Table2 As t2, Table3 As t3;

Please keep in mind this is just a dummy Code so I didn't put afford to go with parameters. It's highly recommended not to do so.

Answer 2

If my assumptions about what you are doing are right you are going in the wrong direction. Access is about ease of use and rapid development. I'm assuming table 1 is countries, table 2 is cities, table 3 is streets and dummy table represents the many to many relationship of addresses. It also looks like you plan to use access to do the data entry. In that case, the best approach is to start by making the appropriate tables and then going to the ribbon in access and selecting database-tools-relationships. Make the following set of normalized relationships.

If we make the relationships first Access sometimes will do a better job auto creating the forms we need for data entry. Creating the forms to insert and update the data is the next step. select the addresses table like shown and click create form on the ribbon and access will automatically create a data entry form where you can update and insert null values with no code required. To make a cell in the address table null just empty the corresponding form cell or vice versa. Unfortunately the form needs a little work to make it more user friendly. We want a form like:

Where we can select from legitimate values for items like cities (combo boxes) and where we can update current records or insert a new one by cycling past the last record(yellow circle bottom). but access defaults to :

So delete the addressid boxes as Access handles ID's behind the scenes and the user never needs to see them. adjust the labels and turn the foreign keys into combo boxes showing a human readable value. for instance right click on city and select change to combobox. then adjust the following settings:

Now we can add new addresses but not new cities and countries. to create a form to add a new city just click on the cities table and click create form again. You will get a form to add and update cities and thanks to setting up the relationships access will even create a subform where you can add addresses at the same time. I recommend deleting the subform if you are not comfortable with it. Adjust your new cities form to suit as you did with addresses: