DB freezing or closing when running query

[canberry]

Hey Everybody,

My DB been running fine until i added a few new queries. It either closes the DB or take a long time that u would take probably a week. My DB pretty basic. 7 Tables, 26 Queries, 30000kb. It behaves like this when i run the query to create a table report. As i said, The create table query ran well even when i had 3 of those. But after creating the 4th, i started experiencing this issue. Any suggestions ??

 

[HiTechCoach]

What version of Access?

Is your database split?

Have you compacted your database(s) lately?

 

[MSAccessRookie]

Hey Everybody,

My DB been running fine until i added a few new queries. It either closes the DB or take a long time that u would take probably a week. My DB pretty basic. 7 Tables, 26 Queries, 30000kb. It behaves like this when i run the query to create a table report. As i said, The create table query ran well even when i had 3 of those. But after creating the 4th, i started experiencing this issue. Any suggestions ??

Can you show these queries?

 

[canberry]

Is my database split ? Not sure what u mean by that
Have i compacted the database ? Not sure i know how to

Could you explain please even though i am thinking its the query thats causing the problem

You want me to post the query or the DB?

 

[boblarson]

Is my database split ? Not sure what u mean by that

http://members.shaw.ca/AlbertKallal/Articles/split/index.htm

Have i compacted the database ? Not sure i know how to

If on Access 2007:
http://www.btabdevelopment.com/main/QuickTutorials/A2K7HowtoCompactandRepair/tabid/112/Default.aspx

You want me to post the query or the DB?

Yes, that would be good.

 

[canberry]

i will read the info on those links. I tried attaching the file but it too big. Even when i zip it, its still 3500kb

 

[boblarson]

i will read the info on those links. I tried attaching the file but it too big. Even when i zip it, its still 3500kb

Did you do a "compact and repair" before zipping it?

 

[canberry]

Waw. Compacting it reallt does cut down the size considerably.

How this BD operates is that i load in data to fdReport
2. Link it to 2 feilds in another table and create a table called "everthing" from the query
3. "Query1" then does some calculation on table "everthing"
4. "Query2" then does some further calculations on "Query1"
5. "Query3" (with a country name before it) produces my final table.

This been working ok until i added the 3rd country. Everytime new data is added for a country, the calculations in 3 query has to change . Thus i will always have the name of a country in front of 3 queries to give me different calculations.

 

[gemma-the-husky]

are these new queries

there may be some design issue giving a cross-product cartesian join that might then take a long while to run.

 

[MSAccessRookie]

The first thing I thought of when I looked at the tables (before even looking at any queries) was NORMALIZE, NORMALIZE, NORMALIZE! At least part of the reason for the issues here is the way that the data needs to be accessed in the current design. For instance, the table Everyhitng contains fields for Rates, MTD, YTD, as well as others, for five different types of credit cards. Also, consider what will happen when one (or more) new credit card(s) are approved for usage. The program queries and tables become obsolete in a hurry.

Reevaluation of the design could go a long way to making things better all the way around.

 

[boblarson]

See here for more about that:
http://www.accessmvp.com/strive4peace/Access_Basics_Crystal_080113_Chapter_03.pdf

 

[canberry]

Hey guys, i did my reading.
The question is, how should i apply it to my DB?
Its hard to make the feilds i use to connect the table primary keys because there are instances where more than one entry contain the same link feild (merchantName or MerchCif).
Could you all give me some heads up on what i can and should change.

fdReport is what is loaded with new data and also the where everything starts from. This table is imported straight from an excel sheet thats why i have those long feild names. If i change them, you see my issue when i ready to load new data. The feild names wouldnt match..