Merging 2 tables together

[welshone]

How can I Append data from 2 tables into one.

so :

append table 1 into master table where staffID=StaffID and append table 2 into master table where staffID=staffID also
only append if the modifydate is greater than the date in the master table .


cheers.

 

[Telsa]

You can use a UNION query. You will have to manually key it in and not use the query grid. But if you go into Query and select SQL Specific, you can write it there. It'd be something like this: (this was taken from Help in Access)


In the Database window, click Queries under Objects, and then click New on the Database window toolbar.


In the New Query dialog box, click Design View, and then click OK.


Without adding tables or queries, click Close in the Show Table dialog box.


On the Query menu, point to SQL Specific, and then click Union.


Enter SQL SELECT statements combined with either the UNION operation if you don't want to return duplicate records or the UNION ALL operation if you do want to return duplicate records.


SELECT [Company Name],[City] FROM [Suppliers] WHERE [Country] = "Brazil"


UNION SELECT [Company Name],[City] FROM [CUSTOMERS] WHERE [Country] = "Brazil"


Note Each SELECT statement must return the same number of fields, in the same order. The corresponding fields are required to have compatible data types with one exception: You can use a Number and a Text field as corresponding fields.


For information on SELECT statements, click For information on the UNION and UNION ALL operations, click .

If you want to specify sorting in a union query, add a single ORDER BY clause to the end of the last SELECT statement. In the ORDER BY clause, specify the field name to sort, which must come from the first SELECT statement. For information on the ORDER BY clause, click .


To see the query's results, click View on the toolbar.
Mary

Rule 1: Don't sweat the small stuff.
Rule 2: EVERYTHING is small stuff!! X-)

 

[welshone]

Excellent Mary, thats the first part, so now I have this :

SELECT [Forename], [Surname],[ModifyDate]
FROM [tblStafffromhome1]

UNION ALL SELECT [Forename], [Surname],[ModifyDate]
FROM [tblStafffromHome2]
ORDER BY [Surname];

but If I have say to Jo Bloggs I want to show the one with the newest modifydate.....


thanks again,
J
(like the spinny eyes)

 

[Telsa]

Create the Union query first. Then create a second query based on the Union query where you can sort descending the modifydate.

BTW: I think's its UNION SELECT not UNION ALL SELECT and I don't think you can do an ORDER BY in a UNION query. Mary

Rule 1: Don't sweat the small stuff.
Rule 2: EVERYTHING is small stuff!! X-)

 

[welshone]

I have now created the union query then a query based on the union showing the max(modifyDate).
problem is I am now trying to run an update but it doesn't seem to work, can you see why ?

UPDATE MaxUnion SET MaxUnion.staffID = [tblstaff].[staffid];

 

[Telsa]

Is MaxUnion your Union query? Can't update union queries I don't believe. You would need to build a table from the query and Update it instead. Mary

Rule 1: Don't sweat the small stuff.
Rule 2: EVERYTHING is small stuff!! X-)