## 49 Replies - 5049 Views - Last Post: 14 January 2011 - 05:28 PM

### #16

## Re: How to calculate two value between different date and different reco

Posted 08 January 2011 - 02:40 AM

tblShift1 : IDShift1, ProcDateShift1, ProcDataShift1

tblShift2 : IDShift2, ProcDateShift2, ProcDataShift2

tblShift3 : IDShift3, ProcDateShift3, ProcDataShift3

query :

CalcProcDataShift1 :

1 select

2 tblShift1.ProcDataShift1 - tblShift3.ProcDataShift3

3 From

4 tblShift1, tblShift3

5 where

6 tblShift3.ProcDateShift3 = DateDiff(dd, -1, Date()) and tblShift1 .ProcDateShift1 = Date()

Do you mean like this, Ionut ?

Thank

tblShift2 : IDShift2, ProcDateShift2, ProcDataShift2

tblShift3 : IDShift3, ProcDateShift3, ProcDataShift3

query :

CalcProcDataShift1 :

1 select

2 tblShift1.ProcDataShift1 - tblShift3.ProcDataShift3

3 From

4 tblShift1, tblShift3

5 where

6 tblShift3.ProcDateShift3 = DateDiff(dd, -1, Date()) and tblShift1 .ProcDateShift1 = Date()

Do you mean like this, Ionut ?

Thank

### #17

## Re: How to calculate two value between different date and different reco

Posted 08 January 2011 - 02:57 AM

My problem, almost same like this :

I have a table that includes production data for each day of

processing, allowing me to compare totals from any two dates where

processing occurred.

My plan is to create one table with the most recent data using the

formula (Max[Prod Date])

I need to create another table for the previous processing date and

since that might be 1,2 and even 3 days earlier, the formula ((Max

[Prod Date])-1) doesn't work as it would often select a date where no

file exists.

Any suggestions for a formula that would pick up the next most recent

date?

David <dbain3...@gmail.com>

2/3/2010 8:58:47 PM

Header

Report as Spam

Best not to use tables but queries instead like this --

qryLastProdDate

SELECT Max(Production.ProdDate) AS LastProdDate, Sum(Production.QTY) AS

Total_Prod

FROM Production, qryLastProdDate;

SELECT Max(Production.ProdDate) AS LastProdDate, Sum(Production.QTY) AS

Total_Prod

FROM Production, qryLastProdDate

WHERE Production.ProdDate < qryLastProdDate.LastProdDate;

I have a table that includes production data for each day of

processing, allowing me to compare totals from any two dates where

processing occurred.

My plan is to create one table with the most recent data using the

formula (Max[Prod Date])

I need to create another table for the previous processing date and

since that might be 1,2 and even 3 days earlier, the formula ((Max

[Prod Date])-1) doesn't work as it would often select a date where no

file exists.

Any suggestions for a formula that would pick up the next most recent

date?

David <dbain3...@gmail.com>

2/3/2010 8:58:47 PM

Header

Report as Spam

Best not to use tables but queries instead like this --

qryLastProdDate

SELECT Max(Production.ProdDate) AS LastProdDate, Sum(Production.QTY) AS

Total_Prod

FROM Production, qryLastProdDate;

SELECT Max(Production.ProdDate) AS LastProdDate, Sum(Production.QTY) AS

Total_Prod

FROM Production, qryLastProdDate

WHERE Production.ProdDate < qryLastProdDate.LastProdDate;

### #18

## Re: How to calculate two value between different date and different reco

Posted 08 January 2011 - 04:17 AM

First, please use code tags for your queries, it's easier to read. Code tags are : [ code ] and [/code ]

(without spaces)

Yes, that is what I meant.

(without spaces)

Quote

tblShift1 : IDShift1, ProcDateShift1, ProcDataShift1

tblShift2 : IDShift2, ProcDateShift2, ProcDataShift2

tblShift3 : IDShift3, ProcDateShift3, ProcDataShift3

query :

CalcProcDataShift1 :

1 select

2 tblShift1.ProcDataShift1 - tblShift3.ProcDataShift3

3 From

4 tblShift1, tblShift3

5 where

6 tblShift3.ProcDateShift3 = DateDiff(dd, -1, Date()) and tblShift1 .ProcDateShift1 = Date()

Do you mean like this, Ionut ?

Thank

tblShift2 : IDShift2, ProcDateShift2, ProcDataShift2

tblShift3 : IDShift3, ProcDateShift3, ProcDataShift3

query :

CalcProcDataShift1 :

1 select

