I am trying to create four tables: Company, Contact, Activities, and Opportunities.
I want them to relate hierarchically. A Company can have many contacts, contacts can have multiple Activities and Opportunities. But you can't have contacts without a company and you can't have Activities and Opportunities without having a contact. I want all PK's in all tables to link to one another, that you cannot create one without the other.
How I can do this in Access 2010?
YYMM00000-000000-A0000 CompanyID-ContactID-ActivityID or YYMM00000-000000-O0000 CompanyID-ContactID-OpportunityID
Any ideas on tagging my primary key (currently autonum as a data type) with an "M" on the front of it. I guess i wanted to keep it as autonumber so i wouldn't have to key in a value
Thought maybe i could create a new field and do an "M" & [ID] in the default value where [ID] is the autonum primary key field but I guess I'm not doing it right - get an error trying to save saying it can't find the field [ID]
I need to create an incrementing number to be used as a primary key in a table, based on a few variables. The format will be YYMM0000. It represents the number of units received each month.
I can come up with the formula to create that string no problem, but I need to create some code to check the table MAIN for the field TRACKER_ID to find the last number and increment it by 1.
i am trying to create a new service database for work. when jobs are booked inthey will be automaticaly given a job number. is there any way to have my autonumber field to have SER in front of each number that is generated??
I have a text source file and inpul layout i.e. field names start and end positions in excel file. I want create a table in access from the text data using excel file layout.
Can you please help me out in this. I am a mainframe programer and recieved an request to work in access.
Autofill text field using combo box selectionHello,
I'm trying to autofill a field on a form using MS-Access 2010 Web version sharepoint using macros without coding.
The form has an existing Combo Box field called Segment, using the row sources "REW", "WTT", "DBM".
A user begins filling out the form and eventually selects the Combo Box for Segment and selects any one of the segment no Multiple selection
There is another text field, which is actually alpha-numeric, called Job_No. I want this to autofill based on their Segment selection, using this format:
REW-YYMMDD-TTTT, or REW-131210-20001 for example, for today with the time displayed as military time and last Job No.(20001) for REW and Also for WTT (30001) as starting number.
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.
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 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?
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?
I have two tables that are related: BUCKETS & LOCATION
BUCKETS has these fields:ID (autonum) Type (type of bucket) Creation Date (when it was created) Operational (if it is operational or not (Yes/No). Location (Location of the bucket) Notes (notes)
LOCATIONS has these fields:LocationAutoID (Autonumber) LocationCity LocationID BucketsOnSite (can have multiple entries)
What I'd like to have done is for LOCATIONS table to be aware of how many different cities there are. So for instance, if there is multiple location entries for the cities of Vancouver and Toronto in there, I want the LocationID to first know how many Torontos there are and in LocationID to combine the LocationCity and a count (starting from 1) to that field.
So for example, if there are three toronto locations and two vancouver locations, LocationID would have TO-01, TO-02, T0-03 for the Toronto locations and VAN-01, VAN-02 for Vancouver locations.
At first I had the expression of [LocationCity] & "-" & [LocationAutoID] in the Expression field in the Field Properties but I quickly realized that it wouldn't count the amount of cities but just add the autonumber and the city (which kind of works, but not ideally).
My first post is on something that is troubling me. I have a Form acting as the display and entry point for data for a contact list, which is composed of two Tables as follows:
Contact - (text fields including: first name, last name, phone number home, phone number work, etc)
Industry Role - (yes/no tick boxes including: film, photographer, audio engineer, producer, reporter, etc)
The two Tables have a one to one relationship based on the URN field which is an autonumber. My problem is that when someone enters say a name, and then ticks a box, the autonumber will add two entries because it seems to see the first table then the second tables as sequential, and not the same thing. How do i go about making a form that can enter new records the same autonumber for two connected Tables?
