Simple Criteria Help

[sahafi]

I need to show the quantity of certain products by using product codes. My
main table has hundreds of thousands of product codes. I have another table
with about 50 codes that I needed to show their product quantity, locations,
etc from the main table. I don't want to list all 50 codes in the criteria
(each code is 8 digits long). So I thought it has to be a better way. I have
tried to refrence my new table:
[tbl_ProductCodes].[Prod_Cd] after using the [Prod_Cd] field in both table
to create a relationship between the 2 tables, but it came up with nothing.
How can I execute this query without having to list all the 50 codes IN(" ","
", etc.)?

Thanks.

 

[KARL DEWEY]

In query design view place both tables in space above the grid. Click on
table tbl_ProductCodes field named Prod_Cd and drag to the other table field
named Prod_Cd.
Only those records that match will be pulled.

 

[sahafi]

Thanks Karl. I do know how to set up a 1:M relationship, and I did that
already. May be I were not clear, but actually I need to create a 'delete'
query that goes every week and delete any quantity related to the codes on
the smaller table (tbl_ProdCd)... by the way this table gets updated every
week with new codes, if any, to be queried and deleted. While your methos
'select query' would work, my users find a 'delete query' much quicker.

Thanks.
--
when u change the way u look @ things, the things u look at change.

In query design view place both tables in space above the grid. Click on
table tbl_ProductCodes field named Prod_Cd and drag to the other table field
named Prod_Cd.
Only those records that match will be pulled.
--
KARL DEWEY
Build a little - Test a little

I need to show the quantity of certain products by using product codes. My
main table has hundreds of thousands of product codes. I have another table
with about 50 codes that I needed to show their product quantity, locations,
etc from the main table. I don't want to list all 50 codes in the criteria
(each code is 8 digits long). So I thought it has to be a better way. I have
tried to refrence my new table:
[tbl_ProductCodes].[Prod_Cd] after using the [Prod_Cd] field in both table
to create a relationship between the 2 tables, but it came up with nothing.
How can I execute this query without having to list all the 50 codes IN(" ","
", etc.)?

Thanks.

 

[KARL DEWEY]

Your orignal post said you wanted to list by product code. Now you say "I
need to create a 'delete' query ..." and "...delete any quantity related to
the codes ..."
Do you want to 'delete' records or update by removing data from a field?

What is the purpose of tbl_ProdCd as you say it "...gets updated every week
with new codes, if any, to be queried and deleted."

Thanks Karl. I do know how to set up a 1:M relationship, and I did that
already. May be I were not clear, but actually I need to create a 'delete'
query that goes every week and delete any quantity related to the codes on
the smaller table (tbl_ProdCd)... by the way this table gets updated every
week with new codes, if any, to be queried and deleted. While your methos
'select query' would work, my users find a 'delete query' much quicker.

Thanks.
--
when u change the way u look @ things, the things u look at change.

In query design view place both tables in space above the grid. Click on
table tbl_ProductCodes field named Prod_Cd and drag to the other table field
named Prod_Cd.
Only those records that match will be pulled.
--
KARL DEWEY
Build a little - Test a little

I need to show the quantity of certain products by using product codes. My
main table has hundreds of thousands of product codes. I have another table
with about 50 codes that I needed to show their product quantity, locations,
etc from the main table. I don't want to list all 50 codes in the criteria
(each code is 8 digits long). So I thought it has to be a better way. I have
tried to refrence my new table:
[tbl_ProductCodes].[Prod_Cd] after using the [Prod_Cd] field in both table
to create a relationship between the 2 tables, but it came up with nothing.
How can I execute this query without having to list all the 50 codes IN(" ","
", etc.)?

Thanks.

 

[EZ]

This is a temporary work around. The data comes in with some quantities tied
to some locations under certain product codes, that they should be zero (or
they shouldn't show up to begin with). Until the main Org dB gets fixed, we
have to trap these records out and delete them to avoid over stating
production data. So, yes a 'Delete' query that delete unwanted records. I'm
just trying to avoid typing in all codes in the criteria field. I'm hoping
there's a better way of doing this (i.e. reference the tbl_ProdCodes table.

Thanks.