Query to Combine results from two tables

[chineloogbonna]

Hello everyone,
I've been working on trying to combine 2 queries for the last couple of weeks now and I've finally decided to give in and ask.

I have 2 different tables "openCalls" and "closedCalls" . I am trying to run a query that searches both tables.

I've tried Inner Join and relationships and each time they provide results for one table. Not sure what I'm doing wrong.

Thanks.

 

[pbaldy]

Sounds like you want a UNION query:

SELECT Field1, Field2
FROM OpenCalls
UNION ALL
SELECT Field1, Field2
FROM ClosedCalls

Search on union queries for the peculiarities.

 

[theDBguy]

Hi. I'm just curious... Why do you have a separate table for open and closed calls? Do you move open calls to the other table to close them?

 

[chineloogbonna]

The reason I created closed and open tables was to cut down on table size to help with speed. Not sure if that helps?

 

[theDBguy]

The reason I created closed and open tables was to cut down on table size to help with speed. Not sure if that helps?

Were you experiencing problems with speed? How many records are in both tables combined? If you use a UNION query to combine the contents of those two tables, would you have the same problem with speed again, before you split the tables? Just curious...

 

[chineloogbonna]

I haven't had any speed issues yet but I was preparing for future performance.

 

[theDBguy]

I haven't had any speed issues yet but I was preparing for future performance.

And how many records would you ever have in both tables, combined? I am just thinking you may be preparing for something that may never happen. And as a result, you are probably making your life harder than it needs to be.

 

[pbaldy]

I should have mentioned that the data would typically be in one table, with a field to designate which were closed.

 

[chineloogbonna]

Do you generally archive your records after a certain amount?

 

[pbaldy]

Generally no. I've got tables with millions of records spanning 20+ years that perform just fine. I do have a commercial limousine reservation app where they did archive records once the reservation had occurred. That said, an updated version of that app does not archive records.

 

[theDBguy]

Do you generally archive your records after a certain amount?

Hi. If you're asking me, I don't. If your backend is Access, the only limit I suggest you worry about is the file size.

 

[pbaldy]

I should add that being able to not archive may depend on proper design. You'd never open a form bound directly to a large table, thus trying to pull all records. You'd open it filtered via wherecondition or query so it only loaded the necessary records. Indexing will allow the database engine to pull those records quickly.

 

[chineloogbonna]

Thanks to all of you! I will stick to one table.

 

[pbaldy]

I think you'll be happier in the long run.

 

[theDBguy]

Thanks to all of you! I will stick to one table.

Hi. Good luck and let us know how it goes. Cheers!