Re: [MSAccessProfessionals] Union Query vs. Append query

Thank You Duane. 

We were told years ago that we needed to get rid of Access databases because they are problematic and cause security issues. We laughed because the university offers the application and their security defense was shot down when they said that macros can bring down the network with a virus. I flatly told them that macros are in every office application in the suite, so they turned beet red and shut up. I honestly think they did not know that. This is our IT department. 

I did not think through the fact that the union query would have to be run every time the report was run. makes sense. 

The work from home has switched my strategy to do all of the heavy lifting and processes for the users but with the speed reduction at home forces me to put it on the users when they need it. 

Thank You

Jim Wagner

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Duane Hookom <duanehookom@hotmail.com>
Sent: Thursday, July 16, 2020 9:16 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Union Query vs. Append query

 

Okay, I'll not question the process. The append method runs just once while a union query runs every time you run a report or query against the data. I typically prefer to automate (as much as possible) and run the append. If you choose to use a union query, make sure you use UNION ALL rather than just UNION.

Regards,

Duane 

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Jim Wagner <josephwagner2@outlook.com>
Sent: Thursday, July 16, 2020 10:53 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Union Query vs. Append query

 

Duane,

the data is stored in PeopleSoft. I download the tables from Hyperion. I do not think I explained my process very well.

I download about 20 tables from Hyperion each day and then export them to Excel. 

Then a process i created deletes all of the records in each of the tables from the previous day.

then a process appends the records to the now empty tables with the current days data

There are some tables from the Hyperion tables that are very large and take forever to process and export to excel and some tables are larger than the limits of Excel. 

So I split them with a filter based on a date. That way i do not spend an entire day doing this process. So the smaller Hyperion table is exported to excel and then imported into a database. The smaller table is then sent to several databases for specific users. 

So in the user database, the process is run to combine the current day table  with records >= to a certain date to the table from the previous day minus the >= certain date after the delete query runs and deletes those records. 

Because of the fear of upper management folder permissions were created and not every user has access to every folder on the network. So we had to create databases for specific needs in specific folders. Hence the need to send the table to a different database. 

Because of the work from home thing, I am trying to find more efficient ways to process the information. Do not ask about SQL. I have and they gave me the lamest excuse that it was not secure. I laughed also. And I work at the largest research university in the US. 

So my original question was to see what is more efficient union query or append query between these tables.  

Thank You

Jim Wagner

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Duane Hookom <duanehookom@hotmail.com>
Sent: Thursday, July 16, 2020 8:22 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Union Query vs. Append query

 

Hi Jim,

Are you suggesting someone other than you performs the daily update? I have several similar applications that do this every day. Most of my historical data can be edited so I need to refresh at least 10 days to capture updates in the source data. Most of my data refreshes are automated using SQL server functionality however at least one for COVID production reporting is still manual at this point.

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Jim Wagner <josephwagner2@outlook.com>
Sent: Thursday, July 16, 2020 9:09 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Union Query vs. Append query

 

Duane,

Because of folder permissions across the network, I am not allowed to link the spreadsheet. Not everyone has access to my folder.

Jim Wagner

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Duane Hookom <duanehookom@hotmail.com>
Sent: Wednesday, July 15, 2020 6:32 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Union Query vs. Append query

 

If historic records don't change, you seem to be wasting time deleting and appending potentially thousands of records. This only takes time and produces bloat. 

You stated "exporting them to excel and then importing them". Apparently you have an Excel file so I'm not sure why this file can't be linked into your Access app. I would create an append query that appends any records from the Excel file (or import) where the date is greater than the maximum date value in your final production table.

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Jim Wagner <josephwagner2@outlook.com>
Sent: Wednesday, July 15, 2020 6:34 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Union Query vs. Append query

 

Duane,

Historic records do not change. 

I do not use the >=Date()-3 because of consistency from Hyperion and the database queries I feel should look the same. 

