I would like to know how can I insert a new row between an existing row in an Access Table
For example,
I want to insert a new row between line number 122 and 123. Do I need to get rid of the primary key or change it to Number. What I am doing now in order to have several topics group together on a report such as "Activities Topic" and a Date field for that topic. However, there are various topics under the Activities topic. Each new information that is being updated for this topic I have to reorder the number by the topic name in the Order field so that the date will be in chronoligal order in the report.
I have a database with a form that has 3 fields in a subform. I need to paste there data from multi rows. Now I have to do this column by column and paying attention that I select them correctly. Is there a way to directly insert the data by selecting only 1 field and have the data directly there as it would in excel?
Now we use excel as a "between" road to paste it there and then copy it from excel and paste it into access.
-Existing Access Database contains tables with 1-2 million records
I would like to add a field[dol] to an existing table[rei]. I need this new field[rei].[dol] to be populated with existing data from another table[main] based on the associated field[main].[account1] or [main].[account2] or [main].[account3].
In the [main] table. There is always data in [dol]. But there is NOT always data in the [account] fields. Sometimes there is multiple account numbers per [dol] but not always.
Customers will ALWAYS have at least 1 [account] number and [dol]. Some will have multiple [account] numbers and [dol]. Sometimes these [account] numbers are the same in multiple fields[account1] [account2] [account3].I just need to do a lookup or something to find the [account#] and pull in its [dol] from the [main] table and populate it in [rei].[dol].
Not sure if what I want to do is possible, or at least possible the way things are set up.
I have a massive table - c. 6 million rows. It contains data along these lines:
Plan#, Item, Price, Description, Colour, Value, Location, etc.
The primary key would be Plan# + Item. Each Plan# has approximately 1,000 Items, and there is only 1 Item per Plan#. There are only a limited number of Items (c. 1500) and all or only some Items might be assigned to the Plan#. All items under each unique ID# belong together, sort of in a set. So this huge table has approx. 6,000 unique sets (based on Plan#).
To add to the confusion, Item A under Plan#1 may have different information (Price, Description, Colour, etc.) from Item A under Plan#2. I know this isn't a great way to set up data but this is what I have to work with.
Over the years it's possible that the exact same combination of Items with identical values might have been set up for multiple Plan#s. What I need to do is find any Plan#s which have the exact same combination of Item, Price, Description, etc. So if Plan#R has 200 rows and Plan#S has 201 rows, it automatically doesn't match. If Plan#R has 200 rows and so does Plan#T, all information in each record must match between the two Plan#s (with the exception obviously of Plan#).
I don't think this is possible, and if it is I am sure it's not going to be easy. So far the best I can do is to come up with finding duplicates on Item, Price, Description, etc. but that's only one record at a time and doesn't tell me if the two Plan#s match.
Any help or suggestions would be much appreciated.
I have a database that contains a few tables and queries etc. I made a copy of this database so that I could change some of the VB code with out affecting the database information. I have completed all the code and now I want to input those old tables into my finished verion. I am not sure how to do this. Any ideas on how I could do this would greatly be appreciated. Thanks.
Hi I really need to insert a movie in my database. I have tried inserting a Windows Media Player object in a form, but I dont know how to link it to a movie on my computer. Please help me with this. By the way I'm using DAO, if that helps at all.
Please help me as soon as possible, I'm in a hurry Thanks in advance
i am working on my company's database which is in access 97, and has a backend. the normal entry way when you add a column it wont save as it says it can't save propertys to a linked table. i added the required fields to the backend without any problems and saved and exited. now when i go into the "frontend" the fields arn't showing up on the table. any ideas? thanks briar
Ok, I know this is already part of another thread, but my problem is a bit more complex...
The database I've been designing catalogs traffic signal equipment at every intersection in a 12-county area. There's about 480 intersections in the database. My boss pointed out the need to have the intersections listed in geographic order. For example, if I have three intersections on IL Rte. 5 - John Deere Rd, Bauer Parkway, and Industrial Park Rd, they need to be listed in the order in which they exist on the road (from east to west or north to south). Alphabetically, the intersections would be Bauer, Industrial, John Deere. But, as you drive down the road (from east to west), the intersections occur as Industrial, Bauer, and John Deere.
I can sort the intersections according to their "corridors" without any trouble, but I have to force the geographic order of the individual intersections by assigning each an index value (like "1", "2", and "3"). Better yet, I can assign the indices in multiples of 10, 100 or 1000, (1,2,3 becomes 10,20,30 or 100, 200, 300, etc.) and guarantee there will likely always be a sequential position available in the geographic index for a new intersection. Plus, I can always pick the "middle" of the range for each new insertion. For example, if Bauer has a geographic index of 200 and John Deere's is 300, then I can give Zebulon a value of 250 - leaving equal room for insertions before and after Zebulon in the future.
Pulling this off isn't hard - I know how to do it. The problem is running out of positions in the index. In the case of my database, it is unlikely we'll ever add more than two or three intersections between two existing ones, so using multiples 100 is probably best. In this case, I can add at least 6 intersections before I run into the possibility of having two intersections whose indices have a difference of only 1. Using 1000, the value goes up to 9. Each multiple of ten gives you 3 more entries (minimum) before encountering consecutive indices. I guess the inherent problem is figuring out a more "infinite" way of accomplishing this. I don't readily need it, but on the off chance that I do end up with consecutive intersections (that I have to insert between), it'll require manual re-numbering to fix the problem.
That's my solution, but there may be something simpler. Any thoughts?
Can anyone tell me what i'm doing wrong with this insert statement?
INSERT INTO TBL_PROCESS_NAME VALUES( ' & [Forms]![frmIntroduction]![txtProcess_Name] & ' )
the form is loaded when the query is run, i've tried taking out the & and the ' but to no avail, all i get in the table is [Forms]![frmIntroduction]![txtProcess_Name]
The security table is made up of two primary keys: thing, personorgroup When i run this statement to insert into the security table
INSERT INTO security (thing, personorgroup, accessrights) SELECT '252600649', '4020', '255' FROM PROFILE WHERE not exists(select * from security where security.thing = '252600649' and security.personorgroup = '4020');
I get this error: Server: Msg 2627, Level 14, State 1, Line 25 Violation of PRIMARY KEY constraint 'PK_SECURITY'. Cannot insert duplicate key in object 'SECURITY'. The statement has been terminated.
Anybody know how i can perform my insert successfully? :D
i am trying to achieve a simple thing (at least it seems so to me!)... I am trying to add a new record to a table (LP_Product_Name) with only one field (Product_Name) as a part of a 'on Lost Focus' event of a text box (txb_ProductName). Code:Private Sub txb_productname_LostFocus()' Declare Variables Dim db As Database Dim rs As DAO.Recordset Set db = Nothing Set rs = Nothing' Assign Values to Variables Set db = CurrentDb() Set rs = CurrentDb.OpenRecordset("LP_Product_Name")' Enter New Product name to the table With rs .AddNew .Fields("Product_Name") = Me.txb_productname End With ' Close variables Set db = Nothing Set rs = Nothing' Restore Visible formMe.cmb_productname.Visible = TrueMe.cmb_productname.SetFocusMe.txb_productname.Visible = FalseEnd Sub When I type something in the textbox and shift focus to another field, nothing happens... no error message and no added value to the table! I dont know what is going on. It just doesnt work!
On the same form I also have a combo box that uses the same table (LP_Product_Name) as its rowsource. I want the Combo box to immediately show this added value.
HI, I would like to insert values to differetn tables using a form, is that possible with just access? or does it need VB script or SQL, if so, is there any example to follow. my form made of a combination of two tables attributes ad when I enter say:
name tel number address car car reg booking time
then I would need that info to go to the table it belongs to, as there is three tables, one is the car, the other is customer and the last is booking. thanks
Hi All, This should be a simple thing however Im having some problems. Im trying to insert a record into access. I can insert everything except the date. I cant seem to get the right format. Below is what Im trying: Code:strSQL ="INSERT INTO GISrequest (givenName, surName, email, division, title, purpose, audience, date) VALUES ('firstmname', "&strVal1&", 'anemail@somewhere.com', 'thedivision', 'thetitle', 'purposestring', 'audience',#" & AccessDate(NOW()) & "#)"
I think the error is in how I format the date, but Im not sure. Ive looked on the net for examples but I havent found anything that works... can someone suggest what may be wrong or perhaps provide (or point me to ) an example of inserting a date into an access database with ASP?
hEYA all, ive got a problem, im trying to inserting data from an array to my access table- but cant do this due to this error:-Data type mismatch in criteria expression.
Heres my insert code- which its genereating the error at: Code:sub InsertDBCmd( )'================================================= ========================================SET objRs = SERVER.createObject( "ADODB.recordSet" )dim sqlStatmentsqlStatment = "INSERT INTO Members "sqlStatment = sqlStatment & "( "sqlStatment = sqlStatment & "[A/C no], [Address_Code], "sqlStatment = sqlStatment & "[Managing Director], [Title], [Buying Group Member], [M D Sal], [Rebate Contact], " sqlStatment = sqlStatment & "[Rebate Title], [Rebate Sal], [Replied], [Member Name], [S/Holder 2], [S/Holder], " sqlStatment = sqlStatment & "[ALPHA TYPE], [Member Type], sqlStatment = sqlStatment & ") "sqlStatment = sqlStatment & "VALUES "sqlStatment = sqlStatment & "( "sqlStatment = sqlStatment & "'" & C & "', '" & No & "', " For n = 1 to 15 if n <> 15 then sqlStatment = sqlStatment & "'" & FieldsArray(n) & "', " else sqlStatment = sqlStatment & "'" & FieldsArray(n) & "' " end if Next sqlStatment = sqlStatment & ") "'rESPONSE.WRITE(sqlStatment)'RESPONSE.ENDobjRs.OPEN sqlStatment, objConn,1,3'================================================= ========================================end sub
I have two tables named datadistint and links and i need to insert only some values in five fields of links from datadistint but before inputting values we need to ensure they are already not there in links.
Insert Into links (LinkTitle,LinkURL,LinkDescription,maincat,cat) Select 1,3,2,4,5 From datadistint Where 1 Not in (Select LinkTitle From links) and 3 Not in (Select LinkURL From links) and 2 Not in (Select LinkDescription From links) and 4 Not in (Select maincat From links) and 5 Not in (Select cat From links)
i ran the querry as above but it doesn't work what should i do or what's wrong with this querry, because i need to compare all five fields and then insert only new values.
Hi! Please, please, please help me! I have created a form that should get Date, Day inputed by a user. Then it must place 8 the same records in the table. Any help will be appreciated Thanks in advance
I have been inserting thumbnail pictures into my table for the last six months with no problem. The field in my table displays "Microsoft Photo Editor 3.0 Photo." Now when I enter my thumbnail by inserting an object, the field displays the word "package" and the photo does not appear in the form where the pictures are displayed. What would cause the word "package" to appear when I am inserting an object just like I have been doing for months?
I hope this is the right section for posting this Q.
I use Access 2003 on WinXP pro as front-end & for back-end a MySql on a Linux server. I use MyODBC to connect to the back-end and all the tables are on the back-end. The workstation is connected to the server via VPN (so the server and the station are on different locations).
Quite often I get a problem that not all records are inserted into a table. E.g. I have like 5 - 15 records (up to 10 fields) in one table and I want to transfer/copy them to another:
I am new to microsoft access. I am trying to develop a data base that will store data as well as return means and SEMs. I have multiple parameters entered into an access table. The fields are Group Number, MAP, AP, and KV and are stored in a table called "Source Table". I have a query to return the averages for each group, but I am having trouble making a query to return the SEM. the formula for SEM is
SEM = Standard Deviation / (Count ^ (1/2))
This should be easy because access will return Stdev and Count, but when I try and enter the formula into expression builder, all hell breaks loose. Any advice would be greatly appreciated.
I am doing up a webpage which has a dropdown box with 13 choices(13 different department names eg. A, B, C..) to allow user to insert into database. However, instead of inserting just "department name", it insert "department name," into the database.
Eg. What i want to see in database is A but i get A, instead.
I have checked my SQL statement and there is no "," which should not have been there. Is there anywhere else i should be looking out for in order to correct this problem?
I have a very large database with record that are from 3 to 11 characters long. I would like to run a query to inset dashes at position 5 and 9 so a part number would look like this 0000-000-0000 or following the same convention but never dashes as the last character this is what i currently have UPDATE stihl SET [part number] = left([part number], 4)& "-" & mid ([part number], 5); then run a second as follows
This may be a fairly simple answer (and I hope it is, I really do) but I'm coming up empty searching through manuals and whatever google has on the issue, so I'll toss it out here in hopes that someone can help me.
Is there a way to add a "browse" button or something similar to an Access form field intended for files (word documents mostly)? I'm developing a process management database fror my office, and while I can master the whole "right-click-insert-object" thing, it's really more than the non-technical types in my office are going to be able to muster. Appreciate any help!
I have a table that has the follow fields (this is for a mailing schedule)
Job Name, Mail Date, Job Number, ID #, Material Due Date, Quantity To Mail
Here's the problem I'm having.
For each "Job" that we have, it can have multiple "Mail Date(s)". I'm trying to create a form that will allow a person to enter one "Job Name" and multiple "Mail Date(s)" and have it create a total number of records based on the number of "Mail Date(s)" (total possible is 4). Example below.
I'm creating a mailing for "Free Hot Coffee", I enter the "Job Name" once, and enter 4 "Mailing Date(s)", then click a button and have Access add 4 records for "Free Hot Coffee" each with a separate "Mail Date"
Is this possible? If so how? I was thinking of using a For loop but didn't know if it was possible.
