Double criteria in one field

My company has 18 locations and I want to make 18 reports to control there
inventory to the exact items that is in each store. My idea behind this was
in tables to make a column named Store #. Then in that column put the store
numbers which have that item. For example if three of my locations had “X�
item I would put 1, 4, 6 to say that those are the stores that have the “X�
item. Then in the design view in quires make an expression that states in my
Store # column every item that has a 1 will be pulled and put on store #1’s
report.
When I went and tried this I put as my expression “1� in the store # column
in quires in design view, and then tested a few by putting 1’s in a bunch of
the fields. It then worked perfectly. The downfall to what I am trying to do
is when I put more than one store number in the field, because it then does
not pull that item. I was wondering if there was a different expression I
could use or if I would have to enter the numbers differently in the fields.
The version that i am using is Access 2003

Although Access will allow you to put anything into a text field (e.g., "1,
4, 6"), that doesn't mean doing so is a good idea. A well-normalized
database table will follow the "rule" of "one fact, one field" ... and your
example has three facts in one field.

An alternate approach would be to create a new table (this is an example of
how Access is a relational database). That new table would help you keep
track of which store(s) have which item(s). The records in that table would
consist of a StoreID (from your Store table) and a InventoryID (from your
Inventory table).

Each record would represent a valid Store/Item pair. If you have a store
with no inventory, that store (ID) would have NO rows. If you have an
Inventory item that is in 18 stores, that item would be in 18 rows.

By the way, there is no need to create 18 reports ... you can create a
single report and use a parameter query or a prompt to identify WHICH store
you want to run the report for.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Thibodeau" wrote in message
...
My company has 18 locations and I want to make 18 reports to control there
inventory to the exact items that is in each store. My idea behind this
was
in tables to make a column named Store #. Then in that column put the
store
numbers which have that item. For example if three of my locations had "X"
item I would put 1, 4, 6 to say that those are the stores that have the
"X"
item. Then in the design view in quires make an expression that states in
my
Store # column every item that has a 1 will be pulled and put on store #1's
report.
When I went and tried this I put as my expression "1" in the store #
column
in quires in design view, and then tested a few by putting 1's in a bunch
of
the fields. It then worked perfectly. The downfall to what I am trying to
do
is when I put more than one store number in the field, because it then
does
not pull that item. I was wondering if there was a different expression I
could use or if I would have to enter the numbers differently in the
fields.
The version that i am using is Access 2003

"Jeff Boyce" wrote:

Although Access will allow you to put anything into a text field (e.g., "1,
4, 6"), that doesn't mean doing so is a good idea.

Some design flaw are so fundamental they have a name and when they have more
than one name you've gotta worry g. Names for this particular flaw include
'NFNF' (Not First Normal Form), '1NF violation' and 'Access multivalue field'
(- shurely some mistake - Ed g?)

Jamie.

--

Quit calling me shurely..

"Jamie Collins" wrote:
"Jeff Boyce" wrote:

Although Access will allow you to put anything into a text field (e.g.,
"1,
4, 6"), that doesn't mean doing so is a good idea.

Some design flaw are so fundamental they have a name and when they have
more
than one name you've gotta worry g. Names for this particular flaw
include
'NFNF' (Not First Normal Form), '1NF violation' and 'Access multivalue
field'
(- shurely some mistake - Ed g?)

Jamie.

--

I made 18 columns, one for each location that there are and then I copied an
existing report so I could have the lay out. Then went into query and put all
new 18 columns into the design then put an expression (“1� Or “2� Or “3� Or
“4� Or “N� Or “B� Or “D�) into my first new column. When I went to open the
report the expression worked but in the report that I copied off (which has
the same query) the two old columns were affected but the expression as well.
Is there is solution for fixing this.
"Jeff Boyce" wrote:

Although Access will allow you to put anything into a text field (e.g., "1,
4, 6"), that doesn't mean doing so is a good idea. A well-normalized
database table will follow the "rule" of "one fact, one field" ... and your
example has three facts in one field.

An alternate approach would be to create a new table (this is an example of
how Access is a relational database). That new table would help you keep
track of which store(s) have which item(s). The records in that table would
consist of a StoreID (from your Store table) and a InventoryID (from your
Inventory table).

Each record would represent a valid Store/Item pair. If you have a store
with no inventory, that store (ID) would have NO rows. If you have an
Inventory item that is in 18 stores, that item would be in 18 rows.

By the way, there is no need to create 18 reports ... you can create a
single report and use a parameter query or a prompt to identify WHICH store
you want to run the report for.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Thibodeau" wrote in message
...
My company has 18 locations and I want to make 18 reports to control there
inventory to the exact items that is in each store. My idea behind this
was
in tables to make a column named Store #. Then in that column put the
store
numbers which have that item. For example if three of my locations had "X"
item I would put 1, 4, 6 to say that those are the stores that have the
"X"
item. Then in the design view in quires make an expression that states in
my
Store # column every item that has a 1 will be pulled and put on store #1's
report.
When I went and tried this I put as my expression "1" in the store #
column
in quires in design view, and then tested a few by putting 1's in a bunch
of
the fields. It then worked perfectly. The downfall to what I am trying to
do
is when I put more than one store number in the field, because it then
does
not pull that item. I was wondering if there was a different expression I
could use or if I would have to enter the numbers differently in the
fields.
The version that i am using is Access 2003

Please see the discussion in this thread. From your description, you have
created a spreadsheet, not a well-normalized database table.

If you want to use spreadsheet functions, consider migrating your data to
Excel.

If you want to use Access' features/functions, first normalize your data.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Thibodeau" wrote in message
...
I made 18 columns, one for each location that there are and then I copied
an
existing report so I could have the lay out. Then went into query and put
all
new 18 columns into the design then put an expression ("1" Or "2" Or "3"
Or
"4" Or "N" Or "B" Or "D") into my first new column. When I went to open
the
report the expression worked but in the report that I copied off (which
has
the same query) the two old columns were affected but the expression as
well.
Is there is solution for fixing this.
"Jeff Boyce" wrote:

Although Access will allow you to put anything into a text field (e.g.,
"1,
4, 6"), that doesn't mean doing so is a good idea. A well-normalized
database table will follow the "rule" of "one fact, one field" ... and
your
example has three facts in one field.

An alternate approach would be to create a new table (this is an example
of
how Access is a relational database). That new table would help you keep
track of which store(s) have which item(s). The records in that table
would
consist of a StoreID (from your Store table) and a InventoryID (from your
Inventory table).

Each record would represent a valid Store/Item pair. If you have a store
with no inventory, that store (ID) would have NO rows. If you have an
Inventory item that is in 18 stores, that item would be in 18 rows.

By the way, there is no need to create 18 reports ... you can create a
single report and use a parameter query or a prompt to identify WHICH
store
you want to run the report for.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Thibodeau" wrote in message
...
My company has 18 locations and I want to make 18 reports to control
there
inventory to the exact items that is in each store. My idea behind this
was
in tables to make a column named Store #. Then in that column put the
store
numbers which have that item. For example if three of my locations had
"X"
item I would put 1, 4, 6 to say that those are the stores that have the
"X"
item. Then in the design view in quires make an expression that states
in
my
Store # column every item that has a 1 will be pulled and put on store
#1's
report.
When I went and tried this I put as my expression "1" in the store #
column
in quires in design view, and then tested a few by putting 1's in a
bunch
of
the fields. It then worked perfectly. The downfall to what I am trying
to
do
is when I put more than one store number in the field, because it then
does
not pull that item. I was wondering if there was a different
expression I
could use or if I would have to enter the numbers differently in the
fields.
The version that i am using is Access 2003