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

I can't even find a subject for my question

P: n/a
I have 5 fields (the number can grow) that represents the stock in different
places:
stock1, stock2, stock3, stock4, stock5
From a subform, I need to manipulate their values, depending on a rule
that is defined using an IF statement.
For example:
if parent!stores = 1 then
stock1=stock1+1
endif

Right now I'm using 5 IF's, one for each "stockX" field".
Since I have 5 "stockX" fields and the number can grow in
the future, I was wondering if there is another way to update
those fields using one IF statement.

From the example above, you can understand that "parent!store" holds
the value of the "stock" field I want to update. For example, if
parent!stores
is 1, I want to update stock1. If parent!stores is 2, I want to update
stock2.
I've tried several options, like:
"stock"&parent!stores = "stock"&parent!stores+1
but none of them works.

What's the right way of doing it ?
I'm using Access2000 with SQL server 2000.

Thanks!
Nov 12 '05 #1
Share this Question
Share on Google+
9 Replies

P: n/a
You've got what's referred to as a repeating group with your stock1, stock2,
etc., and they're a symptom of not having normalized your database.

Rather than having n fields on a single row, you should have n separate
rows, where the key for the "new" rows is whatever it is for the existing
row plus the number 1, 2, 3, etc., and you only have a single stock field.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Marius Kaizerman" <ma****@kaizerman.org> wrote in message
news:ne********************@lnews.actcom.co.il...
I have 5 fields (the number can grow) that represents the stock in different places:
stock1, stock2, stock3, stock4, stock5
From a subform, I need to manipulate their values, depending on a rule
that is defined using an IF statement.
For example:
if parent!stores = 1 then
stock1=stock1+1
endif

Right now I'm using 5 IF's, one for each "stockX" field".
Since I have 5 "stockX" fields and the number can grow in
the future, I was wondering if there is another way to update
those fields using one IF statement.

From the example above, you can understand that "parent!store" holds
the value of the "stock" field I want to update. For example, if
parent!stores
is 1, I want to update stock1. If parent!stores is 2, I want to update
stock2.
I've tried several options, like:
"stock"&parent!stores = "stock"&parent!stores+1
but none of them works.

What's the right way of doing it ?
I'm using Access2000 with SQL server 2000.

Thanks!

Nov 12 '05 #2

P: n/a
I don't really understand your solution.
These "stock" fields are columns in a table, which represents the stock
of a product in different places. The table looks like this:

productid
stock1
stock2
stock3
stock4
stock5

How can I use your solution to solve the problem ?

Thanks!

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:KH*******************@twister01.bloor.is.net. cable.rogers.com...
You've got what's referred to as a repeating group with your stock1, stock2, etc., and they're a symptom of not having normalized your database.

Rather than having n fields on a single row, you should have n separate
rows, where the key for the "new" rows is whatever it is for the existing
row plus the number 1, 2, 3, etc., and you only have a single stock field.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Marius Kaizerman" <ma****@kaizerman.org> wrote in message
news:ne********************@lnews.actcom.co.il...
I have 5 fields (the number can grow) that represents the stock in

different
places:
stock1, stock2, stock3, stock4, stock5
From a subform, I need to manipulate their values, depending on a rule
that is defined using an IF statement.
For example:
if parent!stores = 1 then
stock1=stock1+1
endif

Right now I'm using 5 IF's, one for each "stockX" field".
Since I have 5 "stockX" fields and the number can grow in
the future, I was wondering if there is another way to update
those fields using one IF statement.

From the example above, you can understand that "parent!store" holds
the value of the "stock" field I want to update. For example, if
parent!stores
is 1, I want to update stock1. If parent!stores is 2, I want to update
stock2.
I've tried several options, like:
"stock"&parent!stores = "stock"&parent!stores+1
but none of them works.

What's the right way of doing it ?
I'm using Access2000 with SQL server 2000.

Thanks!


Nov 12 '05 #3

P: n/a
Rather than what you have, you should have:

productid
storeid
stock

So that instead of one row

ABC 1 10 30 15 4

you'd have 5 rows

ABC 1 1
ABC 2 10
ABC 3 30
ABC 4 15
ABC 5 4

Then, instead of trying to update stock2 in the row for productid ABC, you'd
be updating stock in the row for productid ABC and storeid 2.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Marius Kaizerman" <ma****@kaizerman.org> wrote in message
news:ne********************@lnews.actcom.co.il...
I don't really understand your solution.
These "stock" fields are columns in a table, which represents the stock
of a product in different places. The table looks like this:

productid
stock1
stock2
stock3
stock4
stock5

How can I use your solution to solve the problem ?

Thanks!

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:KH*******************@twister01.bloor.is.net. cable.rogers.com...
You've got what's referred to as a repeating group with your stock1,

stock2,
etc., and they're a symptom of not having normalized your database.

Rather than having n fields on a single row, you should have n separate
rows, where the key for the "new" rows is whatever it is for the existing row plus the number 1, 2, 3, etc., and you only have a single stock field.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Marius Kaizerman" <ma****@kaizerman.org> wrote in message
news:ne********************@lnews.actcom.co.il...
I have 5 fields (the number can grow) that represents the stock in

different
places:
stock1, stock2, stock3, stock4, stock5
From a subform, I need to manipulate their values, depending on a rule
that is defined using an IF statement.
For example:
if parent!stores = 1 then
stock1=stock1+1
endif

Right now I'm using 5 IF's, one for each "stockX" field".
Since I have 5 "stockX" fields and the number can grow in
the future, I was wondering if there is another way to update
those fields using one IF statement.

From the example above, you can understand that "parent!store" holds
the value of the "stock" field I want to update. For example, if
parent!stores
is 1, I want to update stock1. If parent!stores is 2, I want to update
stock2.
I've tried several options, like:
"stock"&parent!stores = "stock"&parent!stores+1
but none of them works.

What's the right way of doing it ?
I'm using Access2000 with SQL server 2000.

Thanks!



Nov 12 '05 #4

P: n/a
productid
store
stock

productId store stock
AA 1 2
AA 2 100

one column for stock
many rows, one for each store

"Marius Kaizerman" <ma****@kaizerman.org> wrote in message news:<ne********************@lnews.actcom.co.il>.. .
I don't really understand your solution.
These "stock" fields are columns in a table, which represents the stock
of a product in different places. The table looks like this:

productid
stock1
stock2
stock3
stock4
stock5

How can I use your solution to solve the problem ?

Thanks!

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:KH*******************@twister01.bloor.is.net. cable.rogers.com...
You've got what's referred to as a repeating group with your stock1,

stock2,
etc., and they're a symptom of not having normalized your database.

Rather than having n fields on a single row, you should have n separate
rows, where the key for the "new" rows is whatever it is for the existing
row plus the number 1, 2, 3, etc., and you only have a single stock field.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Marius Kaizerman" <ma****@kaizerman.org> wrote in message
news:ne********************@lnews.actcom.co.il...
I have 5 fields (the number can grow) that represents the stock in different places:
stock1, stock2, stock3, stock4, stock5
From a subform, I need to manipulate their values, depending on a rule
that is defined using an IF statement.
For example:
if parent!stores = 1 then
stock1=stock1+1
endif

Right now I'm using 5 IF's, one for each "stockX" field".
Since I have 5 "stockX" fields and the number can grow in
the future, I was wondering if there is another way to update
those fields using one IF statement.

From the example above, you can understand that "parent!store" holds
the value of the "stock" field I want to update. For example, if
parent!stores
is 1, I want to update stock1. If parent!stores is 2, I want to update
stock2.
I've tried several options, like:
"stock"&parent!stores = "stock"&parent!stores+1
but none of them works.

What's the right way of doing it ?
I'm using Access2000 with SQL server 2000.

Thanks!


Nov 12 '05 #5

P: n/a
Ok, that's the way I did it in the beginning.
And then I've found out that I need to deal with triggers
and complicated queries for handling simple calculations and reports.
So I'm stuck with the way I have it now, I did too much, can't throw it
away.
What I need is a way to combine the "stock" with it's number, which is in
the "parent!stores" control. Can it be done ?

Thanks!

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:%m*********************@news01.bloor.is.net.c able.rogers.com...
Rather than what you have, you should have:

productid
storeid
stock

So that instead of one row

ABC 1 10 30 15 4

you'd have 5 rows

ABC 1 1
ABC 2 10
ABC 3 30
ABC 4 15
ABC 5 4

Then, instead of trying to update stock2 in the row for productid ABC, you'd be updating stock in the row for productid ABC and storeid 2.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Marius Kaizerman" <ma****@kaizerman.org> wrote in message
news:ne********************@lnews.actcom.co.il...
I don't really understand your solution.
These "stock" fields are columns in a table, which represents the stock
of a product in different places. The table looks like this:

productid
stock1
stock2
stock3
stock4
stock5

How can I use your solution to solve the problem ?

Thanks!

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:KH*******************@twister01.bloor.is.net. cable.rogers.com...
You've got what's referred to as a repeating group with your stock1,

stock2,
etc., and they're a symptom of not having normalized your database.

Rather than having n fields on a single row, you should have n separate rows, where the key for the "new" rows is whatever it is for the existing row plus the number 1, 2, 3, etc., and you only have a single stock field.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Marius Kaizerman" <ma****@kaizerman.org> wrote in message
news:ne********************@lnews.actcom.co.il...
> I have 5 fields (the number can grow) that represents the stock in
different
> places:
> stock1, stock2, stock3, stock4, stock5
> From a subform, I need to manipulate their values, depending on a rule > that is defined using an IF statement.
> For example:
> if parent!stores = 1 then
> stock1=stock1+1
> endif
>
> Right now I'm using 5 IF's, one for each "stockX" field".
> Since I have 5 "stockX" fields and the number can grow in
> the future, I was wondering if there is another way to update
> those fields using one IF statement.
>
> From the example above, you can understand that "parent!store" holds
> the value of the "stock" field I want to update. For example, if
> parent!stores
> is 1, I want to update stock1. If parent!stores is 2, I want to update > stock2.
> I've tried several options, like:
> "stock"&parent!stores = "stock"&parent!stores+1
> but none of them works.
>
> What's the right way of doing it ?
> I'm using Access2000 with SQL server 2000.
>
> Thanks!
>
>



Nov 12 '05 #6

P: n/a
Bri
Marius,

OK, if you must use the un-normalized table structure then you need to refer to
the field using the 'Index as String' method of refering to the field:

For example, if Me.Parent!stores is 1 then Me.Parent("stock" & Me.Parent!stores)
is the same as Me.Parent!stock1

HTH
Bri

Marius Kaizerman wrote:
Ok, that's the way I did it in the beginning.
And then I've found out that I need to deal with triggers
and complicated queries for handling simple calculations and reports.
So I'm stuck with the way I have it now, I did too much, can't throw it
away.
What I need is a way to combine the "stock" with it's number, which is in
the "parent!stores" control. Can it be done ?

Thanks!

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:%m*********************@news01.bloor.is.net.c able.rogers.com...
Rather than what you have, you should have:

productid
storeid
stock

So that instead of one row

ABC 1 10 30 15 4

you'd have 5 rows

ABC 1 1
ABC 2 10
ABC 3 30
ABC 4 15
ABC 5 4

Then, instead of trying to update stock2 in the row for productid ABC,


you'd
be updating stock in the row for productid ABC and storeid 2.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Marius Kaizerman" <ma****@kaizerman.org> wrote in message
news:ne********************@lnews.actcom.co.il.. .
I don't really understand your solution.
These "stock" fields are columns in a table, which represents the stock
of a product in different places. The table looks like this:

productid
stock1
stock2
stock3
stock4
stock5

How can I use your solution to solve the problem ?

Thanks!


Nov 12 '05 #7

P: n/a
Thanks for your reply!

There is a problem.
stock1, stock2, etc' are fields in a table.
From your example I can understand that you took "stock1" as a control
in the form itself.
What I need to do is this:
stockX=stockX+1 (depending on the value of the control [stores])
Where "X" is the number that exists in the control parent![stores].
So parent![stores] is a control, but stock1, stock2, etc' are fields in a
table.

Thanks!

"Bri" <no*@here.com> wrote in message
news:iQEIb.892050$9l5.866979@pd7tw2no...
Marius,

OK, if you must use the un-normalized table structure then you need to refer to the field using the 'Index as String' method of refering to the field:

For example, if Me.Parent!stores is 1 then Me.Parent("stock" & Me.Parent!stores) is the same as Me.Parent!stock1

