joining unrelated tables

[angelac]

I have two unrelated tables. One tracks customers who have cancelled service. The other tracks customers that have cancelled but still have a balance owing. I need to generate a report showing all cancelled customers. I tried several variations but I just can't seem to design a query that will combine the records into one report. Is it even possible? Please help...

 

[HelenE]

Do you have common fields in these two tables, such as a customer identification number or customer account number? If so, you can join them in a query. You could then enter criteria to selct only the cancelled customers and generate your report from there. HTH

 

[angelac]

There are no fields common. The records in the cancellation table appear only in this table and the records in the other appear only there. I have a customer table but once the customer cancels, all record of him is removed from this table and transferred to the appropriate cancellation table. Does that answer your question? Thank you for your response.

 

[shacket]

You can still use a Union Query. In reading your response, I am not sure if you understood what Helen was getting at. (If you did, I apologize). She was asking if there is a Primary Key involved that remains with the customer when they cancel that would be found in each table.

Even if there is not, you can Union the two together. You need to write Union Queries is SQL language (it is not that hard and the help files are excellent if you are not familiar with it). It would look like this:

SELECT [Name], [Address], [Balance] As [AmountStillOwed]
FROM [tblCancelledAmountStillOwed]

UNION SELECT [Name], [Address], "0" As [AmountStillOwed]
FROM [tblCancelledOweNothing]

The "AS" statement allows you to import a field from a table and rename it.

HTH

 

[angelac]

Thank you very much. The union query was just what I needed. My report now works great!