Can You Define 2 AutoNumbers In Access?
Dec 5, 2007
Hi,
I am trying to create a db for software change requests. I have defined 3 tables:
PCR - Change request table. This table contains all info on the software bug.
Release - Software release table. This table contains info about the release date, platform i.e. SAP
Sponsor - The person funding for the change to take place. This table contains info on the sponsor such as Name.
I have identified that I need a one-to-many relationship between the tables. For example, A release will have many PCR's. A sponsor will have many PCR's they are funding.
My Primary keys are: PCR Table - PCR_ID, Release Table - Release_ID, Sponsors Table - BPM_ID.
In order to get a one to many (the many being on the PCR table) I have put two foreign keys in the PCR Table (Release_ID and BPM_ID). Both of these keys in thier own tables are autonumbers. From my undertstanding to get MS Access to relate the data I need to set the foreign keys in the PCR Table to autonumbers. Or do I? I do not want to change the primary keys in the Release and Sponsors table to datatype number as this would mean user manual input which I am trying to avoid.
Thanks in advance for the help.
Ket
View Replies
ADVERTISEMENT
Feb 19, 2007
Hello Everyone
I have a question which hopefully someone can help me with.
I have a database that links into an Outlook email account. I get the information across via File => Get External Data => Link Tables.
However the issue I have is assigning some kind of autonumber to this table. I am not bothered what the number is, just so that I can differentiate between the records.
Is there an easy way of doing this?
Thanks in adavance
View 1 Replies
View Related
Jul 16, 2013
Is it possible (and how...) to declare a module-specific form variable (or any variable for that matter) at the top of said module, so it doesn't need to be set at the start of each subsequent procedure?
I have a module of code specific to one form with a number of procedures, each one of which requires me to Dim / Set the form variable. It would be much neater if I could do it once at the start.
Code:
Option Compare Database
Option Explicit
Public Sub Populate(lngParameter As Long)Dim frm As Form
[Code] ......
View 5 Replies
View Related
Dec 9, 2005
Hi. This is probably a stupid question but it is becoming annoying trying to figure it out.
I have created two linked tables and then created forms to enter information into them. While i was working i tested several pieces of information by entering information into the tables and forms. Now i am ready to start importing the intial data and the autonumber is at 13 for one table and around 6 or 7 for the other one.
Is there any possible 'easy' way, without creating the whole tables or forms again, that i can reset the autonumbers to start at 1 again?
Thanks, any information appreciated.
Jon.
View 3 Replies
View Related
Feb 15, 2005
I started work for a wireless company a while back, and they keep track of all the mac address's for customers. The problem was that they choose to use the serial number as the primary key.
Nothing wrong with that, but what is wrong is that the serials arnt totaly in order. They werent sure on there serial number scheme so they just kind of made it up at first, then settled into something more concrete later.
The first serial numbers go up to 999, and this is fine, its after this that things get complicated. They totaly skip the 2 thousands and start again at 3000.
What they have assigned me to do, since i am the one with the only access classes from college is fix it.
What they want to do is type the serial number in a box on a form and it bring up that record. I have already built the form and just tried adding a "find record" button but they didnt like it. They want to just type it in the form, not in a nother window.
Any suggestions? I have suggested we could add all the missing serial numbers to the list, but then we have huge sections of blank data. not a real big deal, but i cant remeber for the life of me how to just type in a few recuring numbers and have access finish it off. I can remeber in excel but its not the same.
Thanks in advanced.
View 1 Replies
View Related
Aug 20, 2004
Hi
I am hoping someone can help me here I have created a database with number of forms and queries.
My problem is that my autonumbers dont start from one but start from 40,41,42...etc this started happening
when I was putting some data in the tables for testing and for some reason went all strange have no idea/how
it happened.
How do I get it back to normal with the autonumbers in the tables to start from 1,2,3,4.....etc again?
I hope this is clear to understand.
Cheers
View 1 Replies
View Related
May 24, 2005
1st question and situation with 2 databases Customer and Plan
I have created two databases. In one of them I want to be able to assign each customer a Customer ID (CID). For the other I have a Plan Database where I want to include a (PID) Plan ID, this database will also contain the CID's relating to the first database.
Anyhoo, how can I add an autonumber to the database for my CID's? It says I cannot due so to an existing database? But I just want to assign the existing ones a number and in the future as I add more records to the database, I want the Autonumber function to come into play.
How can I do this for my Customer Table with the CID?
2nd question
How can I add two auto functions in my Plan Database that can give me (DATE EDITED, and DATE CREATED for each record I may add in the future)?
Also for this database, how can I tie the autofunction from my plan database together? I want to be able to have the autofunction also kick in for this database as more customers in the customer table are added, thus creating more plan ID's in my second database?
Any help would be GREATLY appreciated. Thanks!
View 14 Replies
View Related
Jan 3, 2005
I trying to create a table where the primary key is a customerID, using the autonumber function. I would like to leave the new value setting on Random, since two people may be using database. However, I'm getting a lot of negative numbers as CustomerID's. If there a way I can limit the pool of random numbers to positive only.
View 4 Replies
View Related
Mar 23, 2006
I have a Client Table which has the following Primary Key: "Control ID".
The Control ID is an autonumber (ID increases by 1 with each new client).
Now, I also need to create another field called "Client ID". The Client ID is unique for each client, but needs to be a random number, in the range of between 100,000 and 1,000,000.
I've been using the Control ID for the last few months, but now need to add the Client ID. The Controld ID is mainly for internal purposes, and the Client ID will be given to the client.
I can't seem to add this new Client ID field, as tables don't allow for two autonumbers. Is there any way around this? It needs to be an autonumber field as is needs to be random, and entered automatically.
Any help appreciated, thanks!
View 4 Replies
View Related
Jun 17, 2006
I would like to create a custom autonumber to use as my primary key in one of my tables in my database.
I would like my records to be displayed as such C000001 , C000002 and so forth. Does anyone know how todo this?
Thanks in advance.
View 1 Replies
View Related
Mar 22, 2007
Good evening,
I am a little confused. I have one table with the Autonumber field and another table with a field that looks it up.
Tha problem is that I am using a Format for my Autonumber, e.g.
"O0045". And the field with the foreign key refuses to accept it! It needs a number to be entered, i.e. 45, and then it can format it. So on the whole, the LookUp Feature does not work, because it suggests FormattedAutonumbers, which do not fit in (obviously the foreign key accepts numbers only).
Is there a solution? Thank you in advance.
View 2 Replies
View Related
Feb 4, 2005
Uh, hi. I'm new to using access, and I'm wondering if its possible to display an autonumber at the top of a form in a label box. For example, if I had a field set to autonumber and was ading a new record to the database, could I display the autonumber field on the actual form?
View 8 Replies
View Related
Mar 11, 2006
I have an orders database using Ac2K. Main tables are:
tblOrders: Has OrderID which is autonumber, key index (no duplicates)
tblOrderDetail: Has OrderDetailID --long integer
Tables are related in one to many relationships on OrderID-OrderDetailID with both cascades set.
Database is split with tables and front end on server. I can't put front end on desktops because users can access from any of over 600 computers in the school district even tho there are not all that many simultaneous users.
Data is input on main form bound to tblOrders. When user puts in required information (Date,Vendor other etc.), I save record and autonumber generates a new OrderID. The subform window then opens and user then inputs the details of the order to the tblOrderDetail. There can be multiple entries here.
Form and subform are linked by their ID fields as master-child.
PROBLEM: System which runs on network with 2-3 simultaneous users is unstable. It works for a while (maybe 10-20 orders) and then crashes. The subform window is blank (never opens).
When I examine the tables directly I found these problems:
1) Two of the OrderID's (autonumbers) are the SAME! They are usually right above and below each other and usually tied to the same user altho other order info may be different. I don't see any data "collision" if it's the same user. I thought this was IMPOSSIBLE.
2) There is an entry or two in the tblOrderDetail with ######### in one or more fields. The OrderDetailID field is blank. I once found the ######### in the tblOrders as well.
3) The key index is removed from the OrderID.
4) When I go to the relationships window either the link is missing or the cascade and integrity are no longer active. If I try to re-establish, I get a message about missing key index or unmatched data. If I then go and DELETE the records with the ##########, I can re-establish the relationship. And it works for awhile again.
5. Compact and repair doesn't fix anything.
6. One user "thinks" she saw a quick message about "time-out". Is there some setting in Access that I need to change (Tools-Options-Advanced?)
ANY ideas would be much appreciated. There is nothing worse than trying to work with a system that works most, but not all, of the time.
View 4 Replies
View Related
Jan 12, 2005
Hi I have tried searching for an answer with no luck.
I am writing a table to do a filing cabinet archive.
I have a table with 3 fields in one is box no, second is item no and third is a description.
I have the first 2 linked as a primary key. The description is just text.
I want to be able to enter a box no and to have the second (item) to enter a number automatically following a sequential no which is relevant to that box. ie
box no Item No
1...........1
1...........2
1...........3
2...........1
2...........2
3...........1
1...........4 this one takes the next no available for box no 1
2...........3 and this one does the same for box no 2
if you go back to a box and start reinputting it picks up the next number.
should i sack trying to do it with the autonumber?
View 4 Replies
View Related
Jan 12, 2007
Hello All
Firstly I have been reading this forum for a while picking up hints and tips and am very grateful so big grats and thanks to all you guys for the help
The problem I am stuck on at the moment will hopfully be quite simple, but I should point out I have absolutely no skill or knowlede in Modules and VB language I most of my "stuff" via statements within the Design view.
Ok so here we go. I have a query which brings back the top 50 performing sales items, which is done by sorting them in descending order and using that filter thing at the top of the design view, which is set to 50. What I would like to do is insert a field in the query which gives the rank, so the best seller is given number 1, the second best 2 and so on and so forth.
Sorry if there is a topic already, I have tried to search, but couldnt find what I was looking for.
Ta in advance for any help
Mav
View 4 Replies
View Related
Apr 8, 2008
Hi All
This is my first post so hope I get everything right :-)
Right i have three tables which are laid out as such
ID Number PK
Capacity_Band Number
Product Number
Price Number
previously the ID was a autonumber but this has been removed recently. When I try to change the ID back to Autonumber Access throws up an error. Some of the ID's occasionally get deleted which results in non consectutive numbers, normally I would strip the Id field and start again , but unfortunatly the id's are all interlinked , so this is not an option , unless i reset them all !!!!:mad:
My only thought is to set up a complicated series of lookups (probably vlookups in excel to reset the numbers) . Im hoping there is an easier way to reinstate the Autonumber on the ID field ? :confused:
Many Thanks for help in advance
Maverickmonster
View 4 Replies
View Related
May 22, 2015
I need to generate quotation numbers for the quotes that I will be populating from my database. The process now is simply the format of date-n. The date is in standard format being "05222015" and the n is simply the number quote generated on that given date, For example the first quote generated today would be 05222015-1. Best way to generate these numbers automatically.
View 1 Replies
View Related
May 6, 2014
I have split the database I have created via the splitter-wizard and found that all tables with auto-numbers have re-set back to 1.
How do I get around this problem - I have tried running an append query but it flags up an error message. For certain functions to work in the database I need to set autonumbers at different amounts .
View 2 Replies
View Related
Oct 12, 2004
Hi, all... :)
I need to create cascade relationship on fly.
I've succeeded to create the relationship by:
ALTER TABLE tName ADD CONSTRAINT fk_id FOREIGN KEY(ID) REFERENCES tPrimary(ID)
and even succeeded to check the referential integrity by:
ALTER TABLE tName ADD CONSTRAINT fk_id FOREIGN KEY(ID) REFERENCES tPrimary(ID) CASCADE
The real problem comes when i am trying to enforce the cascades. I do not find any expression doing it.
the standarts of sql: on update cascade
or
of sql server: on update cascade go
just don't work in access :(
Thankful ahead
View 1 Replies
View Related
Jul 12, 2007
Hello,
My Access application consists of two MDB files: the application file and the database file. The application file has links to tables from the database file.
Now, I want to define relationships to optimize my database performance. Where should I define them? In the database file? In the application file? Both?
Thank you!
View 3 Replies
View Related
Apr 18, 2006
Hi!
I have a table with x columns.
In a form I can choose 4 different columns to define my search.
Now, I have this SQL-statement, that strangely doesn't work. Can someone help me with this?
SELECT a.Nr, a.Objekt, a.Applikation, a.Modul, a.Datum, a.Anmalare, a.Onskemal, a.Klart, a.Prio, a.Status, a.Ansvarig
FROM tblArenden AS a
WHERE ((IIf(Not IsNull(Forms!frmFiltrera!cboObjekt),a.Objekt Like Forms!frmFiltrera!cboObjekt & "*","*"))<>False) And ((IIf(Not IsNull(Forms!frmFiltrera!cboApplikation),a.Applika tion Like Forms!frmFiltrera!cboApplikation & "*","*"))<>False) And ((IIf(Not IsNull(Forms!frmFiltrera!Prio),a.Prio Like Forms!frmFiltrera!Prio & "*","*"))<>False);
View 1 Replies
View Related
Apr 8, 2007
Hi
I need to define this query:
I have 2 unbounded fields : ShowMonth and ShowYear (Each field represent the month and the year of Date field)
And UserName field
I need to define query that shows the dates and the UserName accordding to ShowMonth,ShowYear and UserName field (Without SQL).
I will be happy if serious answers will be post
Thanks
View 3 Replies
View Related
Oct 23, 2013
I am trying to save my table and I get a message saying "Cannot define field more than once". I only have 14 fields and none of them are repeated. My field titles are: First Name, Last Name, SSN, Wage, Salary, DOB, Hire Date, Years Employed, Phone Number, Alternate Phone, Address, City, State, and Zip. What am I doing wrong?
View 1 Replies
View Related
Dec 27, 2007
Dear all
i have a trouble to define a rank in query, i have a table named SALES
like below
region PRoduct qty
jabar A 10
Jabar B 20
Jabar C 30
Jateng A 50
Jateng B 40
Jateng C 35
i want to give a result in my query like below
region PRoduct qty Rank
jabar C 30 1
Jabar B 20 2
Jabar A 10 3
Jateng A 50 1
Jateng B 40 2
Jateng C 35 3
anyone can help me???thanks
regards
martell
View 1 Replies
View Related
May 12, 2014
How can I define a variable which can be used by another Sub and of course the value stored in it?For instance:
Private SUB A ()
DIM A1 as String
A1 ="ABC"
END SUB
PRIVATE SUB B()
PRINT A1
END SUB
View 5 Replies
View Related
Dec 16, 2004
I'm trying to add filds to a table, and when I try to save I get the error message: cannot define fields more than once. The problem is, there are no duplicate field names. I've run compact and repair (several times) with no change.
Any ideas?
Thanks,
Mary
View 1 Replies
View Related
