I 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 RepliesI 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
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,
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
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?
I 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.
Hi,
I have two tables:
Table A provides total sales volume of the UK shoe market from 2000-2007.
Table B provides the sales volume of different shoe manufacturers from 2000-07.
I want a third table created, called Table C. This should look exactly like Table B but instead of sales volume it shows percentage sales that are calculated by using the figures in Table A and B (i.e. [sales volume from Table B/total sales volume from Table A] * 100).
Could someone point me in the right direction please (assuming that such a table can be created, based on a calculation of figures in other existing tables).
Thank you.
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
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 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 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 RelatedWhen 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 RelatedThe interface being used is a main form with various tabs and a subform on each of these tabs.
There is one field ('max power density') in my database that is calculated using 'Max Rated Power' and 'Cylinder Capacity' however these are in different tables and subforms. The 'max power density' and 'max rated power' are in table and subform 1 but 'cylinder capacity' is in table and subform 2. Is it possible to keep them in separate tables/subforms and still calculate the field?
I 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 have what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?
View 3 Replies View RelatedI have a table that stores orders in. There is a form to input data into this table, that I choose the products from a products table (using a subform) for the order. Once I submit the form, it submits several rows according to how many products I chose (a row for each product and the order) to the orders table.
The orders table has a autonumber primary key ID, so each of these rows has a different ID number.
I also want a column to store an order number (field: OrderNo) that is the same for each row (each product) for this order in question and not be different, allowing me to do a query for that order number and see all the products under it.
So I need the form to lookup the last highest order number from the OrderNo field, add +1 then when I add a product, it gives the SAME number to each one.
I hope this makes sense and I hope you can explain to how I do this? :)
Thanks,
Nick.
How do I add a new column to an existing table, and fill it with number (starting at 1) so I can use that column as the PK? Step-by-step instructions would be great, I'm having a lot of trouble with this!
View 4 Replies View RelatedIs there a way to see what the next autonumber is in my table?
View 8 Replies View RelatedI have a form, its based on a query. That query contains an Autonumber(intID) from a Table(customers). I also have another table(dealers), that uses the ID number as well. Next to the dealer info, I can click the "+" and it'll show all customers pertaining to that dealer.
I've seen some that have had different ID numbers. In the form, one has the ID of 222...while in the Dealer Table it has the ID of 227.
Does anyone know what the problem is?
Also...I have a subform where you can add a dealer to this Dealer Table if it does not exist in the combo box. Here's the code:
If MsgBox("Add New Dealer Number?", vbYesNo, "Warning") = vbYes Then
DoCmd.OpenForm "DealerContact", acNormal, , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
End If
Thanks for the help.
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.
I have a table named tblProjectNumber that has a autonumber field. In design view under the format property I have "NC07-"00000, this returns the a new project number with the text in front of it. What I would really want is for the text the change dynamically with the year. So an example would be NC07-00000 for project numbers created this year but I will manually have to change it for next year and the all of the previous project numbers will change accordingly. I have tried "DateSerial(Year(Date())-"00000 , Date("yy") and I can't get it tto work.Any help would be great.
Tim
Hi :)
I have solved a m:n relationship by adding a junction table. The composite primary key in the junction table is made up of primary keys from the other two tables, and both of theses primary keys are set to autonumber.
My question is this: Is it correct to set both parts f the composite key in he junction table to number rather than autonumber?
I was thinking that since autonumber should be set only once, the junction table should just take on what ever value there is in the primary keys of the other two tables.
Kind Regards
Marie
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 a table that has a primary key that does an autonumber in increments. Then I made some queries that will append records by date and then also another query that will delete what was appended. Then I created a reverse sequence of this process in case I change my mind and would like to put back the records I archived, however, when I do this, the primary key is now thrown off and will still revert to the last primary key that was appended before the archive ever took place. In other words it still thinks that the records I appended before I did the reverse, are still there. So now it will not let me save the record because it is a duplicate.
Is there a way to set the autonumber for the primary key to where I want it to begin?