Hi I cannot answer your question fully other than to say that in the first 6 weeks of learning FMP i learnt more than after 18 months of trying with Access! Scripting can be very powerful, given our database experience i am sure you will find it much easier than someone with no experience at all. I can recommend that you get the advanced version - certainly makes copying and pasting between tables much easier especially if there are a lot of lookup tables.
Point 3 - i have male and female layouts that when you click on the goto button it takes you to the correct layout based on the patients sex using a calculation
Point 4 - i have only just got my head round - I think what you need is conditional lookup values?
Wouldn't touch Access with a barge pole especially now with FMP11 out.
Hope that helps