Thread: Query Syntax Problems
06-01-12, 01:01 #1Registered User
- Join Date
- Mar 2012
- Toronto Ontario
Unanswered: Query Syntax Problems
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:
I also have a service table (tblservice) containing:
serviceID Timing Total
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):
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,
Last edited by Mike Toshiba; 06-01-12 at 01:05.
06-01-12, 04:56 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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
do you have a column called ID in tblservice?I'd rather be riding on the Tiger 800 or the Norton