Help Creating A Table.

Apr 2, 2007

what i am trying to do is have something create a new table. It could either be when a check box is clicked. but preferably when a name is entered into a
text box. i just need the sub command to write. i tried using what access help has: Syntax
CREATE [TEMPORARY] TABLE table (field1 type [(size)] [NOT NULL] [WITH COMPRESSION | WITH COMP] [index1] [, field2 type [(size)] [NOT NULL] [index2] [, ...]] [, CONSTRAINT multifieldindex [, ...]])

The CREATE TABLE statement has these parts:

Part Description
table The name of the table to be created.
field1, field2 The name of field or fields to be created in the new table. You must create at least one field.
type The data type of field in the new table.
size The field size in characters (Text and Binary fields only).
index1, index2 A CONSTRAINT clause defining a single-field index.
multifieldindex A CONSTRAINT clause defining a multiple-field index


but i keep getting an error. if someone could just give me a sample code that i could copy and paste into the code builder to get a simple table with one or 2 coloums i could edit to the specific things i need. i just dont know where to start. thanks for the help.

View Replies


ADVERTISEMENT

Creating A Table To Cross Index Items In Another Table

Sep 14, 2005

How do I create a table that can cross index items in another table. Maybe I am not using the right terms here so let me show a small example.

Say I have a tables of words.
tblWords
numWordID
txtWord

Then I have some entries, all more or less synonyms of each others
fresh
new
clean

Now I want to create a cross-index table, related to the table "tblWords" where I can select synonyms from words already in the table "tblWords", so if I for the word "fresh" add "new" and "clean" as synonyms or entries, if I then go and look at the word "new" it will already have the synonyms "fresh" and "clean", likewise the entry "clean" will then have the synonyms "fresh" and "new".

Kind of a many to many relationship junction table but only with one table!

I hope my explanation have not been to confusing, but let me know if you need a clarification.

Thanks

View 6 Replies View Related

Creating A Table In Sql

Jul 25, 2006

Hi,
I ve got an access db thats attached to a java application.
I need to be able to create a temporary table that stores a result set, its necessary to do this as the java app needs to perform lots of repeated operations on this temp table.

I was think something like this would work:

DROP TABLE tempResults
CREATE TABLE tempResults
AS
SELECT col1, col2, col3 ....
FROM orginalTable


So i tried this:

Create table temptable AS
SELECT education,
FROM supermarketTable;


but it doesnt work, access complains about syntax error in create statment.

Could anyone give me some pointers?

thanks in advance!

View 2 Replies View Related

Creating New Records In A Table

Sep 28, 2006

I think this is a pretty simple thing, but for some reason I'm lost.

I need to add a number of records to a table.

The user would input a starting record number and ending record number (ie 60000, 60003) and access would create those records with the record number field populated with all the values, in other words, it would create 4 records 60000,60001,60002, and 60003

any ideas how this can be done? I'm thinking a query, but it might require coding of a for...next loop???

Thanks in advance for the help

View 4 Replies View Related

Creating An If Statement In A Table

Jun 6, 2007

I have created a database that tracks employee adherence. We have employees monitor adherence and when someone is out of adherence we track the time they are out and also there shift time. I am needing Access to know to change a record from 0:00 (midnight) to 23:59. I know it is probably done with an IF statement but dont know how to write it correctly, or where to place it.

View 1 Replies View Related

Help Creating New Table From Query :(

Aug 10, 2007

Hey, I'm a VBA Newbie. I have a query that pulls information from my form as criteria and i want to create a table with the results...I am having trouble searching online for a solution and Access Help is no help. Is there a way I can do this using vba?

View 1 Replies View Related

Creating One Table From Many Tables

Aug 24, 2007

Hi All.
Need union three TableA, TableB and TableC into one table called TableX? All table has same column name. TableX shouldn't has dublications.
Thanks

View 1 Replies View Related

Creating Table Using Query?

Nov 24, 2004

Dear Friends,
I am creating a table using query, i want to mention the following criteria
1) Required
2) Allow Zero Length
3) Indexed

I could able to give only NoT NULL criteria. How to give other criterias in query.

regards
shruthi

View 1 Replies View Related

Creating A History Table

Sep 26, 2006

Hi, I'm new to the forum.

I would like to know how to create a history table that will copy information from a field called "Status" on my form just in case I inadverently erase old information from that field without me being aware of it with my keyboard keys etc. In others words when I'm am interrupted by someone and I didn't notice I had erase the information by mistake and closed out the form and realized when I go back into it, my old information have been erased and I don't have that information documented anywhere else to re-enter that old information into the Status section on my form.

