HELP! Query wont work now ive put main bulk of data in database

[shapman]

Hi all

I am facing disaster after two months of solid work on this database, the deadline is the morning of the 25th and just when i thought i had got it all working i then put the main bulk of the data into the database and the main search query won't work, it freezes and ends with 'invalid argument', when its the same argument as before when there was less data in it. also the relationships window is acting somewhat strangely, i cant seem to move the tables around within it, this is the same for any databases on my computer (im guessing this could be something to do with my Access on my computer)...

A quick note about the search function. it runs three queries dependent on each other, the master query runs of a sub query which runs off an inititial query (this was found to be a very good way of dealing with null values in the database). two of the the tables it runs off is a one to many relationship with the main table so not all contacts will have that variable, but i don't see why this would affect the query. in short the first query in the sequence searches for a status of a contact, then the second uses these results to search for a sub status then the final query searches for publications contacts want from the sub status query. the three tables it runs from are contacts, status(which includes substatus) and publications.

well, it really is the 11th hour here, quarter past the 11th hour to be honest. short of throwing my computer out the window and running off to a tropical island does anyone have any ideas....????

many many many many thanks in advance....

Shapman

 

[David Eagar]

How was the bulk data incorporated? Was it imported from Excel? If so, check that the data format matches between Excel and Access. I have been caught with this before

 

[Pat Hartman]

Make sure that the tables have appropriate indexes defined to help Jet with the search. You should also try compacting the database to make sure that the query plans get optimized because of the dramatic change in row counts. When you compact a database, Access rebuilds al the tables in primary key order. It also marks all querydefs as "uncompiled". That forces Jet to "recompile" them the first time they are run. One of the things that Jet uses to determine how to process a query is row counts in the tables being joined. Jet may have originally "planned" to do a full table scan because a table contained only a few rows whereas now, it would be more efficient to use an index.