Customer Visits Log

[Guest]

I am currently using Excel to log the number of service visits to customer
premises whilst pulling the customer details from Access. This works ok but I
would rather do it all in Access if possible.

I have looked at a few templates but found it confusing trying to work out
how they are built/designed.

My first design thoughts are: -
Use the existing TblCust that holds name, cost details etc.
Create a table that logs the number of visits per day, per customer
Create a form that populates the above table
Create a query that lists each customer, the total number of visits, cost
per visit, total amount billable per month.

Please advise if there is an easier way to achieve the above

Thanks
Brian.

 

[John W. Vinson]

I am currently using Excel to log the number of service visits to customer
premises whilst pulling the customer details from Access. This works ok but I
would rather do it all in Access if possible.

I have looked at a few templates but found it confusing trying to work out
how they are built/designed.

My first design thoughts are: -
Use the existing TblCust that holds name, cost details etc.
Create a table that logs the number of visits per day, per customer
Create a form that populates the above table
Create a query that lists each customer, the total number of visits, cost
per visit, total amount billable per month.

Please advise if there is an easier way to achieve the above

Thanks
Brian.

Typically one would not store "the number of visits per day" if instead you
can store visits - one record per visit. Once you have that, it's very easy to
create Totals queries that group by customer, by day or by week or by month,
summing or averaging values as you do so. You could easily put a Subform on a
form bound to TblCust to enter visit information.

John W. Vinson [MVP]

 

[Guest]

Hi John,
I think I understand what you mean :-/ I'll try to explain myself a little
better...

We currently use an Excel spreadsheet that has 1 customer per row and 31
columns to represent the days of the month. We simply add the number of
visits in each corresponding cell. A simple =sum(B1:AF1) totals the visits
which we then multiply by the charge rate. (Each customer has different
numbers of visits and are charged different fees)

The way I'm reading your suggestion is to create a record each time we
perform a visit for Bloggs Engineering, even if we perform 8 visits per
night, every night of the year. This might cause some friction within the
office, as I promised to find a more efficient method of data entry...

Thanks
Brian.

 

[John W. Vinson]

Hi John,
I think I understand what you mean :-/ I'll try to explain myself a little
better...

We currently use an Excel spreadsheet that has 1 customer per row and 31
columns to represent the days of the month. We simply add the number of
visits in each corresponding cell. A simple =sum(B1:AF1) totals the visits
which we then multiply by the charge rate. (Each customer has different
numbers of visits and are charged different fees)

That's a good spreadsheet but not a good relational table. That's not to say
that you couldn't use something like this as a user interface, if that's what
the users are familiar with.

The way I'm reading your suggestion is to create a record each time we
perform a visit for Bloggs Engineering, even if we perform 8 visits per
night, every night of the year. This might cause some friction within the
office, as I promised to find a more efficient method of data entry...

If there is no need to track the date and time or any other information about
individual visits ("Why were there 8 visits on the 13th?" "I don't know, we
don't keep any information about visits, just that there were 8") then you
could modify my suggestion to record just a date and the number of visits on
that date in each row of the visits table, using a subform. You can still do a
Totals query to sum the number of visits and calculate the fee.

John W. Vinson [MVP]