Create a where clause based on query results

[chrisdavis]

I'm trying to filter by query or put those values in a distinct query
in a where clause in some sort of list that it goes through but NOT at
the same time. Example:

ROW1
ROW2
ROW3
ROW4 , etc.

I want to go to the first row, do a WHERE statement, return the
results, then go to the next ROW and do the same thing on each distinct
value without selecting them. I also want to do this in case there are
one or many more rows that are added and I want to make sure I get them
all instead of picking them by hand on the form. I think it may be
some sort of VBA array but I'm not VBA savvy. I have about 15 rows now
that I need to do it for.

Any help would be great and thanks!

[ManningFan]

Why would you want to run a WHERE clause on each distinct record? That
would be the pinnacle of inefficiency.

If you could better explain what you're trying to do, I'm pretty sure
someone here could give you a much more efficient way to do it. You
need to explain what values are in each field and what your WHERE
clause is trying to accomplish.

chrisdavis wrote:

I'm trying to filter by query or put those values in a distinct query
in a where clause in some sort of list that it goes through but NOT at
the same time. Example:

ROW1
ROW2
ROW3
ROW4 , etc.

I want to go to the first row, do a WHERE statement, return the
results, then go to the next ROW and do the same thing on each distinct
value without selecting them. I also want to do this in case there are
one or many more rows that are added and I want to make sure I get them
all instead of picking them by hand on the form. I think it may be
some sort of VBA array but I'm not VBA savvy. I have about 15 rows now
that I need to do it for.

Any help would be great and thanks!

[chrisdavis]