Thanks.

View 1 Replies View Related

Creating A Table With All Possible Combinations

Apr 6, 2007

Hi,

I have the following dilemma. I have to make multiple tables with many columns and if I were to type in the data manually it would take me a few weeks. But if I could somehow specify the number of columns(and the number of choices in each column) and then have Access(or Excel) create a table with all the possible combinations it would make my job a LOT easier. For example, let's say I have department stores in multiple cities. I need to create the following table:

CITY DEPT CLOTHING

San Jose Men's Shirts
San Jose Men's Pants
San Jose Men's Shoes
San Jose Women's Shirts
San Jose Women's Pants
San Jose Women's Shoes
Los AngelesMen's Shirts
Los AngelesMen's Pants
Los AngelesMen's Shoes
Los AngelesWomen's Shirts
Los AngelesWomen's Pants
Los AngelesWomen's Shoes

Instead of typing that out manually, is it possible to create some sort of list like:

Cities: San Jose, Los Angeles
Dept: Men's, Women's
Clothing: Shirts, Pants, Shoes

And have Access or Excel create the table with all of the possible combinations?

Thanks for your time.

View 1 Replies View Related

Creating Table Fields

Feb 12, 2008

Is it possible (and logical) for me to create a table with a calculated field from 2 other fields in the same table? Example; I have a long time field (mm/dd/yyyy hh:mm:ss) aka StartTime, and a long time field EndTime. I would like to subtract the StartTime field from the EndTime field to get the total time elapsed, but in hh:mm:ss format. I can get it in hh:mm format, but I really need the hh:mm:ss format. Any Ideas?

View 4 Replies View Related

Creating Views To A MS-Access Table

May 9, 2006

Hi all,

I write a program which is querying some data from a MS-Access database.
The problem here is, that the tablenames contain characters like "/" or "-" and I
do not want to change these names, because I think that the database
structure will be damaged.
To solve this problem I thought of creating a view to the table. I tried creating
this with a SQL statement, but that did not work.
Is there any other possibilty to create a view on a MS-Access table?

Regards,
Stefan

View 2 Replies View Related

Creating A Register System/table

Sep 7, 2006

Ok, firstly im sorry if this is in the wrong forum. I say this because my problem first begins with the tables but then i need to sort a problem out with the form aswell, but i will put it in here.

Here is the problem. Im sorry if im not too clear with this.

I need to create a register system E.G. A child comes into a club, he is registered. At the end of the session the child leaves the club. His depature is registered and his departure time is also recorded down.

Like a paper based school register but electronically.

How would I go about implementing this into a system?

Would I need a child table (with fields such as Child ID, Forename, Surname etc.) and a register table (not sure on the fields)?


k, this bit below probably goes in the forms section...

How would I get this to work on a form so there were boxes to check for arrival and departure, and when a button is clicked the depature time is filled in for each day of the term?

View 5 Replies View Related

Need Help Creating Table From Excel File

Nov 20, 2006

I am stuck with a problem where I need to create a new table from an excel file. In detail my problem is;
The excel file contains an order from a customer, like below,
BOM Component Qty
123 abc 2
123 def 2
234 ert 1
234 qwe 1
234 uio 1

I need to create a table from this data like;
Qty Partno
1 123
4 abc
4 def
1 234
2 ert
2 qwe
2 uio

NB! In reality the components are numbers not letters.
The qty for BOM is always 1 and the component 2xqty.

It is important that the BOM number is listed first and thereafter its components.

So I need to somehow associate the BOM number with its components and list them together as a group.
I just cannot figure out how to do this.

ANy help is greatly appreciated.

View 1 Replies View Related

Dynamically Creating MS Access Table

Feb 5, 2008

Hi,

I am trying to create a series of MS access tables from within a SQL 2000 DTS package. The names of the tables reflect the date range of the data within them. Creating the "Create table ddmmyy ...." statement isn't a problem (full code below), but doesn't run as it creates a Jet error - "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. Does anyone know if what I'm trying to do is possible.

Before anyone suggests it, yes I have posted this on SQL server forums.

declare @TheName char (8)
,@Cmd char (500)

select @TheName = (select max(LastRunDate) from NSP_Analysis_Dates)