2 tblShift1.ProcDataShift1 - tblShift3.ProcDataShift3

3 From

4 tblShift1, tblShift3

5 where

6 tblShift3.ProcDateShift3 = DateDiff(dd, -1, Date()) and tblShift1 .ProcDateShift1 = Date()

Do you mean like this, Ionut ?

Thank

Yes, that is what I meant.

Quote

My problem, almost same like this :

I have a table that includes production data for each day of

processing, allowing me to compare totals from any two dates where

processing occurred.

My plan is to create one table with the most recent data using the

formula (Max[Prod Date])

I need to create another table for the previous processing date and

since that might be 1,2 and even 3 days earlier, the formula ((Max

[Prod Date])-1) doesn't work as it would often select a date where no

file exists.

Any suggestions for a formula that would pick up the next most recent

date?

I have a table that includes production data for each day of

processing, allowing me to compare totals from any two dates where

processing occurred.

My plan is to create one table with the most recent data using the

formula (Max[Prod Date])

I need to create another table for the previous processing date and

since that might be 1,2 and even 3 days earlier, the formula ((Max

[Prod Date])-1) doesn't work as it would often select a date where no

file exists.

Any suggestions for a formula that would pick up the next most recent

date?

select MAX(ProdDate) from Production Where ProdDate < (Select Max(ProdDate) from Production)

### #19

## Re: How to calculate two value between different date and different reco

Posted 08 January 2011 - 07:26 AM

Ionut, on 08 January 2011 - 03:17 AM, said:

First, please use code tags for your queries, it's easier to read. Code tags are : [ code ] and [/code ]

(without spaces)

Yes, that is what I meant.

(without spaces)

Quote

tblShift1 : IDShift1, ProcDateShift1, ProcDataShift1

tblShift2 : IDShift2, ProcDateShift2, ProcDataShift2

tblShift3 : IDShift3, ProcDateShift3, ProcDataShift3

query :

CalcProcDataShift1 :

1 select

2 tblShift1.ProcDataShift1 - tblShift3.ProcDataShift3

3 From

4 tblShift1, tblShift3

5 where

6 tblShift3.ProcDateShift3 = DateDiff(dd, -1, Date()) and tblShift1 .ProcDateShift1 = Date()

Do you mean like this, Ionut ?

Thank

tblShift2 : IDShift2, ProcDateShift2, ProcDataShift2

tblShift3 : IDShift3, ProcDateShift3, ProcDataShift3

query :

CalcProcDataShift1 :

1 select

2 tblShift1.ProcDataShift1 - tblShift3.ProcDataShift3

3 From

4 tblShift1, tblShift3

5 where

6 tblShift3.ProcDateShift3 = DateDiff(dd, -1, Date()) and tblShift1 .ProcDateShift1 = Date()

Do you mean like this, Ionut ?

Thank

Yes, that is what I meant.

Quote

My problem, almost same like this :

I have a table that includes production data for each day of

processing, allowing me to compare totals from any two dates where

processing occurred.

My plan is to create one table with the most recent data using the

formula (Max[Prod Date])

I need to create another table for the previous processing date and

since that might be 1,2 and even 3 days earlier, the formula ((Max

[Prod Date])-1) doesn't work as it would often select a date where no

file exists.

Any suggestions for a formula that would pick up the next most recent

date?

I have a table that includes production data for each day of

processing, allowing me to compare totals from any two dates where

processing occurred.

My plan is to create one table with the most recent data using the

formula (Max[Prod Date])

I need to create another table for the previous processing date and

since that might be 1,2 and even 3 days earlier, the formula ((Max

[Prod Date])-1) doesn't work as it would often select a date where no

file exists.

Any suggestions for a formula that would pick up the next most recent

date?

select MAX(ProdDate) from Production Where ProdDate < (Select Max(ProdDate) from Production)

ok Ionut, your codes worked properly, Thank

Again, how to update data in Query while data in table change, not update data in table by Query by UPDATE command ?

### #20

## Re: How to calculate two value between different date and different reco

Posted 08 January 2011 - 07:35 AM

Sorry, I cannot follow your question. What/when/how do you want to update?

### #21

## Re: How to calculate two value between different date and different reco

Posted 08 January 2011 - 08:32 PM

### #22

## Re: How to calculate two value between different date and different reco

Posted 10 January 2011 - 12:21 PM

You can't update a query based off of a table insertion. What your wanting to do from what i gather is grab the most recent data based on the query you were given when ever you enter a new row into the table. This would require you to manually run that query.

### #23

