That is, of course, your choice. It is regrettable but true that M$ appears to blow hot and cold at different times. I think their problem is that Access grew TOO popular in the scheme of things, perhaps because someone hasn't thought things through - and because their design was self-limiting in a way that they didn't originally anticipate. That self-limiting design was what eventually killed the ADP feature that would have supported a web form setup. Things didn't start well here, but that doesn't stop the fact that you have an issue to be resolved. I can get past the rocky start if you can.
The bigger issue is a non-trivial one of a complex ... I'll call it a "deconvolution" ... related to unflattening a flattened file. If you cannot find your way to something more helpful in the future, come back to this thread and click the "Reply" in the lower right corner. As a reply, it will alert me.
So that I will remember the method I figured out and not have to re-research it, I will offer a couple of notes on the approach so that if you decide to come back later, I'll recall what I saw and can restart faster. Or, others seeing this could pick it up as well.
The Jotform output appears to be essentially an enumeration of every field, with the field identifying information stored linearly in the name row. The solution would be to scan the name row as text data to decide where the associated information from the same column in the data row would go. For the things that would go into the person table, the titles are fairly clear. There is a pattern to the columns that correspond to the child table information and the titles would help identify that part as well. By using something like the SPLIT function, it should be possible to extract the child row number and the correct field name for each child entry. Because of the possibility that the Excel data would exceed 255 columns, the direct import would not be possible, but a loop to decipher it "on-the-fly" directly from Excel might do the trick. The only question remaining is how much data appears in one such file, in terms of how many PERSONS would be represented (how many data rows).
The algorithm would be to use an Excel Object to open the .CSV file and step through the columns. I'm assuming that the data would be starting in row 2 with those ugly titles in row 1. On the Access side, open recordsets - one for the person and one for the meds plus one more for the junction table that would be the ideal way to store this dataset. It would parallel the first of your two JPG offerings in post #1. Stepping by columns, read the header row and then decipher the table field to which it corresponds and in each case, store the content of the data row in the field identified from the column names.
The first several columns represent the person's data. You would eventually, if I read the layout right, reach the first record that is child data and recognize it by the "Please enter..." string. Stop there to save (rs.Update) the person data, which would allow you to retrieve an autonumber key if you were using that to identify persons. (Note: If you had a separate table of recurring clients, you could perform a lookup right here to decide if they already had a client number.)
Then continue your loop to populate the child table. By decomposing the titles and paying attention to the embedded number that is in the middle of each potential child entry title, you would know when you stepped to a new "line item" and could save the line you just accumulated. If you are doing the junction table, at this time you could also enter the data for the new junction record. When you finally reach the point where there is no data remaining as determined by blanks in a critical data element OR by having run out of row 1 titles, you are done with that person record. The next step depends on how many persons are represented in the import file. If there COULD be more persons, you would simply update the data row number and loop through the headers again.
IF you have multiple data rows for multiple people, the only trick is that you determine the end of the run by verifying that the data row has only blank cells remaining. If each file is a single person, you should run out of column headers at the same time you run out of data cells.
The structure of the loop would be based on (probably) a big SELECT CASE ladder selecting what to do with each field as it comes in. For most cases you would do nothing more than store the data element (or convert and store, if numeric) in a field in the open recordsets. For a few cases, you would manipulate the recordsets via .Update, and might also have to do minor maintenance on the "implied child record" number to know that you have started a new child row.
There, that is the approach that I would use in Access. If you find some other way, perhaps that will help you to design your response in that other environment. Good luck with that.
