Query Producing Too Many Records

[kestefon]

My raw data contains a text string with various codes separated by an underscore. For example, my raw data would contain a string like this, "SAMPLE_DATA_ABC1_NYC", and each of those codes would have corresponding name that is mapped out on another table.



I'd like to set up a query that would evaluate the raw data, look up the associated code (e.g. "_ABC1_") and then return the corresponding name. (See screenshot for an example.)



Any idea how to do this? I tried using IIf and InStr statements, but I keep getting excess rows.

SELECT st.Campaign, st.Month, st.Week, st.Placement, st.[Placement ID], 
IIf(InStr(st.placement, ic.[findInventoryCategory]) > 0,ic.[InventoryCat],"N/A") AS [Inventory Category], 
IIf(InStr(st.placement, pth.[findPathway]) > 0,pth.[Pathways],"N/A") AS [Pathway Name]
FROM RawDataFile AS st, mpInventoryCategory AS ic, mpPathway AS pth;

[pbaldy]

The excess rows are almost certainly because you don't have a join between tables in the FROM clause. Without joins, you get a Cartesian product. The tables should be joined on whatever field(s) relate them.

[June7]

Why is multiple discrete values combined into a single string to begin? That is poor data structure.

I expect the main reason is there is no JOIN clause. This results in a Cartesian join - every record of each table joins to every record of each of the other tables.

Also, RawDataFile probably has 1-to-many relationship with both mpInventoryCategory and mpPathway.

Are the Product and City the only two values of interest? These correspond to the ABC1 and NYC components of the string? Can SAMPLE_DATA be discarded? Is the length of each component consistent? Will there always be 4 parts?

[kestefon]

Thanks. Is there any way for me to join the tables? The only common element would be codes such as "_ABC1_", etc., which are found within the string in the RawDataFile.

[JoeM]

One way is to create a query on your one table that has all the combined fields, and create a bunch of calculated fields to pull them out (using Text functions like INSTR, LEFT, RIGHT, MID, etc).

Then, you can link that query to your other table based on the common join field(s).

[June7]

Breaking up 3-part data within query is not too difficult. See post 6 in https://www.accessforums.net/import-...ers-41262.html

So unless the length of each part is the same for all records, more than 3 parts will probably need a custom function.

[John_G]

Hi -

With the common elements embedded in the text string, and not in their own fields, then you can't join the tables in the normal way in design view - you will have to use a where clause in a criteria field, and to do that you would have to know that each part of the string field always starts in at the same character position, and is the same length.

One of the where's might look something like this:

where mid(rawdatafile.string_data,18,3) = pth.city (It is not at all clear from your example what your field names are)

As June7 points out, having multiple data items encoded into the same field is not good database practice, and this is one reason why.

HTH

John

[kestefon]

One way is to create a query on your one table that has all the combined fields, and create a bunch of calculated fields to pull them out (using Text functions like INSTR, LEFT, RIGHT, MID, etc).

Then, you can link that query to your other table based on the common join field(s).

Thanks. So, I actually took that approach in Excel, but I decided to try Access since the formula calculations moved very slowly when I had a "large" dataset (i.e. thousands of rows).

I was using Find and Substitute functions in Excel and finding the nth instance of the underscore. Is there a function that can do that in Access (find the nth instance), or does that have to be a custom function?

[June7]

There is no intrinsic function in Access to do that. Excel functions can be referenced in VBA. Regardless, looks like have to write custom function. Invoking Excel functions within the custom function is an option.