Sum function for data input

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that sums time for various operations say FORM, PRESS, MILL.
From an old FOX-Pro database, this is fine.
The problem is that I want to fetch this summed data into a text box on a
form.
I can open the form & query at the same time then copy & paste this data
across but this is tedious to say the least anyone any Ideas as to hw I can
automate this???
I thought of using dlookup but don’t seem to be able to figure out how to
sum on a group using this function?

Here is the query

THIS IS MASTER QUERY used in sum query

SELECT m_wml.pstk, m_wml.line, m_wml.mc, m_wml.setup, m_wml.runt,
m_wml.ml_trans, m_wml.ml_runper, m_wmach.machrate,
([setup]/Forms!frmquoteform!QTY)+([runt]/[ml_runper]) AS [Unit Time], ([Unit
Time]*[machrate])/100 AS [Unit Cost]
FROM m_wml INNER JOIN m_wmach ON m_wml.mc = m_wmach.machcode
GROUP BY m_wml.pstk, m_wml.line, m_wml.mc, m_wml.setup, m_wml.runt,
m_wml.ml_trans, m_wml.ml_runper, m_wmach.machrate
HAVING (((m_wml.pstk)=[Forms]![frmquoteform]![txtPSTK]));



THIS IS SUM QUERY that I copy the data from & paste it into the text bog on
the form.

SELECT DISTINCTROW QRY_Part_No_OPs.mc, Sum(QRY_Part_No_OPs.[Unit Time]) AS
[Sum Of Unit Time]
FROM QRY_Part_No_OPs
GROUP BY QRY_Part_No_OPs.mc
HAVING (((QRY_Part_No_OPs.mc)="FORM"));
 
Tried that but get an error saying that the object does not automation or
something? may be its the syntax any ideas.

DLookUp([QRY_sum-fORM time]![Sum Of Unit Time],[QRY_sum-fORM
time],[QRY_sum-fORM time]![mc]="FORM")

Above code is in the criteria field in the setvalue macro command and tries
to return the value from sum of unit time where the mc colum = FORM
or should i bind it to a text box then copy it over?

tina said:
have you tried running a DLookup() on your sum query, rather than on a
table?


Jedit said:
I have a query that sums time for various operations say FORM, PRESS, MILL.
From an old FOX-Pro database, this is fine.
The problem is that I want to fetch this summed data into a text box on a
form.
I can open the form & query at the same time then copy & paste this data
across but this is tedious to say the least anyone any Ideas as to hw I can
automate this???
I thought of using dlookup but don't seem to be able to figure out how to
sum on a group using this function?

Here is the query

THIS IS MASTER QUERY used in sum query

SELECT m_wml.pstk, m_wml.line, m_wml.mc, m_wml.setup, m_wml.runt,
m_wml.ml_trans, m_wml.ml_runper, m_wmach.machrate,
([setup]/Forms!frmquoteform!QTY)+([runt]/[ml_runper]) AS [Unit Time], ([Unit
Time]*[machrate])/100 AS [Unit Cost]
FROM m_wml INNER JOIN m_wmach ON m_wml.mc = m_wmach.machcode
GROUP BY m_wml.pstk, m_wml.line, m_wml.mc, m_wml.setup, m_wml.runt,
m_wml.ml_trans, m_wml.ml_runper, m_wmach.machrate
HAVING (((m_wml.pstk)=[Forms]![frmquoteform]![txtPSTK]));



THIS IS SUM QUERY that I copy the data from & paste it into the text bog on
the form.

SELECT DISTINCTROW QRY_Part_No_OPs.mc, Sum(QRY_Part_No_OPs.[Unit Time]) AS
[Sum Of Unit Time]
FROM QRY_Part_No_OPs
GROUP BY QRY_Part_No_OPs.mc
HAVING (((QRY_Part_No_OPs.mc)="FORM"));
Click to expand...
Click to expand...
 
Jedit said:
I have a query that sums time for various operations say FORM, PRESS, MILL.
From an old FOX-Pro database, this is fine.
The problem is that I want to fetch this summed data into a text box on a
form.
I can open the form & query at the same time then copy & paste this data
across but this is tedious to say the least anyone any Ideas as to hw I can
automate this???
I thought of using dlookup but don’t seem to be able to figure out how to
sum on a group using this function?

Here is the query

THIS IS MASTER QUERY used in sum query

SELECT m_wml.pstk, m_wml.line, m_wml.mc, m_wml.setup, m_wml.runt,
m_wml.ml_trans, m_wml.ml_runper, m_wmach.machrate,
([setup]/Forms!frmquoteform!QTY)+([runt]/[ml_runper]) AS [Unit Time], ([Unit
Time]*[machrate])/100 AS [Unit Cost]
FROM m_wml INNER JOIN m_wmach ON m_wml.mc = m_wmach.machcode
GROUP BY m_wml.pstk, m_wml.line, m_wml.mc, m_wml.setup, m_wml.runt,
m_wml.ml_trans, m_wml.ml_runper, m_wmach.machrate
HAVING (((m_wml.pstk)=[Forms]![frmquoteform]![txtPSTK]));



THIS IS SUM QUERY that I copy the data from & paste it into the text bog on
the form.

SELECT DISTINCTROW QRY_Part_No_OPs.mc, Sum(QRY_Part_No_OPs.[Unit Time]) AS
[Sum Of Unit Time]
FROM QRY_Part_No_OPs
GROUP BY QRY_Part_No_OPs.mc
HAVING (((QRY_Part_No_OPs.mc)="FORM"));
Click to expand...


