Dynamically find table name based on field

[Jrod2541]

Just started learning VBA so I'm not sure if this is an easy problem to solve or not. I'm trying to find the name of a table based on the information in another table's field.

So I'm looking into one table called "PreferedVendor" and there is a field in it called "Vendor1" I need to look at the "Vendor1" field and check what's inside of it. If it says for example "ABC", then I need to select the table named "ABC STATUS".

Any idea how I can do that?

I have no idea where to start so I decided to ask you guys

[twinnyfo]

Jrod2541,

Please provide a little more information about what you will be looking for in that Table. If a field is related to another Table then obviously it exists, and you should be able to find out what is in it, but I don't think this is what you are asking.

Please provide greater detail so we can properly assist you.

[Rabbit]

It sounds like the data model is not normalized. If you have multiple tables with the same fields where the only difference is the table name, that's usually a bad idea. It's the reason why your current query is a nightmare to build.

But that's just a guess, you haven't provided enough information for us the know for sure.

[Jrod2541]

Sorry I wasn't clear enough, there is a lot detail I left out so I'm just going to start over. There are 2 tables, not directly connect, there are tables that connect them


The first table is called: "PreferedVendor".
In this table there is a field named: "Vendor1". Inside that field is a list of vendors. There is another field inside the same table named "SKU ITEM #"



Their are several other tables that are named by the vendors in the "Vendor1" field inside the table "PreferedVendor".

So if under the "Vendor1" field there are is a vendor named "ABC", I need to choose the table that is name "ABC STATUS". If inside the field "Vendor1" is a vendor named "XYZ", I need to choose the "XYZ STATUS" table. Inside these STATUS tables is a field named "VENDOR ITEM#" that match the prefered vendors.



Like I said earlier, there are tables that connect "PreferedVendor" and the "(Whatever Vendor1 is) STATUS". These tables are called "(whatever vendor 1 is) CROSS" Inside these tables are two fields called "SKU ITEM #" and "VENDOR ITEM#".

This is how "PreferedVendor" and "[Vendor1] STATUS" are connected.



Something else I should have mentioned eralier is that inside the "[Vendor1] STATUS" tables is a field named "CAN SHIP SAME DAY". I need to check to see if that table says "Yes" or "No" (This is not a boolean or a Yes/No Data Type, It is simply Short Text) for that specific "VENDOR ITEM".

If it says "Yes" Then I make a new row in a table named "orders".

If it says no then I look at the "Prefered Table" again and do this whole process over again with a field named "Vendor2", If it says "Yes" then I make a new row, if not then I move on to "Vendor3". I do this until it reaches "Vendor5"



What I can't do is make an IF statement saying "If Vendor 1 is 'ABC', then check 'ABC STATUS'" because there will be many vendors in the future so this needs to be dynamic.



I think that should cover everything. Tell me If I missed something

[Rabbit]

As I suspected, your data model is poorly designed. You shouldn't have a table for each vendor. It makes querying the data a nightmare as you are personally experiencing right now.

You should redesign the data model to normalize the structure which would make the query much easier.

In your case, you should only have one table for vendor status and include a field that identifies the vendor. That allows you to join on Vendor1 field to the Vendor Status table.

For more information about normalizing your data, please read this article: http://bytes.com/topic/access/insigh...ble-structures

[twinnyfo]

Jrod2541,

I think your first step is to have one table for vendors, rather than having a separate table for each vendor. If you plan on expanding the number of vendors, it would be pure insanity trying to maintain a table for each vendor. With one table, you have a list of all your vendors with all the data that is specific to each vendor in one record.

It is difficult to understand what you want to do with your tables ABC Status etc. Are these just one record? If not, what types of data do you have in these tables?

Without knowing how your tables are set up, it's impossible to say how to set them up better. Right now, the multiple tables option does not seem to fit the basic principles of database normalization (see this topic).

Either way, it looks like you will need some re-engineering, but we can work through those steps to get you up and running.

[Jrod2541]

Alright, Thanks guys. I'll be more careful when organizing my tables. I'll keep that article for the future. As for now, I'm just going to focus on redesigning my database.

Thanks again

[NeoPa]

I fully support what the other guys have said about database normalisation. It may seem a pain to restart from scratch now, but believe me that is nothing to the pain you would have experienced had you proceeded along your original course.

It's probably hard to be hit with something like this and feel really lucky but, trust me, you have been.

As a final tip - Note the spelling of "Preferred". It may seem unimportant but if your work is for someone else (contracting for instance) then misspelling common words is a sure-fire way to lose respect/work.

Good luck with your project and remember we can continue to help.

Last, but not least, if you're new to Access work then you'll find a bunch of helpful tutorials if you follow the link found in Database Analyser.