Restock Query needs help!

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

Guest

I am not too familiar with update queries but I think I'm close.
I have 2 tables: ToolData and ToolCribSignOut, each related through the
ToolCribDesignation (TCD).
My goal is to find records from whenever I run the query (now) back 3
months, to sum up the sign out quantities by TCD, to divide this number by
90, and then multiply by 1.2.
This number then needs to be saved into a column called MinQuantity in
ToolData.

So far I have written this but there is a problem with the syntax:

UPDATE ToolData AS T INNER JOIN ToolCribSignOut AS S ON
T.ToolCribDesignation=S.ToolCribDesignation SET T.MinQuantity = sum
S.SignOutQuantity / 90 * 1.2

WHERE S.[DateModified] = mm (now -3) FROM ToolCribSignOut;


Can anyone help please?!
 
InventoryQueryGuy said:
I am not too familiar with update queries but I think I'm close.
I have 2 tables: ToolData and ToolCribSignOut, each related through the
ToolCribDesignation (TCD).
My goal is to find records from whenever I run the query (now) back 3
months, to sum up the sign out quantities by TCD, to divide this number by
90, and then multiply by 1.2.
This number then needs to be saved into a column called MinQuantity in
ToolData.

So far I have written this but there is a problem with the syntax:

UPDATE ToolData AS T INNER JOIN ToolCribSignOut AS S ON
T.ToolCribDesignation=S.ToolCribDesignation SET T.MinQuantity = sum
S.SignOutQuantity / 90 * 1.2

WHERE S.[DateModified] = mm (now -3) FROM ToolCribSignOut;
Click to expand...
you cannot use any total/aggregation in an update query,
so you are typically left with one slow option --
a domain function such as the DSum() function.

