Table Maintenance Using Outside Data

[Wabash&Erie]

Hi,

I am building a database of banks and credit unions for the company I work for. It is for particular use by my department and not a company-wide effort.

I found a daily download essentially from the federal reserve which includes a unique ID, bank name and other useful information which I want to utilize. Of the full list, the banks fall into 3 categories relative to my company and user team:
1. The bank is a direct customer
2. We have information about the bank which we want to maintain, but they are not as-of-today a direct customer
3. The bank is not a customer and we have no information about the bank.

My idea is to create a definitions table that ties to the Fed's unique ID and otherwise only includes OUR fields of data. I have no goal of editing or copying data from those external tables from the download. Could I have a form that allows users to search/find an institution and then check a box which would either append the new ID to the definitions table? Or, do I copy the full list of IDs only to my internal table and update a yes/no field (customer or not-customer)?

I hope this makes sense. If it requires clarification please let me know.

 

[plog]

Not the clearest. Where is "OUR fields of data" coming from? The Fed file or built internally by your department?

I would suck in the data from the fed, then have a definition table of banks which would hold the Fed ID, your internal reference for the bank and the category it falls into (1-3).

Perhaps you can demonstrate your issue with data.

 

[The_Doc_Man]

Your question is ambiguous in the sense that either method makes a certain kind of sense. The deciding factors would relate to what you want to do with the data now and what, if anything, are your future plans if some bank suddenly becomes a potential or actual customer. HOWEVER, this sentence kind of limits your options: "Could I have a form that allows users to search/find an institution and then check a box which would either append the new ID to the definitions table?" I think this implies that to do the search and check the box, you need to import this information so that it would be present when you want someone to check it. Because your second option just looks like a short form of the first option.

 

[Wabash&Erie]

Yes. I agree it is ambiguous. It made sense when I wrote it. When I went back and reread, it is a little confusing. Ha! But, rather than try to expound (pre-responses) I decided to leave it alone and wait on questions. let me say a few things:
1. Because I knew what I meant, the responses above already appear to address my original question. Having said that . . . .
2. To simplify; I have a database of banks. The goal is to have the primary data (unique ID, bank name, bank address, et al) used from the Fed feed which will never be edited but used for queries and reports.
3. The Fed feed is ALL banks and credit unions in the US. Some of them are customers, some are prospects, and some are out-of-play for our purposes. Therefore, for our internal purposes, every bank in the file falls into one of those three categories.
4. "our data" Let me simplify by saying that there is other bank/credit union data that needs to be in the database that is NOT from the Fed feed. Some of it is input from our internal users such as bank contact information. Some may come from other government entities or from state/local/private banking associations.
5. What I was looking for is the best way to say, "Ok, here is a full list of all institutions from the Fed feed. Let's use their data as the basis and develop reports that take from those tables. But, how do I import a full list of all US institutions and inside my database know which ones are customers and which ones are not? I don't want to duplicate data across tables unnecessarily"

I hope this simplifies what I'm working with and what my question is.

 

[Pat Hartman]

I would keep the two separate as much as possible. Do you know what happens with the download data if a bank goes out of business? Does that bank disappear from the download or does it stay marked as closed? If the later, there is no problem keeping the two separate. What you don't want if possible is to end up with banks in YOUR table that do not exist in the Fed's download.

I would not add new banks from the Fed to your list. Just leave your list small. This will require that you always use a left join when you join your table to the bank table or vice-versa.

When the user wants to add a new bank to your table, he can double-click on a Fed bank and you can pop up a form where he can add the additional data.