I’ve used Access in various roles for a long time, but the role I’ve been in for the last couple of years has had me in a different headspace completely, it seems like I’ve forgotten two-thirds of what I used to know how to do. I’m hoping for some help with a strange database that I’m consolidating out of about a dozen different semi-related lists that other people and departments have created to track info about videos being used in for a huge online training player system that needs to be more centrally available.
The best way to get the data to normalize into the correct parent/child relationships has been to turn all the data into plain text using Excel 2013, and then export the records into Database A; and then to append these string records into Database B, where I was able create the correct relationships in empty tables. This has worked a treat, but NOW I someone has requested that someone be able to query/report the data out according to their divisional “tagging” schemes. The trouble is….there are probably 200 different random tags that range from 2 characters long to probably 30 characters long. My understanding was that all they wanted was for the tags to be CAPTURED, not used for reporting.
So, OK: I know I have to put in parameters….but what? I know I can’t just go to the query design grid and put Like "**"or Like "***" or Like "****" down to 25 wild cards. (I tried. I told you: I’ve forgotten most of what I used to do every day!)
How do this? It’s all string data. Examples: one tag is IB, another is leadership_modular_series; I need one parameter query to pull any set of tags, for any group of characters from 2 to 30 odd.
Here's what the sql window reads behind the query grid:
SELECT tbl_ALL_Parent_Videos.ID, tbl_ALL_Parent_Videos.Tags, tbl_ALL_Parent_Videos.OD_Multi_Virtual_Cert, tbl_ALL_Parent_Videos.English_US_Master_BCID, tbl_ALL_Parent_Videos.BCID_Playlist_Ordinal, tbl_ALL_Parent_Videos.English_File_Name, tbl_ALL_Parent_Videos.English_Display_Name, tbl_ALL_Parent_Videos.English_Category, tbl_ALL_Parent_Videos.Product_handle, tbl_ALL_Parent_Videos.Original_Upload_Date, tbl_ALL_Parent_Videos.English_Brightcove_China_BCI D, tbl_ALL_Parent_Videos.Parent_Insight_Excelerator_i d, tbl_ALL_Parent_Videos.Insights, tbl_ALL_Parent_Videos.Excelerator, tbl_ALL_Parent_Videos.PPT, tbl_ALL_Parent_Videos.VC, tbl_ALL_Parent_VTT.OrgVersionSubtitleFileName, tbl_ALL_Parent_VTT.VTT_final_timestamp, tbl_ALL_Parent_VTT.Running_Time, tbl_ALL_Parent_VTT.Includes_embedded_video, tbl_ALL_Parent_VTT.Embed_Vid_starts, tbl_ALL_Parent_VTT.[Embed_ Vid_terminates], tbl_ALL_Parent_VTT.Embed_video_total_time, tbl_ALL_Parent_VTT.English_vtt_attached
FROM tbl_ALL_Parent_Videos INNER JOIN tbl_ALL_Parent_VTT ON tbl_ALL_Parent_Videos.English_US_Master_BCID = tbl_ALL_Parent_VTT.English_US_Master_BCID;
It's the ALL_Parent_Videos.Tags field that has the string data.
I feel so DUMB. I know I solved the same kind of 'random text' problem in at least 3 other databases in other jobs. I just can’t remember how! Thanks for any help you can give. I'll have to take an Access class before I say I could return the favor.