I'm trying to add up all the emails in a single string , but I can't access the records one after the other. The best I can do is display the currently selected record's adress ( The one with the black arrow in front ) . I tried browsing through the Fieldname.Value propertie ( in this case Me.Courriel.Value ) . I though I could cycle through all the Controls in the form , but Me.Controls.Count returns 3 , so I figured all the records must've been embedded into one control. Can anyone help me :( ?
(Sorry if this is a documented question , I've tried searching but couldn't find anything )
I am designing a bead ornament database for my mom to track inventory of beads, inventory of finished ornaments and cost/pricing. I have a table that joins the Item and bead part with the quantity of each bead part needed, it has the following fields: ItemMatlID, ItemID, BeadPartID, QtySo I have multiple rows of ItemIDs for all the BeadPartID & Qtys.Now I need a query with 1 line per Item and all BeadParts and their quantities. However, I need the BeadPart to be a row data and not a column heading. So a crosstab is out, I believe since it wants to make the BeadPart the column heading and not data in the query.
I am trying to merge data from multiple rows into one row for each unique ID. I can replicate on how to do this on a table, maybe a query or something like that. Attached img shows
I use following function to concatenate data from multiple rows into a single row/field. It puts a comma ',' after each concatenate. What I would like it to do more is to put 'and' instead of comma between the last two concatenated words. For example: Now it gives me Value 1 but I want to get Value 2.
Value 1: FormRegNo CombinedParticipants DC-190 Political parties, CSOs, community residents
Value 2: FormRegNo CombinedParticipants DC-190 Political parties, CSOs and community residents
Function: Option Compare Database Option Explicit Public Function ConcatField(strSQL As String) As String Dim dbs As Database Dim rst As DAO.Recordset Dim strConcat As String
I have a query that has multiple IDs and different information in numerous fields. For example:
ID Field1 Field2 Field3 1 x 1 m 1 b
I need to find a way to concatenate the data so that it shows the information like this:
ID Field1 Field2 Field3 1 x m b
I'm working in Access 2010.
Yes I know this isn't the best way to set up a database, but I'm trying to make fixes to an already existing database that I can't go back and change the way it is set up. I can only find work arounds to accomplish what I need.
So I want a way to update Status of my rows according to numbers list. For example I want to update Status column for multiple numbers to become Done .
Simply I want to update "Null status" to become "Done" according to its numbers according to this list
Code: 1234 53 546 767 2135 6657 43 34
Something like this
I tried "update query" but I don't know how to use criteria to solve this problem. In Excel I did that by "conditional formatting duplicates" -with my number list which I wanted to update - Then "sort by highlighted color" then "fill copy" the status with the value...
I am using VB.NET 2003 and MS Access XP for a desktop application. While developing the application we have a reached a situation where we want to print a report which retrieves records from four tables. Till here it is easy to think that it can be done by a simple SQL JOIN query, but following is the complexity:
The first table stores a single row. The second table stores multiple rows related to the Primary Key field defined in Table One. The third table stores a single row related to the Primary Key field defined in Table One. The fourth table stores a single row related to the Primary Key field defined in Table One.
The above SAVE RECORD option is performed when a user fills a Form of my application. As stated above, all the four tables are inter-related with a Primay Key field (TNo) defined in table one.
I also have a MS Access Report that will print information retrieved from all the four tables. The Report has some of the fields from each of the above table. The SAVE operation is performed in this way:
(1) A unique TNo is generated for a new record that is about to be created. (2) All the entries are saved in their respective tables (mentioned above.) (3) An access query will fetch the records pertaining to this TNo from all the tables to fill the report.
I want to know how to write such a query when I have to fetch multiple rows of a table in between. Is there any way that I can pass the TNO as a parameter to this query that is saved in MS Access?
I have this form which shows an order.... linked to this order are the client, the principal company, and the company that carries out the order. The information for these three companies are all stored in one table (since the information is very similar).
Tables look like: tblOrder - OrderID - ClientID * linked to CompanyID - PrincipalID * linked to CompanyID - Contractor (company that carries out the order) * linked to CompanyID - Date - ...
I have a form with a subquery on it that shows all of the records that the form is based upon. The subquery and form are linked already, so whatever record I change to on the form, the subquery will follow. The records are chronologically ordered by date in ascending order. Depending on what day the form is on, I want to be able to highlight the rows/records of that particular week automatically in the subquery. I was just wondering what method I can use to do the highlighting/selecting of the records if there is such a thing. Hope I was clear...THANKS! =)
I would like to make a form that can insert more than one row at one time. Something like add first column, then ask the user how many of the second row they would like, then prompt them for what is in the second row then add the information for the rest of the columns and have a separate row for each of the second column. So every row with have the same first column, but from there have a different row for how ever many desired in second column. So lets i enter for the first column, ABC, then I wanted 3 rows with ABC, then prompts me for the rest of the information for each of those rows separately.Something looking like this, oh and it would be updating an already existing table.
EDIT: I would also like to know if it is possible so it does it in ranges and dont have to do it manually like you enter the first column then enter a range for the second column and a bunch of rows are made with each value in between the range that was specified.
I have a table with the following columns: Task, Visa type, time it takes to perform the task. There are several taks that are performed for all visa types. I want to create a form to enter data to the table in which for the field visa type I have a list box that can allow multiple values, however, I do not to create a single line with the task and on visa type all the types of visas selected. I want to create a line for each type of visa with the information introduced.
I don't know if this is possible, the reason for which I want for the form to create several rows depending on the visas types is because then I have a query that sums all the types of visas. Can this be possible? I don't want the people to introduce manually directly to the table the data and also that for the same taks they have to enter manually 50 rows with values. I want it to be more simple and easier.
I have 5 tables that I would like to input data in. It can only be done with a single form. The fields I want to input in have the same names in all 5 tables, for example:
Table 1: Name Age DOB
Table 2: Name Age DOB
Table 3: Name Age DOB
Table 4: Name Age DOB
Table 5: Name Age DOB
Is it possible to input data into all of these fields in each table using one textbox for each field?
Preferably without having to use code but if it cannot be done without it then that would be fine.
I would like to know which way is the best way to import excel data from multiple sheets in to multiple tables in access.
For example data from Sheet1 -> Table1, Sheet2->Table2, Sheet3->Table3 etc...
I have tried using this: Cmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, "Table1", "C:Importedfile.xlsx", True, "Sheet1!"
Ironically, data from Sheet2 and Sheet3 seem to be properly imported in to table2 and table3, but some of the data from Sheet1 seems to be missing in Table1 after import.
I am creating a simple data entry form wherein the user will enter the product id and on change the product description and retail price must be displayed.
When the order quantity is entered, the total cost need to be calculated. I am able to do this using DLookUp and simple multiplication.
However, after doing the above, I need another row to appear so that I can accomplish the same for another product.
I was given a task by my boss to create an appraisal system for our department here in one of the local schools in the Philippines.
Anyway, what I created first a form from which all subjects being offered will be displayed. I did this by having a main form (bind to personal info table) and a subform that displays all the subjects (bind to subjects table). Then, the user will simply click on the appropriate rows bearings the subejct that the students wish to enrolled and when the save button is click, such will be saved into the enrol table.
My questions is, what control will I use to allow me to select multiple rows? and how will I be able to save selected rows into the enrol table?
I have a table of products and a table of ordersEach product requires multiple processes to completeCan I make a query that will lookup the products in the orders table and show a list of all the processes that need to be done to complete all the products on order?The bit I dont get is how can a query return multiple rows for each product?
Could somebody please help me! I am new to access and trying to create a database that brings together information from a number of different sources, into one large database. I need to import a large amount of data from another access table and I would like to just cut and paste it into my table. BUT, I can’t figure out how to add multiple rows at once to my existing table. The data that I am importing contains about 6,000 rows and 4 columns. Can anybody tell me how I can do this without having to manually create 6,000 rows, one at a time? I really do not want to do that. Thank you for your help.
But I am getting an error "Missing semicolon( at the end of SQL statement" and placing a semicolon at the end isn't helping either. I can insert a single row so column type or primary key isn't a problem.
Here's the table structure,
rvp - number (pk) rvp_name - text controller_id - number
Is there a way to select several rows of records in a datasheet view of a table and delete all of the selected records?
Every time I try to delete more than one record at a time, I get the following message: "The Micorsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time."
Then it will let me delete one record out of the selection.
I am the only one in that particular table at the time I am trying to delete the record.
I'm new to Access but not SQL, and am having difficulty coming up with a solution to the following problem:
I have one table for all Strings used in all other tables (to make Internationalization easier). The fields are: zId, zType, and zString. zId is an AutoNumber field. zType is a number which defines what type of string it is ( Item Name, Person Name, etc. ). zString is a text field with the actual text string (can be empty).
I have another table for Items, which have (among other things) Names (field name= zName). zName is actually a lookup into the Strings table and stores just the ID.
I want to create a form for people to enter a new Item. When creating a new item using this form, they will have to give it a Name, which will need to add a new entry into the Strings table with the appropriate type (ie, Item Name).
So, my question is "How to do the following..." * on my form to enter Item Data, when a user goes to create a new Item, I must first create a new entry in the Strings table with the correct value for zType. Then, I must get the value for zId for this new entry and assign it to the zName field in the Items table for the new record that I am creating.
I am trying to find a way to combine multiple rows into one (field). I have 2 tables. One table is data the other table is notes. The notes table has a new record created for each new note. Both tables have the Account Number and and Sub Account Number. I am linking the two tables on these account numbers with a left join normally. There can be records in the data table and no records in the notes table. I guess what I want is a query or sql statement i can use in vba that will display all the data from the data table and all the notes from the notes table in on field.
This data is used in a large report. Right now I have the data table set to the report by itself and code on the report to pull the notes from the notes table using a ADO recordset with a do while that just adds each note to a single variable. This works fine and does the job. The only problem is the time it takes to run the report now. If the report has 1000 records with notes on 90% of them and each of those has multiple notes that recordset code has to run thu ALOT. I use to have all the notes just compile to one field in the data table and the report ran very fast that way. I had to change this since I need it this way as to filter notes by time and archive them after a period.
I have read thru so many posts and see lots of code functions to do this but its just the same thing I already have with the recordset code building the combined field. Any other way to do what I am trying? I think I have just been over thinking this stuff at this point an maybe a fresh set of eyes will help. Below are some simple table layouts for the 2.
**Account Number and Sub ID fields in both tables are not unquie.
Data Table: (this table has alot more data but this gives you the idea.) Account_NUM - Sub_ID - Data1 1234 | 3 | 123 N Inc. 1234 | 3 | 666 DRN 4567 | 4 | 543 S Way. 7890 | 5 | zzz ABC
Notes Table: (Yes the notes field is a MEMO field) Account_Num - Sub_ID - Notes 1234 | 3 | notes1 1234 | 3 | notes2 1234 | 3 | notes3
The output I am trying to Get: Account_Num - Sub_ID - Data1 - Notes 1234 | 3 | 123 N Inc. | notes1 notes2 notes3 1234 | 3 | 666 DRN | notes1 notes2 notes3 4567 | 4 | 543 S Way. | NULL 7890 | 5 | zzz ABC | NULL
I am trying to concatenate multiple rows into one record. I googled it and found many functions in VBA that do the job. However my problem is that my query will be linked directly to Excel file and then I get an error message saying that Excel could not recognise this function. I could potentially insert data into new table and link that table to Excel but I need to avoid end-user exposure to Access as much as possible.
So I am desperately trying to find a way of doing this without VBA code.
Example of data:
Customer Name |Order Number Smith |O101 Brown |O102 Smith |O103 Green |O104 Brown |O105
I am trying to achieve below:
Customer Name |Order Number Smith |O101, O103 Brown |O102, O105 Green |O104
