I need to restrict the random autonumber feature to 10 digits and a positive number for a primary key. I work in the medical field, and HIPAA privacy regulations require the use of random ID numbers to identify records. Also, I need the numbers to be positive numbers because we will be using barcode scanning equipment and the possibility of truncating the negative identifier creates the possibility (admittedly remote) of having a duplicate PK barcodes. I understand that we cannot change the Access feature, but any VBA code to generate a truly random 10 digit positive number I can use as a PK?
With Access 2003 I have a tbl_Orders with the field OrderID set as Autonumber>Long Integer>Random. This produces long strings of numbers both positive and minus. Is it possible to set a certain format such as #### and no less than 0 but no more than 9,999?
I don't know if it's possible, but is there any way of forcing the random autonumber generator in tables to be a positive number only?
The autonumber as I've searched on the forum can be a positive or negative number and I'd like to use the random autonumber feature as a primary key for account numbers for my database, but not using the negative numbers as they will look like odd account numbers with the leading negative.
I could possibly remove the leading negative, but there's then the chance that you'll have duplicates.
I've built a system with around 20 tables in it. All of these use the standard ID field offered by default by Access as the primary key. A friend with a background in database design and development for large corporates using Oracle has reviewed the database (he is going to do some VBA programming for me) and suggested that we use more appropriate primary keys based on data attributes.
He has read "somewhere" that there are problems with the standard ID fields and that occasionally the fields are renumbered/reindexed and that this can cause problems. There are a fair number of queries and some complex forms which will need to be updated for these changes but it would be a shame to go through all this work if it isn't really.
Should we use the standard Access IDs and is it worth a chunk of effort to change from where we are now?
Hello everybody, I have been tasked to create a DBase which at the click of a command button will to all intents and purposes generate an examination paper...
At work we are currently using a series of 'Word Doc's'which are static in that we only have a specific set of examination papers numbered 1- 4 which are alternately used on a quarterly basis and as a consequence easily compromised.
I have currently have ten tables in total... named 'Subject1', 'Subject 2' etc etc. Each table varies in the amount of questions held within. 'Subject1' for instance might contain 20 whereas 'Subject3' may only have 5. What I would like to be able to do via an input form is to manually specify the amount of records I would like it to generate, per the questions in the table, in order to generate an exam paper.
The Search Forum has given me some help/guidance, however I am completely lost as to how to apply it to the instance of multiple tables.
Your advice and help would be extremely well appreciated.
P.S... If I have posed this question in the completely wrong forum then please accept my profound apogies!!!
I have a table with about 300,000 records. About ten fairly small fields per record. I am trying to change the length of a text field from 25 to 40 characters, and I get the error message, 'MS Access can't change the data type. There isn't enough disk space or memory'.
I have never seen this message before. I have about 64 Gig of free disk space. What can I do?
I am fairly new to Access 2013 but am trying to create a query that will select random records from three totally unrelated tables and display the results together as if one table -- think video slot machine wheels. Each table has two fields - ID which is the primary key and NAME. The data in the tables are names of states, names of colors, and types of animals. Each table has a different number of records. My end result is a table that selects X number of random records from each table and displays them side by side like this:
Desired Result: Animal Color State cat red Ohio dog blue Texas fox green Iowa
I have been able to create three individual queries that will pull X number of random records by using:
SELECT TOP 10 Animals.[ID], Animals.[Name] FROM Animals ORDER BY Rnd(-(100000*[ID])*Time()); SELECT TOP 10 Colors.[ID], Colors.[Name] FROM Colors ORDER BY Rnd(-(100000*[ID])*Time()); SELECT TOP 10 States.[ID], States.[LongName] FROM States ORDER BY Rnd(-(100000*[ID])*Time());
Using the three queries above I get three separate lists. how to make one query that will randomly pull from all three tables and make the display above?
I've got an Excel sheet with +700k rows and 20 columns that I wanted to import to Access. All fields are text except the field that I want to use as a primary key, but I planned to import that as a text as well.
When I used the import wizard, I set all fields to import as text except for three that I set to memo. The wizard didn't say there was any error after importing the data, but when I checked the table, I noticed there were *a lot* of records where many fields where blank. Some fields where completely unaffected by this problem throughout the entire table, but in the rest of them, there is data missing in many records, and when there is data missing, it is not always the same fields that are missing. I have been unable to find any pattern that explains why sometimes the records were imported correctly, and why sometimes they were not.
I have a MAIN table with autonumbering for the project number. In a table related to the MAIN table I have a project number that I want to be updated when the MAIN table has a new project entered. Can I keep these in sinc automatically?
I need to create a table with 2 sets of different information both referring to job types. the problem is that i need the autonumber which created the job number to scale together. so both tables have a primary key of "job number" and i want the autonumber to only ever use 1 number in both tables.
e.g.
job type 1 - autonumbers - 1,2,3,4,6,7,9,10 job type 2 - autonumbers - 5,8,11,12
OK, My main table has an autonumber field in it to make records unique.
At the moment, the number 5-34 are all in use on active records. But when I create a new record either in the front end form, or the back end directly in the table, it attempts to start the record off with an already existing number.
No idea why this is doing this at all.
Is it possible to reset the autonumber to say 50 so all new records start at 50, then 51 etc.
I really dont want to lose the numbers already assigned, as there are other tables that rely on that autonumber as the clients ID to record all the other data.
Its probably something very simple, but I cant carry on with my data entry at the moment.
I made a simple access project and I want to make a count for my visits, this counter MUST starts from one each morning "every new day", I use autonumber field to give each visit its unique code, I'm ok with that, but i need additional counter that resets each new day,, i watched some videos for making the autonumber starts from different number like 1000 for example using Append query but i didn't figure out how to use this method for resetting counter everyday.
I am creating a database for cases. I want to set autonumber into sequence, Let say if there are data numbering 1,2,3,4,5. If I delete no.2, The data will rearrange in oreder from 1,2,3,4. While If I add a new value it would be the no.5.
Instead of data value autonumber 1,2,3,4,5 as I deleted no.2 and add new records. The data has become 1,3,4,5,6.
In one record (or row?) there I have 10 fields (columns), how can i find the standard deviation of those 10 numbers. Seems to only want to be able to do standard deviation if all data is in one field or column. I tried doing a query but same thing, if all the fields are across, it doesatn find the standard deviation of them. Please help this worthless access user. Thank you, I look foward to all the help I can get. As you can see, I still am struggling with even terminology. Excel is easy to me, access is a challenge to say the least.
the standard value for the yes/no datatype = no..I try to set the standard value to yes but I can not be allowed for yes ? how do I set it to yes ? I have open the table, then I could not change to yes.
I'm creating a database for a travel agency. There are 2 tables, one for customers and one for their bookings. I have established a relationship between the tables so the AutoNumber for customer ID links to a field on the add booking table.
In practice though, it would be easier to enter the customer NAME into the booking table and have it retrieve their ID that way to link them. This seems like I am missing something simple but can't fathom a way to do it.
The other factor is obviously more than one customer will have the same name so I may need to link first name as well to differentiate?
I was adding an autonumber field to an existing table and I assumed the numbering would follow the order of the primary key but that doesn't seem to be the case.
I have 3 tables: [SurveyVendor], [Surveys] and [SurveyResults]
SurveyVendor has 2 fields: [VendorID] - PK [VendorName]
Surveys has 3 fields: [SurveyID] - PK [VendorName] - FK [SurveyName]
SurveyResults has 3 fields: [SurveyResultID] - PK [SurveyName] - FK [SurveyQuantity]
I used the LookUp wizard to establish the relationships, and I chose to include 3 fields when linking [SurveyResults].[SurveyName] to [Surveys].[SurveyID]. I'd like the ID, Survey Name and Vendor Name to be displayed when making a choice in the combo box. And I would like the column to only display the SurveyName.
However, what's happening is the VendorID shows up instead of the VendorName when I click the combo box for [SurveyName]. And the SurveyID shows up in the column results, rather than the Survey Name.
I had a backup from a table that I saved to excel. Somehow we lost all the records from the table 10 minutes later. There where gaps in the id numbers due to some delete records in the past, and when I did try to put it back in the access table the records some how shift. is there a way of still using the ID nr that is an autonumber in the table and when I set the data back from excel and use the append when paste that they show up correctly? Maybe I can set the autonumber to start from the last record on the table?
