Union vs Append Query

[TartanSpecial]

Hi There

I have a real beginners question about Union queries. Am I right in thinking that an Append and a Union queries will return the same values

If this is the case is the difference that the Append queries actually updates a table wheras the Union query simply shows the data in a query form (ie does not create or update any existing table)

If this is the case is it fair to say that using a Union query on large data sets is the quicker method ?

Any advise on Union vs Append queries greatly appreciated

Nick

 

[Norie]

Nick

It really depends what you are doing here.

You would use a UNION query to combine records from tables that have a
similar data structure.

And no on a large dataset it wouldn't be quicker, not in my experience anyway.

Perhaps you could give us some more information on what you actually want to do?

 

[TartanSpecial]

Thanks very much for getting back to me

We have two tables which are being pulled in as text files. The first one shows all our Contacts ID who have been contacted by our Salesteam (Salesperson ID) and the other shows all those contacts which are on our Salesteams list. So effectively we have

Table 1 - Contacted

Salesperson ID, Contact ID

Table 2 - On Contact List

Salesperson ID, Contact ID

The business would like to know which ones appear only on one list (and if so which one) and which appear on both. Both tables are quite large as our salespeople contact sometimes numerous contacts/clients

How I thought of doing it was to create a Union Query between the two and add two fields, one a combination of Salesperson ID and Contact ID, the other a score....say 1 for being in Contacted and 2 for being in On Contact list.

I then thought of combining these in a Group by query on the combined field and sum the score. So 3 = on both lists etc

I'm not sure if this sounds the most logical way so if I'm going about it the wrong way please let me know. If this is the correct way then is it better to use a Union than an Append ?

Again, thanks very much for your help. It's greatly appreciated

Nick

 

[SydneyGeek]

Nick

Try using the Find Unmatched query wizard on both tables. You can run it both ways to find --
1. Those in Table 1 but not in Table 2
2. Those in Table 2 but not in Table 1

Use CustomerID as the common field.

To find those that appear in both, run a standard query where you join the 2 tables on CustomerID. Any records that appear in the query represent customers whose ID is on both tables.

Denis

 

[TartanSpecial]

Thank Denis

I thought of using the Unmatched query but I need to run the comparison on two fields - Contact ID plus Salesperson ID ie a contact may be contacted or on the list of several different salespeople. Is it possible to run a unmatched query on two fields ?

Thanks

Nick

 

[SydneyGeek]

Yes. The Wizard may not do it for you, but go into Design view.

Say, for example, that you had ContactID as the join field. You'll see that the join is asymmetric -- it has an arrow to the left or right. Right-click the join arrow and pick the Properties. Check to see which of the join types (1, 2 or 3) is selected.

OK, now create a join using SalespersonID. Right-click the join and set it to the same type as the ContactID join.

Now look at the fields. You will see ContactID from one of the tables, with Is Not Null in the Criteria row. Do the same for SalespersonID from the same table.

That should set you up.

Denis

 

[TartanSpecial]

thanks a lot for your help guys will give it a try

Cheers

Nick