I haven't found the answer to this yet, so hopefully I'm not asking a question already answered.
I'm an amateur, but I do Access fairly well.
Background: I'm trying to import Excel spreadsheets into Access. At work, I investigate problems / issues of a medical sort, and when one happens, we ask the provider / doctor in question to fill out a sheet. Until now, it's been on paper and hand-entered into Access. Access handles the investigation tracking, some of the correspondence, and reports.
I'm trying to do that without as much hand-entering. Our administrators won't let us do a web application because of the privacy of medical data, but a file over secure email is fine (don't ask me, I just follow the rules).
Silly me, I thought "we'll, sure as breathing someone's going to want to change the Excel form over time so I should make this so I can import *any* form without having to code each one". If you've worked these kind of things, you know if you launch new versions of the form, someone's going to still be using the old version.
So far, I have the following processes down:
- search for the Excel sheet,
- import it into a raw temporary table that replicates the Excel.
Next step is to take the Excel spreadsheet (for example a 3x3 sheet with cells A1, A2, A3, B1, B2, B3, C1, etc.) and put it into an Access form where it has two fields: Cell_reference (e.g. "A1") and Cell_Data (whatever is in A1, e.g. "Dr. Smith" as the person submitting the report). The step after this will be matching the form name & version to a reference table that has, to continue the above example, 'A1 is the data on the person submitting the form' (so if in version 2 that reference is switched to B2 all I have to do is change the reference table).
So, basically, I want to move from a 3x3 where each cell potentially has data to 1 field, laying it out vertically, 1x9 (I said cell reference and cell data, but it's easier to think of it this way, as only one has data).
I have no problem generating the cell reference. I define the max number of columns for any form and have a for...next loop.
But getting the data has proven difficult. Importing the Excel into Access is easy. It generates fields for each column, so in our 3x3 example, there would be three fields (A,B, and C) and three records (1,2, and 3).
What I put in just to test the concept out is:
Where frm_Main_Import is the User Interface that has the VBA code to count up the columns and [A],,[C] etc. are field names in the loaded Excel sheet.
I can't write all that code for A, B, C .... especially if it goes to AA, AB, .... There's got to be an easier way.
It would work great if I could do in SQL like I would do in VBA, something like: ' "[" & [Variable that attaches a letter to the column number] & "]"
I'm not *that* familiar with SQL, so I might be missing an easy solution there.
Thanks for reading this far. Any suggestions?
Andy
I'm an amateur, but I do Access fairly well.
Background: I'm trying to import Excel spreadsheets into Access. At work, I investigate problems / issues of a medical sort, and when one happens, we ask the provider / doctor in question to fill out a sheet. Until now, it's been on paper and hand-entered into Access. Access handles the investigation tracking, some of the correspondence, and reports.
I'm trying to do that without as much hand-entering. Our administrators won't let us do a web application because of the privacy of medical data, but a file over secure email is fine (don't ask me, I just follow the rules).
Silly me, I thought "we'll, sure as breathing someone's going to want to change the Excel form over time so I should make this so I can import *any* form without having to code each one". If you've worked these kind of things, you know if you launch new versions of the form, someone's going to still be using the old version.
So far, I have the following processes down:
- search for the Excel sheet,
- import it into a raw temporary table that replicates the Excel.
Next step is to take the Excel spreadsheet (for example a 3x3 sheet with cells A1, A2, A3, B1, B2, B3, C1, etc.) and put it into an Access form where it has two fields: Cell_reference (e.g. "A1") and Cell_Data (whatever is in A1, e.g. "Dr. Smith" as the person submitting the report). The step after this will be matching the form name & version to a reference table that has, to continue the above example, 'A1 is the data on the person submitting the form' (so if in version 2 that reference is switched to B2 all I have to do is change the reference table).
So, basically, I want to move from a 3x3 where each cell potentially has data to 1 field, laying it out vertically, 1x9 (I said cell reference and cell data, but it's easier to think of it this way, as only one has data).
I have no problem generating the cell reference. I define the max number of columns for any form and have a for...next loop.
But getting the data has proven difficult. Importing the Excel into Access is easy. It generates fields for each column, so in our 3x3 example, there would be three fields (A,B, and C) and three records (1,2, and 3).
What I put in just to test the concept out is:
Code:
Data:
IIf([Forms]![frm_Main_Import]![Column_Add_Count]=1,[A],
IIf([Forms]![frm_Main_Import]![Column_Add_Count]=2,[B],
IIf([Forms]![frm_Main_Import]![Column_Add_Count]=3,[C],
IIf([Forms]![frm_Main_Import]![Column_Add_Count]=4,[D],"X"))))I can't write all that code for A, B, C .... especially if it goes to AA, AB, .... There's got to be an easier way.
It would work great if I could do in SQL like I would do in VBA, something like: ' "[" & [Variable that attaches a letter to the column number] & "]"
I'm not *that* familiar with SQL, so I might be missing an easy solution there.
Thanks for reading this far. Any suggestions?
Andy
