[mnazera]

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

[mnazera]

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;

[Ionut]

First, please use code tags for your queries, it's easier to read. Code tags are : [ code ] and [/code ]
(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

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?

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

[mnazera]

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)

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

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?

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 ?

[Ionut]

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

[mnazera]

Ionut, on 08 January 2011 - 06:35 AM, said:

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

Illustration by like this :
Every day I add new record in the tables, then I need my query also update based on current data in the table

Thank

[rgfirefly24]

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.

[mnazera]

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

[Ionut]

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

[rgfirefly24]

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:

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

[mnazera]

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

[rgfirefly24]

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.

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

[mnazera]

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]));

[mnazera]

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

thank

[rgfirefly24]

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?