RE: Export to Excel or calculate within Access?

:)

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.

"John Nurick" wrote:

I know that feeling!
--
John Nurick
Microsoft Access MVP


"Kevin" wrote:

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" wrote:

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 ***, you're more likely to find expertise if you ask in an Excel
group.




"Kevin" wrote:

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 spread*** 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 work*** 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.
.

Loading