Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2012
    Location
    Toronto Ontario
    Posts
    10

    Unhappy Unanswered: Query Syntax Problems

    Hello

    I am trying to calculate the "Average Lifetime Value" of customers. Meaning what is the average total spend of all customers during their time with us & also for a previous 30 day span.

    I have a customers table (customer) containing:
    Code:
    ID 
    FirstName

    I also have a service table (tblservice) containing:
    Code:
    serviceID 
    Timing  
    Total
    note: timing is in format: (mm/dd/yyyy) & tblcustomer & tblservice is a one to many relationship.

    The problem is:

    I NEED it to calculate the average of total spend per customer. So it must total all the purchases made by each customer first, then divide by the total number of customers.

    With the following two queries it seems to be structured correctly BUT I seem to be getting syntax errors & I am not sure what is wrong(the join gets highlighted):

    Code:
    select c.id, c.firstname, avg(s.Total) / (select count(id) from customer) as LifetimeValue
    from tblservice as s join customer as c on s.id = c.id
    group by s.id
    
    select c.id, c.firstname, avg(s.Total) / (select count(id) from customer) as LifetimeValue
    from tblservice as s join customer as c on s.id = c.id
    where (s.Timing)>=DateAdd("d",-30,Date())
    group by s.id

    Any help would be greatly appreciated,

    thank you
    Last edited by Mike Toshiba; 06-01-12 at 01:05.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how do you know what as customer has spent, how are that rtepresented int he data, and or in that table design you presented

    unless you have stored the date as a string then the display format has now relevance to the storage format. Access / JET stores dates as a number since from an arbitary point in time (IIRC 31/12/1899). if you have stored dates as as tring, then you need to seriously consider usign the correct datatype for that coluimn. using the correct datatype means you cna take advanateg of the in build date/time functions within Access VBA
    https://www.google.co.uk/#hl=en&scli...w=1920&bih=995


    do you have a column called ID in tblservice?
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •