First of Sorry, becuase I am posting off topic. I have 2 databases. One in Access and another in MS SQL Server 2000.
DTS Import Export wizard transfers the data between Access and SQL Server well. I am able to get the SQL Scripts for "Create Table", but How to get the SQL Script for "Insert Into Table ..." ?
Can any one please help me out to solve this problem? :confused:
i need to append data that is currently in a single MS Access table into multiple MYSQL tables that have primary keys and auto_ids etc. Therefore I need to run the queries in order so that I can use the new auto_ids correctly, see below:
I am at work, and I have acquired a database that prints labels. They now want the database to be coded so that after certain labels are printed the database will print a blank label. I have the code figured out as a Do While statement in order to print the blank label. The problem I am having is that I am trying to use the Insert Into command to insert the filepath into the table that adds the blank label.
|DoCmd.RunSQL "INSERT INTO Rod_tmakLabels ( Print, [Order] ) SELECT Yes AS Expr1, 'Rods Labels' AS Expr2"|
If I run the above command, it just adds the text "Rods Labels" at the end of the table. Is there anyway with the INSERT INTO command that I can insert the new label between the 2nd and 3rd row and add another row? Or is the command designed only to add a new row to the end? I haven't had any luck searching for this yet.
I need to devise a query to append data for a particular Department_ID from TBL_NEWDATA to TBL_PERSON_ALLOCATIONS where that data does not already exist there. i.e. for Department_ID 'Research', I would want to append 'Person_ID', 'Department_ID' (in this case: 'Research') to TBL_PERSON_ALLOCATIONS for any tuples not already held.
INSERT INTO TBL_PERSON_ALLOCATIONS (Person_ID, Department_ID) SELECT Person_ID, Department_ID FROM TBL_NEWDATA WHERE TBL_NEWDATA.Department_ID='Form...'
[code]...
This Query takes a single argument from a control (Forms!Main!IN_Department), and this is the Department_ID to be updated.Is there any way to do this using a single query or will I have to use sub queries? I'd hoped not to as to keep the database as concise as possible.
I have a normalized DB with one to many relationships, using Primary and Foreign Keys.
I need to do inserts and maintain the PK/FK relationship, which means when I add a new PK I need to insert that PK as FK in other tables in the same transaction.
How do I do this in Access? SQL Server I use transactions, but I can't lock up the tables in Access like that.
I did a bunch of searching and found nothing, which leads me to believe I am way off in my thinking. Below are the dirty details
I have 2 tables, tblName and tblPhone. 1 name can have many phones.
tblName has PKName. tblPhone has PKPhone, FKName.
I have a form where user enters a new Name and PhoneNumber.
Name gets inserted to tblName, assigned with PKName = 100
Phone should get inserted into tblPhone with PKName.
IE Insert into tblPhone (FKName, PhoneNum) VALUES (100,"212-555-1212").
The dumb way I am doing it now is I insert to tblName, query tblname for the PK, then write to tblPhone. This can't be right.
I've searched the site and haven't really come up with an answer for this. I have a Report that I want e-mailed to two people every 10 days. If someone could give me a direction to start in, it would be appreciated.
I have a single table called "Books". One column is "Book title" and one is "Author(s)".
One author may have written several books, and one book may be co-authored by several authors.
It should be ok to use the analyze function to normalize the table into 3 tables. (autors, books and booksauthors_xref)
The problem is that the Authors-column contains one or more autors separated by ;
When I use the analyze function I only get the first autor in the join table. Since authors "AA ; BB" are treated as one, not to entities I had to split the field into a "co-autor" column and manually add one and one co author.
I want generate and load the report using vba when a command button is clicked . (automating the report generation) For example i have table in access that has name, address and email fields and onced command button is clicked the report is generated and loaded.I be happy if some one show me wprking example.Thanks
I have the following very general question, but I hope you can give me a few hints so I can start and find things out on my way.
I just created a database which via VBA procedures runs all kinds of queries and puts the results in tables, for instance I have about 20 tables now with 10 fields each and for every table I want to run the same type of report. I already made one report for one table via the report wizard, I would like to use the layout of this table for all other tables using a VBA code, so when I run this code it will create all these reports for me. What is the best way to start?
i have a table where one column has numbers such as 6104003 and i would like another column to recogzie this number and make a new string in this format 61P04-003 ajacent to it. can i do that in a table or does it have to be a querie? and the bigger question is how do i do this :confused:
I have to generate unique code for each person in an Employee table. This code will not be the primary key for this table. Primary key is an auto number. This Unique code is for refering to each person in all the correspondence/document/everywhere and every person will know his code and refer in all the correspondance.
I want to make a column in this table for this code. There can be two criteria for this code generation--
1. The code will be First alphabet of first name followed by sr no for that letter in 4 digits. Say Mr. Andrew is 99th person with alphbet A then his code will be A0099. Or Miss Jany is first person with letter J, her code will be J0001.
OR
2. The code will be a five digit 7 digit number--of which the first 4 digits are year of joining in organisation and remaining 3 digits are joining serial number of this person in that year.Say Mr. Andrew is 12th person who joined in 1996 then his code will be 1996012. Or Miss Jany is 118th person who joined in 2000, her code will be 2000118. For this I will add two columns namely Joining Year and SrNo in this Table.
I want the Access, by some means to generate this code based on the above critaria in one table.Can you people help me with the ideas about achieving this task. Presently we are manually assigning these codes.
:mad: I know that there is the auto number function, however i would also like it to generate an ID with charaters. In my table theres a first name and surname.
Say for example: Harry Potter, i'd like the ID to generate HP001
then say there's another guy call Harriet Potter, i'd like the it to generate HP002 insead of HP001.
appreciate any help, and if it's impossible just let me know thanx!!!!:)
I'm a novice in fact this is my first serious try at creating a database. I'm setting up a database to track contracts both potential and active for marketing. This will be converted to a SQL when I have it finished. I have a customer table setup in the fashion of a contact list. Most of my forms need to access the information in it via a list box. I want to set these list boxes to generate a new record in my customer table if the name that is entered is not currently in that list. By using the event option "On not in list" I'm able to get the form for the customer table to open but when the new data is saved and the Customer form is closed the information is not available to the list box. How do I get it to update the list box without having the user start from scratch on that particular form?
Is there a way that I could put a command button on my form and on clicking it, it would generate a word document (I have a word document saved) but on the word document, it would populate the address field with the information on the form. I have about 5 fields on the form that will be used in the word document.
I thought about creating a report in access instead, but I might want to edit the word document manually, is there a way to do this?
I have an ID field that I want a custom ID generated based on Name, Phone Number & Record Number.
Here is the code I'm currently using....
'User moved out of supplier name field Public Sub SupplierName_LostFocus() If Me.NewRecord = True Then 'If user adidng new supplier SupplierNameTemp = UCase$(Left$(SupplierName, 3)) 'Store 3 characters from supplier name End If nd Sub
'User moved out of suppliers phone number field Public Sub SupplierPhoneNo_LostFocus() If Me.NewRecord Then 'If adding a new supplier supplierPhoneNoTemp = UCase$(Right$(SupplierPhoneNo, 4)) 'Get 4 right characters of phone # End If End Sub
I then have a procedure which combines the two strings such as....
Private Sub GenerateSupplierId() SupplierID=SupplierNameTemp+SupplierPhoneNoTemp End Sub
The problem is when I check the SupplierPhoneNoTemp & the SupplierNameTemp within the GenerateSupplierID procedure. The contain no information meaning the SupplierID will have nothing as well.
However, when I check SupplierNameTemp within the Public Sub SupplierName_LostFocus() & SupplierPhoneNoTemp within Public Sub SupplierPhoneNo_LostFocus(). They both contain the proper info. It seems they loose this information when moving outside their procedures.
Does anyone know why this would happen & how I could fix this?
Hi i am new to Ms access, and i wanted to know how i could go about to create an auto generating number field that contains letters and numbers, e.g. a computer serial number.
I have created Query so that i can make a report.Report is working fine. it will give me data for required month.I need to generate invoice and save it to a new table so that i can cross check that in future if required.
i have two combo box, one is cboMonth and one is cboYear. i am trying to get the values from these 2 combo box, and used the value to generate report. however, when i click the btnPrintSummary button, system keep prompting me to enter parameter value.
Code: Private Sub btnPrintSummary_Click() Dim stDocName As String Dim stWhere As String Dim blnTrim As Boolean
I want to create a simple data base, in which I have to record Payments received from each customer on daily basis. and on daily basis i want to generate a report for the transaction I have made. example of data table is as follow. payment mode is cash or bank transfer.how to generate reports for day to day transaction.
Fields will include: TransactionDate ProductID ProductName UnitPrice Quantity SubTotal Total AmoundPaid Balance
The subtotal= unitPrice*Quantity Total = Addition of all subtotal AmountPaid = How much each customer paid Balance = AmountPaid - Total
fields the TABLE should contain? (Remember customers will purchase more than one product & there will be more than one reciept)..How I can generate a REPORT using a FORM ? The REPORT should contain the calculations. How do I make access do the caluclations?
I am trying to set a form that when you add a new report it will auto poulate with a number like this S44/13/001. I have not had formal training in Access.
