473,324 Members | 2,166 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Sum avg. transactions by 3, 6, and 12 months and display results on one line

68
Thank you so much for looking at this!

I need code that takes the first table:

tblStartingData
Date Product Customer ID Revenue
3/1/06 MCF 1 $10.00
6/1/06 MCF 1 $15.00
12/1/06 MCF 1 $20.00


and turns it into the second 2 tables:

tblProduct
Product 3moAv 6moAv 12moAv
MCF $3.33 $4.17 $3.75

tblProductCustomer
*The listed fields in this table are Column headers, not row headers (I didn't think I'd have enough room in this post to show them in the correct layout)

Product:MCF
Customer ID:1
Customer3MoAverage:$3.33
Customer6MoAverage:$4.17
Customer12MoAverage:$3.75
%CustomerInProduct in TotalProduct_3mo:100.00%
%CustomerInProduct in TotalProduct_6mo:100.00%
%CustomerInProduct in TotalProduct_12mo:100.00%



This is an extemely simplified version of the data. In actuality the tblStartingData table is 400k records all with different Customer IDs and Products. In the tblProductCustomer I have 'Customer ID' = 1 making up 100% of the product sales. Normally it would be the sum of multiple customers, not just one. I've ruled out other ways to do it (i.e crosstab, pivot table, different records for 3, 6, 12 months instead of seperate columns, etc) becuase none of those options work well with the rest of the project. I thought I could set up the second 2 tables and append the data to them. (for the tblProduct I would group by 'Product' and append the product names, for the tblProductCustomer I would group by 'Product' and 'Customer ID') If that's a good starting point I need to know how to populate the 3, 6, and 12 month calculations. Otherwise, I'm open to entirely creating the tables in code. Thank you!!!!
Mar 30 '07 #1
3 1634
MMcCarthy
14,534 Expert Mod 8TB
Firstly change the Date field to SaleDate, then something like this ...
Expand|Select|Wrap|Line Numbers
  1. SELECT Product,
  2. Sum(IIf(SaleDate BETWEEN Date() and Date()-91,Revenue,0)) As 3MoTotal, 
  3. 3MoTotal/91 As 3MoAverage,
  4. Sum(IIf(SaleDate BETWEEN Date() and Date()-185,Revenue,0)) As 6MoTotal, 
  5. 6MoTotal/182 As 6MoAverage,
  6. Sum(IIf(SaleDate BETWEEN Date() and Date()-365,Revenue,0)) As 12MoTotal, 
  7. 12MoTotal/365 As 12MoAverage
  8. FROM tblStartingData
  9. GROUP BY Product;
  10.  
Mary
Apr 1 '07 #2
dk4300
68
Firstly change the Date field to SaleDate, then something like this ...
Expand|Select|Wrap|Line Numbers
  1. SELECT Product,
  2. Sum(IIf(SaleDate BETWEEN Date() and Date()-91,Revenue,0)) As 3MoTotal, 
  3. 3MoTotal/91 As 3MoAverage,
  4. Sum(IIf(SaleDate BETWEEN Date() and Date()-185,Revenue,0)) As 6MoTotal, 
  5. 6MoTotal/182 As 6MoAverage,
  6. Sum(IIf(SaleDate BETWEEN Date() and Date()-365,Revenue,0)) As 12MoTotal, 
  7. 12MoTotal/365 As 12MoAverage
  8. FROM tblStartingData
  9. GROUP BY Product;
  10.  
Mary

Mary,
Thank you!!! That's is exactly what I'm looking for! I was trying to put the 'Sum' as the 'Then' of the 'If...Then' instead of putting it the 'If...Then' within the 'Sum'.
Thank you so much!
Apr 5 '07 #3
MMcCarthy
14,534 Expert Mod 8TB
Mary,
Thank you!!! That's is exactly what I'm looking for! I was trying to put the 'Sum' as the 'Then' of the 'If...Then' instead of putting it the 'If...Then' within the 'Sum'.
Thank you so much!
You're welcome.
Apr 6 '07 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Tencip | last post by:
Hi everyone, I'm trying to build a simple script that does the following. It should find today's month and year, and then go into a DB query string and look for all records that are from this...
6
by: Christopher J. Bottaro | last post by:
Hi, Why is there no support for explicit transactions in the DB API? I mean like transaction() to start the trans and commit() and rollback() would end the trans or something. The reason why I...
1
by: Arthur Dent | last post by:
I have a log file in this format: YYYYMMDD HHMMSS DATADATADATADATADATA RESULTCODE and I want to be able to parse the log and gleen information about gaps in time between transactions. For...
1
by: Rick | last post by:
I'm having problems with EnterpriseServices transactions running against Oracle 9iR2. I am inconsistently getting Oracle ORA-24761: Transaction Rolled Back results mid-transaction. If I start...
3
by: captain | last post by:
Below is the sql for data with same date need to extract + or - 5 days data of same date also. How to also get data of + and - days related to same date. SELECT IM.Area, IM.Location,...
6
by: Terri | last post by:
I have a table called Transactions with 3 fields: ID, Date, and Amount. Each ID can have multiple transactions in one particular year. An ID might not have had any transactions in recent years. ...
1
by: Bijoy Naick | last post by:
I am having trouble implementing transactions.. Here's my code.. ----- Dim myConnection As OleDbConnection Dim myCommand As OleDbCommand Dim myReader As OleDbDataReader Dim transaction As...
2
by: Joseph S. | last post by:
Hi all, I'm using the following setup: VB.Net (.NET framework 1.1) Access2000 SharpDevelop 1.1 for development (it is very similar to Visual Studio ..NET) using ODBCConnection, ODBCCommand,...
2
by: RAM | last post by:
Hello, (Sorry for my English...) I am learning .NET 2.0 (C#, ASP.NET, ADO.NET etc.). I need to write a database application (SQL Server) consisting of a number of database transactions (like...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.