Hi,
I'm new to this forum and have a question:
I want to create a table with an Autonumber field using a SQL statement,
in Microsoft Access Database, something like this:
Cnn.execute "CREATE TABLE newtable (id Long, Name Char(100)) "
is there something to put in place of "long" to make the field autonumber?
I tried the word "autonumber", but did not work.
Many Thanx.
Hadi.
Hi All,
I am trying to create a make-table query, with a new AutoNumber field.
I know that if you are creating a new Text field you type FieldName: "" in Field and for a Number field you would type FieldName: [], but what do you type for an AutoNumber field?
All,
I have trawled boards and sites, but cannot find the answer!
I have a make-table query, and I wish to create an AutoNumber field.
Now I know that if you put fieldname: "" , a Text field will be created and if enter fieldname: [] then a Binary field will be created.
Is there a code I can use to create an AutoNumber field?
Help appreciated!
Regards,
Jempie
I created a database of "My Cars", "Television", and "Wines" and a Trouble Reports(TR) for each. I have a field TR on each and for now a user can fill it up with number i.e first TR is 1, second Tr is 2 etc etc. I want it automatically filled automatically not manually. However, I want it to do the same for "Television" and "Wines" when I write Trs on them. I am a rookie and I dont know how to do it I attached a copy of the db I created.
View 2 Replies View RelatedI have a table where one field needs to be an autonumber, however, that autonumber needs to be calculated. This field is not based on any other information in the database, but there's a very complicated mathematical process behind it, which I'm figuring out...I just need to know once I get that code complete, how do I tie it into the table?
View 7 Replies View Relatedhi,
field type of autonumber can not be created with sql,why?is autonumber a data type like int,text ect.?but int or text field can be created
When I tried paste some data using front end to my database, Access showed error (can't create record because data would be duplicated). I thought it's impossible because it is autonumber field. So I checked it (manually). I did copy of my database and then for testing, I created record. I was shocked. Next record should has a value of "160" but Access gave "130" then showed an error "Can't create record because data will be duplicated". Of course after compact and repair everything is fine.
View 5 Replies View RelatedI have a form, frmSub, that contains the combo box comProducts. I also have two tables, Products and PurchaseDetail. Both tables have the field ProductID.
I want comProducts to create a new record in the Products table, using the input in a field called Product and then to use the value of ProductID to create a new record in the PurchaseDetail table. Ie, so the PurchaseDetail table has a record that links to another record in the Products table via the feild ProductID.
I hope I was semi-clear.
I need to make changes to a table already containing data. Currently the autonumber field is simply there as a primary key, but I now wish to use it for another number field (Accession No) which was previously completed manually.
As this doesn't seem possible or practical (as I need the flexibility to overwrite the number sometimes), I just want the autonumber to also appear in the Accesion No field when a new record is added (but be manually overwriteable).
I've had a play with default values and lookups, but with no success, and haven't found anything on the forum. Where am I going wrong, or is this just not possible?
Thanks for your help.
Alison.
Hi, I just a fresh user for the Microsoft Access. I have some question on the forms. I created a form and there is a field name Membership Id. In this field I created using AutoNumber format. I was requested this membership number needed to add also some Area Code and BranchCode infront of it. How could I add this into this membershipID so I could have this few info combine and shown in my form(textlabel) ? :p :p
View 1 Replies View RelatedI have the field called subject_id which is autonumber in the form of 10001,10002,... And I want the field with the same format to appear in another table. But appending it leads to different numbering, i.e. the filed becomes 1, 2, ... instead of 10001, 10002 as I've formatted it in its original table. How could I append/copy such a field?
View 1 Replies View RelatedI have two tables linked to each other in one to many relationship. Instead of auto number, the date and shift (Text) is being used as the primary keys (Composite Primary Key). Here is the tables structures,
Payouts Table:
Date: Primary Key
Shift (Day or Night) : Primary Key
Bills Table:
Date: Primary Key
Shift (Day or Night): Primary Key
Autonumber: Primary Key
The tables Payouts and Bills has one to many relationship. One payout row can have many bills. The problem is that I want to start the Autonumber in bills table everyday from 1. As date and shift are different for every day so even if i start bills from 1 everyday, it wont make same primary key. I can do it manually but I want to make it automatically.
I currently have a few tables that use an autonumber as the primary key, however, I would like the autonumber to start with a series of letters if possible. For example: instead of it creating an ID of 1, then, 2, 3, 4, and so on, I would like it to append lets say "ABC" to the front of it; ABC1, ABC2, ABC3, etc.
AP
I need to create a Autonumber field in a table with the following starting value 13-642-2000.
View 5 Replies View RelatedHi,
example:
SELECT tblFalls.Guest_Name, [Account_Number], Count(tblFalls.Account_Number) AS Falls
FROM tblFalls
GROUP BY [Account_Number], [Guest_Name];
Gives me:
Smith, Joe; M698, 1
Blinke, Frank; M686, 2
Neal, Bobbie; M648, 1
I need ot to give me,
1, Smith, Joe; M698, 1
2, Blinke, Frank; M686, 2
3, Neal, Bobbie; M648, 1
each time i run the query i need to list that guests, their number of falls and assign each unique guest a number starting with 1 on up...
How?
yes, yes, i know how to do it in a report, but I need right now to be able to do it in a query alone.. anyone?
I tried:
SELECT Sum(1+), Guest_Name, Account_Number, Count(Account_Number) AS [Falls]
FROM tblFalls
GROUP BY Account_Number, Guest_Name;
=p no luck.. though it looks neat.
I also tried writing a function
Public Function GetQryNum() As Integer
If IsNull(gQryNum) Or gQryNum < 1 Then
gQryNum = 1
GetQryNum = gQryNum
Else
gQryNum = gQryNum + gQryNum
GetQryNum = gQryNum
End If
End Function
SELECT GetQryNum() AS GuestIndex, Guest_Name, Account_Number, Count(Account_Number) AS [Falls]
FROM tblFalls
GROUP BY Account_Number, Guest_Name;
But all i get is a '1' in every row.
Any ideas?
Hi I am trying to make a database, In which I have a table linked with the form.
There are two fields in the table 1.Serial Number & 2. Current Year
I want the serial No. field to be incremented after every record is added & Also the numer should start from "1" again as the Current Year Changes.
Can somebody help me in this.
I am learning new things in access & not that proficient. But i love to work in access.
I have a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).
Currently, I setup the query to pull info from the form field like this:
DateField: [Forms]![frmmain]![DateField]
However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).
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
I have the following vba script and I can not get the yes/no field to work, also with adding field properties of required = yes and the text field to be 50, just examples are fine and I will update what I want.
Code:
Sub test()
Set Db = Application.CurrentDb
Set tdf = Db.TableDefs("1Testing") ' 1Testing is your table name
' First create a field with data type = Text
Set fld1 = tdf.CreateField("FieldT1", dbText) ' Field1 is your Field Name and formatted as text
[Code] ....
I have a Date field in the format X/XX/2012 for all my records.
I want to create a field that labels each record according to its quarter. So if a date is 3/29/2012, I'd want the corresponding field entry to be: Q1 2012. If it's 3/29/2013, I'd want it to be: Q1 2013, etc.
I guess I would run an update query, but I don't know how to build the proper expression in order to update my table with a new field.
I have figured out how to create a quarter #, but I actually need output in the format mentioned above.
I have a form and it has a field as question. I also have a field as I'd in this form from the same table that is autonumber. I want that for each field the question will be created automatically in the table as below:
Is epm ( my Id field) is created
Is epm and is created are string
They should also include Id number of each row. So It would be like:
Is epm -1 implemented
Is epm-2 implemented
.
.
For each row
I'm trying to summarize the value from multiple fields in a table and the total value will be updated on a different table as per highlighted below (taken from Northwind Web Database).
View 3 Replies View RelatedHi,
I am looking for Idea's of how to create a report.
Basically the report is a league table showing Month and YTD, this is the easy bit.
The part I need suggestions and guidance on:
The report also needs to show peoples movements in the league table i.e: whether they have moved up, down or not moved at all from the previous months position.
I was thinking of creating 2 tables: Current Month + Previous Month, in each table the persons position is indicated (an ascending record number is created throughout the table - not sure how to do this yet)
Then using code, lookup each persons position in previous month and write that position number against the persons UID and current position in current months table.
Am I on the right tracks or does someone have an easier solution?
Thanks in advance.
Darrell....
I've got a fields called rev code that contain the following values:
field name: 110 131 250 255 258
field value: 7.49 6 11.25 12.11 78
I'm writing a query that pulls from the first two digits of the rev code and need to round off to the nearest dollar so in my query I'll have a column 11 with a value of 7, a column 13 with a value of 6, then I need to take columns 250 255 and 255 add the values together and round off so I get a column 25 with a value of 101.
How do I do that?
I am wondering if it is possible to automatically create a new field in one table whenever a new record is entered in another table. The name of the field would be the primary key entry of that new record.
I have one table (table 1) in which each record corresponds to a particular mouse with a unique ID number, and each field is the ID number of a particular genetic marker. The table overall shows what genotype (+ or -) a mouse has at each marker.
In the other table (table 2) the primary key of each record is the ID number of a genetic marker, and the fields are several different bits of information about the marker (e.g. what chromosome it's on, its location on the chomosome, etc.)
I would like to have it set up so that if I enter a new genetic marker in table 2 a field named after its marker ID will automatically appear in table 1. Is there a way to do this?
I have 1 table that I duplicated to make 3 tables total. I did this b/c I am trying to create separate columns from the same field and table. The field is Workorder. Each workorder list the workorder number followed by a dash and then code. I am wanting to put all of the workorders with the same code in it's own column.
I have 5 codes that i am searching for. The first column list the workorder and a code (123456789-AD). The second column (123456789-BC). I'm good to this point but my problem occurs next.
The third column i am trying to put 3 types of workorder and it's code in the same column. As follows, (123456789-CD, 123456789-TC, and 123456789-PTC. However, when I do this it takes the results from the 3rd column and applies it to the 3rd column but also the 1st and 2nd column. I tried a UNION query and unless I am doing it incorrectly it does not work.