Make-Table query takes forever (1 Viewer)

[gsrai31]

Hi there, I have following make-table query within a macro which contains a long list of queries, in Access 2003. Normally when processing small dataset, this query runs reasonably quickly. But when processing large data, this query takes hours and then I often get an error that cannot open Holiday table.
Can someone please help me with some VBA code that I could use to create this table or if there is any other solution that could help me with this issue. Thanks

SELECT tbl.ID, Tbl.EntryDate, tbl.EndDate, Dates.Date AS hol_dte, tbl.Valid, 0 AS Holiday, Dates.Weekday, No AS Sent_Hol, No AS Del_Hol INTO Holiday
FROM tbl, Dates
WHERE (((Dates.Date)>=Int([tbl]![EntryDate]) And (Dates.Date)<=Int([tbl]![EndDate])) AND ((tbl.Valid)=Yes));

 

[Ranman256]

Instead of MAKE table, you should use APPEND.
You should also compact regularly. Making tables over and over can make the db slow over time unless you compact.

 

[The_Doc_Man]

As a side note, avoid use of keywords like "Date" as variable names. Access gets confused and maybe later, so will you. You have a table named Holiday and a field named Holiday, which is going to work based on SQL syntax rules but can lead to confusion as well. Also, if tbl.Valid, Sent_Hol, and Del_Hol are Yes/No fields, use TRUE rather than YES and FALSE rather than NO for the SQL even though SQL might understand it. (The latter is more cosmetic than anything else...)

Now, more to the point of your question...

How big is the "large data" case that gives you this problem? What is the data type and format of the following: Dates.Date, Tbl.EntryDate, tbl.EndDate?

Also, is this a single-file database, a front-end/back-end split with pure Access, or a split with Access front-end and some SQL engine for the back-end?

 

[gsrai31]

Hi Thanks for both responses. I will try your suggestions.
It's a single file standalone database in Access 2003. I use the database to import large text files so i can manipulate these, do some calculations, add some reference columns and export into csv format in a structure format for monthly reports. I delete all the data from all tables each month and then import new monthly text files. So i only use this as tool to manipulate the data with some reference tables. The size of 2 text files is huge, both apx 350 MB each.
Dates.Date, Tbl.EntryDate, tbl.EndDate are data/time data types.

thanks again.

 

[plog]

In my experience 80% of make tables and appends are unnecessary--they're used as training wheels for people who aren't great at visualizing data.

Why are you making this table? And how is it used later on? Why can't you just build a series of queries, load your data and then export one SELECT query? Could your process be tighten up, queries combined and eliminated?

 

[gsrai31]

Hi, thanks for your response. Not sure if I fully understand what you mean by make tables and append queries are used as training wheels for people who aren't great at visualizing data. Anyway, the purpose of this table is to count number of saturdays and sundays and any other bank holidays between EntryDate and EndDate. Then remove these number of days from total number of days from EntryDate to EndDate. I am sure there is a better ways of achieving the result, and probably it is not necessary to make this table. But I am just trying to make it work for time being as this database is designed, rather than re-developing this application in Access. Thanks.

 

[plog]

I've found that a lot of people think tables are real data, but the data in queries are temporal and can't be used. I've taken over many systems where Step 1 was running a make table query and Step 2 was running another make table query and Step 3 was another make table query comprised of the prior 2 made tables. The correct way was to turn Step1 query into a SELECT query, Step 2 into a SELECT query and then rebuild step 3 so that it's a SELECT query using those prior 2 queries. No make tables needed.

That's what I mean by poor visualization. The make tables existed just because they couldn't see that the data inside SELECT queries is just as valid and useable as the make tables they ran.

If all you need is a count of non-work days between two dates, all you need is your existing holidays table and a function:

Function get_NonWorkDays(in_StartDate, in_EndDate) As Integer

I've seen many posts on this forum involving this.

 

[gsrai31]

Ah I understand it now. I will follow your advice when re-developing this database soon. Thank you very much for your help.