## Re: How to calculate two value between different date and different reco

Posted 11 January 2011 - 05:11 AM

ok, thank

I have problem to use command Dsum.

I want to calculate Total from query "SummaryH2ExPSA", the name field query is "H2ExPSATotal", the "H2ExPSATotal" is result calculation also, I write command like this in the Query Design :

Field : TotalMTD : DSum("[H2ExPSATotal]","SummaryH2ExPSA")

Total : Expression

Date,H2ExPSA1, H2ExPSA2,H2ExPSA3,H2ExPSATotal,TotalMTD

1/1/2011 20304 20296 17984 58584 485560

1/2/2011 16108 16344 16664 49116 485560

1/3/2011 16340 16544 16300 49184 485560

1/4/2011 16288 16268 16272 48828 485560

1/5/2011 16208 16320 16268 48796 485560

1/6/2011 16248 16288 16212 48748 485560

1/7/2011 16288 16336 16372 48996 485560

1/8/2011 16488 16536 11876 44900 485560

1/9/2011 6408 10688 15668 32764 485560

1/10/2011 18616 18536 18492 55644 485560

As per example, result of Total MTD is wrong.

Can someone help me ?

Thank

I have problem to use command Dsum.

I want to calculate Total from query "SummaryH2ExPSA", the name field query is "H2ExPSATotal", the "H2ExPSATotal" is result calculation also, I write command like this in the Query Design :

Field : TotalMTD : DSum("[H2ExPSATotal]","SummaryH2ExPSA")

Total : Expression

Date,H2ExPSA1, H2ExPSA2,H2ExPSA3,H2ExPSATotal,TotalMTD

1/1/2011 20304 20296 17984 58584 485560

1/2/2011 16108 16344 16664 49116 485560

1/3/2011 16340 16544 16300 49184 485560

1/4/2011 16288 16268 16272 48828 485560

1/5/2011 16208 16320 16268 48796 485560

1/6/2011 16248 16288 16212 48748 485560

1/7/2011 16288 16336 16372 48996 485560

1/8/2011 16488 16536 11876 44900 485560

1/9/2011 6408 10688 15668 32764 485560

1/10/2011 18616 18536 18492 55644 485560

As per example, result of Total MTD is wrong.

Can someone help me ?

Thank

### #24

## Re: How to calculate two value between different date and different reco

Posted 11 January 2011 - 05:21 AM

the general use of aggragate functions is like this ("othercolumn" can be missed out)

select SUM(H2ExPSATotal), othercolumn From Table WHERE some_Condition GROUP By otherColumn

### #25

## Re: How to calculate two value between different date and different reco

Posted 11 January 2011 - 06:39 AM

here is the definition of DSUM from techonthenet.com

The syntax for the DSum function is:

DSum ( expression, domain, [criteria] )

expression is the numeric values that you wish to sum.

domain is the set of records. This can be a table or a query name.

criteria is optional. It is the WHERE clause to apply to the domain.

what is happening with your use of DSum is it is saying ok i'm going to take the expression that you gave me "[H2ExPSATotal]" from the domain(table) "SummaryH2ExPSA" and i'm going to sum them up and put the results in the column TotalMTD.

what you have would be like saying this:

Can you tell me EXACTLY what you are trying to do, as

tells me that you did exactly what you were expecting. You calculated the total from the field H2ExPSATotal

The syntax for the DSum function is:

DSum ( expression, domain, [criteria] )

expression is the numeric values that you wish to sum.

domain is the set of records. This can be a table or a query name.

criteria is optional. It is the WHERE clause to apply to the domain.

what is happening with your use of DSum is it is saying ok i'm going to take the expression that you gave me "[H2ExPSATotal]" from the domain(table) "SummaryH2ExPSA" and i'm going to sum them up and put the results in the column TotalMTD.

what you have would be like saying this:

SELECT Sum([H2ExPSATotal]) AS TotalMTD FROM SummaryH2ExPSA

Can you tell me EXACTLY what you are trying to do, as

Quote

I want to calculate Total from query "SummaryH2ExPSA", the name field query is "H2ExPSATotal", the "H2ExPSATotal" is result calculation also

tells me that you did exactly what you were expecting. You calculated the total from the field H2ExPSATotal

This post has been edited by **rgfirefly24**: 11 January 2011 - 06:41 AM

### #26

## Re: How to calculate two value between different date and different reco

Posted 11 January 2011 - 07:56 AM

Yes, I would like this :

1 SELECT Sum([H2ExPSATotal]) AS TotalMTD

