Calculations

[Guest]

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?

 

[Ed Robichaud]

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

 

[John Vinson]

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]

 

[Guest]

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

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]

 

[Guest]

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....

.[Date] -1 won't always work.
How can I get the date before if it's not just 1 date behind?

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]

 

[John Vinson]

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....

.[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]

 

[Guest]

SELECT tblForecastReports.FCDate,
tblForecastReports.fcrCurParcelOrd+tblForecastReports.fcrCurLTLOrd+tblForecastReports.fcrCurCUPUOrd+tblForecastReports.fcrPD1ParcelOrd+tblForecastReports.fcrPD2ParcelOrd+tblForecastReports.fcrPD1LTLOrd+tblForecastReports.fcrPD2LTLOrd+tblForecastReports.fcrPD1CUPUOrd+tblForecastReports.fcrPD2CUPUOrd
AS TOFD,
tblForecastReports.fcrCurParcelLns+tblForecastReports.fcrCurLTLLns+tblForecastReports.fcrCurCUPULns+tblForecastReports.fcrPD1ParcelLns+tblForecastReports.fcrPD2ParcelLns+tblForecastReports.fcrPD1LTLLns+tblForecastReports.fcrPD2LTLLns+tblForecastReports.fcrPD1CUPULns+tblForecastReports.fcrPD2CUPULns
AS TLFD,
tblForecastReports.fcrCurParcelPcs+tblForecastReports.fcrCurLTLPcs+tblForecastReports.fcrCurCUPUPcs+tblForecastReports.fcrPD1ParcelPcs+tblForecastReports.fcrPD2ParcelPcs+tblForecastReports.fcrPD1LTLPcs+tblForecastReports.fcrPD2LTLPcs+tblForecastReports.fcrPD1CUPUPcs+tblForecastReports.fcrPD2CUPUPcs
AS TPFD,
tblForecastReports.fcrCurParcelVal+tblForecastReports.fcrCurLTLVal+tblForecastReports.fcrCurCUPUVal+tblForecastReports.fcrPD1ParcelVal+tblForecastReports.fcrPD2ParcelVal+tblForecastReports.fcrPD1LTLVal+tblForecastReports.fcrPD2LTLVal+tblForecastReports.fcrPD1CUPUVal+tblForecastReports.fcrPD2CUPUVal
AS TVFD, tblForecastReports_1.FCDate,
tblForecastReports_1.fcrPD1ParcelOrd+tblForecastReports_1.fcrPD2ParcelOrd+tblForecastReports_1.fcrPD1LTLOrd+tblForecastReports_1.fcrPD2LTLOrd+tblForecastReports_1.fcrPD1CUPUOrd+tblForecastReports_1.fcrPD2CUPUOrd
AS TOLFD,
tblForecastReports_1.fcrPD1ParcelLns+tblForecastReports_1.fcrPD2ParcelLns+tblForecastReports_1.fcrPD1LTLLns+tblForecastReports_1.fcrPD2LTLLns+tblForecastReports_1.fcrPD1CUPULns+tblForecastReports_1.fcrPD2CUPULns
AS TLLFD,
tblForecastReports_1.fcrPD1ParcelPcs+tblForecastReports_1.fcrPD2ParcelPcs+tblForecastReports_1.fcrPD1LTLPcs+tblForecastReports_1.fcrPD2LTLPcs+tblForecastReports_1.fcrPD1CUPUPcs+tblForecastReports_1.fcrPD2CUPUPcs
AS TPLFD,
tblForecastReports_1.fcrPD1ParcelVal+tblForecastReports_1.fcrPD2ParcelVal+tblForecastReports_1.fcrPD1LTLVal+tblForecastReports_1.fcrPD2LTLVal+tblForecastReports_1.fcrPD1CUPUVal+tblForecastReports_1.fcrPD2CUPUVal
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.

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....

.[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]

 

[John Vinson]

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+tblForecastReports.fcrCurLTLOrd+tblForecastReports.fcrCurCUPUOrd+tblForecastReports.fcrPD1ParcelOrd+tblForecastReports.fcrPD2ParcelOrd+tblForecastReports.fcrPD1LTLOrd+tblForecastReports.fcrPD2LTLOrd+tblForecastReports.fcrPD1CUPUOrd+tblForecastReports.fcrPD2CUPUOrd
AS TOFD,
tblForecastReports.fcrCurParcelLns+tblForecastReports.fcrCurLTLLns+tblForecastReports.fcrCurCUPULns+tblForecastReports.fcrPD1ParcelLns+tblForecastReports.fcrPD2ParcelLns+tblForecastReports.fcrPD1LTLLns+tblForecastReports.fcrPD2LTLLns+tblForecastReports.fcrPD1CUPULns+tblForecastReports.fcrPD2CUPULns
AS TLFD,
tblForecastReports.fcrCurParcelPcs+tblForecastReports.fcrCurLTLPcs+tblForecastReports.fcrCurCUPUPcs+tblForecastReports.fcrPD1ParcelPcs+tblForecastReports.fcrPD2ParcelPcs+tblForecastReports.fcrPD1LTLPcs+tblForecastReports.fcrPD2LTLPcs+tblForecastReports.fcrPD1CUPUPcs+tblForecastReports.fcrPD2CUPUPcs
AS TPFD,
tblForecastReports.fcrCurParcelVal+tblForecastReports.fcrCurLTLVal+tblForecastReports.fcrCurCUPUVal+tblForecastReports.fcrPD1ParcelVal+tblForecastReports.fcrPD2ParcelVal+tblForecastReports.fcrPD1LTLVal+tblForecastReports.fcrPD2LTLVal+tblForecastReports.fcrPD1CUPUVal+tblForecastReports.fcrPD2CUPUVal
AS TVFD, tblForecastReports_1.FCDate,
tblForecastReports_1.fcrPD1ParcelOrd+tblForecastReports_1.fcrPD2ParcelOrd+tblForecastReports_1.fcrPD1LTLOrd+tblForecastReports_1.fcrPD2LTLOrd+tblForecastReports_1.fcrPD1CUPUOrd+tblForecastReports_1.fcrPD2CUPUOrd
AS TOLFD,
tblForecastReports_1.fcrPD1ParcelLns+tblForecastReports_1.fcrPD2ParcelLns+tblForecastReports_1.fcrPD1LTLLns+tblForecastReports_1.fcrPD2LTLLns+tblForecastReports_1.fcrPD1CUPULns+tblForecastReports_1.fcrPD2CUPULns
AS TLLFD,
tblForecastReports_1.fcrPD1ParcelPcs+tblForecastReports_1.fcrPD2ParcelPcs+tblForecastReports_1.fcrPD1LTLPcs+tblForecastReports_1.fcrPD2LTLPcs+tblForecastReports_1.fcrPD1CUPUPcs+tblForecastReports_1.fcrPD2CUPUPcs
AS TPLFD,
tblForecastReports_1.fcrPD1ParcelVal+tblForecastReports_1.fcrPD2ParcelVal+tblForecastReports_1.fcrPD1LTLVal+tblForecastReports_1.fcrPD2LTLVal+tblForecastReports_1.fcrPD1CUPUVal+tblForecastReports_1.fcrPD2CUPUVal
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]

 

[Guest]

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!!!

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+tblForecastReports.fcrCurLTLOrd+tblForecastReports.fcrCurCUPUOrd+tblForecastReports.fcrPD1ParcelOrd+tblForecastReports.fcrPD2ParcelOrd+tblForecastReports.fcrPD1LTLOrd+tblForecastReports.fcrPD2LTLOrd+tblForecastReports.fcrPD1CUPUOrd+tblForecastReports.fcrPD2CUPUOrd
AS TOFD,
tblForecastReports.fcrCurParcelLns+tblForecastReports.fcrCurLTLLns+tblForecastReports.fcrCurCUPULns+tblForecastReports.fcrPD1ParcelLns+tblForecastReports.fcrPD2ParcelLns+tblForecastReports.fcrPD1LTLLns+tblForecastReports.fcrPD2LTLLns+tblForecastReports.fcrPD1CUPULns+tblForecastReports.fcrPD2CUPULns
AS TLFD,
tblForecastReports.fcrCurParcelPcs+tblForecastReports.fcrCurLTLPcs+tblForecastReports.fcrCurCUPUPcs+tblForecastReports.fcrPD1ParcelPcs+tblForecastReports.fcrPD2ParcelPcs+tblForecastReports.fcrPD1LTLPcs+tblForecastReports.fcrPD2LTLPcs+tblForecastReports.fcrPD1CUPUPcs+tblForecastReports.fcrPD2CUPUPcs
AS TPFD,
tblForecastReports.fcrCurParcelVal+tblForecastReports.fcrCurLTLVal+tblForecastReports.fcrCurCUPUVal+tblForecastReports.fcrPD1ParcelVal+tblForecastReports.fcrPD2ParcelVal+tblForecastReports.fcrPD1LTLVal+tblForecastReports.fcrPD2LTLVal+tblForecastReports.fcrPD1CUPUVal+tblForecastReports.fcrPD2CUPUVal
AS TVFD, tblForecastReports_1.FCDate,
tblForecastReports_1.fcrPD1ParcelOrd+tblForecastReports_1.fcrPD2ParcelOrd+tblForecastReports_1.fcrPD1LTLOrd+tblForecastReports_1.fcrPD2LTLOrd+tblForecastReports_1.fcrPD1CUPUOrd+tblForecastReports_1.fcrPD2CUPUOrd
AS TOLFD,
tblForecastReports_1.fcrPD1ParcelLns+tblForecastReports_1.fcrPD2ParcelLns+tblForecastReports_1.fcrPD1LTLLns+tblForecastReports_1.fcrPD2LTLLns+tblForecastReports_1.fcrPD1CUPULns+tblForecastReports_1.fcrPD2CUPULns
AS TLLFD,
tblForecastReports_1.fcrPD1ParcelPcs+tblForecastReports_1.fcrPD2ParcelPcs+tblForecastReports_1.fcrPD1LTLPcs+tblForecastReports_1.fcrPD2LTLPcs+tblForecastReports_1.fcrPD1CUPUPcs+tblForecastReports_1.fcrPD2CUPUPcs
AS TPLFD,
tblForecastReports_1.fcrPD1ParcelVal+tblForecastReports_1.fcrPD2ParcelVal+tblForecastReports_1.fcrPD1LTLVal+tblForecastReports_1.fcrPD2LTLVal+tblForecastReports_1.fcrPD1CUPUVal+tblForecastReports_1.fcrPD2CUPUVal
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]