sequentially group number query results

[browndudley]

I searched the forum and found something very close to helping me with what I'm trying to do but not exactly.

I need to sequentially group number the results in an access query as if by grouping by PUR_DATE AND CSCODE - but I do not want to group the query... So that a field "GRP" would yield results like this:

PUR_DATE CSCODE GRP
1/5/2009 1456 1
1/5/2009 1456 1
1/5/2009 1456 1
1/5/2009 985 2
1/5/2009 985 2
1/5/2009 985 2
1/6/2009 5684 3
1/6/2009 5684 3
1/6/2009 2310 4
1/6/2009 2310 4
1/6/2009 2310 4
1/7/2009 985 5
1/7/2009 985 5


Any ideas out there?

[ChipR]

So you want duplicates to be assigned the same GRP number? Not too difficult to code, but it may be possible with pure SQL. Going to think about it, but I suspect one of the experts can do it.

[browndudley]

Yes, if i understand you correctly. CSCODE is a customer number. PUR_DATE are purchase dates. Multiple CSCODES on the same date means that customer purchased more than one item. So I do want duplicate CSCODEs to have the same GRP number as long as it is on the same date. A customer will come back and make a purchase on multiple dates though... so If its the same CSCODE but on a different date it will have a differnt GRP number. I want the GRP number to be unique to the pur_date AND cscode.

[ChipR]

What is this for? Do you just want to be able to

SELECT * FROM myTable WHERE GRP = n

instead of

SELECT * FROM myTable WHERE PUR_DATE = x and CSCODE = y

This actually violates table normalization.

[OldBirdman]

This appears to be a table design issue.

Assigning a group number after the data is already in a table is not a good practice. There will always be a period of time that no group is assigned to some records.

If group# were Order#, it could be assigned at the time the data is entered. These records appear to be a bridge table between customer & product, but they lack the customer#.

Edit: This was written simultaneously to ChipR, above

[browndudley]

I need the Access report to not completely botch the order that the records are displayed in a report.

If I have a group header of customer in the reort it puts the all the same customers' transactions for every single date under that header. Not what I want.

If i have a group header of Pur_Date it puts every single customer for that date under that header. Not what I want.

i want the report to flow just like the query yeild's its results.... by grouping by Pur_Date AND CSCODE. But i can group the query b/c i need to see the indivdual records in the report. I can achieve this by having a group header in my report by GRP.

Hope I'm making sense.

[FishVal]

The idea with enumeration looks natural, however it will overcomplicate things.
Much simpler is to produce calculated field from these two fields using whatever operation you like, e.g. concatenation.

SELECT PUR_DATE & SCCODE AS GRP, * FROM [YourTable];

[browndudley]

Kind of like this but not exactly the same.... grouping a litte different i think:

http://bytes.com/groups/ms-sql/81197-grouping-records-assigning-sequential-number

[browndudley]

Thanks FishVal,
being a newbie would you mind expanding a little bit? I like the idea but not sure how to have it calculate sequential numbering but only jump to the next number when the CSCODE changes.

[FishVal]

I would discourage you from using sequential numbering at all since all you need is a single field which value is unique for a given PUR_DATE / CSCODE combination.
The main problem with sequential numbering is that a result for a given record depends not only on the record's fields values but on results of calculations for "previous records" which is not so easy to implement as it seems.

On the other hand having two fields, unique combination of which defines report group you can easily produce single unique field using values of these two fields as arguments in whatever operation you like. The only constraint to the result is that it is unique for a given group, it doesn't need to be beautiful, meaningful or even visible.

The query I've posted before will give something like the following

PUR_DATE CSCODE GRP
1/5/2009 1456 1/5/20091456
1/5/2009 1456 1/5/20091456
1/5/2009 1456 1/5/20091456
1/5/2009 985 1/5/2009985
1/5/2009 985 1/5/2009985
1/5/2009 985 1/5/2009985
1/6/2009 5684 1/6/20095684
1/6/2009 5684 1/6/20095684
1/6/2009 2310 1/6/20092310
1/6/2009 2310 1/6/20092310
1/6/2009 2310 1/6/20092310
1/7/2009 985 1/7/2009985
1/7/2009 985 1/7/2009985

[browndudley]

FishVal
Thank you so much!

[FishVal]

You are welcome.

Best regards,
Fish.