UPDATE ToolData AS T
SET T.MinQuantity =
(
DSum("SignOutQuantity","ToolCribSignOut",
"[ToolCribDesignation] = ' " & T.ToolCribDesignation & " '
AND
[DateModified] >=
DateSerial(Year(Date()), Month(Date()) -3, Day(Date()))")
/90
)
* 1.2;

if ToolCribDesignation is type number,
remove the single quote delimiters...

"[ToolCribDesignation] = " & T.ToolCribDesignation & "

if they are type text,
remove the spaces that were used to make clearer...

"[ToolCribDesignation] = '" & T.ToolCribDesignation & "'

the solution above assumed there would be no [DateModified]
for a date "in the future"
 
Gary, you're great!

Thank you very much. Works like a charm, at least I think!
How is it that the number is rounded every time, its what I was hoping for
but I'm just curious?


YEAH!!! you just made my day!


Gary Walter said:
InventoryQueryGuy said:
I am not too familiar with update queries but I think I'm close.
I have 2 tables: ToolData and ToolCribSignOut, each related through the
ToolCribDesignation (TCD).
My goal is to find records from whenever I run the query (now) back 3
months, to sum up the sign out quantities by TCD, to divide this number by
90, and then multiply by 1.2.
This number then needs to be saved into a column called MinQuantity in
ToolData.

So far I have written this but there is a problem with the syntax:

UPDATE ToolData AS T INNER JOIN ToolCribSignOut AS S ON
T.ToolCribDesignation=S.ToolCribDesignation SET T.MinQuantity = sum
S.SignOutQuantity / 90 * 1.2

WHERE S.[DateModified] = mm (now -3) FROM ToolCribSignOut;
Click to expand...
you cannot use any total/aggregation in an update query,
so you are typically left with one slow option --
a domain function such as the DSum() function.

UPDATE ToolData AS T
SET T.MinQuantity =
(
DSum("SignOutQuantity","ToolCribSignOut",
"[ToolCribDesignation] = ' " & T.ToolCribDesignation & " '
AND
[DateModified] >=
DateSerial(Year(Date()), Month(Date()) -3, Day(Date()))")
/90
)
* 1.2;

if ToolCribDesignation is type number,
remove the single quote delimiters...

"[ToolCribDesignation] = " & T.ToolCribDesignation & "

if they are type text,
remove the spaces that were used to make clearer...

"[ToolCribDesignation] = '" & T.ToolCribDesignation & "'

the solution above assumed there would be no [DateModified]
for a date "in the future"
Click to expand...
 
If you copied and pasted the SQL that
I gave you, it was because of the magic "*"
that I used in the formula. 8-)

Truly, I don't know.....

best guess is that we are updating
a type Long column and the final
float result is being "cast" to Long.

Is that what you meant by "rounding?"


InventoryQueryGuy said:
Thank you very much. Works like a charm, at least I think!
How is it that the number is rounded every time, its what I was hoping for
but I'm just curious?


YEAH!!! you just made my day!


Gary Walter said:
InventoryQueryGuy said:
I am not too familiar with update queries but I think I'm close.
I have 2 tables: ToolData and ToolCribSignOut, each related through the
ToolCribDesignation (TCD).
My goal is to find records from whenever I run the query (now) back 3
months, to sum up the sign out quantities by TCD, to divide this number by
90, and then multiply by 1.2.
This number then needs to be saved into a column called MinQuantity in
ToolData.

So far I have written this but there is a problem with the syntax:

UPDATE ToolData AS T INNER JOIN ToolCribSignOut AS S ON
T.ToolCribDesignation=S.ToolCribDesignation SET T.MinQuantity = sum
S.SignOutQuantity / 90 * 1.2

WHERE S.[DateModified] = mm (now -3) FROM ToolCribSignOut;
Click to expand...
you cannot use any total/aggregation in an update query,
so you are typically left with one slow option --
a domain function such as the DSum() function.

UPDATE ToolData AS T
SET T.MinQuantity =
(
DSum("SignOutQuantity","ToolCribSignOut",
"[ToolCribDesignation] = ' " & T.ToolCribDesignation & " '
AND
[DateModified] >=
DateSerial(Year(Date()), Month(Date()) -3, Day(Date()))")
/90
)
* 1.2;

if ToolCribDesignation is type number,
remove the single quote delimiters...

"[ToolCribDesignation] = " & T.ToolCribDesignation & "

if they are type text,
remove the spaces that were used to make clearer...

"[ToolCribDesignation] = '" & T.ToolCribDesignation & "'

the solution above assumed there would be no [DateModified]
for a date "in the future"
Click to expand...
Click to expand...
 
I like magic!! That is what I mean though, Gary. Thanks again.

If you have a chance could you check out the post called: If, then, Insert
date, else.

I have posted another question that when answered will complete steps 2 of 3
in my restock routine.


Cheers.


Gary Walter said:
If you copied and pasted the SQL that
I gave you, it was because of the magic "*"
that I used in the formula. 8-)

Truly, I don't know.....

best guess is that we are updating
a type Long column and the final
float result is being "cast" to Long.

Is that what you meant by "rounding?"


InventoryQueryGuy said:
Thank you very much. Works like a charm, at least I think!
How is it that the number is rounded every time, its what I was hoping for
but I'm just curious?


YEAH!!! you just made my day!


Gary Walter said:
:
I am not too familiar with update queries but I think I'm close.
I have 2 tables: ToolData and ToolCribSignOut, each related through the
ToolCribDesignation (TCD).
My goal is to find records from whenever I run the query (now) back 3
months, to sum up the sign out quantities by TCD, to divide this number by
90, and then multiply by 1.2.
This number then needs to be saved into a column called MinQuantity in
ToolData.

So far I have written this but there is a problem with the syntax:

UPDATE ToolData AS T INNER JOIN ToolCribSignOut AS S ON
T.ToolCribDesignation=S.ToolCribDesignation SET T.MinQuantity = sum
S.SignOutQuantity / 90 * 1.2

WHERE S.[DateModified] = mm (now -3) FROM ToolCribSignOut;


you cannot use any total/aggregation in an update query,
so you are typically left with one slow option --
a domain function such as the DSum() function.

UPDATE ToolData AS T
SET T.MinQuantity =
(
DSum("SignOutQuantity","ToolCribSignOut",
"[ToolCribDesignation] = ' " & T.ToolCribDesignation & " '
AND
[DateModified] >=
DateSerial(Year(Date()), Month(Date()) -3, Day(Date()))")
/90
)
* 1.2;

if ToolCribDesignation is type number,
remove the single quote delimiters...

"[ToolCribDesignation] = " & T.ToolCribDesignation & "

if they are type text,
remove the spaces that were used to make clearer...

"[ToolCribDesignation] = '" & T.ToolCribDesignation & "'

the solution above assumed there would be no [DateModified]
for a date "in the future"
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

Can you spot the Syntax error?? 3
Help Please!! Putting together a Challenging Query 2
Add null value to date field. 4
Query not doing anything. 2
Update specific record based on max date and max time. 1
If, then, Insert date, else. 3
addition from one table to another. 1
update only using last record. 3

Back
Top