updating stock level from transaction table

[simon.harris]

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

[Justin Hoffman]

<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.

[simon.harris]

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

Simon

[simon.harris]

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!

[Justin Hoffman]

<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?

[Justin Hoffman]

"Justin Hoffman" <[e-mail]> 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.

[simon.harris]

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

[Justin Hoffman]

<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?

[simon.harris]

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.

[Justin Hoffman]

<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.