UPDATE QUERY HELP REQUIRED

V

vandy

Hi All,

I have a scenario where an item is reserved for a project when the item is
used up it has to be updated to a free status so that it can be used by other
projects.

With the help of the group i was able to update the table and move data of
free items to another table called rawmaterial deleting the corresponding
records from the source table. Right now i am trying to accomplish the same
task without appending or deleting records.

I want to check if hte qoh =0 . if it is i have a feild in tbltransaction
called status .

if qoh=0 than update status to Available and update project no to null.

this way i should be able to come up with a report listing items which are
available.

problem:

A msg stating cannot have aggregate function in where clause
(sum[units]*[type])=0)

UPDATE tblprojects RIGHT JOIN (tblitems INNER JOIN tbltransactions ON
tblitems.ItemID = tbltransactions.TranItemID) ON tblprojects.ProjectID =
tbltransactions.PID SET tblprojects.Pno = Null, tbltransactions.status =
"Available"
WHERE (((Sum([UNITS]*[TYPE]))=0));

How to update the status as available and pno as null for criteria qoh=0

what does the message mean and how else can i query to get the desired
results.

thanks for you help in advance.
 
M

Michel Walsh

The SUM has to be performed over all the records of the whole table,
or only over the records relative to the actual project,
or only over the records relative to the same unitID,
or something else?

[unit] and [type] are from the same table, or from different tables?


Assuming unit and type come from the same table, and that you SUM over the
records related to the same unitID,

change the where clause to something looking like:


WHERE 0= DSUM("unit*type", "tableNameWIthUnitAndType", "unitID=" & unitID )



Hoping it may help,
Vanderghast, Access MVP
 
V

vandy

Hi Michel,

Thanks for replying to my query again. Given below is the structure to my
table

Table Structure
tblprojects~~Table Name
pno – project nmber – primary key
pname- project name

tblitems~~ Table Name
Itemno - Item no – primary key
Prno - project number – primary key ( combination of both keys makes the
data unique)
Uom – Unit of measure
Cat_id – Cat id
Stock_no – Item stock no
Nuclear – Nuclear material or not
Selected – select flag



tbltransactions~~Table Name
transactionID – Autonumber
Itemno – Foreign key connecting tblitems
ItemID
DOT – Date of Transaction
Units – Qty
Location – Location stored
IssuedTo – Issued to person or job
Status- available or not

I have the units and type in the same table and i want to update satus to
available for all items were qoh=0

qoh is calculated as sum(units * type)


UPDATE tblprojects RIGHT JOIN (tblitems INNER JOIN tbltransactions ON
tblitems.ItemID=tbltransactions.TranItemID) ON
tblprojects.ProjectID=tbltransactions.PID SET tblprojects.Pno = Null,
tbltransactions.status = "available"
WHERE 0=DSUM("units*type","tbltransactions");


using this query access tells me that 0 rows are being updated. I tested it
for couple of items were issuing = receiving ie. qoh =0 but there is no
update of status or the pno does not get updated to null. what should i add
further for this to work.

thanks for your patience.




Michel Walsh said:
The SUM has to be performed over all the records of the whole table,
or only over the records relative to the actual project,
or only over the records relative to the same unitID,
or something else?

[unit] and [type] are from the same table, or from different tables?


Assuming unit and type come from the same table, and that you SUM over the
records related to the same unitID,

change the where clause to something looking like:


WHERE 0= DSUM("unit*type", "tableNameWIthUnitAndType", "unitID=" & unitID )



Hoping it may help,
Vanderghast, Access MVP



vandy said:
Hi All,

I have a scenario where an item is reserved for a project when the item is
used up it has to be updated to a free status so that it can be used by
other
projects.

With the help of the group i was able to update the table and move data of
free items to another table called rawmaterial deleting the corresponding
records from the source table. Right now i am trying to accomplish the
same
task without appending or deleting records.

I want to check if hte qoh =0 . if it is i have a feild in tbltransaction
called status .

if qoh=0 than update status to Available and update project no to null.

