Looking for the odd one out

  • Thread starter Thread starter Ian
  • Start date Start date
I

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
 
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
Click to expand...

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.
Click to expand...

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!
 
John Vinson said:
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.
Click to expand...

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
 
John Vinson said:
Can you split the field after downloading it?
Click to expand...

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 think so, I can clear everything after the dot so should be able to move
all the information from that into a new field.
Click to expand...

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)
 
Back
Top