select @Cmd = 'CREATE TABLE `NSP_Analysis_' + @TheName + '` (`TYPE` VarChar (200) NULL, `VENDOR_NO` VarChar (10) NULL, `VENDOR_NAME` VarChar (50) NULL, `AMOUNT_CLAIMED` Currency NULL, `REGION` VarChar (100) NULL, `CLUSTER` VarChar (100) NULL, `PLANT_CODE` VarChar (15) NULL, `PLANT_NAME` VarChar (50) NULL, `DATE_OF_SERVICE` DateTime NULL, `STATUS` VarChar (200) NULL, `TICKET_NO` VarChar (20) NULL, `NOTES` VarChar (255) NULL, `DATE_ENTERED` DateTime NULL, `AuthorisedByName` VarChar (50) NULL)'
sp_executesql @Cmd

View 1 Replies View Related

Creating An Index Using Code? Any Other Way Besides On The Table?

Feb 17, 2005

I am pullling data from a query using an unbound form and a query that that uses linked tables. I can not edit the index of the tables, so is there a way i can create a new index for sorting data in a form?

View 2 Replies View Related

Creating A Table To Export To Excel

Aug 11, 2006

how easy is it to have access create a table and export it to excel, and then have fields on an already made spreadsheet filled in.

View 1 Replies View Related

Creating Table In Design View

Feb 4, 2006

While creating table in design view is it possible to set the value of a field as sum of two other field?

for example:

I have a table with field a and b.
I want to have one move field c in the table such that c=a+b.

Please advise on the above

Thanks
Badri

View 1 Replies View Related

Creating SQL Statement To Create Table

Apr 19, 2007

Hey all

I have an online Access database and dont want to have to take it offline (download, update, upload) to create a new table.

Therefore I want to use a CREATE statement that I can run via an ASP page to create any additional tables.

I don't want to have to create the statements by hand so im looking for a way to create a table on my local version of access and export the SQL statement that would be used to create this table.

Any idea?

Thanks

View 1 Replies View Related

Creating A Form And Linking To The Table

Jul 11, 2007

Hi Team,

I want to create a form which will act as an outage tracker. I will be having 2 kinds of tables.

1. Type of leave
2. Headcount with employee schedules
3. Goals for outage

Please find the scenarios

1. In the front end as soon as the Team Manager selects his or her name the next combo box should show the names of his agents.

2. Once the user selects the date of leave, if the leave is being marked 48hrs in prior and the total outage is less than the goal then it should show the planned leaves options as per the leaves saved in the "Type of leaves" table or else a message box should pop-up stating the reason for not showing planned leaves options.

3. Also the form should show the %outage marked on the tool for the week.

Need your help as I am pretty new with access.

Regards,
Malay

View 3 Replies View Related

Creating A New Access Table From A Recordset

Jul 21, 2007

I'm experimenting in MS Access VBA reading from one MS Access application to another as visible in the code below. My problem is that I'm reading a recordset from an Access file and want to take that recordset (rec) to create a table in the current databank. If I run an cmd.commandtext "make table query" it runs the query across the connection and places the table in the remote database (as expected). Can I take the recordset and create a local table using ADODB command or is there a better approach. (I should note that the remote MS Access file does not have an .mdb suffix) Thanks in advance.

Sub get_indbyind()

Dim strInputfile As String
Dim dlgOpen As FileDialog
Dim bob As String
bob = Application.CurrentDb.Name

' select connection
Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
With dlgOpen
.AllowMultiSelect = False
.Show
End With
strInputfile = dlgOpen.SelectedItems.Item(1)

' make connection
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
Dim strcnn As String
strcnn = "Provider=Microsoft.jet.OLEDB.4.0; Data Source=" & strInputfile
cnn.Open strcnn
' MsgBox "connection made"

' create recordset
Dim rec As ADODB.Recordset
Set rec = New ADODB.Recordset
rec.Open "SELECT * FROM SATransfers;", cnn

Dim n As Long
Dim i As Long

Do While Not rec.EOF
Debug.Print rec.Fields(0).Value; rec.Fields(1).Value; rec.Fields(2).Value; rec.Fields(3).Value
rec.MoveNext
Loop

' create table from connection cnn
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Dim rs As New ADODB.Recordset
cmd.ActiveConnection = cnn
cmd.CommandText = "SELECT SATransfers * INTO tmp1 FROM SATransfers;"
Set rs = cmd.Execute


cnn.Close
Set cnn = Nothing
Set cmd = Nothing
End Sub

View 2 Replies View Related

Client Records/Table: Creating A Pop Up?

Jan 25, 2008

Hi,

Firstly, I apologise if this has been posted before. I have done a couple of searches of the forums, but can't find what I'm after... and being new to access, I'm finding it hard to know what exactly to look for. How to even explain what I want is difficult!