2 FROM SummaryH2ExPSA

but I want TotalMTD appear day per day, not result Dsum all day also go the first day

, exactly the TotalMTD increase day per day :

Illustration like this

date, Data, TotalData

1/1/2011, 3, 3

1/2/2011, 5, 8

1/3/2011, 10. 18

1/4/2011, 5, 23

Can you give solve in QUERY DESIGN :

Field : ?

Total : ?

When I put Expression in Total, command become wrong.

The other colum I have critera : Date1 = Date2 and Date2 = Date3

But we put "Group By" in Total column, it's result like the first example post

Thank

1 SELECT Sum([H2ExPSATotal]) AS TotalMTD

2 FROM SummaryH2ExPSA

but I want TotalMTD appear day per day, not result Dsum all day also go the first day

, exactly the TotalMTD increase day per day :

Illustration like this

date, Data, TotalData

1/1/2011, 3, 3

1/2/2011, 5, 8

1/3/2011, 10. 18

1/4/2011, 5, 23

Can you give solve in QUERY DESIGN :

Field : ?

Total : ?

When I put Expression in Total, command become wrong.

The other colum I have critera : Date1 = Date2 and Date2 = Date3

But we put "Group By" in Total column, it's result like the first example post

Thank

### #27

## Re: How to calculate two value between different date and different reco

Posted 11 January 2011 - 12:51 PM

whats your trying to do I don't think is possible using a calculated field. You would have to run a query that updates the table on a daily basis.

I believe this should do what you want. It joins the table to itself on an offset of 1 day. Try this and see if it produces the results you want. You can change the UPDATE/SET to a SELECT and verify the data before you start to update tables.

UPDATE <Table> SET <totalrow> = d1.total FROM <table> INNER JOIN ( SELECT T1.[Date], T1.Value, SUM(T2.Value) as Total FROM @Table T1 CROSS JOIN @Table T2 WHERE T2.Date <= T1.Date GROUP BY T1.Date, T1.Value )d1 on <table>.Date = d1.Date

I believe this should do what you want. It joins the table to itself on an offset of 1 day. Try this and see if it produces the results you want. You can change the UPDATE/SET to a SELECT and verify the data before you start to update tables.

This post has been edited by **rgfirefly24**: 11 January 2011 - 01:43 PM

### #28

## Re: How to calculate two value between different date and different reco

Posted 11 January 2011 - 07:47 PM

my codes like this :

SELECT [Calc Shift 1-109].DateShift1 AS Expr1, [Calc Shift 1-109].H2ExPSA1 AS H2ExPSA1, [Calc Shift 2-109].H2ExPSA2 AS H2ExPSA2, [Calc Shift 3-109].H2ExPSA3 AS H2ExPSA3, [H2ExPSA1]+[H2ExPSA2]+[H2ExPSA3] AS H2ExPSATotal, DSum(" [H2ExPSATotal] ","SummaryH2ExPSA") AS TotalMTD

FROM [Calc Shift 1-109], [Calc Shift 2-109], [Calc Shift 3-109]

WHERE ((([Calc Shift 1-109].DateShift1)=[Calc Shift 2-109]![DateShift2] And ([Calc Shift 1-109].DateShift1)=[Calc Shift 3-109]![DateShift3]));

SELECT [Calc Shift 1-109].DateShift1 AS Expr1, [Calc Shift 1-109].H2ExPSA1 AS H2ExPSA1, [Calc Shift 2-109].H2ExPSA2 AS H2ExPSA2, [Calc Shift 3-109].H2ExPSA3 AS H2ExPSA3, [H2ExPSA1]+[H2ExPSA2]+[H2ExPSA3] AS H2ExPSATotal, DSum(" [H2ExPSATotal] ","SummaryH2ExPSA") AS TotalMTD

FROM [Calc Shift 1-109], [Calc Shift 2-109], [Calc Shift 3-109]

WHERE ((([Calc Shift 1-109].DateShift1)=[Calc Shift 2-109]![DateShift2] And ([Calc Shift 1-109].DateShift1)=[Calc Shift 3-109]![DateShift3]));

### #29

## Re: How to calculate two value between different date and different reco

Posted 11 January 2011 - 07:56 PM

and I want the TotalMTD reset every date 1 of every month

thank

thank

### #30

## Re: How to calculate two value between different date and different reco

Posted 12 January 2011 - 06:28 AM

at this point, I'd like to see you attempt your own solution. Also your starting to confuse me again. What is that query you posted, and how does it relate to what you were asking with the summary table?