Creating a unique id from 2 fields with a twist!

[frantik]

Please help!!

I am creating a DB that records inspection data. This data falls into a number of categories, A,B,C..

I need to create a unique ref no for each report based on the category and the number of entries for that category, I am sure I have seen something similar on this site but cannot find it - blind?? ie.

Cat: No
A 1
A 2
A 3
B 1
C 1
A 4
C 2

This needs to be created when I select the Cat from a combo box on the form! Is there an easy way to do this?

REally hope someone can help I am at a complete loss - am almost a beginner too!!! - Thanks

 

[mvillara]

Please, Try to explain it a little better the problem. I can not grasp it. Try to expone me an example to understand the whole concept.

 

[frantik]

Will try and explain it a litle better:

Want to calculate a reference number from the inputted data for each record. This reference number will depend on the category of data and also the number of that category input so far. Example below: - hopefully all will be self calculating except the category input. - I need to count the occurences of each one and use that result to form the reference number

Rec no. | Category | No. of that cat. | Reference No.
1 | A | 1 |A1
2 | A | 2 |A2
3 | B | 1 |B1
4 | A | 3 |A3
5 | C | 1 |C1

I hope that is clearer!!!

 

[Mahathma]

1.
In query, right click ina any field row and select Total.
Select Category and No in the rows.
Select 'Group By' in Category and No Columns.
It should display grouped on these two columns.

It is not a good idea to mix number and text as you may face problems while sorting but, if you do not see a problem, try the following.

2.
You can join these two fields and display in query.
To do this, right click on Field, click Build and select the required fields. Use '&' operator to join the fields.
Guru