Export to Excel or calculate within Access?

[Kevin]

Hi all,
I have been using Office for many years but only recently began to use Excel
and Access together to produce the data I needed. I prefer Excel as Access
often frustrates me

I have created an Excel spreadsheet that pulls basic customer details from
our Access database, this works fine. I then have 31 columns representing the
days of the month, which we populate with data. Another worksheet multiplies
this data by the charge rates within Access and we produce a billing report
for our accounts department.

When we add or remove a customer in Access, the Excel list of customers
updates ok but the data that we have previously entered does not move up or
down as expected! I have tried each of the 3 options relating to 'data range
changes' without success.

Should I try to do all of the above in Access using a Form to populate a
data table or can I force Excel to move our data to match the Access
customers?

Thanks
Brian.

 

[John Nurick]

Hi Kevin,

I'd do it all in Access, with one or more tables for the billing data, a
form to enter them and a query and report to extract and present the results.

As for integrating data queried from Access with values and formulas on an
Excel sheet, you're more likely to find expertise if you ask in an Excel
group.

 

[Kevin]

Hi John,
Thanks for replying, my main problem is the existing db as it was thrown
together 10+ years ago and has been chopped/changed/abused ever since. I
think I might be better creating a new db and importing test data in.

Thanks
Brian.

 

[John Nurick]

I know that feeling!

 

[Kevin]

I have created the following tables: -
tblCustomers - basic details including a 6 digit contract number as the PK
tblServices - basic details including a 1 digit service ID as the PK
linkCustomersServices - using the two PK's as foreign keys

Entering data in the link table results in the '+' subtables showing the
correct data in both the tblCustomers and tblServices tables. I'm guessing
this is correct and that I use a Form later to populate the link table?

Next question is, should I create a new tblVisits to record the amount of
visits performed at each customer site or should I add this data to the link
table somehow?

I want to use a query to generate a report that lists each customer along
with the total number of visits per month, multiplied by the charge rate to
calculate a total.

By the way, each customer receives visits every night but the number varies
quite often.

Thanks
Brian.