Compare Fields in Subform then format

  • Thread starter Thread starter Bill Phillips
  • Start date Start date
B

Bill Phillips

I have a physical inventory application. In the form there is an ItemID which
is linked to the subform via ItemId.

The subform can have 1 or more records representing a single location. On
each of these records there can be multiple counts because sometimes people
don't count the same quantities. I want to compare all the counts from a
single location and see if I have 2 that match. How can I accomplish this?
the counts are all entered in in a batch # number consecutively from 0 to 20.
I may have just 1 count or up to 20, and it will vary.

Sample Data:
Item A
Location A has counts of 1,3,4,7, 3
Location B has counts of 6,12,6

I would like to change formatting for Location A both counts = 3 and do the
same for location B for both counts of 6. I think I know how to do the
formatting, I can't quite get how to evaluate the fields. I don't know if I
need to evaluate the value of the controls or what.

thanks for any assistance. Using Access 2003.
 
On Tue, 13 Oct 2009 14:47:01 -0700, Bill Phillips

You may need to more rigorously specify your requirements. For example
what should happen if LocationA has counts 1,1,1,2,2,2,3,3,3,3,4
IOW, do you want to highlight ALL counts with >= 2 matches, or the
largest one, or ...
To count in SQL you could use something like:
select ItemID, count(*) from myTable
group by ItemID
having count(*) >= 2

-Tom.
Microsoft Access MVP
 
How is the inventory data stored for a location? Post table structure with
field names and datatype. Post sample data from the table.
 
Back
Top