By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,462 Members | 799 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,462 IT Pros & Developers. It's quick & easy.

updating stock level from transaction table

P: n/a
I have a 'master' stock list with current stock levels (integer) and
another table that contains transactions (i.e. item#, #items added/
removed, date, user etc). What I need is an update query that runs down
the transaction table and modifies the 'master' stock level
accordingly. I've created an 'update query' and put in the
[master.stock] field. In the 'update to' slot I've got [master]![stock]
+ [transaction]![#items]

Running the query comes up with the message 'updating n records' but it
doesn't actually do it )-;

Any ideas?

Simon

Nov 13 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
<si**********@kingshc.nhs.uk> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...
I have a 'master' stock list with current stock levels (integer) and
another table that contains transactions (i.e. item#, #items added/
removed, date, user etc). What I need is an update query that runs down
the transaction table and modifies the 'master' stock level
accordingly. I've created an 'update query' and put in the
[master.stock] field. In the 'update to' slot I've got [master]![stock]
+ [transaction]![#items]

Running the query comes up with the message 'updating n records' but it
doesn't actually do it )-;

Any ideas?

Simon

Post the SQL for the query.
Nov 13 '05 #2

P: n/a
Will do, when my colleague who is designing the db comes back in
tommorrow )-; Thanks for the prompt reply!

Simon

Nov 13 '05 #3

P: n/a
OK, here is the SQL for the update query;

UPDATE Trans INNER JOIN [Items List] ON Trans.[Ref#] = [Items
List].[Ref#] SET [Items List].[In Stock] = [Items List]![In
Stock]+[Trans]![Quantity]
WHERE (((Trans.Updated)=No));

It was created with the 'expression builder' in the 'update to' cell of
the QBE grid so I am assuming the format of the field names etc is
correct (-; The expression builder window just has;

[Items List]![In Stock]+[Trans]![Quantity]

As [In Stock] and [Quantity] are both integers I don't see why I can't
just add them together )-:

Any ideas / suggestions gratefully rec'd.

Simon Harris
P.S.
I was planning on updating the [Updated] field to 'Yes' at the same
time but I thought I'd cross one bridge at a time!

Nov 13 '05 #4

P: n/a
<si**********@kingshc.nhs.uk> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
OK, here is the SQL for the update query;

UPDATE Trans INNER JOIN [Items List] ON Trans.[Ref#] = [Items
List].[Ref#] SET [Items List].[In Stock] = [Items List]![In
Stock]+[Trans]![Quantity]
WHERE (((Trans.Updated)=No));

It was created with the 'expression builder' in the 'update to' cell of
the QBE grid so I am assuming the format of the field names etc is
correct (-; The expression builder window just has;

[Items List]![In Stock]+[Trans]![Quantity]

As [In Stock] and [Quantity] are both integers I don't see why I can't
just add them together )-:

Any ideas / suggestions gratefully rec'd.

Simon Harris
P.S.
I was planning on updating the [Updated] field to 'Yes' at the same
time but I thought I'd cross one bridge at a time!

This query should work if the tables are set up properly. I might expect:
[Items List].Ref# to have a unique index (possibly primary key)
Trans.Ref# would be indexed - probably allowing duplicates
The [In Stock] and [Quantity] fields would be required so that you can't
have null values in there to mess up the addition.

Does this help you to make the query updateable?

Nov 13 '05 #5

P: n/a
"Justin Hoffman" <j@b.com> wrote in message
news:d6**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
<si**********@kingshc.nhs.uk> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
OK, here is the SQL for the update query;

UPDATE Trans INNER JOIN [Items List] ON Trans.[Ref#] = [Items
List].[Ref#] SET [Items List].[In Stock] = [Items List]![In
Stock]+[Trans]![Quantity]
WHERE (((Trans.Updated)=No));

It was created with the 'expression builder' in the 'update to' cell of
the QBE grid so I am assuming the format of the field names etc is
correct (-; The expression builder window just has;

[Items List]![In Stock]+[Trans]![Quantity]

As [In Stock] and [Quantity] are both integers I don't see why I can't
just add them together )-:

Any ideas / suggestions gratefully rec'd.

Simon Harris
P.S.
I was planning on updating the [Updated] field to 'Yes' at the same
time but I thought I'd cross one bridge at a time!

This query should work if the tables are set up properly. I might expect:
[Items List].Ref# to have a unique index (possibly primary key)
Trans.Ref# would be indexed - probably allowing duplicates
The [In Stock] and [Quantity] fields would be required so that you can't
have null values in there to mess up the addition.

Does this help you to make the query updateable?


If it's not too late - I would probably be a bit more cautious than this.
Ater all it doesn't leave you much of an audit trail, does it? Is there any
way you could create a temp table from an aggregate query of the Trans
table - i.e. group by Ref# and sum the quantity. You could even add a
separate field in the [Items List] table for the summed trans quantities.
Only as a last step, when you are happy with the results, should you add the
two quantities.

PS You don't mention if this is a procedure to be carried out as a one-off
or will be automated and repeated.



Nov 13 '05 #6

P: n/a
Problem solved - thanks a lot! The fields are defined *exactly* as you
suggested (-: However, because the the data was imported from another
app, the [in stock] field was full of nulls. A quick update to '0' and
we are in business!
Another question now springs to mind (if that's OK with you). How to
filter the form I'm using to update the trans table to only show
records that *aren't* incorporated in the item_list table.
I.e. 'where [updated] = no'

TIA,

Simon

Nov 13 '05 #7

P: n/a
<si**********@kingshc.nhs.uk> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Problem solved - thanks a lot! The fields are defined *exactly* as you
suggested (-: However, because the the data was imported from another
app, the [in stock] field was full of nulls. A quick update to '0' and
we are in business!
Another question now springs to mind (if that's OK with you). How to
filter the form I'm using to update the trans table to only show
records that *aren't* incorporated in the item_list table.
I.e. 'where [updated] = no'

TIA,

Simon


If this is a normal bound form with the updated field displayed (perhaps
with a checkbox control), then can you not simply press the 'filter by form'
button, untick the box, and then apply the filter. Or did you mean
programmatically?
Nov 13 '05 #8

P: n/a
I guess I was thinking of doing it programmatically (i.e. invisibly to
the user). I didn't want to put the 'updated' field on the
'transaction' form where it might be 'unticked' accidentally causing
the amount to be updated more than once on the master table.

Nov 13 '05 #9

P: n/a

<si**********@kingshc.nhs.uk> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
I guess I was thinking of doing it programmatically (i.e. invisibly to
the user). I didn't want to put the 'updated' field on the
'transaction' form where it might be 'unticked' accidentally causing
the amount to be updated more than once on the master table.


You have a number of choices. This one opens the form pre-filtered:

Docmd.OpenForm "MyForm",acNormal,,"[Updated]=0"

But perhaps the form should always show these records, in which case you
would be better off basing the form on a query where you only have the
necessary records. This way, users can't decide to switch the filter off or
alter it to another set of criteria.
I understand the need to stop users doing silly things, but once you get
started on this you may need to make sure that they don't have access to the
database window, can't access standard toolbars and implement user-level
security. I don't know how far down that route you have gone.
Nov 13 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.