Extracting a single incidence of each product code from a database

[Guest]

Hi!

I have a large data base with 21 columns and 75,000 rows. I am only
interested in two of the columns that list Product Name and Product Code
Number. Because the database lists shipments of each product code made to
some 50 customers in the course of the year, each product code and product
name occurs hundreds of times.

With a query, and ignoring the "extraneous" columns, I want to extract a
single row of each Product Name and Product Code # so that I can develop a
Price List for each product. How do I go about doing that?

Thank you very much

 

[Guest]

Choose Product Name and Product Code # fields for your query. Click the
Totals button on the toolbar (the Sigma button). In the grid, select Group
By in the Totals section for your two fields. The result should be a single
instance for each combination of Product Name and Product Code #.

 

[Storrboy]

Hi!

I have a large data base with 21 columns and 75,000 rows. I am only
interested in two of the columns that list Product Name and Product Code
Number. Because the database lists shipments of each product code made to
some 50 customers in the course of the year, each product code and product
name occurs hundreds of times.

With a query, and ignoring the "extraneous" columns, I want to extract a
single row of each Product Name and Product Code # so that I can develop a
Price List for each product. How do I go about doing that?

Thank you very much

Use the DISTINCT predicate.

SELECT DISTINCT tbl.field1, tbl.Field2 .....

 

[Guest]

This step worked perfectly, thanks. However I am uncertain about the next
steps, which is to (a) create a new lookup table (perhaps called PRICE LIST)
with a new column "List price" (b) type in the list prices manually.

Then my goal is to add a new column "List Price" in the main database and
and have it look up the prices by product code from the PRICE LIST table.

I haven't used Access in quite a while and I am very rusty, so your further
guidance will be greatly appreciated. I believe I still remember how to
create the lookup links but I am not sure about the steps of creating a
lookup table from the query that you have helped me with.

Many thanks!

 

[Guest]

A quick and easy next step would be to change the query you just made into a
Make Table query. When run, it will create a new table containing your
product list. Then just add a List Price field to this new table, and
manually enter prices. This could be your List Price table.

To connect your Main Table to the List Price table, you could add a List
Price ID field to each.

 

[Guest]

Boy, am I rusty!!! I totally forgot about the Make Table queries. Thank you
very much for your help.