Compare Query

[netrusher]

I have a table that shows Part Numbers. Each part number can be listed multiple times depending on how man Build ID's it is associated to.
Example:
Part Nubmer 123456 could be associated with:
Build Id's F187, A910, F001, C810, E010, C809
or it might only be associated to 1, 2, 3, 4,or 5 of those Build Id's.
What I want my query to do is find any Part Number that is only associated with anything less than all six of the Build Id's.
Example:
Part Number 123456 is only associated with:
Build Id's F187 & A910 or only F001 & C809 or only E010.
If this was the case I would like for that Part Number to show up in the Query along with whatever Build Id's it is associated with as long as it is not associated with all six Build Id's. The Build Id Field is also part of the table.

Any help on how to do this.

Hope all of this makes sense.

Thanks!

 

[lupins46]

Put you own table name into this:

Select mytable.* from mytable inner join
(SElect [Part Number] from mytable group by [Part Number] Having count([part mumber]) <6) as q1
on mytable.[part number] = q1.[part number]

This assumes that the only time a record is present is when it contains a BuildID and that there are no duplicate BuilIds for a part number.

 

[netrusher]

lupins46,

Thank you very much for your reply. I am lost about the inner join stuff. All of the data I have is in one Table. Actually it is a report utilizing Lansa Client which I brought into Access. Your assumptions are correct. How do I take what you sent me and put into a query? My two field names are: Product (which is the part number) and Build_ID. The are in the same table.

Thanks again.

 

[lupins46]

Start a new query, when you get the Show Tables dialog just click Cancel. The first toolbar button will now say 'SQL' indicating SQL view of the query. Click the button to show the SQL window.
Paste the SQL i gave you into the window, overwriting anything that is there.
Now fix the sql to use your own field names and table name.
You should now be able to go into design view of the query and see how it is built. Save the query. Run the query.

 

[lupins46]

The way the query is working is like this:

there is a group query within the brackets which selects all part numbers which have less than 6 records.
(You can cut this bit out on its own and create a separate query if you want to see that on its own.
SElect [Part Number] from mytable group by [Part Number] Having count([part number]) <6
)
This bit of the query now contains a list of part numbers which do not have 6 records.

Then this query is joined to the same table again using the part number as the link field so that you can pull out all the records with those part numbers obtained in the subquery.