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! 9 1314
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!
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!
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!
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!
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! > >
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!
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!
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!
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: James |
last post by:
How can i programicly find out what operating system i am using? i have
tried using:
getenv("OS");
it works works on xp/2000 but it doesn't seem to work on windows95/98/ME.
im using ms visual...
|
by: lannsjo |
last post by:
I have a "userstuff" table like this:
| uid | user_id | typedb | dbid |
-----------------------------------
| 1 | 12 | boards | 3 |
| 2 | 13 | boards | 3 |
| 3 | ...
|
by: Victor Hadianto |
last post by:
Hi,
I have a simple XSD for example like this:
<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified" attributeFormDefault="qualified">...
|
by: skgolden |
last post by:
My husband and I own a small temporary labor company and deal with 4
major clients (A,B,C & D), each of which has about 2 dozen units in
our tristate area that we deal with (ie, Company A, units...
|
by: kkrizl |
last post by:
I'm having a very bizarre problem. I open up my table in datasheet
view, and I click on edit, find. In the Find and Replace dialog box, I
enter an address (123 main for example). If I set the...
|
by: Frank Wisniewski |
last post by:
Hi All,
This may be off subject but I have a feeling that some of you may be able to
give me input or point me in the right direction at least.
I have a number of people that want me to build...
|
by: =?Utf-8?B?QmVydGll?= |
last post by:
I submitted a question on Monday 5 February 2007 about the size of my file
increasing when I save a file from my designer and now I can't find it??
--
bertie
|
by: kj7ny |
last post by:
Is there a way that I can programmatically find the name of a method I
have created from within that method? I would like to be able to log
a message from within that method (def) and I would like...
|
by: C C++ C++ |
last post by:
Hi all, got this interview question please respond.
How can you quickly find the number of elements stored in a a) static
array b) dynamic array ?
Rgrds
MA
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |