If you only store qty in the [Inv] table, how do you KNOW that alt part #
223 has qty = 1?
Regards
Jeff Boyce
Microsoft Office/Access MVP
message
Hi Jeff
I am sure that your not "dense". Sometimes I have a hard time saying
what
I
mean.
If I have part number 123 in stock, there is no problem, but if there
are
20
alternate part numbers for 123, I have to key in all of the alternate
part
numbers until I find one with a quantity in the inv field. Shouldn't
there
be a way to compare the text (both fields are text fields) in my "alt"
field
with the "p/n" field and then display the quantity that is associated
with
the "p/n" field.
For example, when I have part number 223 in stock, but not 123, 456, or
789
the subform now displays:
p/n alt qty
123 456 0
123 789 0
123 223 0
I would like the subform to display:
p/n alt qty
123 456 0
123 789 0
123 223 1
The query I am using is taking the quantity information from the "inv"
table
for p/n 123.
I would like to build a query that would look at the part numbers 456,
789,
and 223 in the "inv" table.
Thank you
david
:
I'm still dense ...
If the only table that has quantity is the Inventory table, then
wouldn't
knowing the qty of the 'parent' Inventory part number tell you how
many
you
have in stock? Where is "alternate part number items in stock" kept?
Regards
Jeff Boyce
Microsoft Office/Access MVP
message
Hi Jeff
For example, if a customer calls me and asks for part number 123.
My
computer might show 20 alternate part numbers for p/n 123. Now I
have
to
key
in all 20 alternate part numbers until I see which one I have in
stock.
If I
could display the quantities next to the alternat part number it
would
save
time.
I hope this helps.
Thanks
:
I must be missing something.
If the only place you store [qty] is in your [Inv] table, then why
are
you
trying to find out how many parts there are with an [Alt]![ID]? It
doesn't
seem like you are recording that information...
Regards
Jeff Boyce
Microsoft Office/Access MVP
in
message
Hi Jeff
My "alt" table consists of the following fields: "id" which is a
number
generated by Access, "p/n" which is the original part number and
is
related
to "p/n" field in the "inv" table, and "alt" which is the
alternate
part
number.
No, my "alt" table does not have a quantity field. All of my
quantities
are
stored in the "inv" table.
Thanks in advance
:
"How" depends on "what". You described some fields for your
[Inv]
table,
but none of the fields from your [Alt] table.
How does the [Alt] table "know" which [Inv] record it belongs
to?
Does your [Alt] table include a field for quantity (I assume
that
what
you
mean by "qty").
Post the SQL of your query.
Regards
Jeff Boyce
Microsoft Office/Access MVP
"hard2findparts" <
[email protected]>
wrote
in
message
I have an access database for our parts inventory. My "inv"
table
holds
information such as part number, mfg, qty, cost, list, etc.
Another
table
"alt" holds alternate part number information. I have a
subform
that
displays alternate part numbers for the part number that I key
in.
I
would
like to display a qty field after the alternate part number
that
shows
how
many of each alternate part number I have in stock. I tried
using a
query,
but it only shows the quantity of the original part number
keyed
in...Any
help would be appreciated...Thanks...