I am designing a database for the clients we deal with a work. It started out as being a database of 'post' and correspondence, so the Idea was, we'd have a databse of all the clients... and by selecting their names, it would bring up a list of all the letters that have come in for them. I have managed this part fine - probably basic, but fine.

What I would like to do, is expand it to more than just 'letters'. Each client has done a particular 'work' with us, and each client has particular details that are unique. For example, a client will have made certain transactions through a bank account.

I hope to be able to click on a client, and have a screen or page pop up with details and comments about that particular client. I have tried hyperlinking but that doesn't really produce what I'm after. For example, I tried creating a "report" with a page for each client, so that I could have comments for each client - hyperlinked to the table. But all it does is link to the "whole report" and not a particular page for a particular client.

I'm sure this is sounding really confusing:
I have a client named "Peter Litman". His name and details are within a "client" table (listing all clients). I hope to be able to click on his name (or row) and bring information (comments, details etc.) about him. I thought this could be some sort of "pop up", or link to a special page. ??

The same would then apply with all other clients.

Any ideas? questions?

thanx.

View 2 Replies View Related

Tables :: Creating A Secondary Table

Oct 27, 2014

I have an Access database, where one of the tables is near the maximum amount of fields (241 of 255), was just wondering if I could set up a secondary table that would be linked to the original table by a specific field and would store the other fields that would like to add to the table.

View 7 Replies View Related

Creating New Record From New Data In A Different Table

Oct 12, 2012

I have an access database that I use to manage my car dealership, part of it holds data of a stock vehicles, another aspect is invoicing.As part of the invoicing form, I have a part exchange vehicle section which creates a new record in the part exchange table.

What I would like to be able to do is when a new vehicle is added to the part exchange vehicle table it automatically adds it to the main vehicle table so it shows as a stock vehicle.

View 1 Replies View Related

Creating A Table Based On The Latest Entry

Jun 14, 2005

I am working on a jobs database where employees enter information where the job is being handed off to. I want to create a table showing the latest job entry by date. The jobs are listed by "Job Number" and when I try to create a table and remove the duplicate "Job Number" it does not always remove the oldest entries.

Any help would be appreciated.

View 1 Replies View Related

Help Needed In Creating A Table Inside Db UsingVBA

Apr 27, 2005

Hi experts. i wrote this code that supposed to create table inside the access db but when i run it i
get this error:

compile error:
user -defined type not defined for Dim db as Database



Itt is a command button that calls CreateDatabase. I be happy if some one help me fix this. Thanks

My code:
Option Compare Database

Public Sub CreateDatabase()
' Routines to create the tables.
CreateTablePlayers
'CreateTablePenalties
' Routine to create relations for the tables.
CreateRefInt
End Sub

Private Sub CreateTablePlayers()
' Local variables
Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Dim idx As Index
Set db = CurrentDb()
Set tbl = db.CreateTableDef("Players")
' Adding fields
Set fld = tbl.CreateField("playerno", dbInteger, 0)
fld.Required = True
tbl.Fields.Append fld
Set fld = tbl.CreateField("name", dbText, 25)
fld.Required = True
tbl.Fields.Append fld

Set fld = tbl.CreateField("initials", dbText, 3)
fld.Required = True
tbl.Fields.Append fld
Set fld = tbl.CreateField("birth_date", dbDate, 0)
fld.Required = False
tbl.Fields.Append fld
Set fld = tbl.CreateField("sex", dbText, 1)
fld.Required = True
tbl.Fields.Append fld
Set fld = tbl.CreateField("joined", dbInteger, 16)
fld.Required = False
tbl.Fields.Append fld
Set fld = tbl.CreateField("leagueno", dbText, 4)
fld.Required = False
tbl.Fields.Append fld

' Adding primary key
Set idx = tbl.CreateIndex("Players_PK")
idx.Primary = True
idx.Unique = True
Set fld = idx.CreateField("playerno")
idx.Fields.Append fld
tbl.Indexes.Append idx

' Add Table to the database
db.TableDefs.Append tbl
End Sub

' Adding Referential Integrity
Private Sub CreateRefInt()
' Local variables
Dim db As Database
Dim fld As Field
Dim rel As Relation
Set db = CurrentDb()
Set rel = db.CreateRelation("PlayersPenaltiesRel", "Players", "Penalties")
rel.Attributes = dbRelationUpdateCascade
Set fld = rel.CreateField("playerno")
fld.ForeignName = "playerno"
rel.Fields.Append fld
db.Relations.Append rel
End Sub

Private Sub Command1_click()
CreateDatabase
End Sub

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved