Query to Combine results from two tables (1 Viewer)

chineloogbonna

Registered User
Joined
Jul 30, 2018
Messages
65
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

Wino Moderator
Staff member
Joined
Aug 30, 2003
Messages
33,684
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

I’m here to help
Joined
Oct 29, 2018
Messages
7,846
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

Registered User
Joined
Jul 30, 2018
Messages
65
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

I’m here to help
Joined
Oct 29, 2018
Messages
7,846
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...
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,846
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

Wino Moderator
Staff member
Joined
Aug 30, 2003
Messages
33,684
I should have mentioned that the data would typically be in one table, with a field to designate which were closed.
 

pbaldy

Wino Moderator
Staff member
Joined
Aug 30, 2003
Messages
33,684
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.
 

pbaldy

Wino Moderator
Staff member
Joined
Aug 30, 2003
Messages
33,684
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.
 

pbaldy

Wino Moderator
Staff member
Joined
Aug 30, 2003
Messages
33,684
I think you'll be happier in the long run.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom