3 Questions - Suffix's & Date Queries & Auto Number

[matthewnsarah07]

I have three questions regarding a staff holiday request database I have created. Hope someone can help!

1) When a member of staff fills out a request form, the primary key is an auto number which can obviously then be used to track a request. I collect requests from 3 locations for now, Greater Manchester(GM); Lancashire(LN); Cheshire(CH)

Staff select there location as above from a combo box (combolocation) on the form - is it possible for another field to combine the auto number and the location initials to produce a reference number that is stored on the table so it would appear for example as 123CH? Can this be done with the primary key itself?

2) All requests have to be dealt with in 3 days, how do I set up a query that takes the current date and returns all requests 3 days or older?
A field [Complete] will be marked 'New' if the request has not been dealt with, the date is found in [Date of Request]

3) My form auto numbers itself as the primary key as stated in Q1, I have had a few people coming to me saying that the database has duplicated a record number (multiple users may use the form at the same time) is this possible and if so is there a definate way to stop it happening?

Thanks for your help, always appreciated

Matt

 

[Fifty2One]

The ID is already stored and the city code is already stored on the same record also. I would suggest that you just have the two elements put together for display or printing and not store duplicate information on a table. Something like [CityCode]&format([ID],"0000") would return numbers like LN0001 or GM0014.

if you use <Date()-3 in your search criteria it will give you dates earlier then 3 days ago

i have never seen an autonumber duplicate itself.

 

[neileg]

1) When a member of staff fills out a request form, the primary key is an auto number which can obviously then be used to track a request. I collect requests from 3 locations for now, Greater Manchester(GM); Lancashire(LN); Cheshire(CH)

Staff select there location as above from a combo box (combolocation) on the form - is it possible for another field to combine the auto number and the location initials to produce a reference number that is stored on the table so it would appear for example as 123CH? Can this be done with the primary key itself?

I agree with Fifty2One. Don't store duplicate data.

2) All requests have to be dealt with in 3 days, how do I set up a query that takes the current date and returns all requests 3 days or older?
A field [Complete] will be marked 'New' if the request has not been dealt with, the date is found in [Date of Request]

You don't need to store this data. You know that the request is new if the date field is blank. You can use a calculated field in a query or on a form to show 'Complete' or 'New'.

3) My form auto numbers itself as the primary key as stated in Q1, I have had a few people coming to me saying that the database has duplicated a record number (multiple users may use the form at the same time) is this possible and if so is there a definate way to stop it happening?

Check the properties for your PK field just in case it isn't set to Indexed, No duplicates.

 

[Dennisk]

Autonumbers should never, never be exposed to a user. They are for internal use only mainly primary/Foreign Keys. Primary keys may be unpredictable. I have seen numbers jump from 11,000 to 120,00',0'0 also if your indexes are currupt you may get duplicates.

Use a diffrernt scheme for generating unique numbers that a user can use.

 

[matthewnsarah07]

Can anyone suggest a good way of generating numbers, rather than the primary key that could be used as a unique reference for each record submitted?

Thanks to all for your help

 

[Dennisk]

I posted a function to do just this last week and the previous month so search the forum for autonumber