2

I have 3 tables

ConnectivityFile where I have the ids for some properties

CHAINDD where I have the hotel codes (they can be repeated) for the same properties

Export where I want to have all the hotel codes and their ids (a join for the previous tables)

I can achieve that with the query below

query2 = "SELECT ConnectivityFile.ID as [Hotel ID], ConnectivityFile.HotelCode  
FROM ConnectivityFile 
INNER JOIN (SELECT DISTINCT CHAINDD.HOTELCODE as [Hotel Code] from CHAINDD) as T 
ON ConnectivityFile.HotelCode = T.[Hotel Code]"

DoCmd.RunSQL "INSERT INTO Export " & query2

Now the issue I am facing is that let's say the hotel code from table CHAINDD is not present on table ConnectivityFile. I would like to point that out as "Not Connected" as the ID on the export table.

How can achieve that? I know there is the IIF statement when I have done some trial and error with no success. Where do you place the IIf statement?

Thank you very much for all your help and time

June7
  • 19,874
  • 8
  • 24
  • 34
Natalia Fontiveros
  • 115
  • 1
  • 1
  • 9
  • 2
    I see you are using an inner join. Are you familiar with outer joins? – Ruud Helderman Dec 10 '18 at 19:51
  • 2
    *Not Connected* is a string type, so an exception will be raised if Export's `[Hotel ID]` is integer type. – Parfait Dec 10 '18 at 19:52
  • Show us db schema, sample data, current and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. [**How to create a Minimal, Complete, and Verifiable example**](http://stackoverflow.com/help/mcve) – Juan Carlos Oropeza Dec 10 '18 at 19:54
  • 2
    I agree with @RuudHelderman. You need an outer join. – Robert Harvey Dec 10 '18 at 19:55
  • 1
    @RuudHelderman, Thank you , I needed an outer join as you suggested. I didnt see it thank you – Natalia Fontiveros Dec 12 '18 at 17:14

1 Answers1

0

as long as your query actually works, the IIF goes something like this:
query2 = "SELECT IIF(ISNULL(T.HOTELCODE), ""Not Connected"", ConnectivityFile.ID) as [Hotel ID], ConnectivityFile.HotelCode
FROM ConnectivityFile LEFT JOIN (SELECT DISTINCT CHAINDD.HOTELCODE as [Hotel Code] from CHAINDD) as T ON ConnectivityFile.HotelCode = T.[Hotel Code]"

basically, if the code is there, use it, otherwise put a string staying "Not connected"
Note that the hotel code field in Export must be able to handle NON-unique data - ie: not be a primary key.

trevor
  • 257
  • 3
  • 9