Ms access query - help needed

[Sarma]

I have table (a) that contains the following info:

ProductCostSpecnProduct A100MProduct A100NProduct A100OProduct A100PProduct B125NProduct B125PProduct B125QProduct B125RProduct C150MProduct C150OProduct C150P

I have another table that indicates the specifications I am looking for in a product together:

Needed SpecnSpecn NSpecn P

I need to identify those products which meet both the specifications. In this case, Product A and Product B meet both the specifications and I need to build a query to obtain this answer.

 

[pbaldy]

People could probably help easier if you could format the sample data to make it readable.

 

[Sarma]

I typed properly but it is appearing the way it does once the query is posted. Thanks for your interest.

In one table, I have a list of products which meet various specifications. Some of these specifications are common between products. I have a set of specifications and I want to identify those products which meet all the specifications that appearing in the second table. When I make the 'join' query, what output I am getting is a list of products which meet atleast one of the specifications whereas I want the list that meets all the specifications and not any one of them. Hope I have clarified.

First table is in the following format:

Product Specn Cost

Second table is in the following format:

Specns needed:


I want to find a list of Products which meet all the Specns that I listed.

Hope this clarifies.

Rgds.

 

[MSAccessRookie]

I typed properly but it is appearing the way it does once the query is posted. Thanks for your interest.

In one table, I have a list of products which meet various specifications. Some of these specifications are common between products. I have a set of specifications and I want to identify those products which meet all the specifications that appearing in the second table. When I make the 'join' query, what output I am getting is a list of products which meet atleast one of the specifications whereas I want the list that meets all the specifications and not any one of them. Hope I have clarified.

First table is in the following format:

Product Specn Cost

Second table is in the following format:

Specns needed:


I want to find a list of Products which meet all the Specns that I listed.

Hope this clarifies.

Rgds.

It looks like nothing more than a Standard SQL Statement will be required to resolve the situation here. The structure of a Standard SQL statement would be similar to the following:

SELECT {Fields that you want to get (separated by commas)}
FROM {First Table Name} INNER JOIN {Second Table Name} ON {First Table Name}.{What they JOIN on} = {Second Table Name}.{What they JOIN on }
WHERE {Condition to Test}

Rather than give you an answer, I have given you an explanation that you should be able to use to come up with your own answer. Get back to us with any questions

 

[Sarma]

Is it possible to send you a direct message by Email ? Thanks.

 

[Sarma]

In one table, I have a list of specifications I am interested in. The format is as under:

Table Name: "Needed Product Specifications"

Column Heading: Specification

Under this heading there are 2 (can be more) specifications which I have listed, which I am interested in.

There is a second table. Name: "Offered Products and their Specifications"

In this table, I have various products and the specifications they meet. Column headings are: Product and Specification

I created a query wherein Specification in the first table is joined with Specification in the second table.

The query (named "Products meeting needed Specifications") resulted in a list of products which meet either first specification or the second specification or both. What I need is a list of products which meet both the specifications.

I have tried my best to explain the situation and look forward to your help.

Rgds.

 

[jdraw]

Please post a copy of your sql, and use code or quote tags to make it readable.

This sounds a lot like homework or a school problem.

 

[MSAccessRookie]

In one table, I have a list of specifications I am interested in. The format is as under:

Table Name: "Needed Product Specifications"

Column Heading: Specification

Under this heading there are 2 (can be more) specifications which I have listed, which I am interested in.

There is a second table. Name: "Offered Products and their Specifications"

In this table, I have various products and the specifications they meet. Column headings are: Product and Specification

I created a query wherein Specification in the first table is joined with Specification in the second table.

The query (named "Products meeting needed Specifications") resulted in a list of products which meet either first specification or the second specification or both. What I need is a list of products which meet both the specifications.

I have tried my best to explain the situation and look forward to your help.

Rgds.

I suspect that your issue is in the criteria for your Query, but as JDraw indicated, we cannot provide assistance of any value unless we know what you have already done. We do not want to do the work for you, but are willing to review any work that you have done, and make suggestions. Post the SQL Code from the Query that you have, and we can go from there.

 

[Sarma]

SELECT [Offered Products and their Specifications].Product, [Offered Products and their Specifications].Specification
FROM [Needed Product Specifications] INNER JOIN [Offered Products and their Specifications] ON [Needed Product Specifications].Specification=[Offered Products and their Specifications].Specification;

I used the above SQL. It gave me a list of 52 items which met either one of the specifications or both of the specifications. From this list, I have to subtract those items which met only one of the two specifications. I need a list of all items which meet both the specifications. Possible ?

 

[MSAccessRookie]

SELECT [Offered Products and their Specifications].Product, [Offered Products and their Specifications].Specification
FROM [Needed Product Specifications] INNER JOIN [Offered Products and their Specifications] ON [Needed Product Specifications].Specification=[Offered Products and their Specifications].Specification;

I used the above SQL. It gave me a list of 52 items which met either one of the specifications or both of the specifications. From this list, I have to subtract those items which met only one of the two specifications. I need a list of all items which meet both the specifications. Possible ?

As I suspected, you left out the Condition to test (WHERE Clause) from your Query. You should be able to use the WHERE Clause to filter your data and return what you are looking for. Give it a try, and you can get back to us with any additional questions.

 

[Sarma]

I have solved the issue differently.

I noticed that when I wanted a list of products meeting two specifications, the query gave me product meeting one specification and two specifications. I don't need the product meeting one specification as I want my product to meet both the specifications. So, I went to duplicate wizard which gave me a list of products having duplicates, means the list that I am looking for. Problem solved ?

No. There are times when I want a simple product that meets only one Specn. Then the duplicate thing will fail and I will obtain 0 results. So what I did was I counted the number of specifications that I need. May be 1, 2, 3, etc. Then I went to duplicate wizard design and imposed a condition that the duplicate counting should match with the number of specns I am looking for. This has totally solved the situation.

Now I am having a unique utility. Thanks for your time and help.

 

[MSAccessRookie]

I have solved the issue differently.

I noticed that when I wanted a list of products meeting two specifications, the query gave me product meeting one specification and two specifications. I don't need the product meeting one specification as I want my product to meet both the specifications. So, I went to duplicate wizard which gave me a list of products having duplicates, means the list that I am looking for. Problem solved ?

No. There are times when I want a simple product that meets only one Specn. Then the duplicate thing will fail and I will obtain 0 results. So what I did was I counted the number of specifications that I need. May be 1, 2, 3, etc. Then I went to duplicate wizard design and imposed a condition that the duplicate counting should match with the number of specns I am looking for. This has totally solved the situation.

Now I am having a unique utility. Thanks for your time and help.

I suspect this will not quite resolve the issue either. I think you might be over thinking this. All you should need to do is add a WHERE statement to your SQL Query that chooses records with the correct specification.

Something like the following might work (substituting the appropriate values for Spec1 and Spec2):

WHERE (([Offered Products and their Specifications].Specification=[B]{Spec1}[/B]) 
OR ([Offered Products and their Specifications].Specification=[B]{Spec2}[/B]))