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?

Did but no luck. I guess what I am missing here is Selecting in IIF condition. I guess, I gave the condition but don't know where to send Select query. — Mohammad Rafi, Dec 09 '20 at 00:24
Can't compare anything to Null so =Null or <>Null will never work right. Are you saying the data in table is optional so field may be null? I don't use dynamic parameterized queries. I build WHERE clause in VBA and apply to form/report/action SQL constructed in VBA. — June7, Dec 09 '20 at 01:21
What I want is, If user don't select any selection in my VB.net combobox, then send ```DbNull.Value```, and in query skip that part (dummyName in Table2 in this case), and enter all other values, and so on. I also realized my query in checking with```'iif``` if table contains Null Value. That is not what I want. I want if my query has no row insert Null in ```FK_Table2ID```. Have idea how to get that? By the way, No experience in VBA unfortunately. Doing ADO.NET plain. Thanks @June7 — Mohammad Rafi, Dec 09 '20 at 02:09
Okay, construct action SQL statement in whatever language is used. But try: `Table2.dummyName LIKE combobox & "*"` — June7, Dec 09 '20 at 03:40
Thanks for your answer. ```Table2.dummyName Like 'Berlin' & '*'``` Produce one row. Perfect. But when I have nothing, let's say ```Table2.dummyName Like '' & '*'``` Or ```Table2.dummyName Like Null & '*'``` then it creates 28 rows in my dummyTable as my Table2 has 28 rows. Where I want if it's nothing than insert nothing in dummyTable(FK_Table2ID) @June7 — Mohammad Rafi, Dec 09 '20 at 03:59
Edit question to show sample data and desired output. I don't see how Access query alone can accomplish what you seem to want. I would program this in VBA. — June7, Dec 09 '20 at 04:04
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 25 years** ago) and its use is discouraged — marc_s, Dec 09 '20 at 05:27
Thanks for mentioning. This is the query you get if you design with MS Acess Design View. Just used for demonstration. @marc_s — Mohammad Rafi, Dec 09 '20 at 05:36

mazoula · Answer 1 · 2020-12-09T12:18:15.057

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:

That's some afford!!! Thanks a lot!! I do agree with you, access is about to ease the process. I will keep that in mind and will try to convert as you recommended. Thanks again, that's really a big afford. — Mohammad Rafi, Dec 09 '20 at 08:47

Mohammad Rafi · Accepted Answer · 2020-12-09T09:35:26.993

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.

Insert foreign key with and without Null value

2 Answers2