I have a question about Access and whether this is even possible.
I have an Access database that I run a query on twice per month and pull out specific records that need to be reported to a State agency. I run the query and export these records to an Excel file so that the data looks like this:
Now that I have the records that need to be reported, I must create a report with fixed field lengths. The only way I have been able to accomplish this is to have an Access Database that looks like this:
At this point, I enter the data manually since you can see that the Excel file has three records for Acute Care Surgery, so what I do is enter the last name of the customer and their license number in the field name1 and lic1, then the second record in name2 and lice2, and so on. There are 30 of these fields across the top name1, lic1...name30, lic30. Once I have finished entering the information manually, I export it to a fixed length txt file so that it comes out in the format required by the State agency.
Is there a way to take the rows of the Excel sheet and get the information into the corresponding field in the Access database? I have been trying to figure out a way to either extract the data directly from one Access file to another, or use Excel as the intermediary file where I have at least isolated the information I need. I've used sample data here in the examples so the customer information is kept private.
Any help would be appreciated.