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!!!!
3 1634
Firstly change the Date field to SaleDate, then something like this ... -
SELECT Product,
-
Sum(IIf(SaleDate BETWEEN Date() and Date()-91,Revenue,0)) As 3MoTotal,
-
3MoTotal/91 As 3MoAverage,
-
Sum(IIf(SaleDate BETWEEN Date() and Date()-185,Revenue,0)) As 6MoTotal,
-
6MoTotal/182 As 6MoAverage,
-
Sum(IIf(SaleDate BETWEEN Date() and Date()-365,Revenue,0)) As 12MoTotal,
-
12MoTotal/365 As 12MoAverage
-
FROM tblStartingData
-
GROUP BY Product;
-
Mary
Firstly change the Date field to SaleDate, then something like this ... -
SELECT Product,
-
Sum(IIf(SaleDate BETWEEN Date() and Date()-91,Revenue,0)) As 3MoTotal,
-
3MoTotal/91 As 3MoAverage,
-
Sum(IIf(SaleDate BETWEEN Date() and Date()-185,Revenue,0)) As 6MoTotal,
-
6MoTotal/182 As 6MoAverage,
-
Sum(IIf(SaleDate BETWEEN Date() and Date()-365,Revenue,0)) As 12MoTotal,
-
12MoTotal/365 As 12MoAverage
-
FROM tblStartingData
-
GROUP BY Product;
-
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!
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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,...
|
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.
...
|
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...
|
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,...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
|
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...
| | |