HTH
Bri

Marius Kaizerman wrote:
Ok, that's the way I did it in the beginning.
And then I've found out that I need to deal with triggers
and complicated queries for handling simple calculations and reports.
So I'm stuck with the way I have it now, I did too much, can't throw it
away.
What I need is a way to combine the "stock" with it's number, which is in the "parent!stores" control. Can it be done ?

Thanks!

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:%m*********************@news01.bloor.is.net.c able.rogers.com...
Rather than what you have, you should have:

productid
storeid
stock

So that instead of one row

ABC 1 10 30 15 4

you'd have 5 rows

ABC 1 1
ABC 2 10
ABC 3 30
ABC 4 15
ABC 5 4

Then, instead of trying to update stock2 in the row for productid ABC,


you'd
be updating stock in the row for productid ABC and storeid 2.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Marius Kaizerman" <ma****@kaizerman.org> wrote in message
news:ne********************@lnews.actcom.co.il.. .

I don't really understand your solution.
These "stock" fields are columns in a table, which represents the stock
of a product in different places. The table looks like this:

productid
stock1
stock2
stock3
stock4
stock5

How can I use your solution to solve the problem ?

Thanks!

Nov 12 '05 #8

P: n/a
Well, I've used your example with docmd.runsql (for updating values on the
"stock" fields
instead of using "stockX=stockX+1") and it worked.

Thanks!!
"Bri" <no*@here.com> wrote in message
news:iQEIb.892050$9l5.866979@pd7tw2no...
Marius,

OK, if you must use the un-normalized table structure then you need to refer to the field using the 'Index as String' method of refering to the field:

For example, if Me.Parent!stores is 1 then Me.Parent("stock" & Me.Parent!stores) is the same as Me.Parent!stock1

HTH
Bri

Marius Kaizerman wrote:
Ok, that's the way I did it in the beginning.
And then I've found out that I need to deal with triggers
and complicated queries for handling simple calculations and reports.
So I'm stuck with the way I have it now, I did too much, can't throw it
away.
What I need is a way to combine the "stock" with it's number, which is in the "parent!stores" control. Can it be done ?

Thanks!

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:%m*********************@news01.bloor.is.net.c able.rogers.com...
Rather than what you have, you should have:

productid
storeid
stock

So that instead of one row

ABC 1 10 30 15 4

you'd have 5 rows

ABC 1 1
ABC 2 10
ABC 3 30
ABC 4 15
ABC 5 4

Then, instead of trying to update stock2 in the row for productid ABC,


you'd
be updating stock in the row for productid ABC and storeid 2.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Marius Kaizerman" <ma****@kaizerman.org> wrote in message
news:ne********************@lnews.actcom.co.il.. .

I don't really understand your solution.
These "stock" fields are columns in a table, which represents the stock
of a product in different places. The table looks like this:

productid
stock1
stock2
stock3
stock4
stock5

How can I use your solution to solve the problem ?

Thanks!

Nov 12 '05 #9

P: n/a
Bri
Marius,

Sorry, I thought that the StockX fields were represented by controls in the
parent Form. If they are in a separate Table then, yes, the only way to update
the table is via an Update query. You might want to look into the alternate
method of running a query though:

Dim db as DAO.Database
Set db = CurrentDB
db.Execute stSQL, dbFailOnError

This method has several advantages over the DoCmd.RunSQL stSQL method:
- Faster execution
- Error Trapping
- No confirmation Dialog Box to interupt the process (although you can suppress
these with a DoCmd.SetWarnings False before the DoCmd.RunSQL stSQL)
- After the query has run you can find out how many records were affected with
db.RecordsAffected

HTH
Bri

Marius Kaizerman wrote:
Well, I've used your example with docmd.runsql (for updating values on the
"stock" fields
instead of using "stockX=stockX+1") and it worked.

Thanks!!
"Bri" <no*@here.com> wrote in message
news:iQEIb.892050$9l5.866979@pd7tw2no...
Marius,

OK, if you must use the un-normalized table structure then you need to


refer to
the field using the 'Index as String' method of refering to the field:

For example, if Me.Parent!stores is 1 then Me.Parent("stock" &


Me.Parent!stores)
is the same as Me.Parent!stock1

HTH
Bri


Nov 12 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.