Create Table Query from Mutliple "Fields"

[andmunn]

Hello All!

Thanks in advance for the help i'm hopefully about to receive. This one has me stumped. Please note the attachment to better illustrate my issue. I need to create a query (or multiple queries) to solve this problem.

I essentially have 2 table:

1. Table BIC

A list of codes that will be updated monthly, which will be the basis for querying the second table. Approx 100 rows of data.

2. Table Original

A data file obtained from IT where i'll need to sort it to find any codes that are including in Table1. This includes approx ~ 10,000 row of data.

** Please note, the "BIC" from "Table BIC" can appear in any of the 5 BIC columns in Table Original.

What i need to do is create a query that will:

1. Search the "BIC" from "Table BIC" in all 5 columns of "Table Original".

2. Where it has a hit, it will create new table - for example, the first row of table Original includes the BIC "ABC" in the "BIC 1" column. A query would create table "ABC" and place this whole record (all 8 fields) in new table "ABC". No modification needed.

3. Where two (or more) BIC's from "Table BIC" appear in one record in "Table Original" - the result will only need to be placed in one of the new tables (really doesn't matter which one). For example, Record #4 includes the BIC "ABC" in field "BIC1" and the BIC "DEF" in the field "BIC4". Therefore, a new table would be created (either ABC or DEF) to capture this information.

Let me know if this makes sense. I'm entirely stumped if there is a way to do this.

Thanks!
Andrew.

 

[plog]

No it doesn't make sense, because you haven't explained why you need this improperly structured table system. You've got 2 red flags with this:

1. You shouldn't store specific information in a table/field name.
2. You shouldn't have multiple tables with the same structure.

Then when you look at it specifically, this means you could potentially have 100 tables (1 for every record in BIC). I don't know what the endgame is, but this seems like its only going to cause more issues down the road.

How are these tables going to be used?

 

[andmunn]

Hi Plog,

You are correct - in the end, there is a potential to have 100 tables. I agree this could potentially be done better. I've followed up with the requester to get a better understanding of the end-to-end process.

Essentially the way it works now, once the report is sorted (this is done manually now), a certain minimum # of "BIC's" (from each new "created table") are reviewed based on a number of criteria including: a minimum # to be reviewed, a minimum $ threshold, etc.

I'll try to find out more and update it here.

Andrew.

 

[andmunn]

Hi Plog,

I've clarified a few points.

1) What i'm creating here really is just an "automated filtering/reporting" process - the "created" tables will then be manually exported (cut and paste) to an excel file for further analysis/action - hence why the need to create "separate" tables.

2) I don't have the ability to edit how the data comes in from "Table Original" (this is an import of an excel sheet received from IT).

2) While i stated above i wanted to avoid duplication (i.e.// where one record could have multiple BIC's), this really isn't necessary. Therefore, where a BIC (for example, ABC) is present in "Table Original", it would appear in it's own "ABC Table". If that same record had another BIC (in one of the other BIC fields), it would also appear in that table as well.

Does that help at all?
Andrew.

 

[plog]

the "created" tables will then be manually exported (cut and paste) to an excel

I can't in good conscience allow that to happen to someone. Here's what I would do:

Create a UNION query comprised of 5 SELECT queries (one for each BIC column), using this template:

SELECT BIC@ AS BICTable, ID, Amt, DateBIC, BIC1, BIC2, BIC3, BIC4, BIC5 
 FROM TableOriginal
 WHERE BIC@ Is Not Null

You would replace the @ symbols in that query with the numbers 1-5, so that you have a UNION query made up of 5 of those templates (1 for each number 1-5). Save that query as sub_Main.

Then you would bring sub_Main and TableBIC into a query, linking the BIC field in TableBIC to the BICTable field in sub_Main. Run that query and you have all the records you need per your instructions above. The BICTable field tells you which table they would have gone into had you created hundreds of tables.

From there you should be able to write some VBA that loops through that query and automatically spits the data out to individual Excel files. Of course, I'm certain that whomever is going to 'analyze' those hundreds of files could be helped out further if you understood the process more.