Subtracting Access tables

[PeterCol]

I have 2 tables.

Table 1 contains 2 single field records in it, one containing the number 1 the other number 2.

Table 2 has one record containing the number 1.

I want to subtract table 2 from table 1 to end up with a third table which will have one record containing the number 2.

How do I construct a query to do this on the access design grid or using Access SQL?

Thanks in advance.

 

[mdmilner]

SELECT [Table2].[fld1]+[Table3].[fld1] AS answer FROM Table2, Table3;

Go to the QBE wizard. Open up a table based on the first Table chosen.
Right-click in the gray area above, and 'add' the second Table.

Go into the field listed for fld1 in Table2. Change it to whatever name you want the answer to be ('answer' is above) - put a colon, then reference the specific fields you want with a + sign in between. If both fields are number format, it will add them.

Alternatively, once in QBE, go into SQL mode and type the above.
---
All that said.

Why do you want to do this? My thought is, that despite how quick this is to setup, perhaps you're approaching your actual work problem incorrectly.

Mike

 

[PeterCol]

Thanks Mike. What you have given me adds the values of the fields in Table 1 and 2 and puts the concatenated result in table 3.

My actual problem is that I have an appointments table listing customers and the date of their appointment. The table contains 60,000 records with most customers having numerous appointments. I want to determine which customers have no appointments planned for the future.

I can easily determine those with future appts and I can create a table listing all customers. Taking one away from the other would leave me with a list of those without future appts, but access does not support subtraction in this way. I am hoping someone knows the work around.

Thanks

 

[Dugantrain]

Ok, this is what you do:
You're going to need a field in the table which will uniquely identify each record (I use Autonums in every one of my tables for this task, but if you already have something to identify each Customer record, that'll work too).

-Write the query which finds all Customers with future appointments.
-Write another query and base it off of this query. In the Query Design, you're going to Add the original table as well as the query. You want to join your table to the query on the Unique Identifier field. Make the join an Outer Join (show all records in the original table and only those records in the query which are equal).
-Pull in whatever fields you want to see from the original table. Pull the join field from the query. Unclick 'show'. In the criteria of this field, write:

Is Null

You will now have a query which shows all records of Customers without future appointments.

 

[PeterCol]

Dugantrain

Most Customers with future appts also have past appts. Your method removes the future appts from the resulting table but leaves the past appts for those customers still there, so I still can't identify those without future appts.

 

[PeterCol]

Dugantrain i got it. You set me on the right path. I needed to:

1. uniquely list all customer codes
2. uniquely list all customer codes with future appts
3. Join 1 and 2 with allrecords from 1 included and only those matching in 2.
4. In the join show customer codes from 1, include customer codes from 2 but hide this field and set criteria to "is null"

Thanks for your help.