Access crosswalk table ideas

[mishmish3000]

Good morning and happy Friday!
I've attached an Excel file with some questions about constructing a crosswalk table in Access, for state-wide vaccination codes and charges. We have a situation here where we need to count vaccinations given at health departments, but each regional health department (and there are 13 of them here in TN) has been allowed to make up their own abbreviations for individual shot codes. When we at the Central Office talk about shot A, it might be called Shot AGHE at one region, Shot 7827A at another... you get the idea.
I'd like to construct a crosswalk table in Access to make sense of all this... and it's a bit complicated! So I thought I'd ask you for your feedback!
Thanks
MishMish3000

[HansV]

Are the various fees intended to be added together, or are they completely separate from each other?

[mishmish3000]

Howdy!
Well, since we need to see how much was paid by each fee, we'd need to keep them separate, I think. So you could say, How many chickenpox shots were given statewide, by each region, by each type of insurance payment?
Does that sound goofy?
MishMish3000

[HansV]

I think you need several tables:

1) Standard shot codes:

x553.png

2) A table translating between regional and standard codes:

x554.png

3) A fee types table

x555.png

4) A fee table

x556.png

It's more complicated to set it up this way, but in the end it'll be much more flexible, you'll be able to filter any which way.

[mishmish3000]

That looks great! I'll work on that and see how it goes. Thanks, HANS!
MishMish3000