Access lookup wizard and how it can affect table relationships in Access 2007 2010 2013 2016

in this video uh I want to show you something interesting that happens with the Lookup Wizard with Microsoft Access uh in this case I have a simple database first of all I have apartment number and tenant name and you can see it just has uh theart Pate number and the Tenant name the ID is a primary key it's also uh the Auto number field and then we have a current tenant field that's a yes no fuel then we have another table that's called tenant notes and I want to be able to have this field over here be a lookup uh so let's go ahead and go back into design view and we're going to pick on the app pivate number and tenant name and then for the data type we'll go into the Lookup Wizard uh in this case uh I want the lookup field to get the values from another table so I'll click that and then click on next the table I want to go to is called apartment number and tenant name so we'll get that and pick on next now uh we're going to use the ID field and the apartment and tenant name might as well sort it by the apartment and tenant name now notice how I'm going to hide the key column right there and I'm going to keep that checked uh so that in the actual display of the table the appartement intendant name will display and then uh that'll be fine we'll pick on finish and we're going to say yes to save this table now now it says some data may be lost you know uh it's kind of interesting here it's going to change the data type from a text field to a number field and the reason that it does that is you have to look at this other table you see it's really in the actual table it's going to store the ID field which is a number field uh so that that's why this field over here became a number uh let me show you how we're going to deal with that in a query so I'm going to right click on uh the tab here and pick on data sheet view see in the Wizard I made it display the text field and I hid the uh the um actual number but really it's storing the number in the table the lookup is just provided in that text field right there um but here's the problem U let me show you a little bit more about this table right now I'm going to go back to design view I'm going to pick on that field and uh it's really important to look at the lookup tab here and notice how it is getting the uh if I look at the row Source it is getting the uh the ID and the apartment name but look at this uh information here it does say two column counts but notice how the First Column width is zero uh so that means it's going to hide the apartment um the ID however that's really what's being stored in the table all right the problem occurs when we start to make a query out of that table so we're going to go ahead and close that window and we'll save it now I'm going to make a query here and so I'll pick on the create menu and we'll do query design and I'm going to try to uh use a query between those two tables so here's tenant and then here's the apartment name now I'm going to pick on close here now actually because I did the relationship the way I wanted to um the the link is already there but I'm going to delete these links within the query now because that let me let me get rid of this table for a second I want to pick the uh the field here you see even here it shows the it's displaying the apartment number and the tenant but in the table the actual ID is being stored in that field uh so when I want to make a query to the other table I'm going to pick on show table here and pick on apartment number and tenant name now it it kind of brought the relationships over but I'm going to delete those if you were going to make that relation ship yourself you would think that they're going to be linked on the apartment number and tenant name and Link those two Fields together but that's not really going to work in fact uh this is what the person said to me I'm going to try to highlight that and try to run that and it says data type mismatch that's because this is really a number field near and now this is really a text field so actually you want to make the link between the apartment number and the ID field and then we can pick on the current tenant field from the other table and then it'll allow us to run it just fine as we can see all right so the trick with that is when the Lookup Wizard uh happened it actually stored the ID from the other table instead of the text it's just that the ID is a hidden field but that's what's being really stored in the other table and therefore that's what you have to use uh when you're making a query and you're trying to link back to the second table so uh this is a great question that somebody uh had and hopefully you can see how they resolve that issue