Modifying export query

[ittechguy]

I'm not sure if I have a SQL problem and I'm not even entirely sure how to phrase it in the title, so please bare with me. It might not even be an access problem. If so, I'm sorry.

Many of you are aware of my database project. In short, it keeps track of equipment which is located in rooms (inside buildings).

I have built a query which I use to export data from tblEquipment (and a couple other tables) as a txt in csv format. I use the txt file to run a vb script which creates the config folders/files needed to log into our switches. The config file is used to create "sessions". Basically what it looks like is it will display a folder. When I click on the folder, it will drop down and display several .ini config files which are labeled as the hostname of the switch I'm logging into. I've created the query and set it to export as txt and run the script so that just one button click will update our config files and it saves us a lot of time.

Here is my SQL:

SELECT tblEquipment.EquipmentIP AS hostname, tblEquipment.EquipmentName AS session_name, "SSH2" AS protocol, "" AS username, "Bldg "+tblBuilding.BuildingName AS folder, "VT100" AS emulationFROM (tblBuilding INNER JOIN tblRoom ON tblBuilding.BuildingPK = tblRoom.BuildingFK) INNER JOIN (tblCabinet INNER JOIN tblEquipment ON tblCabinet.CabinetPK = tblEquipment.CabinetFK) ON tblRoom.RoomPK = tblCabinet.RoomFK;

As you can see, there are a few fields which are not located in the database but are needed in the txt file because the script needs them. I've added "Bldg " to the beginning of every folder name so that the folders will be called "Bldg A", "Bldg B", etc.

The problem I'm having is that while most building names are actual building names (and don't need an address to be listed), a small few (like 5-10) are houses which of course, have an address and don't have a building name. I would like if these houses were in their own folder in the config file, as opposed to how it is with the buildings, one folder per building. I do not want to re-design the database to handle addresses because it is not worth it for these select few. But "Bldg 'This address' " doesn't make much sense.



Not sure how to handle this. Maybe there's something that can be done in the SQL. Also I should add, I don't mind if each address is hard coded in because there is not many and its extremely unlikely any would be added or removed in the future.

[ittechguy]

I've found a couple solutions. The first is to use CASE buildingpk IN() to specify if its a certain buildingPK, then it is a "house" ELSE Bldg + etc....

Second, and far easier (like idk why I didn't think of this before easier) is to include another field in tblBuilding called "folder" or something similar and require that that field not be empty and then specify the folder name. So "Building A", "House", etc. It is another field with mostly duplicate data, but I think its worth it. Vs. hard coding, and then if data changes, it won't work like it should.

I'm marking this as solved. Thanks anyways! Sometimes typing out my questions is enough to get me thinking to figure it out on my own.