473,387 Members | 3,787 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 software developers and data experts.

Create a where clause based on query results

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!

Jan 16 '07 #1
8 3453
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!
Jan 16 '07 #2
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!
Jan 16 '07 #3
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!
Jan 16 '07 #4
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.

Jan 16 '07 #5
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" <manning...@gmail.comwrote:
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!
Jan 16 '07 #6
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..
Jan 16 '07 #7
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..
Jan 16 '07 #8
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?
Jan 17 '07 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Thomas Jerkins | last post by:
When I write a create table SQL statement I want to add some information about the column heading of each column. How does the exact syntax for the create table look like (which includes this column...
3
by: A.V.C. | last post by:
Hello, I found members of this group very helpful for my last queries. Have one problem with CASE. I can use the column name alias in Order By Clause but unable to use it in WHERE CLAUSE. PLS...
6
by: Bruce | last post by:
I want to create a new table based on an existing table, but I don't want the tables to have any enforced relationship. Is this possible without having to do a CREATE TABLE and an INSERT? ...
3
by: Peter Phelps | last post by:
My problem is as follows: I need automatically iterate through a single field in a table and use the values in the field to create an in-statement. Currently, the character limitation in the...
8
by: John Welch | last post by:
I have a command button with the following code: DoCmd.OpenForm "frmSearchAssignments", , , "SearchAssignmentID = 1" (SearchAssignmentID is the PK, auto number) When it runs, the form opens but...
2
by: David Smithz | last post by:
Hi, If you run a query which has a WHERE statement in which has a few possibilities separated OR statements, e.g. Select * from table where (Afield = 2) OR (Bfield = 2) OR (Cfield = 2) In...
2
by: Jim.Mueksch | last post by:
I am having a problem with using calculated values in a WHERE clause. My query is below. DB2 gives me this error message: Error: SQL0206N "APPRAISAL_LESS_PRICE" is not valid in the context where...
9
by: Yitzak | last post by:
Hi spent a few hours on this one wrote a query that joined on results of 2 other queries. Qry3 using Qry1 and Qry2 When I used Qry1.FasText <cstr(Qry2.FasInteger) in the where clause - got...
1
by: csolomon | last post by:
Hello: I am using two queries to get one result set. The issue is, I return no data when I combine them into one query. I have listed both queries, as well as the 3rd query that shows them...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.