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