Conditional serial numbers

[Jondb]

I have two related queries relating to serial numbers, and to a summary field.

First query: I have a number field which I want to auto-enter a serial number, but I want that serial number to depend on the value in another field in the same table. So for example the records should create like this:

Field 1 'blue', Field 2 '001'

Field 1 'blue', Field 2 '002'

Field 1 'red', Field 2 '001'

Field 1 'blue', Field 2 '003'

Field 1 'red', Field 2 '002'   etc...

Field 2 needs to be a number field with an auto-enter calculation because I need to be able to enter a value that overrides this on occasion.

Second query: I also want a summary field that gives a count of the number of records based on a particular field. I know this can be done by performing a find first, but I want to keep it simple. Example:

I'd like field 3 to show the number of records which contain the current value in field 1. So if the current record shows 'blue' in field 1, field 3 should show the number of records in the current table that also contain the value 'blue' in field 1.

I'm sure these are relatively simple, but I can't find specific answers for either.

Thanks for your help

Jon

[Lee Smith]

Hi Jon,

Thanks for updating your Profile.

I'm pretty sure that you have meaningful names for your fields and they aren't Field 1, Field 2 etc..

Please use your real naming convention when posting questions. This not only helps us understand your question better, our replies will use them making it easier for you to implement them into your solution.

Why not attach a copy of your file (or a mockup) so we can see your schema better.

Lee

[Jondb]

Hi Lee-

Attached is a screenshot of the relationship between the two tables.

A brief explanation: This is a sales/stock record for limited edition prints. Each record in the 'Edition' table is a print edition - ie a particular image with title, size of edition and other details.

Each record in the 'Single print' table is a record of the sale of a single print. The match field 'Edition ID' brings in the relevant information on the edition. The 'print number' field in the 'single print' record then needs to assign the print a serial number based on the edition ID. 

Prints usually sell in sequence, but some collectors request a particular number within the edition, so whereas my serial number might be at 5 for a particular edition, I might also have sold prints nos. 12 and 20.

This is why 1) I need the 'print number' field to be modifiable, and 2) why I need a record count ('Total sold' field) for each edition ID.

1) I have the 'print number' field set up to be a unique value. In the case of a conflict I can skip the pre-sold number and reset the next serial setting on the field.

2) The ´Total sold' field is for keeping track of availability, so in the above example needs to show 7 prints sold rather than just relying on the serial number.

I hope that's clear.

Thanks

Jon