@JS Smith
First and foremost, if you can imagine the data manipulations you want (and DOCUMENT them so you don't forget later), you can probably do it in Access. Will it be easy? Can't tell you because I don't have insight into your imagination.
You are correct, it is a DBA sin to duplicate data - but there are loopholes and exceptions everywhere. There are also workarounds that avoid duplication in some cases.
Duplicating data is not a sin if there is some temporary or variable nature to certain data elements and you want to capture a snapshot of data at a certain time. Making a copy is one way to do that. The implication is that if things change over time, you need to duplicate CURRENT values knowing that the source table might change later. E.g. a cost table where you capture current costs and when you make a new order that gets approved, you have to go by those recorded costs even if the contributing costs change next week.
When data sets are either invariant over time OR you have a history of their changes complete with effective dates, there are ways to use complex queries to tie everything together so that you DON'T duplicate your data. This implies that you can keep records of your cost changes. Usually it is an either/or situation - make copies of current data OR track changes in the contributing data. When you CANNOT keep track of changes, the old "thou shalt not keep duplicated data" rule goes out the window. Hope this helps you understand what is going on with that rule.
Pat Hartman was talking about using JOIN queries so that you can look up stuff "automagically" behind the scenes by tying tables together based on information common to both tables. This "tying together" is the primary function of a JOIN. Pat is one of our most experienced members; she can offer a wealth of information and suggestions for best practices. For the cases where you want a selection made by a user to cause other fields to be filled in without further user action (see your post #13, 1st of your numbered items), a combo box and at worst a little bit of VBA code would probably do that item completely.
Can't speak to the #2 item of same post other than to say "Probably not that hard."
The #3 item might or might not be tricky. It will be a matter of exactly what you wanted that to look like, but I assure you Access can do that. The trick will be to FIRST decide what you want to do AND what it should look like. THEN you can discuss implementation issues.
It's like my late father-in-law used to say. (He was a carpenter/contractor who built a lot of houses.) "Measure twice, cut once." It applies here as "Think twice, implement once."
