Hello, first-time poster and first time designing an Access database. I've made good headway, but I'm possibly entering treacherous waters as I'm considering breaking one of the relational database design rules - so I thought I'd check here first, if any of the experts can offer advice.



The rule I'm considering breaking is to essentially make a calculated field into a foreign key for a table. The work-around to make that happen would be to run an action query to make the query (and the calculated field within it) into a new table. But I have to ask myself - should I? Or should I find some other work-around, or reconsider the overall design?

I'm not going to list the entire design here, but I will highlight the basic relevant components.


  • Emails come in as a populated format with info about the customer and their requested item.
  • The email information is stored in an Access table "Requests", and an auto-number field assigns a RequestID for each request.
  • A 'CreateFolder' module runs some VB script that takes the RequestID and creates a file folder on a shared computer drive, with the RequestID as part of the folder name. Various employees save documents associated with that request in that folder.
  • A 'ImportFilepath' module runs some VB script that scans the shared computer drive, and imports the filepath for all folders/files within the RequestID folders. All the filepath information is now in Access as a table 'Filepath'. Every filepath has a RequestID in the filepath, since it is a folder name.
  • Successive use of the 'ImportFolder' module clears and rebuilds the 'Filepath' table in access, to account for files being moved around and created and eliminate files that have been deleted.
  • A query 'SplitFilePath' splits the filepath into various columns, one of which isolates the original RequestID that was saved in the filepath. The other columns have useful information conveyed by the files saved in the folder. However the formula to split the filepath within Access makes that column a calculated field.
  • Access does not want to link the "Requests" table RequestID primary key with the "SplitFilePath" query RequestID that was extracted in a one to many relationship (e.g. many files in one RequestID folder), because it is a calculated field.


If I make the 'SplitFilePath' query an action query, converting the information into a new table, that will allow me to link it, but that breaks database design. Additionally, that table would also have to be cleared each time I run the import module. It may be possible to isolate the RequestID from the filepath using VB, but that's currently beyond my VB coding skills.

(Note: VB is visual basic, the code writing in the module feature)

What do you think? Make it an action query that converts the calculated fields into a table?
Hope to figure out a solution at the source using VB coding?
Some other approach / better design?

Feedback appreciated!