Tags: access, customer, customerpremises, database, details, excel, log, microsoft, mysql, number, oracle, pulling, service, sql, visits, whilst

Customer Visits Log

On Database » Microsoft Access

5,410 words with 3 Comments; publish: Sun, 01 Jun 2008 15:18:00 GMT; (25078.13, « »)

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.

All Comments

Leave a comment...

  • 3 Comments
    • On Thu, 26 Jul 2007 07:30:02 -0700, Kevin <Kevin.ms-access.todaysummary.com.discussions.microsoft.com>

      wrote:

      >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]

      #1; Sun, 01 Jun 2008 15:19:00 GMT
    • 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" wrote:

      > On Thu, 26 Jul 2007 07:30:02 -0700, Kevin <Kevin.ms-access.todaysummary.com.discussions.microsoft.com>

      > wrote:

      >

      > 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]

      >

      #2; Sun, 01 Jun 2008 15:21:00 GMT
    • On Thu, 26 Jul 2007 12:54:02 -0700, Kevin <Kevin.ms-access.todaysummary.com.discussions.microsoft.com>

      wrote:

      >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]

      #3; Sun, 01 Jun 2008 15:22:00 GMT