Speed Is An Issue--Is Something More Robust Needed?

[mpoor]

I have a recently compacted & repaired 236kb Access file. I have four tables linked (coming from other copacted databases). I created a union query which contains the following statement:

SELECT [2 Record_id], [2 Email_addr], [2 Male], [2 State]
FROM 20020408EFXMainFileA
WHERE [2 Male]="1"

UNION SELECT [2 Record_id], [2 Email_addr], [2 Male], [2 State]
FROM 20020408EFXMainFileB
WHERE [2 Male]= "1";

The query takes almost 10 minutes to run and generates 1,231,201 records. I need to revise the sql to include 7 or 8 more statements but I fear, if I do, that the thing will take forever to run. Is there some way to speed it up? Am I doing something wrong? Is my server/PC robust enough for the task. I've been working off of a dedicated server but I actually have my choice of two systems to choose from. See my server/pc specs below:

SERVER
O/S: WIN2K Member Server
Memory: 256 mb
CPU: 650 mhz
Hard Drive: NTFS w/C:Master & D:Slave
C: 2 gig left
D: 6 gig available
Software: Access2K (only have this one application on the system)

PC
O/S: Win98 SE
Memory: 384 mb
CPU: 650 mhz
Hard Drive: Fat32
5 gig available
Software: Office2K and others

 

[tlbroadbent]

You should post your question in an Access forum unless you are linking SQL Server tables into your Access database.

I recommend forum701 - "Microsoft: Access Queries and JET SQL." Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[mpoor]

Sorry 'bout that. Thanks Terry, I'll post this in an Access forum.