I need to display info from two different table into one query

[hard2findparts]

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...

 

[Jeff Boyce]

"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]

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

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...

 

[Jeff Boyce]

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

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

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...

 

[hard2findparts]

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

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

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...

 

[Jeff Boyce]

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

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


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

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...

 

[hard2findparts]

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

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


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

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...

 

[Jeff Boyce]

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

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


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

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...

 

[hard2findparts]

Hi Jeff

That information is stored in the INV table. "p/n" and "qty" columns

Thanks
david

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

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


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

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...

 

[Jeff Boyce]

Aha (I hope)!

Are you saying that the [alt] part numbers also exist in the [Inv] table as
[part numbers]?

So, using the data you provided, the [Inv] table would have:

123 0
456 0
789 0
223 1

and your [alt] table would have:

123 456
123 789
123 223

If this is how your data is structured, then one approach would be to create
a query that joins the [Inv] table to the [alt] table (via the 'parent' part
number ... in this example, "123"). The next step is to put a second copy
of the [Inv] table in the query, joining from the [alt] part number (e.g.,
"456" ...) to the [Inv-second copy].partnumber (the first column). Then
pull in the [qty] field ... from that SECOND copy of the [Inv] table.

That way, you are seeing the quantity of the alt part number, not the
'parent'.

Does that get you closer?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff

That information is stored in the INV table. "p/n" and "qty" columns

Thanks
david

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...

 

[hard2findparts]

Hi Jeff

I didn't realize that could be done. That sounds like it should work...
Here is my new sql:
SELECT INV.[P/N], ALTERNATE.ALT, INV_1.[P/N], INV_1.MFG, INV_1.QTY
FROM (INV INNER JOIN ALTERNATE ON INV.[P/N] = ALTERNATE.[P/N]) INNER JOIN
INV AS INV_1 ON ALTERNATE.ALT = INV_1.[P/N];
When I try to view the subform i get an error that says: The specified
field '[ALTERNATE Query].[P/N]' could refer to more than one table listed in
the FROM clause of your SQL statement.

What is wrong with my sql?

Aha (I hope)!

Are you saying that the [alt] part numbers also exist in the [Inv] table as
[part numbers]?

So, using the data you provided, the [Inv] table would have:

123 0
456 0
789 0
223 1

and your [alt] table would have:

123 456
123 789
123 223

If this is how your data is structured, then one approach would be to create
a query that joins the [Inv] table to the [alt] table (via the 'parent' part
number ... in this example, "123"). The next step is to put a second copy
of the [Inv] table in the query, joining from the [alt] part number (e.g.,
"456" ...) to the [Inv-second copy].partnumber (the first column). Then
pull in the [qty] field ... from that SECOND copy of the [Inv] table.

That way, you are seeing the quantity of the alt part number, not the
'parent'.

Does that get you closer?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff

That information is stored in the INV table. "p/n" and "qty" columns

Thanks
david

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...

 

[hard2findparts]

Hi Jeff

IT WORKS IT WORKS IT WORKS IT WORKS

Sorry I got a little excited...

I had to change the query info in the subform also.

Doing it this way will let me add another couple of features that I was
wanting

THANK YOU SO MUCH...
David

Aha (I hope)!

Are you saying that the [alt] part numbers also exist in the [Inv] table as
[part numbers]?

So, using the data you provided, the [Inv] table would have:

123 0
456 0
789 0
223 1

and your [alt] table would have:

123 456
123 789
123 223

If this is how your data is structured, then one approach would be to create
a query that joins the [Inv] table to the [alt] table (via the 'parent' part
number ... in this example, "123"). The next step is to put a second copy
of the [Inv] table in the query, joining from the [alt] part number (e.g.,
"456" ...) to the [Inv-second copy].partnumber (the first column). Then
pull in the [qty] field ... from that SECOND copy of the [Inv] table.

That way, you are seeing the quantity of the alt part number, not the
'parent'.

Does that get you closer?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff

That information is stored in the INV table. "p/n" and "qty" columns

Thanks
david

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...

 

[Jeff Boyce]

You are welcome.

Jeff

Hi Jeff

IT WORKS IT WORKS IT WORKS IT WORKS

Sorry I got a little excited...

I had to change the query info in the subform also.

Doing it this way will let me add another couple of features that I was
wanting

THANK YOU SO MUCH...
David

Aha (I hope)!

Are you saying that the [alt] part numbers also exist in the [Inv] table
as
[part numbers]?

So, using the data you provided, the [Inv] table would have:

123 0
456 0
789 0
223 1

and your [alt] table would have:

123 456
123 789
123 223

If this is how your data is structured, then one approach would be to
create
a query that joins the [Inv] table to the [alt] table (via the 'parent'
part
number ... in this example, "123"). The next step is to put a second
copy
of the [Inv] table in the query, joining from the [alt] part number
(e.g.,
"456" ...) to the [Inv-second copy].partnumber (the first column). Then
pull in the [qty] field ... from that SECOND copy of the [Inv] table.

That way, you are seeing the quantity of the alt part number, not the
'parent'.

Does that get you closer?

Regards

Jeff Boyce
Microsoft Office/Access MVP



message

Hi Jeff

That information is stored in the INV table. "p/n" and "qty" columns

Thanks
david

:

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

in
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


"hard2findparts" <[email protected]>
wrote
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...