Try using a DSum instead:

DSum("[Unit Time]", "QRY_Part_No_OPs", "mc="FORM")
 
yes, i think your syntax is wrong. try something like

DLookUp("[Sum Of Unit Time]","QRY_sum-fORM time","[mc]='FORM'")

hth


Jedit said:
Tried that but get an error saying that the object does not automation or
something? may be its the syntax any ideas.

DLookUp([QRY_sum-fORM time]![Sum Of Unit Time],[QRY_sum-fORM
time],[QRY_sum-fORM time]![mc]="FORM")

Above code is in the criteria field in the setvalue macro command and tries
to return the value from sum of unit time where the mc colum = FORM
or should i bind it to a text box then copy it over?

tina said:
have you tried running a DLookup() on your sum query, rather than on a
table?


Jedit said:
I have a query that sums time for various operations say FORM, PRESS, MILL.
From an old FOX-Pro database, this is fine.
The problem is that I want to fetch this summed data into a text box on a
form.
I can open the form & query at the same time then copy & paste this data
across but this is tedious to say the least anyone any Ideas as to hw
Click to expand...
I
can
automate this???
I thought of using dlookup but don't seem to be able to figure out how to
sum on a group using this function?

Here is the query

THIS IS MASTER QUERY used in sum query

SELECT m_wml.pstk, m_wml.line, m_wml.mc, m_wml.setup, m_wml.runt,
m_wml.ml_trans, m_wml.ml_runper, m_wmach.machrate,
([setup]/Forms!frmquoteform!QTY)+([runt]/[ml_runper]) AS [Unit Time], ([Unit
Time]*[machrate])/100 AS [Unit Cost]
FROM m_wml INNER JOIN m_wmach ON m_wml.mc = m_wmach.machcode
GROUP BY m_wml.pstk, m_wml.line, m_wml.mc, m_wml.setup, m_wml.runt,
m_wml.ml_trans, m_wml.ml_runper, m_wmach.machrate
HAVING (((m_wml.pstk)=[Forms]![frmquoteform]![txtPSTK]));



THIS IS SUM QUERY that I copy the data from & paste it into the text
Click to expand...
bog
on
the form.

SELECT DISTINCTROW QRY_Part_No_OPs.mc, Sum(QRY_Part_No_OPs.[Unit Time]) AS
[Sum Of Unit Time]
FROM QRY_Part_No_OPs
GROUP BY QRY_Part_No_OPs.mc
HAVING (((QRY_Part_No_OPs.mc)="FORM"));
Click to expand...
Click to expand...
Click to expand...
 
Cool both work just fine
My problem now is that i need to use wild cards etc

DSum("[Unit Cost]","QRY_Part_No_OPs","[mc]='M*'")

where I want to sum on everything begining with M, i THAUGHT i COULD JUST
DROP IN * BUT THIS RETURNS NOO DATA?

eg
MILL
MILLC
MTURNC
MTURN
MGENERAL
MSUBCO

Marshall Barton said:
Jedit said:
I have a query that sums time for various operations say FORM, PRESS, MILL.
From an old FOX-Pro database, this is fine.
The problem is that I want to fetch this summed data into a text box on a
form.
I can open the form & query at the same time then copy & paste this data
across but this is tedious to say the least anyone any Ideas as to hw I can
automate this???
I thought of using dlookup but don’t seem to be able to figure out how to
sum on a group using this function?

Here is the query

THIS IS MASTER QUERY used in sum query

SELECT m_wml.pstk, m_wml.line, m_wml.mc, m_wml.setup, m_wml.runt,
m_wml.ml_trans, m_wml.ml_runper, m_wmach.machrate,
([setup]/Forms!frmquoteform!QTY)+([runt]/[ml_runper]) AS [Unit Time], ([Unit
Time]*[machrate])/100 AS [Unit Cost]
FROM m_wml INNER JOIN m_wmach ON m_wml.mc = m_wmach.machcode
GROUP BY m_wml.pstk, m_wml.line, m_wml.mc, m_wml.setup, m_wml.runt,
m_wml.ml_trans, m_wml.ml_runper, m_wmach.machrate
HAVING (((m_wml.pstk)=[Forms]![frmquoteform]![txtPSTK]));



THIS IS SUM QUERY that I copy the data from & paste it into the text bog on
the form.

SELECT DISTINCTROW QRY_Part_No_OPs.mc, Sum(QRY_Part_No_OPs.[Unit Time]) AS
[Sum Of Unit Time]
FROM QRY_Part_No_OPs
GROUP BY QRY_Part_No_OPs.mc
HAVING (((QRY_Part_No_OPs.mc)="FORM"));
Click to expand...


Try using a DSum instead:

DSum("[Unit Time]", "QRY_Part_No_OPs", "mc="FORM")
Click to expand...
 
Jedit said:
Cool both work just fine
My problem now is that i need to use wild cards etc

DSum("[Unit Cost]","QRY_Part_No_OPs","[mc]='M*'")

where I want to sum on everything begining with M, i THAUGHT i COULD JUST
DROP IN * BUT THIS RETURNS NOO DATA?

eg
MILL
MILLC
MTURNC
MTURN
MGENERAL
MSUBCO
Click to expand...


WIldcards require the use of the LIKE operator.

DSum("[Unit Cost]","QRY_Part_No_OPs","[mc] Like 'M*'")
 
Back
Top