Jim

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Duane Hookom <duanehookom@hotmail.com>
Sent: Wednesday, July 15, 2020 3:57 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Union Query vs. Append query

 

You didn't answer if your historic records change. Tell us why you can't delete and append where datefield >= Date()-3

Sent from my mobile

On Jul 15, 2020, at 2:41 PM, Jim Wagner <josephwagner2@outlook.com> wrote:



Duane,

If the table gets large quickly, i change the date cutoff anytime i need to. but typically i change it at fiscal year end. it helps me remember when to change it. but sometimes i change it at the end of the year. This is in hyperion that i make the change. then in the delete query, i change the date to match the hyperion query. That way every day the table gets refreshed with new data in hyperion and then gets uploaded into the table in the database using the delete and append query. 

So the delete query deletes the records from >=1/1/2020 for example from the main table with 295K records. 

then the table that is created from the daily hyperion results is used in the database to append the data >=1/1/2020 to the main table

unfortunately i am unable to link to the excel file. they do not like to allow people access to certain folders where the data resides. that is another chapter that i do not even want to explain.  

 does that help Duane? 

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Duane Hookom <duanehookom@hotmail.com>
Sent: Wednesday, July 15, 2020 12:14 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Union Query vs. Append query

 

Jim,

You have never stated whether historic records are updated which would require refreshing data back a certain numbers of days. If historic records aren't updated, I would probably link to the Excel file and import only the new records using an append query.

Regards,

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Jim Wagner <josephwagner2@outlook.com>
Sent: Wednesday, July 15, 2020 1:53 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Union Query vs. Append query

 

Sean,

I have been doing it the way i described for a very long time. With Working from home, and the upload speeds, i am looking for ways to speed up the processes for the users. Being more efficient is my goal

Jim Wagner

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Sean Cooper via groups.io <smcjb=yahoo.com@groups.io>
Sent: Wednesday, July 15, 2020 11:42 AM
To: MSAccessProfessionals@groups.io <msaccessprofessionals@groups.io>; MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Union Query vs. Append query

 

No not at all. Everyday a very large table is imported and then a make table query is run that retrieves the needed records and columns. Hence how I end up with the daily tables. I'd never thought of it before reading your question, but I could probably just append those records to a master record table. Would that be a better / faster way to handle it?

Sean

On Wednesday, July 15, 2020, 01:34:48 PM CDT, Jim Wagner <josephwagner2@outlook.com> wrote:

Sean,

is there a specific need or usage for the individual tables during the time frame between creation of the individual tables and the creating of the monthly tables? I am curious

Jim Wagner

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Sean Cooper via groups.io <smcjb=yahoo.com@groups.io>
Sent: Wednesday, July 15, 2020 10:51 AM
To: msaccessprofessionals@groups.io <msaccessprofessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Union Query vs. Append query

 

Let me ask a related question.

Everyday I add about a 1000 records to my database. I currently import these into a new table and then through a union query combine them with my currrent records. At the end of the month I take all the daily tables and combine them into a monthly table and modify the union query to reflect that. So currently I have a union query that is combining 6 tables of monthly data and approximately 10 days of daily data to create a master data set. Would it be better if I just appended the new data everyday to a single table?

Sean

On Wednesday, July 15, 2020, 12:22:12 PM CDT, Paul Baldy <pbaldy@gmail.com> wrote:

I'm confused, as the two types of queries do different things.  You'd need to use the type that accomplished your goal.

Do you get 295k new records a day, or is that the total number of records in the table?  If the total, that's not really a large number, so you may not need to delete records.

Paul

On Wed, Jul 15, 2020 at 09:43 AM, Jim Wagner wrote:

Hello all,

I download every day records from hyperion and bring them into the database after exporting them to excel and then importing them into the database. because the record count is 295K i delete the records in the database from a certain date and then append the newly downloaded records thus keeping my record count low. 
But I was thinking today. what would be faster to do a union query or an append query?

Thanks for the thoughts

Jim Wagner