I have a large parts list that I am going to enter into a table and through a
query I want to report what is low on inventory. My problem being that the
design view query only allows me so many slots to enter Criteria and or
Fields.

Huh? You only have one "onhand" field to look at, right?

Just put in a criteria under that field. Something like...

<5



--
Rick B



"Dustin" <(E-Mail Removed)> wrote in message
news:1ED17A60-46A7-4851-8524-(E-Mail Removed)...
>I have a large parts list that I am going to enter into a table and through
>a
> query I want to report what is low on inventory. My problem being that the
> design view query only allows me so many slots to enter Criteria and or
> Fields.

It seems odd that you would have a large number of fields to query. An
inventory database would generally only have two fields to query for a
reorder, a field that shows the current inventory amount, and a field showing
at what point to reorder an item.

"Dustin" wrote:

> I have a large parts list that I am going to enter into a table and through a
> query I want to report what is low on inventory. My problem being that the
> design view query only allows me so many slots to enter Criteria and or
> Fields.

I'm guessing he has a misdesigned table, where Column 1 is the inventory for
Product 1, Column 2 is the inventory for Product 2 and so on, but I guess we
won't know until he posts back.

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


"Rick B" <Anonymous> wrote in message
news:(E-Mail Removed)...
> Huh? You only have one "onhand" field to look at, right?
>
> Just put in a criteria under that field. Something like...
>
> <5
>
>
>
> --
> Rick B
>
>
>
> "Dustin" <(E-Mail Removed)> wrote in message
> news:1ED17A60-46A7-4851-8524-(E-Mail Removed)...
>>I have a large parts list that I am going to enter into a table and
>>through a
>> query I want to report what is low on inventory. My problem being that
>> the
>> design view query only allows me so many slots to enter Criteria and or
>> Fields.
>
>

As to your specific question: you can add more rows or columns to the query
grid (in Design view) by clicking the Insert menu item and choose either
Rows or Columns. However, I suspect (as others have) that you have a
database design issue as well.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L


"Dustin" <(E-Mail Removed)> wrote in message
news:1ED17A60-46A7-4851-8524-(E-Mail Removed)...
> I have a large parts list that I am going to enter into a table and
through a
> query I want to report what is low on inventory. My problem being that the
> design view query only allows me so many slots to enter Criteria and or
> Fields.

I am new at this, I will try and explain better. In my table I have a Part No
field and fields for each stores inventory of that part no. And in my query I
selected all these fields across the top. Then in the Critera I tried to put
all the part no's under the part no field, and all the low inventory criteria
under the store inventory fields. And I found that there seemed to be a limit
to how far I could go down in criteria and how far over I could select
fields.
I will try inserting more fields in design view like Roger said. But if
there is a better way for me to do this, I would appreciate the advice.
Thanks

"Roger Carlson" wrote:

> As to your specific question: you can add more rows or columns to the query
> grid (in Design view) by clicking the Insert menu item and choose either
> Rows or Columns. However, I suspect (as others have) that you have a
> database design issue as well.
>
> --
> --Roger Carlson
> MS Access MVP
> Access Database Samples: www.rogersaccesslibrary.com
> Want answers to your Access questions in your Email?
> Free subscription:
> http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L
>
>
> "Dustin" <(E-Mail Removed)> wrote in message
> news:1ED17A60-46A7-4851-8524-(E-Mail Removed)...
> > I have a large parts list that I am going to enter into a table and
> through a
> > query I want to report what is low on inventory. My problem being that the
> > design view query only allows me so many slots to enter Criteria and or
> > Fields.
>
>
>

If you're saying that you've got fields corresponding to store inventory so
that you've got a single row representing the inventory for all of your
stores for a single product, then your table isn't properly normalized.

I'm guessing you've got something like:

ProductId
InventoryForStore1
InventoryForStore2
InventoryForStore3
InventoryForStore4
.....

What do you do if you get another store?

You should have one row for each store. The aphorism is "Rows are cheap,
columns are expensive".

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


"Dustin" <(E-Mail Removed)> wrote in message
news:AEA12908-AFF3-4A68-AFC6-(E-Mail Removed)...
>I am new at this, I will try and explain better. In my table I have a Part
>No
> field and fields for each stores inventory of that part no. And in my
> query I
> selected all these fields across the top. Then in the Critera I tried to
> put
> all the part no's under the part no field, and all the low inventory
> criteria
> under the store inventory fields. And I found that there seemed to be a
> limit
> to how far I could go down in criteria and how far over I could select
> fields.
> I will try inserting more fields in design view like Roger said. But if
> there is a better way for me to do this, I would appreciate the advice.
> Thanks
>
> "Roger Carlson" wrote:
>
>> As to your specific question: you can add more rows or columns to the
>> query
>> grid (in Design view) by clicking the Insert menu item and choose either
>> Rows or Columns. However, I suspect (as others have) that you have a
>> database design issue as well.
>>
>> --
>> --Roger Carlson
>> MS Access MVP
>> Access Database Samples: www.rogersaccesslibrary.com
>> Want answers to your Access questions in your Email?
>> Free subscription:
>> http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L
>>
>>
>> "Dustin" <(E-Mail Removed)> wrote in message
>> news:1ED17A60-46A7-4851-8524-(E-Mail Removed)...
>> > I have a large parts list that I am going to enter into a table and
>> through a
>> > query I want to report what is low on inventory. My problem being that
>> > the
>> > design view query only allows me so many slots to enter Criteria and or
>> > Fields.
>>
>>
>>

Hi Douglas,

My table looks like this.

Part Id Store1 Inv Store2 Inv Store3 Inv ........ Store10 Inv
DR1240 28 30 35
DR1241 32 28 40

I was thinking my query in design view should look like this.

Part Id Store1 Inv Store2 Inv Store3 Inv ......
Store10 Inv

Criteria DR1240 >28 >28 >28
or DR1241 >35 >35 >35

I have found that I can add more rows and columns inorder to do this, but is
it the right way?

What do you think?

Thanks for replying


"Douglas J. Steele" wrote:

> If you're saying that you've got fields corresponding to store inventory so
> that you've got a single row representing the inventory for all of your
> stores for a single product, then your table isn't properly normalized.
>
> I'm guessing you've got something like:
>
> ProductId
> InventoryForStore1
> InventoryForStore2
> InventoryForStore3
> InventoryForStore4
> .....
>
> What do you do if you get another store?
>
> You should have one row for each store. The aphorism is "Rows are cheap,
> columns are expensive".
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Dustin" <(E-Mail Removed)> wrote in message
> news:AEA12908-AFF3-4A68-AFC6-(E-Mail Removed)...
> >I am new at this, I will try and explain better. In my table I have a Part
> >No
> > field and fields for each stores inventory of that part no. And in my
> > query I
> > selected all these fields across the top. Then in the Critera I tried to
> > put
> > all the part no's under the part no field, and all the low inventory
> > criteria
> > under the store inventory fields. And I found that there seemed to be a
> > limit
> > to how far I could go down in criteria and how far over I could select
> > fields.
> > I will try inserting more fields in design view like Roger said. But if
> > there is a better way for me to do this, I would appreciate the advice.
> > Thanks
> >
> > "Roger Carlson" wrote:
> >
> >> As to your specific question: you can add more rows or columns to the
> >> query
> >> grid (in Design view) by clicking the Insert menu item and choose either
> >> Rows or Columns. However, I suspect (as others have) that you have a
> >> database design issue as well.
> >>
> >> --
> >> --Roger Carlson
> >> MS Access MVP
> >> Access Database Samples: www.rogersaccesslibrary.com
> >> Want answers to your Access questions in your Email?
> >> Free subscription:
> >> http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L
> >>
> >>
> >> "Dustin" <(E-Mail Removed)> wrote in message
> >> news:1ED17A60-46A7-4851-8524-(E-Mail Removed)...
> >> > I have a large parts list that I am going to enter into a table and
> >> through a
> >> > query I want to report what is low on inventory. My problem being that
> >> > the
> >> > design view query only allows me so many slots to enter Criteria and or
> >> > Fields.
> >>
> >>
> >>
>
>
>

As I stated, your table design is sub-optimate.

You should have:

Part Id Store Id Inventory
DR1240 1 28
DR1240 2 30
DR1240 3 35
DR1241 1 32
DR1241 2 28
DR1241 3 40

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


"Dustin" <(E-Mail Removed)> wrote in message
news:0CC6E24B-2EE1-488C-8BD7-(E-Mail Removed)...
> Hi Douglas,
>
> My table looks like this.
>
> Part Id Store1 Inv Store2 Inv Store3 Inv ........ Store10 Inv
> DR1240 28 30 35
> DR1241 32 28 40
>
> I was thinking my query in design view should look like this.
>
> Part Id Store1 Inv Store2 Inv Store3 Inv ......
> Store10 Inv
>
> Criteria DR1240 >28 >28 >28
> or DR1241 >35 >35 >35
>
> I have found that I can add more rows and columns inorder to do this, but
> is
> it the right way?
>
> What do you think?
>
> Thanks for replying
>
>
> "Douglas J. Steele" wrote:
>
>> If you're saying that you've got fields corresponding to store inventory
>> so
>> that you've got a single row representing the inventory for all of your
>> stores for a single product, then your table isn't properly normalized.
>>
>> I'm guessing you've got something like:
>>
>> ProductId
>> InventoryForStore1
>> InventoryForStore2
>> InventoryForStore3
>> InventoryForStore4
>> .....
>>
>> What do you do if you get another store?
>>
>> You should have one row for each store. The aphorism is "Rows are cheap,
>> columns are expensive".
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no private e-mails, please)
>>
>>
>> "Dustin" <(E-Mail Removed)> wrote in message
>> news:AEA12908-AFF3-4A68-AFC6-(E-Mail Removed)...
>> >I am new at this, I will try and explain better. In my table I have a
>> >Part
>> >No
>> > field and fields for each stores inventory of that part no. And in my
>> > query I
>> > selected all these fields across the top. Then in the Critera I tried
>> > to
>> > put
>> > all the part no's under the part no field, and all the low inventory
>> > criteria
>> > under the store inventory fields. And I found that there seemed to be a
>> > limit
>> > to how far I could go down in criteria and how far over I could select
>> > fields.
>> > I will try inserting more fields in design view like Roger said. But if
>> > there is a better way for me to do this, I would appreciate the advice.
>> > Thanks
>> >
>> > "Roger Carlson" wrote:
>> >
>> >> As to your specific question: you can add more rows or columns to the
>> >> query
>> >> grid (in Design view) by clicking the Insert menu item and choose
>> >> either
>> >> Rows or Columns. However, I suspect (as others have) that you have a
>> >> database design issue as well.
>> >>
>> >> --
>> >> --Roger Carlson
>> >> MS Access MVP
>> >> Access Database Samples: www.rogersaccesslibrary.com
>> >> Want answers to your Access questions in your Email?
>> >> Free subscription:
>> >> http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L
>> >>
>> >>
>> >> "Dustin" <(E-Mail Removed)> wrote in message
>> >> news:1ED17A60-46A7-4851-8524-(E-Mail Removed)...
>> >> > I have a large parts list that I am going to enter into a table and
>> >> through a
>> >> > query I want to report what is low on inventory. My problem being
>> >> > that
>> >> > the
>> >> > design view query only allows me so many slots to enter Criteria and
>> >> > or
>> >> > Fields.
>> >>
>> >>
>> >>
>>
>>
>>

