speeding up queries

[frustratedwithaccess]

Hello All,



I am somewhat new to access and struggling with some issues at the moment. Specifically I am attempting to join a large number of tables in a query. After I make the necessary joins the query is running extremely slow to the point it puts my computer in a non-responsive mode and I cannot view the data in "data sheet view".

To describe the database, I have a master table "SampleDates" that has 12,000 records with several fields. The two main fields I am attempting to use for joins (and that are common across all of the other other tables) are "ClimateID" and "Dates". The other tables are weather elements from 1 through to 27. Each weather table is named "element1", "element2" "element3" etc. and have approximately 120,000 records each. Each element table contains differing dates and climateID's and slightly different numbers of records. So basically, in the query I am attempting to draw out all the weather data from each element table that matches the dates and climate ID's in the master table "SampleDates". As I say when I create the joins this bogs down my computer. The steps I have take to remedy this are:

- compact and repair

- increasing my virtual space on my computer

- adding a primary key, which is an autonumbered ID field, to each table

- creating relationships between the SampleDates table and all of the elements. However due to the nature of my data
(i.e. climateID and dates have duplicate data unless used with some of the other fields in both element tables and SampleDate table) I can't enforce referential integrity so have only used right outer joins...27 joins from the SampleDate table to elements tables for both climateID and date.

- created indexes for the appropriate fields in each table

However, none of this is speeding up my query by any means. Any other suggestions??

[alansidman]

Joining 27 tables seems like a lot. It makes me wonder if your schema maybe should be different.
Instead of 27 tables, one table with the following fields

recordID (PK)
DatesField
ClimateIDs (FK)
ElementType
DataField

[frustratedwithaccess]

Thanks for the response. Any help is much appreciated! In terms of creating one table from the 27 tables, I am struggling with this as well. Each element table has similar data however each differs in number of dates, and the number of climate ID's. As well each element has a unique weather field. For example, element1 is mm of rain, element2 is cm of snow, element3 is daily bright sunshine etc. How do I join these tables?

What I have done so far is create a complete list of dates that encompass every date from all element tables. Basically a table with unique dates ranging from January 1, 1970 to December 31, 2010. With this table I was hoping to join all the elements by date, and list all the data from each data table side by side. However, as soon as I get past the first join, records start duplicating. Any ideas how to avoid the duplicate data. (I have tried using the distinct command but without success. The query returns the same number of records with duplicates).

[June7]

If each date is in each of the 27 tables only once, then should not be 'duplicate' in the query. I suspect there is another key that needs to be considered, such as a project ID. In which case would need a 'master' table of all possible date/project pairs and the query would be a compound key join.

If you want to provide db for analysis, follow instructions at bottom of my post.

[frustratedwithaccess]

In each element table there is the possibility that dates are repeated several times. What makes them unique would be the associated climate id.

[frustratedwithaccess]

Each element table has the following fields ID (autonumbered PK), ClimateID (indexed as yes, duplicates OK), Date (indexed as duplicates OK), and one weather field as discussed above.

[June7]

Then there is your pair and compound key.

I'm with alan, normalized structure should be best, although I should be the last one to say that. I have a laboratory db that is very non-normalized.

Still, if you want to provide db, will look at.

[frustratedwithaccess]

my dates table consists of an ID field (autonumbered) and dates (indexed yes, no duplicates...PK). There are unique dates from January 1, 1970 to December 31, 2010 so all I need to do is add in each all contents of element table as it corresponds to each date. i.e. I want my dates table to draw out all the dates and climate id's from each table without duplicates.

[frustratedwithaccess]

Database1.zip

This is a reduced version of my database. As I say, I have indexed the appropriate fields and created relationships however not sure if I have done this correctly or in the most efficient manner. In any case, I am trying to join "Sheet1" (dates) to each fields from each element table. The original table for "sheet1" is used here with no changes, however, to make the 2mb size for posting, I have reduced the number of records in each element table and only included 5 of the tables. The original number of records in each table ranges from approximately 120000 to 180000, and as seen in the attached database, each element table differs in the records of both climate id's and dates. The entire database also has 27 element tables. When making the joins for my query, duplicate records are created as soon as I get past joining element1 to the dates table, Sheet1 (i.e. as soon as I join element 2, duplicates are created and the number of records skyrockets).

Anyway, I am hoping someone can give me some suggestions for creating one streamlined table out of my dates table and the 27 element tables.

[ssanfu]

Having a PK field as Text type is slower than a Long Integer type. See
http://www.fmsinc.com/free/newtips/primarykey.asp
http://www.utteraccess.com/wiki/index.php/Autonumbers

"Date" is a reserved word and shouldn't be used as an object name (I fixed this)
Here is a list of reserved words:http://www.allenbrowne.com/AppIssueBadWord.html

Shouldn't use spaces, punctuation or special characters (exception is the underscore) in object names. (I fixed this)


I agree with Alan and June about normalizing tables...... so I did.
I also set up an index to keep from having duplicate Date and ClimateID entries.

Seems to be very fast when opening Query1.....

[frustratedwithaccess]

Thanks so much for the help with my database, and the links! It is much appreciated.

[frustratedwithaccess]

The only thing I am struggling to understand is the date change in each element field. I understand the reserved word part, but why was the format changed from "Date/Time" to "Number". As well, I have tried this in the full version of my db and I end up getting a string of numbers, example: Saturday, January 01, 1972 becomes 26299

[ssanfu]

I'm not sure what you mean.....

I changed the table "Sheet1" to have the autonumber field "ID" as the primary key. In each of the 5 "Element" tables I added a FK field named like "E1Date_FK". The "E1" prefix is for table "Element1", "E2" is for "Element2", etc. The prefix is to know which field is in which table.

I deleted the Date field in each of the Element tables because it was redundant data.

Look at the relationship window. It shows how/what fields the tables are linked (related).

[June7]

A normalized structure would be like:

ClimateID	DateRead	Element	Data	Flag
6012198	1/1/1972	MaxAir	-3.3
6012198	1/1/1972	MeanAir	-6.1
6012198	1/1/1972	MinAir	-8.9
6012198	1/2/1972	MaxAir	-5
6012198	1/2/1972	MeanAir	-10.6
6012198	1/2/1972	MinAir	-16.1

This would be 1 table instead of 27 tables and no master dates table. The only difference in fields for the 5 tables provided was the Flag field. Are there other differences between the 27 tables?

I used a UNION query to generate the combined dataset then used the UNION to make a new table. (Or manually create the new table and run 27 INSERT SELECT queries.) However, producing the side-by-side output would then require a CROSSTAB query.

Are you still collecting data? I think your next concern is exceeding the Access 2GB size limit.