Creating ID# from 2 fields

[D Soyars]

I am trying to connect date and address numbers to create a ID# say opened new file january 15th 2020 and the address is 1234 main im looking for an ID # of 2001151234. Soo... YYMMDDAAAA please help iv been working on this database for far too long

[UnklDonald418]

I've been looking at this problem and am wondering about your motivation for creating this number. If you are planning on using this as a Primary Key then don't bother because is will only cause problems. It is much safer to rely on Primary Key values that are Auto generated by the database engine.

That said, here is a possible way of generating a number in that format, but without knowing your data there need to be some assumptions.
For all the records in "YourTableName" the address field needs a numeric component followed by a SPACE character, and then the street name. If some records have an address like "PO Box 25" then this approach won't work properly.
There also needs to be a Date or Date/Time field.
And of course an Integer field named "ID#"

Edit and Run the following query template to verify that it will generate the proper values.

Code: Select all

SELECT "DateFieldName", "AddressFieldName", RIGHT( YEAR( "DateFieldName" ), 2 ) || CASEWHEN( MONTH( "DateFieldName" ) < 10, '0', '' ) || MONTH( "DateFieldName" ) || CASEWHEN( DAY( "DateFieldName" ) < 10, '0', '' ) || DAY( "DateFieldName" ) || LEFT( "AddressFieldNameFieldName", LOCATE( ' ', "AddressFieldName" ) - 1 ) AS "ID#" FROM "YourTableName"

If it works then use it and some further edits in the following SQL statement to update the "ID#" field in "YourTableName" to the generated number.

Code: Select all

UPDATE "YourTableName" SET "ID#" = <the working query goes here>  AS "X" 
WHERE "YourTableName"."AddressFieldName" = "X"."AddressFieldName" AND "X"."DateFieldName" = "YourTableName"."DateFieldName" 

The update needs to be successfully executed at Tools>SQL. Anytime you execute anything there you are bypassing the Base front end and dealing directly with the underlying database engine. To insure that the front end knows about any changes always select View>Refresh Tables.

[D Soyars]

Thank you for your reply.
The reasoning for this is to have a number that will give us a quick refrence to when it was created and the address. We are trying to keep track of (at some point) thousands of contacts with notes and information about the individual properties. We need a good ID# to give the customer and starting at 1 will not work. Date alone wont work for multiple contacts a day. I am open to suggestions. Fyi openoffice keeps crashing and im no coding genius below average actually. Just trying to get what we need done. Thank you for you patients and replies.

[UnklDonald418]

The default Primary Key field set to Auto works best. That value is needed by the database engine to uniquely identify a particular row in a table. Without a Primary Key a table cannot be edited, and a Primary Key without the Auto value requires the user to enter a unique number. That almost always results in frustration when the user picks an invalid number and the database locks up, waiting for a valid entry.
If your objection to the values beginning with 1 is just the small numbers you could edit and execute the following SQL statement at Tools>SQL.

Code: Select all

ALTER TABLE "YourTableName" ALTER "ID"  GENERATED ALWAYS AS IDENTITY (start with 100000) ;

followed by selecting View>Refresh Tables.
That example would result in 6 digit ID numbers, beginning with 100000. You could add another digit or two if you like, but 6 digit numbers are often easier to remember than longer ones.
Alternatively, you could ignore the auto generated Primary Key values and let the database use those for it's own purposes and add a field to hold an alternate ID#. Then it could be something like you propose, but that would probably add some complexity to the data entry process.

Or you may not need a number at all.
It would be relatively simple to create a Base Form where someone could enter a date and have all the transactions for that date listed by address on a SubForm. Then select one of the addresses from the list to display the transaction details on a SubSubForm.
You could have another Form where someone could enter an address and see all the transactions for that address listed by date on a SubForm. Then select one of the dates from the list and display the transaction details on a SubSubForm.