Store Query Results in a Temporary Table (1 Viewer)

JithuAccess

Member
Local time
Today, 05:29
Joined
Mar 3, 2020
Messages
297
Hello Guys,

Could you guys please let me know how to store query results in a Temporary Table?

I was preparing a Weekly, Monthly Yearly Mail Logs Report. There are 8 Tables belongs to 8 Different Departments. By running 8 Different queries I am capturing Total Incoming and Outgoing Correspondences of these 8 Departments. Then I created 1 main Report and 8 Sub Reports in that Main Report to show the total Correspondences of all the 8 Departments.

I think this is a very unprofessional way. I want to store the query results in a Temporary Table and in this Table I will be storing the Total Incoming and Out Going Correspondences of 8 different Departments. Then I will be creating 1 Report and giving this Table as Record Source. This would be for Weekly Stats and then I will be creating 2 other Temporary Tables for Monthly and Yearly stats and then Reports. So there will be only three Reports.

This would be my Table:

1619186713236.png


and in this Table I will be storing the Query Results

Could you please let me know how to do this?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:29
Joined
Oct 29, 2018
Messages
21,358
Hi. Why do you have 8 separate tables in the first place? You may not need a temporary table. Have you tried using a UNION query instead?
 

JithuAccess

Member
Local time
Today, 05:29
Joined
Mar 3, 2020
Messages
297
Hi. Why do you have 8 separate tables in the first place? You may not need a temporary table. Have you tried using a UNION query instead?
Because we have 8 different Departments.

Let me try Union Query.

Thanks for your suggestions
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:29
Joined
Jan 23, 2006
Messages
15,364
What exactly is the rationale for this?

There are 8 Tables belongs to 8 Different Departments.
It seems that the basic design may not be normalized nor addressing the "bigger issue". Perhaps you could provide more details and why 8 tables vs 1.

OOOooops: I see DBguy has asked already. And if you expand and get 9 departments or 12, will each get their own table??

Perhaps you could post a graphic of your tables and relationships.
 

JithuAccess

Member
Local time
Today, 05:29
Joined
Mar 3, 2020
Messages
297
What exactly is the rationale for this?


It seems that the basic design may not be normalized nor addressing the "bigger issue". Perhaps you could provide more details and why 8 tables vs 1.
Thanks a lot for your reply.

We want to know how many Correspondences we Sent and Received from and to each 8 Different Departments. For Example in the past week we may received 8 Incoming mails and we have sent 10 Outgoing mail for Department 1. For Department 2 we may received 2 Incoming mails and sent 5 Outgoing Mails. So we need to find exactly how much mails we Sent/Received for each Department

Thanks
 

plog

Banishment Pending
Local time
Today, 06:29
Joined
May 11, 2011
Messages
11,613
There's 12 months in a year, why not 96 tables (12*8)? Actually make it 192 by moving incoming\outgoing to table names as well.

You've decided that you need whole new tables to differentiate data when all you need is fields. When you have multiple tables with the same structure (e.g. 1 per department) you are then using the table name itself as a place to store data that could easily go into a field.

Instead of those 8 tables you create 1 table with the exact same structure and add an additional field to designate what department that record is for.

I fear this is just one example of improperly setting up your data. Can you complete your relationship tool and post a screenshot?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:29
Joined
Oct 29, 2018
Messages
21,358
Because we have 8 different Departments.

Let me try Union Query.

Thanks for your suggestions
Hi. I was on my phone earlier and didn't see the image you posted. I'm afraid that's not a good table for your report either. You might run out of space.
 

JithuAccess

Member
Local time
Today, 05:29
Joined
Mar 3, 2020
Messages
297
Hi. I was on my phone earlier and didn't see the image you posted. I'm afraid that's not a good table for your report either. You might run out of space.
Thanks a lot for your suggestion. I think I need to create a Union Query and this is the only solution. Let me try.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:29
Joined
Oct 29, 2018
Messages
21,358
Thanks a lot for your suggestion. I think I need to create a Union Query and this is the only solution. Let me try.
A UNION query is basically a temporary way to restructure your 8 tables when you should have only one table to begin with.
 

JithuAccess

Member
Local time
Today, 05:29
Joined
Mar 3, 2020
Messages
297
A UNION query is basically a temporary way to restructure your 8 tables when you should have only one table to begin with.

Hello DB Guy,

I have created 1 Select query to get the Total Count starting with 2 Departments and it is working fine. Then I have create a Union Query:


Code:
SELECT [Dept1 Total New] from [Query3] UNION SELECT [Dept2 Total New] from [Query4].

But I am getting Query Result in Rows:

1619191116386.png


I want the Result like:

1619191182737.png


Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:29
Joined
Oct 29, 2018
Messages
21,358
Hello DB Guy,

I have created 1 Select query to get the Total Count starting with 2 Departments and it is working fine. Then I have create a Union Query:

SELECT [Dept1 Total New] from [Query3] UNION SELECT [Dept2 Total New] from [Query4]. But I am getting Query Result in Rows:

View attachment 91039

I want the Result like:

View attachment 91040

Thanks
Yeah, don't worry about that. Keep building your UNION query and let the result to go into rows. As I said earlier, putting them into columns could still be problematic if you have too many departments. However, once you have finished building your UNION query, you can then create a CROSSTAB query to display the result in columns for your report. If you need help, you can post a sample copy of your db with test data.
 

JithuAccess

Member
Local time
Today, 05:29
Joined
Mar 3, 2020
Messages
297
Yeah, don't worry about that. Keep building your UNION query and let the result to go into rows. As I said earlier, putting them into columns could still be problematic if you have too many departments. However, once you have finished building your UNION query, you can then create a CROSSTAB query to display the result in columns for your report. If you need help, you can post a sample copy of your db with test data.

Thanks a lot for your Help.
 

JithuAccess

Member
Local time
Today, 05:29
Joined
Mar 3, 2020
Messages
297
That's understandable. You just need to play with it until it's happy. Although the minimum requirement is 3 fields, there's no rule against using the same field more than once.
Thanks a lot. Let me first create the Union query.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:29
Joined
Feb 19, 2002
Messages
42,981
@JithuAccess
The Union query solves the immediate problem but it would be far better to solve the underlying issue. You should NOT have 8 tables. You should have only 1. You can use a combo to filter the data so that dept 1 users can see only dept 1 data. You can even use login security to always limit Joe to seeing only dept 8 data.

Clearly you are not worried about how to handle adding a new department but you should at least consider the ripple effect this will cause and the number of objects that would need to be changed to accommodate the new dept. If you built an order entry application, would you add a table for each customer? I don't think so. If you created a training application, would you create a new table for each training course? NO.

Fix the problem sooner rather than later.
 

Users who are viewing this thread

Top Bottom