If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Calculations
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? |
#3
|
|||
|
|||
Calculations
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] |
#4
|
|||
|
|||
Calculations
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] |
#5
|
|||
|
|||
Calculations
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] |
#6
|
|||
|
|||
Calculations
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] |
#7
|
|||
|
|||
Calculations
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] |
#8
|
|||
|
|||
Calculations
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] |
#9
|
|||
|
|||
Calculations
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] |
Thread Tools | |
Display Modes | |
|
|