Hi Manningfan, I appreciate that..well I'm trying to take a distinct
query recordset (i.e.,
RATE
PERCENT
DESCRIPTION

I have about 15 of these so far (they may change later or there maybe
more of them eventually). It's a simple "group by" query.

I need to take each of these values and do a where = RATE for one
recordset, PERCENT as another recordset, "DESCRIPTION" as another
recordset..I'm trying to take these individual queries and export them
to Excel sheets with a macro..I can't figure out how to take the string
for each row and create output queries for each returned
row..eventually I will have 15 recordsets..I have a form as a listbox
with the values in there..I can highlight each one and get my recordset
that way but thought there was an easier way to do it with a string of
some sort but do not know how to write it in VBA..I need to take each
of these values in the WHERE clause of the query and create the
recordsets..

Does that make more sense? Sorry I wasn't clear earlier...

ManningFan wrote:

Why would you want to run a WHERE clause on each distinct record? That
would be the pinnacle of inefficiency.

If you could better explain what you're trying to do, I'm pretty sure
someone here could give you a much more efficient way to do it. You
need to explain what values are in each field and what your WHERE
clause is trying to accomplish.

chrisdavis wrote:

I'm trying to filter by query or put those values in a distinct query
in a where clause in some sort of list that it goes through but NOT at
the same time. Example:

ROW1
ROW2
ROW3
ROW4 , etc.

I want to go to the first row, do a WHERE statement, return the
results, then go to the next ROW and do the same thing on each distinct
value without selecting them. I also want to do this in case there are
one or many more rows that are added and I want to make sure I get them
all instead of picking them by hand on the form. I think it may be
some sort of VBA array but I'm not VBA savvy. I have about 15 rows now
that I need to do it for.

Any help would be great and thanks!

[ManningFan]

Chris -
This is still quite confusing, so I'll throw some stuff out there
based on assumptions. If I'm on the right track and you need more
help, let me know. If I'm totally off-base, try to correct my
assumptions.

It sounds like you need a table, we'll call it tblVariables. Create
2 fields in the table; fldVars and fldVals. Your fldVars is going to
be filled with "RATE", "PERCENT", "DESCRIPTION", etc... and the
corresponding fldVals is going to be the values you want to use in your
WHERE statements. i.e.

fldVars fldVals
--------- ----------
RATE 20
PERCENT 25
DESCRIPTION This is a description

Then what you would do is query your table (we'll call that one
tblData) for each field in tblVariables. I think you can use a loop
statement to loop through each record of tblVariables.

set db = currentdb
set rec = db.openrecordset("tblVariables")

do while rec.eof = false

Set strSQL = "Select * from tblData where " & Rec(fldVars) & " = " &
rec(fldVals) & ""
docmd.runsql strSQL

loop

You're going to need to do something with the data between each loop,
perhaps write it to a single table or maybe you need to export it to
excel. You would do that before the "Loop" statement.

NOTE: The above is "air code" in that it's off-the-cuff and not
guaranteed to work verbatim. You may need to adjust the syntax, but
the logic should be correct.

chrisdavis wrote:

Hi Manningfan, I appreciate that..well I'm trying to take a distinct
query recordset (i.e.,
RATE
PERCENT
DESCRIPTION

I have about 15 of these so far (they may change later or there maybe
more of them eventually). It's a simple "group by" query.

I need to take each of these values and do a where = RATE for one
recordset, PERCENT as another recordset, "DESCRIPTION" as another
recordset..I'm trying to take these individual queries and export them
to Excel sheets with a macro..I can't figure out how to take the string
for each row and create output queries for each returned
row..eventually I will have 15 recordsets..I have a form as a listbox
with the values in there..I can highlight each one and get my recordset
that way but thought there was an easier way to do it with a string of
some sort but do not know how to write it in VBA..I need to take each
of these values in the WHERE clause of the query and create the
recordsets..

Does that make more sense? Sorry I wasn't clear earlier...

ManningFan wrote:

Why would you want to run a WHERE clause on each distinct record? That
would be the pinnacle of inefficiency.

If you could better explain what you're trying to do, I'm pretty sure
someone here could give you a much more efficient way to do it. You
need to explain what values are in each field and what your WHERE
clause is trying to accomplish.

chrisdavis wrote:

I'm trying to filter by query or put those values in a distinct query
in a where clause in some sort of list that it goes through but NOT at
the same time. Example:
>
ROW1
ROW2
ROW3
ROW4 , etc.
>
I want to go to the first row, do a WHERE statement, return the
results, then go to the next ROW and do the same thing on each distinct
value without selecting them. I also want to do this in case there are
one or many more rows that are added and I want to make sure I get them
all instead of picking them by hand on the form. I think it may be
some sort of VBA array but I'm not VBA savvy. I have about 15 rows now
that I need to do it for.
>
Any help would be great and thanks!

[ManningFan]

I just noticed in the above code you're going to need to put
"rec.movenext" before the loop, otherwise you'll just keep processing
the same record from tblVariables over and over.

[chrisdavis]

lol I'm definitely not explaining it right..
No, I need those actual names as the WHERE statement

i.e., where RATE
where PERCENT
where DESCRIPTION
where (the next field in the table)

I have a group by query with the names, I just need those actual names
as a string so I end up with 15 spreadsheets, therefore 15 distinct
names..maybe I'm making it too complicated, the query I mean..

Do you know how to write a string to get those TEXT values individually
to end up with 15 spreadsheets and 15 recordsets? If there are 18
names next time, I would expect 18 spreadsheets, with the unique
values..I really appreciate all your help! I am learning as I go..

On Jan 16, 4:30 pm, "ManningFan" <[e-mail]:

Chris -
This is still quite confusing, so I'll throw some stuff out there
based on assumptions. If I'm on the right track and you need more
help, let me know. If I'm totally off-base, try to correct my
assumptions.

It sounds like you need a table, we'll call it tblVariables. Create
2 fields in the table; fldVars and fldVals. Your fldVars is going to
be filled with "RATE", "PERCENT", "DESCRIPTION", etc... and the
corresponding fldVals is going to be the values you want to use in your
WHERE statements. i.e.

fldVars fldVals
--------- ----------
RATE 20
PERCENT 25
DESCRIPTION This is a description

Then what you would do is query your table (we'll call that one
tblData) for each field in tblVariables. I think you can use a loop
statement to loop through each record of tblVariables.

set db = currentdb
set rec = db.openrecordset("tblVariables")

do while rec.eof = false

Set strSQL = "Select * from tblData where " & Rec(fldVars) & " = " &
rec(fldVals) & ""
docmd.runsql strSQL

loop

You're going to need to do something with the data between each loop,
perhaps write it to a single table or maybe you need to export it to
excel. You would do that before the "Loop" statement.

NOTE: The above is "air code" in that it's off-the-cuff and not
guaranteed to work verbatim. You may need to adjust the syntax, but
the logic should be correct.

chrisdavis wrote:

Hi Manningfan, I appreciate that..well I'm trying to take a distinct
query recordset (i.e.,
RATE
PERCENT
DESCRIPTION

I have about 15 of these so far (they may change later or there maybe
more of them eventually). It's a simple "group by" query.

I need to take each of these values and do a where = RATE for one
recordset, PERCENT as another recordset, "DESCRIPTION" as another
recordset..I'm trying to take these individual queries and export them
to Excel sheets with a macro..I can't figure out how to take the string
for each row and create output queries for each returned
row..eventually I will have 15 recordsets..I have a form as a listbox
with the values in there..I can highlight each one and get my recordset
that way but thought there was an easier way to do it with a string of
some sort but do not know how to write it in VBA..I need to take each
of these values in the WHERE clause of the query and create the
recordsets..

Does that make more sense? Sorry I wasn't clear earlier...

ManningFan wrote:

Why would you want to run a WHERE clause on each distinct record? That
would be the pinnacle of inefficiency.

If you could better explain what you're trying to do, I'm pretty sure
someone here could give you a much more efficient way to do it. You
need to explain what values are in each field and what your WHERE
clause is trying to accomplish.

chrisdavis wrote:
I'm trying to filter by query or put those values in a distinct query
in a where clause in some sort of list that it goes through but NOT at
the same time. Example:

ROW1
ROW2
ROW3
ROW4 , etc.

I want to go to the first row, do a WHERE statement, return the
results, then go to the next ROW and do the same thing on each distinct
value without selecting them. I also want to do this in case there are
one or many more rows that are added and I want to make sure I get them
all instead of picking them by hand on the form. I think it may be
some sort of VBA array but I'm not VBA savvy. I have about 15 rows now
that I need to do it for.

Any help would be great and thanks!

[ManningFan]

The code I gave you should do just that.

Set strSQL = "Select * from tblData where " & Rec(fldVars) & " = " &
rec(fldVals) & ""

The first time you run it, strSQL will actually read:
"Select * from tblData where RATE = "20"

Because RATE is the value of fldVars in the first record, and "20" is
the value of fldVals in the first record.

The next time it will read:
"Select * from tblData where PERCENT = "25"

Because PERCENT is the value of fldVars in the second record, and "25"
is the value of fldVals in the second record.

etc...

If you're doing a Group By query and you don't need the values, then
the code would be:
Set strSQL = "SELECT * from tblData GROUP BY " & Rec(fldVars) & ""
Am I totally missing it, or does that put you on track?

chrisdavis wrote:

lol I'm definitely not explaining it right..
No, I need those actual names as the WHERE statement

i.e., where RATE
where PERCENT
where DESCRIPTION
where (the next field in the table)

I have a group by query with the names, I just need those actual names
as a string so I end up with 15 spreadsheets, therefore 15 distinct
names..maybe I'm making it too complicated, the query I mean..

Do you know how to write a string to get those TEXT values individually
to end up with 15 spreadsheets and 15 recordsets? If there are 18
names next time, I would expect 18 spreadsheets, with the unique
values..I really appreciate all your help! I am learning as I go..

[chrisdavis]

That's awesome..I'm getting a runtime error of 3265 error, I'm not sure
why..I have googled it and it says my ADOX isn't active, which it
is..something about the parent catalog..

How do I take that code you gave me and "call it" in the query and get
15 recordsets?

ManningFan wrote:

The code I gave you should do just that.

Set strSQL = "Select * from tblData where " & Rec(fldVars) & " = " &
rec(fldVals) & ""

The first time you run it, strSQL will actually read:
"Select * from tblData where RATE = "20"

Because RATE is the value of fldVars in the first record, and "20" is
the value of fldVals in the first record.

The next time it will read:
"Select * from tblData where PERCENT = "25"

Because PERCENT is the value of fldVars in the second record, and "25"
is the value of fldVals in the second record.

etc...

If you're doing a Group By query and you don't need the values, then
the code would be:
Set strSQL = "SELECT * from tblData GROUP BY " & Rec(fldVars) & ""
Am I totally missing it, or does that put you on track?

chrisdavis wrote:

lol I'm definitely not explaining it right..
No, I need those actual names as the WHERE statement

i.e., where RATE
where PERCENT
where DESCRIPTION
where (the next field in the table)

I have a group by query with the names, I just need those actual names
as a string so I end up with 15 spreadsheets, therefore 15 distinct
names..maybe I'm making it too complicated, the query I mean..

Do you know how to write a string to get those TEXT values individually
to end up with 15 spreadsheets and 15 recordsets? If there are 18
names next time, I would expect 18 spreadsheets, with the unique
values..I really appreciate all your help! I am learning as I go..

[ManningFan]

I'd just put it as the OnClick event of a button on a form somewhere.
Click the button, it spits out 15 (or 18, or however many) recordsets.

You may have to change "Dim db as database" to "Dim db as ADO.database"
and "Dim rec as recordset" to "Dim rec as ADO.recordset" to get rid of
that error. or maybe "ADO' needs to be "DAO", I can never remember...
Also, check your references (Tools -References from a code window) to
make sure you have everything you need.

chrisdavis wrote:

That's awesome..I'm getting a runtime error of 3265 error, I'm not sure
why..I have googled it and it says my ADOX isn't active, which it
is..something about the parent catalog..

How do I take that code you gave me and "call it" in the query and get
15 recordsets?