Help Plz!

[tfinch]

I have been trying to create a report that would count the # of new sales orders weekly.
The problem is that multiple sales order records are created due to system limitations
(when actually there should only be one order.) The PO# is usally the same or may
have a slight modification. For example,
PO# Order# Qty Description
12345 abcde 100 pants
12345-1 fghti 200 shirts
12876 dkgl 300 socks
12876-1 kdlg 400 pants
12876-2 mdkeo 500 shirts
The above represents 2 actual orders. Is there a formula for finding "like" information
and grouping together without actually knowing the string beforehand? And then getting a count?
Having all the details underneath would be great to support the count.

Thanks in advance for help and suggestions!

 

[odin1701]

Do you have an ID field?

If you do, then you can do a pretty simple totals query to do this.

Design a new query, click on the Sigma ("E" looking greek character) button - this will give you a totals query.

Drag the PO# and select "Group By" under the Totals area.

Then drag the ID (or any field which you know has data in it in every record) and under Totals for that item select "Count".

This will group all identical PO#'s and give you a count of how many records exist for each PO#. So you will have the # of PO's and the number of sales orders, I'm assuming....without looking at the data I couldn't be sure.


BTW this doesn't happen to be the tfinch from AT does it?

 

[tfinch]

Not from AT but thanks for info. Will check it out.

 

[tfinch]

AT or ATL?

 

[odin1701]

AT, you'd know what I was talking about Some other forums I post on.