Looking for the odd one out

[Ian]

Hi

I have a query which takes a list of products, whereby each product can be
broken down into several different packages. for example:

AB1234.01
AB1234.02
AB1234.03

Each of these products has several different criteria which must match
across all of the products, irrelevant of what is after the "." The query I
have at the moment removes everything after the dot, then groups all the
products together. Any products where they do not match don't group, next I
count the products, and where the count of the product is greater than 1
link this back to the original table (which has an extra column for each
product without the information after the dot) to display all matching
records.

The problem is however that, say I have 10 packages and only 1 of them does
not match, my query will display all 10 packages and I have to manually
search through to find the odd one out. Which when I have about 50,000
products is very time consuming.

I have spent all afternoon trying to figure out a way to only display the
odd one's, but can't seem to get the result I am after. Can someone point me
in the right direction of how I can only show the incorrect records? I am
not sure whether I can do this with queries, or if I will need to use some
VBA.

I am hoping someone can help me out with this here, hopefully I made some
sense above. Thanks for any help.

Ian

 

[Jen]

If I'm understanding correctly, your first query groups by
the first six characters of the prod id and does a count
function on those records. - so for your sample data, your
output would be:

prod_id_prefix count
AB1234 3

If that's correct, I think the way to do it would be after
you count the recs, the WHERE clause should be WHERE
prod_count = 1 , then link this back to your original
table and this should give you what you need.

Regards,
Jen

 

[John Vinson]

Hi

I have a query which takes a list of products, whereby each product can be
broken down into several different packages. for example:

AB1234.01
AB1234.02
AB1234.03

Ideally this field should be *TWO* fields, the ProductID and the
PackageNo. It's much easier to put them together than to pull them
apart.

The problem is however that, say I have 10 packages and only 1 of them does
not match, my query will display all 10 packages and I have to manually
search through to find the odd one out. Which when I have about 50,000
products is very time consuming.

It sounds like an "Unmatched Wizard" query is the ticket here. I
confess I don't quite understand your queries here, but it is NOT
necessary to manually search through fifty records, much less fifty
thousand!

 

[Ian]

Ideally this field should be *TWO* fields, the ProductID and the
PackageNo. It's much easier to put them together than to pull them
apart.

I agree it would be much easier to have two fields, unfortunately though my
access database is a download from an AS/400 database where its only 1 field


Thanks for the reply

 

[Ian]

Can you split the field after downloading it?

I think so, I can clear everything after the dot so should be able to move
all the information from that into a new field.

 

[John Vinson]

I think so, I can clear everything after the dot so should be able to move
all the information from that into a new field.

I'd keep both fields, or use three (formally redundnant) fields; you
can easily split the composite field using

Left([fieldname], InStr([fieldname], ".") - 1)

and

Val(Mid([fieldname], InStr([fieldname], ".") + 1)