Queries :: Insert Multiple Groups Of Text Fields In Table
Feb 17, 2015
I am trying to do is to make a for loop to insert multiple text fields in on table.
Depending on the counter (Zaehler) it should insert that representing text field. for example if Zeahler is 1 it should input whatever is inside the text field KVP_Kfm1 if its 2 then it should input the textfield KVP_Kfm2 and so on. here is the code that I'm trying to work but sadly it wont.I believe that the mistake is that my syntax is wrong but i cant figure out what is right.
Code:
For Zaehler = 0 To (Forms!frmCMP!txtAuslaufjahr - Forms!frmCMP!txtAnlaufjahr)
SQL = "INSERT INTO tblLifecycle_Projektion(ID_Berichtstand, KVP_Kfm, KV P_technisch, AeJ, MoPf, skAe, MiBst, Sonstige_Effekte, " & _
"KVP_technisch_FTR, KVP_Kfm_FTR, AeJ_FTR, MoPf_FTR, sk Ae_FTR, Sonstige_Effekte_FTR, Jahr) VALUES " & _
Please bare with me as I am an Absolute Access VB code novice . I have a Form in my Access DB and I found the Following code on this site and placed it behind my search button :
Private Sub SearchButton_Click() DoCmd.OpenReport "All users", acViewPreview, , "[AUP].[First Name] = '" & Me.[Text34] & "'"
End Sub
The Search works great but only searches on the 1 Field "First Name" . How would I expand the VB code to include another 2 fields within my table . ( the other 2 fields I want to serach appear on the Form ) .
I have a table that is basically a survey form. The same series of options was available for 35 questions, and the table used to have a text string written for each answer. Because of all the repetitive data, I created a second table that assigned a number value to each of the nine possible options in these 35 separate fields. What happened is that, instead of the same text strings repeated over and over (and taking up real estate), now each of the 35 columns had a single number in them.
Now comes the day of reckoning and TPTB want a query with the raw data and the original text strings back in instead of the numbers. I was thinking doing something along the lines of a DLookup, but I can't seem to make that work in a query correctly. Apart from calling the same table and linking it over and over to the different fields in the original data table (see photo for how insane that is).
Field A Field B Result Chips Fries Replacement of 'Chips' with 'Fries' on menu. (null) Fries Addition of 'Fries' on menu. Chips (null) Deletion of 'Chips' on menu.
Can I do this in Access using some form of append query?
A list of codes that will be updated monthly, which will be the basis for querying the second table. Approx 100 rows of data.
2. Table Original
A data file obtained from IT where i'll need to sort it to find any codes that are including in Table1. This includes approx ~ 10,000 row of data.
** note, the "BIC" from "Table BIC" can appear in any of the 5 BIC columns in Table Original.
What i need to do is create a query that will:
1. Search the "BIC" from "Table BIC" in all 5 columns of "Table Original".
2. Where it has a hit, it will create new table - for example, the first row of table Original includes the BIC "ABC" in the "BIC 1" column. A query would create table "ABC" and place this whole record (all 8 fields) in new table "ABC". No modification needed.
3. Where two (or more) BIC's from "Table BIC" appear in one record in "Table Original" - the result will only need to be placed in one of the new tables (really doesn't matter which one). For example, Record #4 includes the BIC "ABC" in field "BIC1" and the BIC "DEF" in the field "BIC4". Therefore, a new table would be created (either ABC or DEF) to capture this information.
My colleague has a multiple table database and uses lookups for some fields. The simple query was to extract 3 fields and display all records from those fields. The result of the query was that the selected fields were listed first followed by all other fields which are usually not displayed.
I am fairly new to Access and I would like to create a form to allow users to create their own query. I would like to allow users to select multiple fields (perhaps with checkboxes?) from all possible fields in a table to return either all data from that field or narrow their search by inputting certain criteria or choosing from a drop down into a text box. Is this possible in Access and any detailed specifics on how to achieve this?
I am just querying a single table, no relationship involved with another table. As you can see form the attached jpeg, the ZIP field in some cases is empty. I would run a search using Is NULL but the field is NOT numerical. It's a long story but I had to make this field a TEXT field. Basically, what statement do I have to insert in the criteria field to just pull up the EMPTY ZIP fields?
I have a parent table (tblLabels) and a child table (tblRevision) where the revision history for the parent table is kept.
The parent table is populated via an excel import and may have several records imported at once. Instead of having the user manually enter a new record note in the child table for each record imported into the parent table, I've created a form that collects the necessary data (date, person who added the record, person who authorized the record, and notes) and then creates a revision history for each new record.
This is what I have so far:
Code:
Private Sub cmdAddNotes_Click() Dim strSQL As String Dim RevisionDate As String Dim RevisionRevisedBy As String Dim RevisionDesc As String
[Code] ....
When I run the code nothing happens. No error, no new records create, etc. My suspicion is that I may need to use an INSERT INTO query instead of an UPDATE query, but I'm not sure how to go about matching up the foreign keys if that's the case.
I am trying to make a query that outputs the minimum "Need Year" AND ALSO if the need year was equal to 9999 it shows "NO DATA".
This is what I have so far for checking the minimum value:
field: Need Year: MinofList(PMS_output!pqi_ny,PMS_output!iri_ny,PMS_ output!sdi_ny,pms_output!sai_ny)
I am not sure if I should be putting it in the criteria to check whether this minimum value (need year) equals to 9999 or not and if it does, it says "NO DATA" instead of 9999.
I created an update query to a text field in a table. However, it inserts the data at the end of the string and I need to insert it at the beginning of the string that already exists. How do I move it to the front instead of the end?
This is probably in the wrong forum, but I'm not sure what area the answer would cover, either queries, macros, VBA etc.
I have a form, on which is a listbox with multiple selection enabled getting it's data from a query. What I want to do is for the user to be able to select multiple products from the listbox and have some VBA code or query concatenate each id and insert them into a table, separated by commas so I can separate them again for reports etc.
Eg.
Listbox: ID 1 2 3
User selects 1 and 2 and clicks submit. Selections are concatenated to 1,2 and inserted into the table.
Please bear in mind I'm not the best at this kind of advanced databasing, so a simple or at least easy to follow answer would be very appreciated.
I have a simple form (frmAddPaper): txtPaper where user enters name of new newspaper, and cboCity, where user selects the newspapers town.
Also Close and Save buttons.I also have a table, tblCity. Columns as follow: CityID, City, Paper1, Paper2, Paper3, Paper4, Paper5, Paper 6.
Some cities have values (Newspaper names) in just Paper1 field. Others in Paper 1 & Paper 2, and some in Paper1, Paper2 and Paper3.I want to add the txtPaper value, to the first empty column, in the row where cboCity matches City column.
I have around 30 text box on a form and trying to insert the value of those 30 text boxes in a table from vba.
Here is my code
Private Sub Submit_Click() Dim a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z, aa, bb, cc, dd As String Dim SQL As String a = Month b = Week c = Type_of_Contact d = Country
[Code] ....
From above code I am not able to get my working done
Can we use a variable in referring a field in insert query?
I am using variable because I am not able to write the whole code in a single line and when i am pressing enter it gives me a error.
I'm creating a simple relational database of all the attorneys our company work with. I'd like to have three tables: "Law Firms" (firm name & other info), "Attorneys" (what firm, personal info, and what practice groups s/he is involved in), and lastly "Practice Areas" (all different practice areas with codes).
Now, my issue is that people are usually involved in multiple practice areas. Thus, I can't just put a code for one practice area in "Attorneys" table, but I can't put multiple codes in either in one field (right?) because it'd mess up the relations.
What I'd like to accomplish is for people to be able to go to our intranet and, using a form (?), select a law firm and a practice group and get a list of attorneys.
Does anyone know of a good way to solve this? It doesn't have to be pretty, since the DB isn't going to be huge (2000-2500 records).
Is it possible to create a query that will display the top x (say 10) records for each code in a set?
The record set that I have includes a country id and some quantity information. In short the record looks something like
Country Code (approx 30) Service Code (approx 20) Qty
What I would like to do is to aggregate all instances of a service at country level and provide a sum of the service count. Then, I am only interested in the top x services (biggest quantity) and am looking for query output along the lines of
Country1, Service Ranked 1 , Qty Country1, Service Ranked 2 , Qty ... Country1, Service Ranked 10 , Qty Country2, Service Ranked 1 , Qty Country2, Service Ranked 2 , Qty ... Country2, Service Ranked 10 , Qty
down to CountryX, Service Ranked 10, Qty
Can anyone help me here, I have tried top x etc. to no avail
i am making a contacts database. I have a table to hold the names and addresses of 700 people. I have another table holding the names of the mailing groups those 700 people can be in. One person can be in multiple mailing groups.
i made another table, and i typed in all the people and the multipe groups they were in. For example, a there would be seven records for a person if they belonged to seven groups
then i made a query relating that table to the addresses table. I want to be able to open forms to show an individual group, and cycle through the people in that group only.
i can do this, but access wont let me edit any of the records.
is there somthing wrong with my relationships? how do i tell accesss to allow records be in multiple groups?
There are thousands of records, unique by the Prod WH combination, across 5 districts. What I want is to pull the (highest) Top 3 Aged Inv$ values (Prod WH) for Each district.
If you want to use a "DoCmd.RunSQL "INSERT INTO" command to insert data in a table and the data to insert comes from a table and a form, could this be done in one pass?
So...writing a record wit 4 values from table1 together with a additional value from a textbox in table2 as 5 values.
I have a database with around 5083 records and I am trying to find the three most common numbers called in any one record such as 5 22 and 39 has been called together 50 times
Here is the structure # 1 through 5 is a number between 1 and 39 but never duplicated in same record
Record 1 |draw#|Date|#1|#2|#3|#4|#5| Record 2 |draw#|Date|#1|#2|#3|#4|#5| Record 3 |draw#|Date|#1|#2|#3|#4|#5| Record 4 |draw#|Date|#1|#2|#3|#4|#5| Record 5 |draw#|Date|#1|#2|#3|#4|#5| Record 6 |draw#|Date|#1|#2|#3|#4|#5| Record 7 |draw#|Date|#1|#2|#3|#4|#5| and so on for 5083 records
I HAVE A DATASETS cSV TEXT FORMAT WHICH HAS A SELECTION OF FIELDS THAT NEED TO BE IMPORTED INTO A TABLE. USING THE ADVANCED IMPORT FACILLITY I HAVE BEEN UNABLE TO IMPORT THE DATA PLEASE HELP AS THIS IS FOR MY A-LEVEL STUDENTS.
THE FIELDS REQUIRED IN THE SESSION TABLE ARE, Booking ID, Customer ID, Date Booking Made, Pickup Point, Payment Made
I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?
