Combine fields from different rows into one field
Combine same IDs in report but with Different Values
Is it possible to combine rows with same channel ID's but with different values? for ex: in pic, you see the ID B0175 with 11 rows. but retain thevalues from all eleven rows and put them in one line? so it would display a "1" in whatever month it belongs to
Exclude rows to get to data/field names
I've got a HUGE database to create. I have 60 GB worth of excel files or so I need to combine and the first 6 rows are things such as company name, which machine created the spreadsheet and so forth. The field names are on row 6. How do I make it so the wizard knows my fields names are on row 6 and the data needed starts on row 7? I would go into the original files and delete them, but I'm not allowed to manipulate the data, not to mention it's 60 GB worth of files and that alone would take forever.
Also side note, When I combine two spreadsheets in a query, will the database understand to put a N/A or whatever in the value if one spreadsheet has an extra field name
Concatenate multiple rows
Is there a good way to concatenate data from multiple rows into one field? I have several rows with different dates and I need to combine the comment text for each of the dates into one field and group it as one record. How can I do that?
Union Query (choosing between two fields)
I'm trying to combine a few queries with a union query and was wondering if something like this scenario is possible to code:
Basically, there is an ID field (column 1) that I've already put in place and then I have two fields of values to choose from (Field A and Field B) so that I will end up with two columns in total. If Field A = 0, I'd like the query to take the value from Field B and multiply it by -1 and have that be the value in column 2. If there is already a value besides 0 in Field A, I'd like the query to take that value.
Also, if the ID field is blank or is labeled 'Excluded,' I'd like the query to leave those rows out.
Combine City State, Zip into one display field
I have a field on my form that I need to combine my [city] [state] and [zip] fields into one field, BUT ALSO make that field editable. I already know how to simply combine those fields using =[city]&" "&[State]&", "&[Zip] in the control source, but although that displays everything in the correct format, I need to be able to edit it also.
So for example if the field were to display "New York NY, 10011" and I wanted to change it to "Manhattan NY, 10012" I could just type it into the field and it would update the apropriate fields.
I have a table that consists of 10 fields. Six of the fields are populated from a drop down list. Some of the lists have as many as 12 choices to choose from and some, as little as 5. I want to create a report that will show me the number of time each one of the possible choices in that particular field has been chosen. All six fields are text.
Currently I have six different reports each based on a different query. What I have done for each of the reports is created a header group and put the field I want to count into the header group plus a =Count("*") control text box. This gets me the data but only for one field. I then export each report to an excel file and combine the results from all six. Is there a way to combine this into one query to populate one report?
Looking for some help again. I have a table that stores training records so if employee A went to training they enter the data on a form and save it so a new record is created in this table. Now if employee B goes to the same training later that year it creates another record in the table. Now through some queries I am able to make another table look like I have in the example picture provided. But when I create a report and group by the training I get 6 rows since there are the 6 records. I was hoping to combine the records through queries or maybe another way so that they can be grouped so that I would really only have 2 rows one for Mike and one for John. I appreciate
form formula to combine two fields
I've been trying (with no luck) to combine two fields in a form.
The 1st field is TD1 and the 2nd is TD2.
I've tried all type of combinations but none of them work except for the following:
=[TD1] + [TD2]
The contents of these fields are 10 and 20 respectively.
BUT, it creates 1020 and I'd like the results to be 30 (add them together) instead.
I've looked all over the place but can't find anything that addresses this.
I also tried to do this within the query by adding a new field TD1TD2 but I also couldn't figure out how to get that to work
dlookup in access
I have a table from which I want to select an inteval of rows. I then want to summarize one field from the selected rows. The selection criteria can identify each of the rows.
For each selection of rows there will be a different amount of rows. How do I create a query that selects the rows that will create the answer I am looking for.
How shall I enter the selection criteria for the first and the last row to get all rows in between?
I've tried several things I have found around the net, including here. I'm assuming (based on the other code I have tried) this will have to be VBA?
I am trying to combine a cell from different rows in the table.
Combine the values of two fields on a form
I want to take the existing values in two fields on a short form and combine them to create a unique ID for each record.
The first field is a text field that contains an acronym representing a company, e.g., USPS. This value will be selected from a Combo Box.
The second field contains a date (Short Date format, 10/11/2010), which will not necessary be the current date.
I want to change the Date field into a yyyymmdd format, insert a period at the end, and then append the acronym field so I'll end up with something that looks like this:
The ultimate goal is for this to run as an On Exit Event Procedure after I enter the date and move on to the next field on my form.
I have a query with six columns. The first column is item description and the next five columns are numeric fields. In some rows, certain numeric fields have zero and the other columns have some integers. In some rows all the five columns have have zeros.
Is it possible to hide the rows only where all the five numeric field collumns have zeros
Introduction to Expressions and Operations
The data fields we have used so far were created in tables and then made available to other objects (forms and reports), so those objects can implement their own functionality without worrying about displaying empty or insignificant fields. In various scenarios, you will need to display a field that is a combination of other fields. For example, you may need to combine a first name to a last name fields in order to create a full name field, or, to calculate an employees weekly salary, you may need to retrieve the value of a Salary field and multiply it with the value of a total number of hours worked in a week.
Combine two fields into one field
I need to combine two number fields into a third field. The 'AutoNumber' field with the 'Class' field into a new field called 'Owner Number.' So, if someone's 'AutoNumber' was 1 and their 'Class' was 1, their 'OwnerNumber' would be 11. Thank you very much. I attached a Workbo
How to detect user pasting multiple rows into a datasheet
I have a datasheet application that updates additional fields based on the input of one field using VBA code. Due to the design constraints, I use the following technique.
1. After the field is updated, I do a Refresh to save the row
2. Then I retrieve the other values I need using VBA code and save them.
3. Finally, I read the saved row, update the additional fields, and update the row.
This works fine as long as the operator enters the new row manually or even if he/she does a copy & paste of one row. However, if he/she copies and pastes multiple rows, then the Refresh doesn't insert the multiple rows and my step 3 above fails. Is there a way I can detect that multiple rows are being inserted so I can handle the event accordingly?
Combine two fields, Null fields involved
Title may not make much sense, so if you have come this far then stick with me.
I want to combine two fields for a form and probably on a couple of reports as well. I want a comma to show if both fields contain a value, however, if only one of the fields has a value and the other does not, then I do not want a comma.
For example, there are college athletes that have played some international ball before playing collegiality.
Lets say I have a field called 'College' and another as 'International Club.'
Obviously, I know I can display this as Team: [College] & ", " & [International Club]. However, by doing this, for any player with just International experience and no College, their field will display as ", International Club."
To sum things up, I just want a comma to show when it is necessary
Hide Fields With Null Values
I need to prevent blank rows from displaying in a report. The blank rows represent fields with null values, which appear in almost every record.
I have attached a simplified example with two columns. The left column contains labels and the right column contains text boxes. In this example, there are three blank rows. I would like toprevent these blank rows from displaying, and I would like the first three fields from the next record (not shown) to replace the blank rows.
In the Details Section and in text box properties, I have the Can Grow and Can Shrink properties set to Yes. I have tried a variety of solutions but nothing has worked (probably because I am not correctly applying the solutions!).
How to fill same data for a range of rows under a specific field
I have a table of 5 fields and more than 100,000 records(rows). I need to fill a certain range of rows(example 4,500 - 95061) under a specific field (column) with a fixed data say 12:05 AM. Could you help me how to do it? If I need any VB function please let me know the codes. Example:
ID Fld1 Fld2 Fld3 Fld4 Fld5
Now I need to replace data's for ID 4500 - 95061 under Fld3 with "12:05 AM
combine 2 fileds to make a primary key field using formulas (not SQL programing)
I am a new Access user and not quite up with SQL programming yet. I am trying to combine 2 fields (Build and Unit) into 1 and make that my primary key field. I was hoping to use a formula similar to that in EXCEL but cant find anyway to do this - can someone guide a novice through this (Access
How not to count empty fields in query
I must say that I just recently started working with access 2010 (and databases in general) so I need help.
There is a huge table with some 150 rows of data, now I know how to select data. What I want to do is count the number of entries in each row, but to exclude the empty fields (ones with null value), so I count only the ones with data in. There is an ID and DATE field that I use to filter for what period I want the rows calculated, but I always get all the fields counted even if threre are empty ones without data.
this is what I got so far (counting the rows 24, and 25)
SELECT Count(24) AS REC, count(25) AS REC_K
WHERE (((anketa.id)=[ID]) AND ((anketa.date)>=[From] And (anketa.date)<=[To]));