Unique record table query

[jon.mark.wright]

Hi All,

I am using a software drawing program that intelligently link information from a drawing to a database and vice versa. I have many tables and queries that I have created over time that enable auto populating of tables, creation of tables and so on. All of which enable me with tabular documents for ordering purposes.

The problem I currently have is: -
I want to create a table that looks at an existing table, analysis various fields for duplicate information and then adds one record to a new table. This is to enable me to create a spares list based on the entire list.

It may be best for me to explain in standard terms, I have a full valve list with various items, some duplicates some not. I am required to produce a spares list that considers only one of each type.

Can some please point me in the right direction?

many thanks Jon

 

[redalert]

Without knowing what fields you have, this query would produce a new table called tbl_Products from table Products with one record per product. You will obviously need to substitute table and field names. Does this give you right the idea?

SELECT [Product Code], [Product Name] INTO tbl_Products
FROM Products GROUP BY [Product Code], [Product Name]
ORDER BY [Product Code];

 

[jon.mark.wright]

Without knowing what fields you have, this query would produce a new table called tbl_Products from table Products with one record per product. You will obviously need to substitute table and field names. Does this give you right the idea?

SELECT [Product Code], [Product Name] INTO tbl_Products
FROM Products GROUP BY [Product Code], [Product Name]
ORDER BY [Product Code];

Hi Red Alert,

Many thanks for the response.
I think I kind of understand the query. If I was to give you an example for you to look at it may give me a better understanding.

The original table is called "General Valves" in this table I have have Tag, Description, connection, etc.
Now tag field is just a sequential number so i would not require that transferring to a new table but what i would like the query to do is analyse the table and append a new row in the new table for every item that is unique , so for example if I have description - ball valve and connection - flanged in row 1 and have the same info in row 2 then only one item will show in the new table as the fields are matched.

As an added bonus a quantity of each item would also be great but not sure if i could program that easily.

 

[redalert]

Try This:

SELECT description, connection,COUNT([tag]) AS ProductCount Into tbl_Products FROM [General Valves]
GROUP BY description, connection
ORDER BY description, connection

 

[jon.mark.wright]

Try This:

SELECT description, connection,COUNT([tag]) AS ProductCount Into tbl_Products FROM [General Valves]
GROUP BY description, connection
ORDER BY description, connection

Hi Red Alert,

That is excellent. I have just added the code with all the relevant fields as per my table and it work great.

Many thanks again.
jon

 

[jon.mark.wright]

Hi RedAlert

I Just have one additional question is it possible to add extra fields to the output table that are not a part of the grouping.
i.e. my original table shows description, connection, manufacturer and model.
Therefore if i wanted it to display all fields these fields in the output table but only sort duplicates for description and connection is this still possible?

 

[redalert]

If the Manufacturer and Model fields have the same values for all of the records representing a single product then you can group on them. If there are different values then you will get extra records in the result, one record for each combination of description, connection, manufacturer and model.

Give it a go and see what record counts you get and try to work out where the differences, if any, come from.

 

[jon.mark.wright]

If the Manufacturer and Model fields have the same values for all of the records representing a single product then you can group on them. If there are different values then you will get extra records in the result, one record for each combination of description, connection, manufacturer and model.

Give it a go and see what record counts you get and try to work out where the differences, if any, come from.

Hi Redalert,

unfortunatley it does filter the count even further. I have had a play around with different parts of the string but cant seem to find a way of adding any additional fields without them being grouped. Is there a way I can create the table with only grouping a few fields of the table..

 

[redalert]

You have discoveder that for a combination of description and connection
values you records that have different combinations of manufacturer and model values. You need to decide if thes scenarios are valid and if they are then you need to ascertain why.

It maybe that two different valves have the same description and connection and if so then these two fields do not adequately describe the value. It may be the case that you have different manufacturer and or model values for the same description and connection values in which case can a particuler valve come from different manufacturers or have different model numbers / codes / descriptions?
Send in a copy of the General Valves table in a database or spreadsheet and I will be able to help you more.

 

[jon.mark.wright]

Hi Red Alert

I have posted a part of the database including one table a query that pulls the info from the table in a manner that I want then the query and and the generated table.

The Cad Package uses the table Component valves as a link from that table i run a query that pulls out the exact fields and selection of valves that i require, this is the table that goes out for enquiry and subsequent order. Now what I will be required to do is produce a 2 years spares list for the project which is generally a list of one of every unique application.
I want the list to be as per the original list (21 General valves) with a couple of specific fields that are not applicable i.e drawing no. rev, etc. I need this 2 years list to group specific fields, i.e. manufacturer connection, size, etc but not group fields such as service, flowrates, etc as valves can be selection can be the same for valve with a service of nitrogen or LPG.

Hope that makes sense.

 

[redalert]

See attached jpeg image.

As suspected, you have records for the same valve with different manufacturer and model data. This is the reason for the difference in record counts.

Your data is not normalised which causes problems. Maybe this is an issue to address in the future.

Can you describe what you need without making reference to how you think that you can acheive it by creating a query?