Normalizing Data

Hi John, you may consider just copying the data over to Excel, manually correcting them (and/or transposing them), and copying them back to Access.  You need queries if there are lookups involved, as shown in Richard's video.  But in your picture, I don't see the need of any complex lookups.  If you already know drug ID 1 is "a", 2 is "b", etc., you can manually type them instead of building a query to look them up.

To avoid this kind of situation, always remember that data can expand vertically more easily than they can horizontally.  If you have to keep adding new data horizontally with new columns Caution1, Caution2, etc., then they should probably expand vertically (i.e. in rows) instead.

Done.
Using 4 appended queries, one by one , I normalised the data into another table. It was mentioned in example 3 of extended cut of "Normalizing Data" by Richard. I missed that part and thought that I could use just one query to do so.

Can you guys think that one query can do it ? just curious. May be ask ChatGPT later.

Anyway , thanks both Kevins