this way i should be able to come up with a report listing items which are
available.

problem:

A msg stating cannot have aggregate function in where clause
(sum[units]*[type])=0)

UPDATE tblprojects RIGHT JOIN (tblitems INNER JOIN tbltransactions ON
tblitems.ItemID = tbltransactions.TranItemID) ON tblprojects.ProjectID =
tbltransactions.PID SET tblprojects.Pno = Null, tbltransactions.status =
"Available"
WHERE (((Sum([UNITS]*[TYPE]))=0));

How to update the status as available and pno as null for criteria qoh=0

what does the message mean and how else can i query to get the desired
results.

thanks for you help in advance.
Click to expand...
Click to expand...
 
M

Michel Walsh

It seems your SUM has to occur over the same itemNo. Without its third
argument, DSum( ) occurs over all the records of the whole table.


Also, while I see a field called unit, I don't see one called type, in
tblTransactions


Try:

WHERE 0=DSUM("units*type","tbltransactions", "itemNo=" & itemNo );



The sum then occurs only over all the records in tblTransactions having a
specified value for itemNo.



Hoping it may help,
Vanderghast, Access MVP


vandy said:
Hi Michel,

Thanks for replying to my query again. Given below is the structure to my
table

Table Structure
tblprojects~~Table Name
pno - project nmber - primary key
pname- project name

tblitems~~ Table Name
Itemno - Item no - primary key
Prno - project number - primary key ( combination of both keys makes
the
data unique)
Uom - Unit of measure
Cat_id - Cat id
Stock_no - Item stock no
Nuclear - Nuclear material or not
Selected - select flag



tbltransactions~~Table Name
transactionID - Autonumber
Itemno - Foreign key connecting tblitems
ItemID
DOT - Date of Transaction
Units - Qty
Location - Location stored
IssuedTo - Issued to person or job
Status- available or not

I have the units and type in the same table and i want to update satus to
available for all items were qoh=0

qoh is calculated as sum(units * type)


UPDATE tblprojects RIGHT JOIN (tblitems INNER JOIN tbltransactions ON
tblitems.ItemID=tbltransactions.TranItemID) ON
tblprojects.ProjectID=tbltransactions.PID SET tblprojects.Pno = Null,
tbltransactions.status = "available"
WHERE 0=DSUM("units*type","tbltransactions");


using this query access tells me that 0 rows are being updated. I tested
it
for couple of items were issuing = receiving ie. qoh =0 but there is no
update of status or the pno does not get updated to null. what should i
add
further for this to work.

thanks for your patience.




Michel Walsh said:
The SUM has to be performed over all the records of the whole table,
or only over the records relative to the actual project,
or only over the records relative to the same unitID,
or something else?

[unit] and [type] are from the same table, or from different tables?


Assuming unit and type come from the same table, and that you SUM over
the
records related to the same unitID,

change the where clause to something looking like:


WHERE 0= DSUM("unit*type", "tableNameWIthUnitAndType", "unitID=" &
unitID )



Hoping it may help,
Vanderghast, Access MVP



vandy said:
Hi All,

I have a scenario where an item is reserved for a project when the item
is
used up it has to be updated to a free status so that it can be used by
other
projects.

With the help of the group i was able to update the table and move data
of
free items to another table called rawmaterial deleting the
corresponding
records from the source table. Right now i am trying to accomplish the
same
task without appending or deleting records.

I want to check if hte qoh =0 . if it is i have a feild in
tbltransaction
called status .

if qoh=0 than update status to Available and update project no to null.

this way i should be able to come up with a report listing items which
are
available.

problem:

A msg stating cannot have aggregate function in where clause
(sum[units]*[type])=0)

UPDATE tblprojects RIGHT JOIN (tblitems INNER JOIN tbltransactions ON
tblitems.ItemID = tbltransactions.TranItemID) ON tblprojects.ProjectID
=
tbltransactions.PID SET tblprojects.Pno = Null, tbltransactions.status
=
"Available"
WHERE (((Sum([UNITS]*[TYPE]))=0));