I see, then the part Id which is the PK should be turned to a text and have
autonumber field as my PK. Correct?

"Douglas J. Steele" wrote:

> As I stated, your table design is sub-optimate.
>
> You should have:
>
> Part Id Store Id Inventory
> DR1240 1 28
> DR1240 2 30
> DR1240 3 35
> DR1241 1 32
> DR1241 2 28
> DR1241 3 40
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Dustin" <(E-Mail Removed)> wrote in message
> news:0CC6E24B-2EE1-488C-8BD7-(E-Mail Removed)...
> > Hi Douglas,
> >
> > My table looks like this.
> >
> > Part Id Store1 Inv Store2 Inv Store3 Inv ........ Store10 Inv
> > DR1240 28 30 35
> > DR1241 32 28 40
> >
> > I was thinking my query in design view should look like this.
> >
> > Part Id Store1 Inv Store2 Inv Store3 Inv ......
> > Store10 Inv
> >
> > Criteria DR1240 >28 >28 >28
> > or DR1241 >35 >35 >35
> >
> > I have found that I can add more rows and columns inorder to do this, but
> > is
> > it the right way?
> >
> > What do you think?
> >
> > Thanks for replying
> >
> >
> > "Douglas J. Steele" wrote:
> >
> >> If you're saying that you've got fields corresponding to store inventory
> >> so
> >> that you've got a single row representing the inventory for all of your
> >> stores for a single product, then your table isn't properly normalized.
> >>
> >> I'm guessing you've got something like:
> >>
> >> ProductId
> >> InventoryForStore1
> >> InventoryForStore2
> >> InventoryForStore3
> >> InventoryForStore4
> >> .....
> >>
> >> What do you do if you get another store?
> >>
> >> You should have one row for each store. The aphorism is "Rows are cheap,
> >> columns are expensive".
> >>
> >> --
> >> Doug Steele, Microsoft Access MVP
> >> http://I.Am/DougSteele
> >> (no private e-mails, please)
> >>
> >>
> >> "Dustin" <(E-Mail Removed)> wrote in message
> >> news:AEA12908-AFF3-4A68-AFC6-(E-Mail Removed)...
> >> >I am new at this, I will try and explain better. In my table I have a
> >> >Part
> >> >No
> >> > field and fields for each stores inventory of that part no. And in my
> >> > query I
> >> > selected all these fields across the top. Then in the Critera I tried
> >> > to
> >> > put
> >> > all the part no's under the part no field, and all the low inventory
> >> > criteria
> >> > under the store inventory fields. And I found that there seemed to be a
> >> > limit
> >> > to how far I could go down in criteria and how far over I could select
> >> > fields.
> >> > I will try inserting more fields in design view like Roger said. But if
> >> > there is a better way for me to do this, I would appreciate the advice.
> >> > Thanks
> >> >
> >> > "Roger Carlson" wrote:
> >> >
> >> >> As to your specific question: you can add more rows or columns to the
> >> >> query
> >> >> grid (in Design view) by clicking the Insert menu item and choose
> >> >> either
> >> >> Rows or Columns. However, I suspect (as others have) that you have a
> >> >> database design issue as well.
> >> >>
> >> >> --
> >> >> --Roger Carlson
> >> >> MS Access MVP
> >> >> Access Database Samples: www.rogersaccesslibrary.com
> >> >> Want answers to your Access questions in your Email?
> >> >> Free subscription:
> >> >> http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L
> >> >>
> >> >>
> >> >> "Dustin" <(E-Mail Removed)> wrote in message
> >> >> news:1ED17A60-46A7-4851-8524-(E-Mail Removed)...
> >> >> > I have a large parts list that I am going to enter into a table and
> >> >> through a
> >> >> > query I want to report what is low on inventory. My problem being
> >> >> > that
> >> >> > the
> >> >> > design view query only allows me so many slots to enter Criteria and
> >> >> > or
> >> >> > Fields.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>