Calculations

I am trying to learn how to do some calulations with my data.
Say I have 2 tables:
Total Orders
Total Hours

Each table's Primary key is a date.
The 2 tables are linked by the date.

Orders Table has these Feilds
Date
Today's Orders
Orders left from Yeasterday
Orders from Previous days

Hours Table has these fields
Date
Total Hours

Calculations i need to build example:
1st Calculation:
8/10/06 Total Orders - 8/11/06 Yesterday's Orders + Previous Orders = Total
Orders Shipped.

2nd Calculation:
Total Orders Shipped / Total Hours = Orders per hour

Do these need to be done by a query?
And how do I do calculations like that for 1 date vs other date.(hope that
makes sense)

I hope this is clear enough.
I can do totals and calculations for the same date, just not one date info -
diffrent date info.
Anyone understand what I am getting at and can help?

You should thoroughly re-think your data structure. There are many examples
of inventory control that you can crib from, such as the "Northwinds"
example and the inventory template that both ship with Access.

You normally do NOT store calculation results. You store order details,
then calculate as needed for forms/reports.
-Ed


"Sydious" wrote in message
news
I am trying to learn how to do some calulations with my data.
Say I have 2 tables:
Total Orders
Total Hours

Each table's Primary key is a date.
The 2 tables are linked by the date.

Orders Table has these Feilds
Date
Today's Orders
Orders left from Yeasterday
Orders from Previous days

Hours Table has these fields
Date
Total Hours

Calculations i need to build example:
1st Calculation:
8/10/06 Total Orders - 8/11/06 Yesterday's Orders + Previous Orders =
Total
Orders Shipped.

2nd Calculation:
Total Orders Shipped / Total Hours = Orders per hour

Do these need to be done by a query?
And how do I do calculations like that for 1 date vs other date.(hope that
makes sense)

I hope this is clear enough.
I can do totals and calculations for the same date, just not one date
info -
diffrent date info.
Anyone understand what I am getting at and can help?

On Wed, 30 Aug 2006 00:40:01 -0700, Sydious
wrote:

I am trying to learn how to do some calulations with my data.

Then DON'T DO THEM IN TABLES.

Tables are to store *data*. Your table should ideally have one record
per order, or - if you are totalling the orders externally to Access
and don't care about orders, one record per day.

Say I have 2 tables:
Total Orders
Total Hours

Each table's Primary key is a date.
The 2 tables are linked by the date.

Orders Table has these Feilds
Date
Today's Orders
Orders left from Yeasterday
Orders from Previous days

These values can be looked up from the Total Orders table using a
query. They should not be stored in a table.

Hours Table has these fields
Date
Total Hours

Calculations i need to build example:
1st Calculation:
8/10/06 Total Orders - 8/11/06 Yesterday's Orders + Previous Orders = Total
Orders Shipped.

2nd Calculation:
Total Orders Shipped / Total Hours = Orders per hour

Do these need to be done by a query?

Yes.

And how do I do calculations like that for 1 date vs other date.(hope that
makes sense)

Join the tables to the query TWICE using criteria to select the date;
for instance, you can add [Total Orders] to the query grid twice
(Access will assign an alias name to the second instance). Put a
criterion on the second instance's date of

=[Total Orders].[Date] - 1

John W. Vinson[MVP]

Thanks.
That was the help I needed.
Think I got it to work.
Just couldn't get my head around it at first.

"John Vinson" wrote:

On Wed, 30 Aug 2006 00:40:01 -0700, Sydious
wrote:

I am trying to learn how to do some calulations with my data.

Then DON'T DO THEM IN TABLES.

Tables are to store *data*. Your table should ideally have one record
per order, or - if you are totalling the orders externally to Access
and don't care about orders, one record per day.

Say I have 2 tables:
Total Orders
Total Hours

Each table's Primary key is a date.
The 2 tables are linked by the date.

Orders Table has these Feilds
Date
Today's Orders
Orders left from Yeasterday
Orders from Previous days

These values can be looked up from the Total Orders table using a
query. They should not be stored in a table.

Hours Table has these fields
Date
Total Hours

Calculations i need to build example:
1st Calculation:
8/10/06 Total Orders - 8/11/06 Yesterday's Orders + Previous Orders = Total
Orders Shipped.

2nd Calculation:
Total Orders Shipped / Total Hours = Orders per hour

Do these need to be done by a query?

Yes.

And how do I do calculations like that for 1 date vs other date.(hope that
makes sense)

Join the tables to the query TWICE using criteria to select the date;
for instance, you can add [Total Orders] to the query grid twice
(Access will assign an alias name to the second instance). Put a
criterion on the second instance's date of

=[Total Orders].[Date] - 1

John W. Vinson[MVP]

Ok I got it to work.
I ran into a problem I didn't think about....
The day before doesn't always exsist.
There is a gap in the dates because of weekends.
so....
[Table].[Date] -1 won't always work.
How can I get the date before if it's not just 1 date behind?


"John Vinson" wrote:

On Wed, 30 Aug 2006 00:40:01 -0700, Sydious
wrote:

I am trying to learn how to do some calulations with my data.

Then DON'T DO THEM IN TABLES.

Tables are to store *data*. Your table should ideally have one record
per order, or - if you are totalling the orders externally to Access
and don't care about orders, one record per day.

Say I have 2 tables:
Total Orders
Total Hours

Each table's Primary key is a date.
The 2 tables are linked by the date.

Orders Table has these Feilds
Date
Today's Orders
Orders left from Yeasterday
Orders from Previous days

These values can be looked up from the Total Orders table using a
query. They should not be stored in a table.

Hours Table has these fields
Date
Total Hours

Calculations i need to build example:
1st Calculation:
8/10/06 Total Orders - 8/11/06 Yesterday's Orders + Previous Orders = Total
Orders Shipped.

2nd Calculation:
Total Orders Shipped / Total Hours = Orders per hour

Do these need to be done by a query?

Yes.

And how do I do calculations like that for 1 date vs other date.(hope that
makes sense)

Join the tables to the query TWICE using criteria to select the date;
for instance, you can add [Total Orders] to the query grid twice
(Access will assign an alias name to the second instance). Put a
criterion on the second instance's date of

=[Total Orders].[Date] - 1

John W. Vinson[MVP]

On Wed, 30 Aug 2006 19:29:01 -0700, Sydious
wrote:

Ok I got it to work.
I ran into a problem I didn't think about....
The day before doesn't always exsist.
There is a gap in the dates because of weekends.
so....
[Table].[Date] -1 won't always work.
How can I get the date before if it's not just 1 date behind?

Please post the SQL view of the query that you're currently using.
You're going to need a "subquery", which can be a bit complex - I'd
like to see the actual current query so I can suggest a working
version rather than something which might not fit!

John W. Vinson[MVP]

SELECT tblForecastReports.FCDate,
tblForecastReports.fcrCurParcelOrd+tblForecastRepo rts.fcrCurLTLOrd+tblForecastReports.fcrCurCUPUOrd+ tblForecastReports.fcrPD1ParcelOrd+tblForecastRepo rts.fcrPD2ParcelOrd+tblForecastReports.fcrPD1LTLOr d+tblForecastReports.fcrPD2LTLOrd+tblForecastRepor ts.fcrPD1CUPUOrd+tblForecastReports.fcrPD2CUPUOrd
AS TOFD,
tblForecastReports.fcrCurParcelLns+tblForecastRepo rts.fcrCurLTLLns+tblForecastReports.fcrCurCUPULns+ tblForecastReports.fcrPD1ParcelLns+tblForecastRepo rts.fcrPD2ParcelLns+tblForecastReports.fcrPD1LTLLn s+tblForecastReports.fcrPD2LTLLns+tblForecastRepor ts.fcrPD1CUPULns+tblForecastReports.fcrPD2CUPULns
AS TLFD,
tblForecastReports.fcrCurParcelPcs+tblForecastRepo rts.fcrCurLTLPcs+tblForecastReports.fcrCurCUPUPcs+ tblForecastReports.fcrPD1ParcelPcs+tblForecastRepo rts.fcrPD2ParcelPcs+tblForecastReports.fcrPD1LTLPc s+tblForecastReports.fcrPD2LTLPcs+tblForecastRepor ts.fcrPD1CUPUPcs+tblForecastReports.fcrPD2CUPUPcs
AS TPFD,
tblForecastReports.fcrCurParcelVal+tblForecastRepo rts.fcrCurLTLVal+tblForecastReports.fcrCurCUPUVal+ tblForecastReports.fcrPD1ParcelVal+tblForecastRepo rts.fcrPD2ParcelVal+tblForecastReports.fcrPD1LTLVa l+tblForecastReports.fcrPD2LTLVal+tblForecastRepor ts.fcrPD1CUPUVal+tblForecastReports.fcrPD2CUPUVal
AS TVFD, tblForecastReports_1.FCDate,
tblForecastReports_1.fcrPD1ParcelOrd+tblForecastRe ports_1.fcrPD2ParcelOrd+tblForecastReports_1.fcrPD 1LTLOrd+tblForecastReports_1.fcrPD2LTLOrd+tblForec astReports_1.fcrPD1CUPUOrd+tblForecastReports_1.fc rPD2CUPUOrd
AS TOLFD,
tblForecastReports_1.fcrPD1ParcelLns+tblForecastRe ports_1.fcrPD2ParcelLns+tblForecastReports_1.fcrPD 1LTLLns+tblForecastReports_1.fcrPD2LTLLns+tblForec astReports_1.fcrPD1CUPULns+tblForecastReports_1.fc rPD2CUPULns
AS TLLFD,
tblForecastReports_1.fcrPD1ParcelPcs+tblForecastRe ports_1.fcrPD2ParcelPcs+tblForecastReports_1.fcrPD 1LTLPcs+tblForecastReports_1.fcrPD2LTLPcs+tblForec astReports_1.fcrPD1CUPUPcs+tblForecastReports_1.fc rPD2CUPUPcs
AS TPLFD,
tblForecastReports_1.fcrPD1ParcelVal+tblForecastRe ports_1.fcrPD2ParcelVal+tblForecastReports_1.fcrPD 1LTLVal+tblForecastReports_1.fcrPD2LTLVal+tblForec astReports_1.fcrPD1CUPUVal+tblForecastReports_1.fc rPD2CUPUVal
AS TVLFD, [TOFD]-[TOLFD] AS [Total Orders Shipped], [TLFD]-[TLLFD] AS [Total
Lines Shipped], [TPFD]-[TPLFD] AS [Total Pcs Shipped], [TVFD]-[TVLFD] AS
[Total Value Shipped]
FROM tblForecastReports, tblForecastReports AS tblForecastReports_1
WHERE (((tblForecastReports_1.FCDate)=[tblForecastReports].[FCDate]+1));

I never looked at a query using SQL view before. So this is new to me.
In the past post's i said 1 day before. It is actualy 1 day ahead, or I need
the Next availble day ahead of the current one.
Thanks for your help.



"John Vinson" wrote:

On Wed, 30 Aug 2006 19:29:01 -0700, Sydious
wrote:

Ok I got it to work.
I ran into a problem I didn't think about....
The day before doesn't always exsist.
There is a gap in the dates because of weekends.
so....
[Table].[Date] -1 won't always work.
How can I get the date before if it's not just 1 date behind?

Please post the SQL view of the query that you're currently using.
You're going to need a "subquery", which can be a bit complex - I'd
like to see the actual current query so I can suggest a working
version rather than something which might not fit!

John W. Vinson[MVP]

On Thu, 31 Aug 2006 18:22:02 -0700, Sydious
wrote:

I never looked at a query using SQL view before. So this is new to me.
In the past post's i said 1 day before. It is actualy 1 day ahead, or I need
the Next availble day ahead of the current one.

So there are multiple days, in the future as well as in the past, in
your table? If so, you'll be able to use a Subquery as a criterion
(instead of Date() + 1):

SELECT tblForecastReports.FCDate,
tblForecastReports.fcrCurParcelOrd+tblForecastRepo rts.fcrCurLTLOrd+tblForecastReports.fcrCurCUPUOrd+ tblForecastReports.fcrPD1ParcelOrd+tblForecastRepo rts.fcrPD2ParcelOrd+tblForecastReports.fcrPD1LTLOr d+tblForecastReports.fcrPD2LTLOrd+tblForecastRepor ts.fcrPD1CUPUOrd+tblForecastReports.fcrPD2CUPUOrd
AS TOFD,
tblForecastReports.fcrCurParcelLns+tblForecastRepo rts.fcrCurLTLLns+tblForecastReports.fcrCurCUPULns+ tblForecastReports.fcrPD1ParcelLns+tblForecastRepo rts.fcrPD2ParcelLns+tblForecastReports.fcrPD1LTLLn s+tblForecastReports.fcrPD2LTLLns+tblForecastRepor ts.fcrPD1CUPULns+tblForecastReports.fcrPD2CUPULns
AS TLFD,
tblForecastReports.fcrCurParcelPcs+tblForecastRepo rts.fcrCurLTLPcs+tblForecastReports.fcrCurCUPUPcs+ tblForecastReports.fcrPD1ParcelPcs+tblForecastRepo rts.fcrPD2ParcelPcs+tblForecastReports.fcrPD1LTLPc s+tblForecastReports.fcrPD2LTLPcs+tblForecastRepor ts.fcrPD1CUPUPcs+tblForecastReports.fcrPD2CUPUPcs
AS TPFD,
tblForecastReports.fcrCurParcelVal+tblForecastRepo rts.fcrCurLTLVal+tblForecastReports.fcrCurCUPUVal+ tblForecastReports.fcrPD1ParcelVal+tblForecastRepo rts.fcrPD2ParcelVal+tblForecastReports.fcrPD1LTLVa l+tblForecastReports.fcrPD2LTLVal+tblForecastRepor ts.fcrPD1CUPUVal+tblForecastReports.fcrPD2CUPUVal
AS TVFD, tblForecastReports_1.FCDate,
tblForecastReports_1.fcrPD1ParcelOrd+tblForecastRe ports_1.fcrPD2ParcelOrd+tblForecastReports_1.fcrPD 1LTLOrd+tblForecastReports_1.fcrPD2LTLOrd+tblForec astReports_1.fcrPD1CUPUOrd+tblForecastReports_1.fc rPD2CUPUOrd
AS TOLFD,
tblForecastReports_1.fcrPD1ParcelLns+tblForecastRe ports_1.fcrPD2ParcelLns+tblForecastReports_1.fcrPD 1LTLLns+tblForecastReports_1.fcrPD2LTLLns+tblForec astReports_1.fcrPD1CUPULns+tblForecastReports_1.fc rPD2CUPULns
AS TLLFD,
tblForecastReports_1.fcrPD1ParcelPcs+tblForecastRe ports_1.fcrPD2ParcelPcs+tblForecastReports_1.fcrPD 1LTLPcs+tblForecastReports_1.fcrPD2LTLPcs+tblForec astReports_1.fcrPD1CUPUPcs+tblForecastReports_1.fc rPD2CUPUPcs
AS TPLFD,
tblForecastReports_1.fcrPD1ParcelVal+tblForecastRe ports_1.fcrPD2ParcelVal+tblForecastReports_1.fcrPD 1LTLVal+tblForecastReports_1.fcrPD2LTLVal+tblForec astReports_1.fcrPD1CUPUVal+tblForecastReports_1.fc rPD2CUPUVal
AS TVLFD, [TOFD]-[TOLFD] AS [Total Orders Shipped], [TLFD]-[TLLFD] AS
[Total
Lines Shipped], [TPFD]-[TPLFD] AS [Total Pcs Shipped], [TVFD]-[TVLFD]
AS
[Total Value Shipped]
FROM tblForecastReports, tblForecastReports AS tblForecastReports_1
WHERE tblForecastReports_1.FCDate=(SELECT Min(X.[FCDate]) FROM
tblForcastReports AS X WHERE X.FCDATE tblForcastReports.FCDATE);


John W. Vinson[MVP]

Thanks alot John!
I realized I sent you the wrong Query.
But I looked at what you did and did that with the correct query and got the
right results. Thanks alot for your help on this. I can now move on!
THANKS AGAIN!!!



"John Vinson" wrote:

On Thu, 31 Aug 2006 18:22:02 -0700, Sydious
wrote:

I never looked at a query using SQL view before. So this is new to me.
In the past post's i said 1 day before. It is actualy 1 day ahead, or I need
the Next availble day ahead of the current one.

So there are multiple days, in the future as well as in the past, in
your table? If so, you'll be able to use a Subquery as a criterion
(instead of Date() + 1):

SELECT tblForecastReports.FCDate,
tblForecastReports.fcrCurParcelOrd+tblForecastRepo rts.fcrCurLTLOrd+tblForecastReports.fcrCurCUPUOrd+ tblForecastReports.fcrPD1ParcelOrd+tblForecastRepo rts.fcrPD2ParcelOrd+tblForecastReports.fcrPD1LTLOr d+tblForecastReports.fcrPD2LTLOrd+tblForecastRepor ts.fcrPD1CUPUOrd+tblForecastReports.fcrPD2CUPUOrd
AS TOFD,
tblForecastReports.fcrCurParcelLns+tblForecastRepo rts.fcrCurLTLLns+tblForecastReports.fcrCurCUPULns+ tblForecastReports.fcrPD1ParcelLns+tblForecastRepo rts.fcrPD2ParcelLns+tblForecastReports.fcrPD1LTLLn s+tblForecastReports.fcrPD2LTLLns+tblForecastRepor ts.fcrPD1CUPULns+tblForecastReports.fcrPD2CUPULns
AS TLFD,
tblForecastReports.fcrCurParcelPcs+tblForecastRepo rts.fcrCurLTLPcs+tblForecastReports.fcrCurCUPUPcs+ tblForecastReports.fcrPD1ParcelPcs+tblForecastRepo rts.fcrPD2ParcelPcs+tblForecastReports.fcrPD1LTLPc s+tblForecastReports.fcrPD2LTLPcs+tblForecastRepor ts.fcrPD1CUPUPcs+tblForecastReports.fcrPD2CUPUPcs
AS TPFD,
tblForecastReports.fcrCurParcelVal+tblForecastRepo rts.fcrCurLTLVal+tblForecastReports.fcrCurCUPUVal+ tblForecastReports.fcrPD1ParcelVal+tblForecastRepo rts.fcrPD2ParcelVal+tblForecastReports.fcrPD1LTLVa l+tblForecastReports.fcrPD2LTLVal+tblForecastRepor ts.fcrPD1CUPUVal+tblForecastReports.fcrPD2CUPUVal
AS TVFD, tblForecastReports_1.FCDate,
tblForecastReports_1.fcrPD1ParcelOrd+tblForecastRe ports_1.fcrPD2ParcelOrd+tblForecastReports_1.fcrPD 1LTLOrd+tblForecastReports_1.fcrPD2LTLOrd+tblForec astReports_1.fcrPD1CUPUOrd+tblForecastReports_1.fc rPD2CUPUOrd
AS TOLFD,
tblForecastReports_1.fcrPD1ParcelLns+tblForecastRe ports_1.fcrPD2ParcelLns+tblForecastReports_1.fcrPD 1LTLLns+tblForecastReports_1.fcrPD2LTLLns+tblForec astReports_1.fcrPD1CUPULns+tblForecastReports_1.fc rPD2CUPULns
AS TLLFD,
tblForecastReports_1.fcrPD1ParcelPcs+tblForecastRe ports_1.fcrPD2ParcelPcs+tblForecastReports_1.fcrPD 1LTLPcs+tblForecastReports_1.fcrPD2LTLPcs+tblForec astReports_1.fcrPD1CUPUPcs+tblForecastReports_1.fc rPD2CUPUPcs
AS TPLFD,
tblForecastReports_1.fcrPD1ParcelVal+tblForecastRe ports_1.fcrPD2ParcelVal+tblForecastReports_1.fcrPD 1LTLVal+tblForecastReports_1.fcrPD2LTLVal+tblForec astReports_1.fcrPD1CUPUVal+tblForecastReports_1.fc rPD2CUPUVal
AS TVLFD, [TOFD]-[TOLFD] AS [Total Orders Shipped], [TLFD]-[TLLFD] AS
[Total
Lines Shipped], [TPFD]-[TPLFD] AS [Total Pcs Shipped], [TVFD]-[TVLFD]
AS
[Total Value Shipped]
FROM tblForecastReports, tblForecastReports AS tblForecastReports_1
WHERE tblForecastReports_1.FCDate=(SELECT Min(X.[FCDate]) FROM
tblForcastReports AS X WHERE X.FCDATE tblForcastReports.FCDATE);


John W. Vinson[MVP]