Form Structure Advice/Sanity Check

[dominicdunmow]

Hi there...this is a little longwinded but please bear with me.

I have created a Revenue Forecasting database. Functionality should allow users to input new customer order details and secondly to be able to leaf through records to find particular orders.

A main form contains generic customer details (bound to tblCustomerDetail).

A command button on main form opens a continuous form where revenue, quarters and years are added (bound to tblCustomerRevenue).

A subform of main form displays Revenue profile (horizonatally), i.e order number and revenue over time (bound to tblXtabCustomerRevenueProfile).

To get revenue over time an xtab query pivots tblCustomerRevenue, then a make table query adds the xtab output to the tblXtabCustomerRevenueProfile.

This structure kind of works except for if I want to change customer order revenue on the continuous form, I can't immediately update that particular customer's record on the main form (by running the crosstab and then maketable query) as the tblXtabCustomerRevenueProfile is in use by the main form.

I guess I could probably get around this somehow, but it all seems rather clumsy and I can't help thinking there must be an easier way around such an apparent simple database concept.

Can anyone provide me with advice please?

Thanks