Hi,
Sorry, its late and I am frustrated. My title for the post leaves a lot to be desired.
I need to create a report that can show totals for each item, regarless of its suffix(xyz). So in other words it will take just the first 5 numerical characters and if there are 2 that are the same, it will combine them.
ITEM, JAN
12345xyz, 2
12345, 3
22222, 7
33333xyz, 2
33333, 4
The result I am expecting is
ITEM JAN
12345 5
22222 7
33333 6
I tried writing a query and using a LEFT([ITEM],5) fx to pull just the first 5 characters, and I used a total query to get the summary data. I end up with only the results of the first instance of the ITEM. In other words it forgets to display the second instance of 12345 and 33333.
Any thoughts of suggestions would be greatly appreciated. Have a great day.