Update query to merge two fields into one

Hey all,

New to access but learning every day. I'm working to merge two access databases so I can retire the old one. I'm currently working on merging multiple fields from one table into one table that will allow one--> many. In the current setup [CFISupp] any particular CFI can only have 1 C152 Check.

Data in separate tables marked with [] 1st Table is [ACAuth] and Second table is [CFISupp]. In [ACAuth] Type Check refers to a specific field in [CFISupp] like 1=C152check, 2=C172check. In [ACAuth] the Date field should be copied from the date fields C152check and C172Check. CFIID is a unique person. Disregard "" in the example, they are only used to explain my meaning, not to indicate I want a string. Example..

[ACAuthID"Auto Number" / Type Check/ Date / CFIID] ................ [CFIID / C152Check / C172 Check]

For Example in [CFISupp] I have
.................................................. .......................................[200 / (1/1/2012) / (1/15/2012)]

I would like it to transfer to [ACAuth] like
[1"autonumber" / 1"Type, because date of check came from field C152Check" / (1/1/2012) "date from [CFISupp]![C152Check]" / 200 "CFIID=200"
[2 "autonumber" / 2"Type, because date of check came from field 172check" / (1/15/2012) "Date from [CFISupp]![C172Check]" / 200 "CFIID = 200"

or numbers only
[1 / 1 / (1/1/2012) / 200]
[2 / 2 / (1/15/2012) / 200]

With this format I can allow the user to input a second C152 Check, making the table look like

[1 / 1 / (1/1/2012) / 200]
[2 / 2 / (1/15/2012) / 200]
[3 / 1 / (1/20/2012) / 200]


I've tried some basic update queries to accomplish what I am trying and I can get the C152 check data over into the [ACAuth] table but when I try to update C172 check data it over writes the 152 date with the 172 date for the same CFIID. I want it to add a new record with the same CFIID and the 172 date.

Help is appreciated! Hopefully my description makes some sense.
Chris


Post your answer or comment

comments powered by Disqus
How using SQL query in Acces to combine two fields into one.
For example:
Field1=Month
Field2=Date
===>
Field3=Month & Date

How to merge two fields at one in report

This is my problem...I have a very simple query SELECT Tigers.[Error Code], Tigers.[Error Description] FROM Tigers; and it runs just fine however what I am trying to do is combine these two fields into one. All this information is in the same database. Thanking You in advance for your assistance in this matter.

Hi all. Is there some way to merge 2 tables into one in a query? Is it possible? Thank u.

Dear all
I need to merge a number of databases of the same type into one database in access. can you please help me with code itself.
thanks.

Ampah

Hi guys, first post

my problem is quite simple (as far as i can tell) but none-the-less i'm stumped.

what i want to do is merge 2 fields into a separate field with the first field being Unique_Id (autonumber, indexed and no dupes) merge with the second field Type (3 letter field) so it should populate the third field automatically as NDE123 etc , but what i also need (which looking around the internet for afew hours held no aveil) is for it to be automatic even when a new records is made, so when a new record is inevitably made the first field will be autonumberd, the 2nd will have a default value, so it will mostly be entirly automatic, is this possible? or am i clutching at straws.

Thanks, Callum

Hi,

I am creating a relational database for my A-level ICT coursework project (this is the first time I've ever used database software) and am trying to create an update query. Essentially, I have a table of parts (tblPart) and a table of parts that have been used (tblPartsUsed) and I need to create a query that will subtract the value in the 'Quantity' field in tblPartsUsed from the value in the 'QuantityInStock' field in tblPart. I assume I need to put something into the "Update To:" field in the query design, but I don't really know how to do this.

For instance, if there are three Seagate Momentus HDDs in stock, and one is sold, I need the value in the 'QuantityInStock' field in tblPart to decrease to two.

Thanks in advance for any help.

Hi all

I am trying to do a query where I pull data from 4 tables. The 4 tables are Quarter1, Quarter2, Quarter3 and Quarter4. From each table I want to pull PayDate and Done. Each PayDate and Done is prefixed by the appropriate Quarter e.g. QTR1PayDate, QTR1Done.

PayDate is a date/time field and Done is a Yes/No. The idea is that once the PayDate is done, the checkbox is ticked. A report can then be run which only outputs the unchecked boxes at the end of each week.

The problem....

I can query all the fields easily enough but I want to be able to run a report where the dates can cross over the Quarters (e.g Quarter1 = Jan, Feb, Mar) so if I wanted to know Mar and April I could run the same query and not two different ones.

The way I had it working was to do a union query and merge the fields into two columns called Quarter and Done in a table called PayDate. This worked fine when I updated the checkbox in the table itself but not through a form.

I basically either want to update data in 2 places (PayDate and Quarter1 etc) or somehow query the columns into one.

I hope this makes some sort of sense and appreciate any help!

Thanks in advance

I have a database with two tables, Buyer and Products, with one-to-many relationship. The key of Buyer is an autonumber ID, which is the foreign key to table Products.

The problem is that i have two copies of this database with different records (with ID starting from 1 to each one) and i want to merge the databases into one.

Any ideas how to do this without lose the connection of the records?

Thank you in advance.

Hello all,

I wanna ask about SQL codes on how to join 2 fields from 2 different tables in Access into 1 new field in new table proposedly. It is not that I want to combine those two fields into 1 new combined field like this. (many turned up giving this solution)

First Field (First Name) from Table A: John
Second Field (Last Name): Stuart
Third Field (First Name) & (Last Name): John Stuart

I have different case whereby I want

First Field (Product) from Table A: Book
Second Field (Product) from Table B: Pencil
Third Field (Product): Book
Pencil

The two values would not be combined. In other way round, if the Product in Table B does not exist in Table A, it would be added to a new row in the new field (Third Field).

Hoping for feedbacks. TIA.

Say I have table A with fields 1, 2, 3, 4, and 5 and then a table B also with fields 1, 2, 3, 4, and 5. I want to merge table A into table B.

I want to take entries 3, 4, and 5 from table B and match them up in table A where both entries 1 and 2 are the same.

For example, if table A and table B had the same entry in both the first and second fields, take the third, fourth, and fifth from table B and put them into table A. If fields 1 and 2 do not match up in both tables, do nothing and leave table A alone in that field.

I am trying to use an update query to copy 4 fields from the current record on a form to a different table. A command button will execute the query, as well as other commands, via a macro.

"Edit Patient" is the form which the user uses to navigate the main table
"Print Template" is the table that I would like to copy the data to

Here's what I have so far (which updates absolutely nothing):

UPDATE [Print Template] SET [Print Template].[First Name] = [Forms]![Edit Patient]![First Name], [Print Template].[Last Name] = [Forms]![Edit Patient]![Last Name], [Print Template].[D/A] = [Forms]![Edit Patient]![D/A], [Print Template].DOB = [Forms]![Edit Patient]![DOB];


No criteria are necessary. If the command button is clicked, I want the update to occur.

Where am I going wrong? (novice user here, so be gentle)

Hi I have a problem where I want to concatenate many fields into one field, separated by a , or a ;

The table on the left shows the ID listed many times and it is only listed once on the right. In the names field on the right table I want the names from the left table listed to be separated by the , or ; based on the ID. The tables below are just an example of a much larger database.


I work on the database someone else created. Now I want to merge two data fields into one. How do that?

Thanks for any help!

Hello,
I don`t know if it is even possible, maybe it is simple. I want to merge two queries in one. I have:

TABLE

	Code:
	NR    TYPE    TEXT1    TEXT2
1    ML     TEXT    TEXT
2    ML     TEXT    TEXT
3    ML     TEXT    TEXT
4    ML     TEXT    TEXT
5    ML     TEXT    TEXT
6    ML     TEXT    TEXT
7    ML     TEXT    TEXT
8    ML     TEXT    TEXT
9    ML     TEXT    TEXT
10   ML     TEXT    TEXT
11   ML     TEXT    TEXT
12   ML     TEXT    TEXT
1    NA     TEXT    TEXT
2    NA     TEXT    TEXT
4    NA     TEXT    TEXT
7    NA     TEXT    TEXT
9    NA     TEXT    TEXT
10   NA     TEXT    TEXT
11   NA     TEXT    TEXT
4    NA     TEXT    TEXT
7    NA     TEXT    TEXT
2    ML     TEXT    TEXT

---------------------------------------------------
[Query N]

	Code:
	NR
1
10
11
2
4
7
9

SQL: SELECT DISTINCT [NR]
FROM [TABLE]
WHERE [TYPE] Like "NA";
------------------------------------------------------------------
[Final Query]

	Code:
	 
TABLE.NR    TYPE    Query N.NR    TEXT1    TEXT2
1           ML      1            TEXT    TEXT
2           ML      2            TEXT    TEXT
3           ML                   TEXT    TEXT
4           ML      4            TEXT    TEXT
5           ML                   TEXT    TEXT
6           ML                   TEXT    TEXT
7           ML      7            TEXT    TEXT
8           ML                   TEXT    TEXT
9           ML      9            TEXT    TEXT
10          ML      10           TEXT    TEXT
11          ML      11           TEXT    TEXT
12          ML                   TEXT    TEXT
1           NA      1            TEXT    TEXT
2           NA      2            TEXT    TEXT
4           NA      4            TEXT    TEXT
7           NA      7            TEXT    TEXT
9           NA      9            TEXT    TEXT
10          NA      10           TEXT    TEXT
11          NA      11           TEXT    TEXT
4           NA      4            TEXT    TEXT
7           NA      7            TEXT    TEXT
2           ML      2            TEXT    TEXT

SQL: SELECT TABLE.[NR], TABLE.[TYPE], [Query N].NR, TABLE.[TEXT1], TABLE.[TEXT2]
FROM [TABLE] LEFT JOIN [Query N] ON TABLE.NR=[Query N].NR;
---------------------------------------------------------------
I want to put [Query N] into [Final Query], so there would be only one table [TABLE] and one query [Final Query].

I don't understand how to do this. At first i think about something like this:

	Code:
	SELECT TABLE.[NR], TABLE.[TYPE], (SELECT DISTINCT [NR]
FROM [TABLE]
WHERE [TYPE] Like "NA") as TEST, TABLE.[TEXT1], TABLE.[TEXT2]
FROM [TABLE] LEFT JOIN [???] ON TABLE.NR=???.TEST;

but it has no sense:

Hello Everybody,

I'm not really great at using Access, but I need to make a simple application, and one issue is stumping me. I have two tables: INBOUND_INVOICE_TBL

[Invoice Number] --Text
, [Part Number] --Text
, Description --Text
, Quantity --Number
, [Quantity Received] --Number
, [Receipt Complete] --Yes/No


RECEIPT_TBL

[Invoice Number] --Text
, [Part Number] --Text
, [Quantity Received] --Number

I need an update query to perform two actions on the inbound table when the inbound invoice is still open (denoted by the Yes/No field INBOUND_INVOICE_TBL.[Receipt Complete])

The first action is that Sum(RECEIPT_TBL.[Quantity Received]) grouped by [Invoice Number] and [Part Number] should be updated to the field INBOUND_INVOICE_TBL.[Quantity Received]

The second action is IIf(Sum(RECEIPT_TBL.[Quantity Received]) = INBOUND_INVOICE_TBL.Quantity,-1,0), again grouped by [Invoice Number] and [Part Number]

For both actions, the [Invoice Number] and [Part Number] fields should exist in both tables.

I have been looking around on this site and some others, and I believe the first calculation needs a DSum() function, but I haven't been able to get that to work, and I have no idea on the IIf function. Any help you could provide would be greatly appreciated as this is driving me nuts.

Hi, all.

I really need help.

I have to consolidate clients with two different ID numbers into one ID number. There are 41 clients (in tables with 100,000+ records), each with two different ID numbers, so I potentially need to change 82 ID numbers throughout 100 tables. The 100 tables may or may not contain the id number I want to change. As an example, “Client 1” has a Current IDs of 6954 and 10870, so I want to change both current ID's to 88101 across my 100 tables. Each table has only one of the Current ID’s.

I also have a TableID that has the Current ID and NewID for these 41 clients if that is useful. I can write an update query for each ID for each of the 100 tables. But that takes so long to do and is prone to error. Is there an easier way to do this? I am fairly new at SQL but have been using Access for several years (though not at a proficient level). I have Access 2010.

Thanks all for your help.

Katie

I have a form with a combo box for a lookup. Right now it is looking people up based on their Current Family name. I also have a field for maiden name and I want to combine both the current name and maiden names into one column (not concatenate) so people can look up by using either. I know how to use a Union query to do this with information from two different tables, but how do you do this with data from just one table. Any ideas????

Hi there.
Does anyone know how to use an update query to copy some records from one table into another?
I have table1 which has all the contact details and what I would like to do is work out a process for importing records from an excel spreadsheet.
What I have so far is as follows:

1. In access go into the queries tab and select update query
2. Open both table1 and table2
3. Drag the fields to be updated (table1)
4. In Update to select [table2].[table2 column name]

When i run this nothing appears to happen. 0 records are appended.
Does anyone now how to go around this with another solution or should I be using other SQL methods?

All comments much appreciated
Thanks

Hi, I'm totally new to access, so please forgive my limited knowledge. I have to coordinate a dbase in the US, one in the UK, and one in the KSA. I have multiple documents that may, or may not, be in each of the three dbases. I need to merge all three, and maintain them so that the document additions, as well as any changes associated with that document, (i.e. "revisions"), are updated. Having looked at access and having read through some of the Access forum questions, I understand that it can do that, but after having identified the information from the dbases that will go into access, I'm trying to establish a consistant process for maintaining and updating it. Can I export my information from each of the dbases, into an excel spreasheet, (1 for US, 1 for UK, and 1 for SA), and have the access dbase search for the document number, compare the data following the document number, and if it's the same ignore it, and if it's different, update it?

Is that possible? (Between the three dbases, we're talking of approximately 80,000 documents.)

I'm trying to muddle through this and get access and excel to do as much of this as possible, as it's a very tedious and time consuming process to manually check each one, one or two times a week.

Any information, suggestions, or condolenses would be appreciated!

I have two recordsets(rst) one created in the current DB by a local query and the other created from and external DB with a second query. Both recordsets contain very similar data. Now I would like to merge the two recordsets. I would like to do this with a third query rather than a VBA process loop. To accomplish this would I need to create a temporary tables for both RSTs and then run the third query on them or can I run a query against the two RSTs?
By 'merge' I mean to add most records but remove the earlier of any records that have 4 specific columns that are equal. (Each record has a transaction date).

Good evening all,

I am trying to combine duplicate values in a query record to create one value for the duplicate values. Attached is a copy of the query design I am working with.Publication1.jpg What I would like to achieve - if column one in record one is the same as column one record two, three, four, etc., I would like to combine these fields into one field.

Thanks as always for any and all help offered.

I have a table and would like to merge all the fields into one text field of the table after the insert.
I'm using adp (access/sql server).

When using standard sql the null values give a problem and i don't want to use the if clause as it will give too much coding.
Example

fld 1 fld2 fld 3 mergefield
aaa bb aaabb
aa ee aaee
etc

when using select fld1+fld2+fld3 the null values cause whole rows to disappear. Anybody knows an easy workaround?

I have two fields the have differing dates in them. I need an action query to make the date in one field the same as in the other. I have thousands of records to do this to. Is there a way via an action query (update?) to do this or does it have to be an SQL statement query?


Not finding an answer? Try a Google search.