Need help with database setup

[NickWren]

I am having troubles setting up a database that have multiple test results and multiple facilities. All our data comes from excel spreadsheets that have an indicating name that we call a "Wren ID" = [FacilityAbbreivation] & "-" & Format([FacilitySampleID], "0000") & "-" & [TestAbbreivation] (ex. PL-0001-NT)
Most test we do are a molecular based PCR test that gives us a numerous results depending on the tests ( NETest = 55, Colon = 8, Multimyloma = 33, Melanoma = 35, Pancreas = 28, ect...). CGA is an ELISA assay based test that we only get a "Wren ID" and a CGA score.


Each test needs to start off with a new numbering system.

PL-0001-NT - PolandSample1
PL-0002-NT - PolandSample2
PL-0003-NT - PolandSample3
...
HOL-0001-NT - HollandSample1
HOL-0002-NT - HollandSample2

PL-0001-CGA - PolandSample2
PL-0002-CGA - PolandSample3
...
HOL-0001-CGA - HollandSample2

PL-0001-PC - PolandSample1
PL-0002-PC - PolandSample3
...
HOL-0001-PC - HollandSample1

Here is what I have so far... clearly I need help
TestDatabase.zip

[vicsaccess]

Nick, i'm confused on what part you are needing help on. Are you just needing to rename the given file names?

[ssanfu]

I am also confused.

I looked at your tables - they are set up like a spreadsheet. Spreadsheets are typically short and wide. Databases are typically tall and narrow.
I reworked your tables and created some forms. Don't know anything about your process, but take a look. Maybe it will help.

It is still pretty rough. After you get it working manually, you can try to automate adding data using VBA.