How to update the status as available and pno as null for criteria
qoh=0

what does the message mean and how else can i query to get the desired
results.

thanks for you help in advance.
Click to expand...
Click to expand...
Click to expand...
 
V

vandy

Hi Michel,

It worked it is updating the status with status "available" thanks for you
help once again. I need to recheck and test it with sample data.

thanks once again.


Michel Walsh said:
It seems your SUM has to occur over the same itemNo. Without its third
argument, DSum( ) occurs over all the records of the whole table.


Also, while I see a field called unit, I don't see one called type, in
tblTransactions


Try:

WHERE 0=DSUM("units*type","tbltransactions", "itemNo=" & itemNo );



The sum then occurs only over all the records in tblTransactions having a
specified value for itemNo.



Hoping it may help,
Vanderghast, Access MVP


vandy said:
Hi Michel,

Thanks for replying to my query again. Given below is the structure to my
table

Table Structure
tblprojects~~Table Name
pno - project nmber - primary key
pname- project name

tblitems~~ Table Name
Itemno - Item no - primary key
Prno - project number - primary key ( combination of both keys makes
the
data unique)
Uom - Unit of measure
Cat_id - Cat id
Stock_no - Item stock no
Nuclear - Nuclear material or not
Selected - select flag



tbltransactions~~Table Name
transactionID - Autonumber
Itemno - Foreign key connecting tblitems
ItemID
DOT - Date of Transaction
Units - Qty
Location - Location stored
IssuedTo - Issued to person or job
Status- available or not

I have the units and type in the same table and i want to update satus to
available for all items were qoh=0

qoh is calculated as sum(units * type)


UPDATE tblprojects RIGHT JOIN (tblitems INNER JOIN tbltransactions ON
tblitems.ItemID=tbltransactions.TranItemID) ON
tblprojects.ProjectID=tbltransactions.PID SET tblprojects.Pno = Null,
tbltransactions.status = "available"
WHERE 0=DSUM("units*type","tbltransactions");


using this query access tells me that 0 rows are being updated. I tested
it
for couple of items were issuing = receiving ie. qoh =0 but there is no
update of status or the pno does not get updated to null. what should i
add
further for this to work.

thanks for your patience.




Michel Walsh said:
The SUM has to be performed over all the records of the whole table,
or only over the records relative to the actual project,
or only over the records relative to the same unitID,
or something else?

[unit] and [type] are from the same table, or from different tables?


Assuming unit and type come from the same table, and that you SUM over
the
records related to the same unitID,

change the where clause to something looking like:


WHERE 0= DSUM("unit*type", "tableNameWIthUnitAndType", "unitID=" &
unitID )



Hoping it may help,
Vanderghast, Access MVP



Hi All,

I have a scenario where an item is reserved for a project when the item
is
used up it has to be updated to a free status so that it can be used by
other
projects.

With the help of the group i was able to update the table and move data
of
free items to another table called rawmaterial deleting the
corresponding
records from the source table. Right now i am trying to accomplish the
same
task without appending or deleting records.

I want to check if hte qoh =0 . if it is i have a feild in
tbltransaction
called status .

if qoh=0 than update status to Available and update project no to null.

this way i should be able to come up with a report listing items which
are
available.

problem:

A msg stating cannot have aggregate function in where clause
(sum[units]*[type])=0)

UPDATE tblprojects RIGHT JOIN (tblitems INNER JOIN tbltransactions ON
tblitems.ItemID = tbltransactions.TranItemID) ON tblprojects.ProjectID
=
tbltransactions.PID SET tblprojects.Pno = Null, tbltransactions.status
=
"Available"
WHERE (((Sum([UNITS]*[TYPE]))=0));

How to update the status as available and pno as null for criteria
qoh=0

what does the message mean and how else can i query to get the desired
results.

thanks for you help in advance.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

check box query 1
cascading delete coding help required 20
VBA works in orignal but fails in copy of database 1
search form not giving desired results 2
Query Help 5
update query vs make query 5
Moving data into a new table once selected from a form 9
Update Query Help 2

Top