Access Database Users Question

[Track Collector]

I have a large access database and I would like extract from it a number of records and place them in a new and separate database.

The current large database contains something like 300,000 individual records, and each record has 200+ fields associated with it.

The records I want to extract from the current database will be associated with a unique (compound) criteria of database fields 1, 2, and 3.

For example, one (compound) combination set might be something like:
Field 1 = "x" AND Field 2 = "y" AND Field 3 = "z"

A second and unique (compound) combination set might be something like:
Field 1 = "ab" AND Field 2 = "p" AND Field 3 = "m"

A third unique (compound) combination set might be something like:
Field 1 = "rp" AND Field 2 = "R7" and Field 3 = "TAP"

All told, there are going to be 300 to 400 different (compound) criteria sets.

I'm not sure a filter would allow such a high number of (compound) criteria sets to be typed in, and I'm not familiar with things like virtual basic.

Any suggestions/ideas on how to quickly and easily achieve the above goal? I am using Access 2010.

Thanks,


Chris

[spiketoo]

Hehe - you'll probably get diff answers here. Give five developers specs and you'l get five different solutions.

Even with Visual Basic, it wouldn't necessarily be quick. But just using query design, you could select your criteria and create a Make Table. Now I'm not sure how many rows you can have in the Criteria section - I know you can insert add'l rows and I think the WHERE clause that gets created has a max of 99 AND stmts but frankly, I've never maxed out so YMMV.

If 99 is the max, just create a second and third query that appends the add'l records to the table you created above.

Next...

[Red Knave]

Well let me be the 2nd answer :)
and qualify it by saying I don't use Access

I think the quickest and easiest method is to loop through your db with vba and use something like
Dim the table and a recordset
get first record
do until eof
If (field1 = "a" and field2 = "b" and field3 = "c") or
(field1 = "d" and field2 = "e" and field3 = "f") or
(field1 = "g" and field2 = "g" and field3 = "i") or ...
(field1 = "x" and field2 = "y" and field3 = "z") then
update new recordset
get next record
loop
end if
If (field1 = "aa" and field2 = "bb" and field3 = "cc") or
(field1 = "dd" and field2 = "ee" and field3 = "ff") or
(field1 = "gg" and field2 = "hh" and field3 = "ii") or ...
(field1 = "xx" and field2 = "yy" and field3 = "zz") then
update new recordset
get next record
loop
end if

and repeat for all the Ifs/end ifs to get all the conditions
loop

I'm not sure how big an If can be but you'll find out if it's too big :).
Check for code samples on StackOverflow or msdn. The hard part will be writing all the conditions. Put the most likely sets of conditions at the top and the least likely at the bottom just to speed up the retrieval as much as possible.
I guess you could use select where but I do know that where has a limit and it's less than 99 (maybe 32?) but it's still another option.
Also, you'll definitely want to test your new table for correctness.