loop though records in a query?

[Ben8765]

Hello,

I have a query called query1.
I also have query2 which is a list of unique records in query1 based on
certain fields.

For each unique record in query2, I would like to select the records in
query1 that match the values in query2. *Then make a union query, a crosstab
query, and then union each set of unique records into one big query (for a
report).*

The union stuff between the *s is not important.

What I want to know is:

Is it possible to loop through records using sql?
I think it is possible to do this through vba, by looping through the
records in query1 and then generating the queries I want through vba.

If it is not possible to do this through sql, then any pointers about the
vba method would be appreciated. It seems it would be simpler if I could just
do it through sql though.

So, again my question is: Is it possible to loop through records using sql
(and then generate queries)?

If necessary, I can provide examples of my data, although it seems difficult
to effectively show tables/queries in text format.

-Ben

 

[KARL DEWEY]

Use this substituting your query and field names --
SELECT [Query1].*
FROM [Query2] INNER JOIN [Query1] ON [Query2].[Field1] = [Query1].[Field1]
AND [Query2].[Field2] = [Query1].[Field2] AND [Query2].[Field3] =
[Query1].[Field3] ... etc ...;

 

[John W. Vinson]

So, again my question is: Is it possible to loop through records using sql
(and then generate queries)?

No.

SQL is a "set based" language, not a procedural language. It has no "go to" or
"loop" or such constructs; a SQL query applies logic in an "all at once"
paradigm.

That said, no looping is needed to accomplish your desired result, just
(apparently) some appropriate joins.

 

[Ben8765]

Hi,

I think i have already tried this. Won't this just select the records in
query1 that are related to the unique records in query2?

What I want to do is (in SQL?):

For each record in query2 (eample record: field1=88, field2=2, field3='GG,
DS')
select records in query1 where field1=88, field2=2, field3='GG, DS'
do a union query on these records
do a crosstab query on these records
append these records to a master crosstab query(for a report)
Next (next record in query2)

Is it possible to loop through the records in a query using SQL?

Thanks very much,

-Ben

Use this substituting your query and field names --
SELECT [Query1].*
FROM [Query2] INNER JOIN [Query1] ON [Query2].[Field1] = [Query1].[Field1]
AND [Query2].[Field2] = [Query1].[Field2] AND [Query2].[Field3] =
[Query1].[Field3] ... etc ...;

--
Build a little, test a little.

Hello,

I have a query called query1.
I also have query2 which is a list of unique records in query1 based on
certain fields.

For each unique record in query2, I would like to select the records in
query1 that match the values in query2. *Then make a union query, a crosstab
query, and then union each set of unique records into one big query (for a
report).*

The union stuff between the *s is not important.

What I want to know is:

Is it possible to loop through records using sql?
I think it is possible to do this through vba, by looping through the
records in query1 and then generating the queries I want through vba.

If it is not possible to do this through sql, then any pointers about the
vba method would be appreciated. It seems it would be simpler if I could just
do it through sql though.

So, again my question is: Is it possible to loop through records using sql
(and then generate queries)?

If necessary, I can provide examples of my data, although it seems difficult
to effectively show tables/queries in text format.

-Ben

 

[Ben8765]

Hi,

I have created the query with the joins like you suggested.


Now what I need to do
----------------------------
For each group of records in query1 that are related to the record in query2:
Do a union query
Do a crosstab query
Union(?) the crosstab to a master crosstab query

I have already done these queries as individual queries. But i need to do
this process for each group of records in query1 that are related to the
unique record in query2.


I should explain why I am doing this in case there is a better way:
------------------------------------------------------------------------
I have a crosstab query which is based on a union query (union transposes
the records). This crosstab uses the "Total: First" option (because there are
non-numeric characters in the crosstab values). Because I am using the
"Total: First" option, I need to use a where clause to tell the queries to
use a specified group of records in query1. I want to make crosstabs of all
of the croups of records, not just the specified ones.
I need a master crosstab to include crosstab rows for all of the groups of
records in query1 that are related to a unique record in query2.

I am doing this so I can make a report that shows each crosstab - grouped by
"Study Site".

-Ben

Use this substituting your query and field names --
SELECT [Query1].*
FROM [Query2] INNER JOIN [Query1] ON [Query2].[Field1] = [Query1].[Field1]
AND [Query2].[Field2] = [Query1].[Field2] AND [Query2].[Field3] =
[Query1].[Field3] ... etc ...;

--
Build a little, test a little.

Hello,

I have a query called query1.
I also have query2 which is a list of unique records in query1 based on
certain fields.

For each unique record in query2, I would like to select the records in
query1 that match the values in query2. *Then make a union query, a crosstab
query, and then union each set of unique records into one big query (for a
report).*

The union stuff between the *s is not important.

What I want to know is:

Is it possible to loop through records using sql?
I think it is possible to do this through vba, by looping through the
records in query1 and then generating the queries I want through vba.

If it is not possible to do this through sql, then any pointers about the
vba method would be appreciated. It seems it would be simpler if I could just
do it through sql though.

So, again my question is: Is it possible to loop through records using sql
(and then generate queries)?

If necessary, I can provide examples of my data, although it seems difficult
to effectively show tables/queries in text format.

-Ben

 

[KARL DEWEY]

Now what I need to do
----------------------------
For each group of records in query1 that are related to the record in query2:
Do a union query ------------ What are you going to union?????????
Do a crosstab query ---------???
Union(?) the crosstab to a master crosstab query ----???

I did not follow your explaination of what you are trying to accomplish.
Maybe if you posted examples for each step of the process I might understand.

--
Build a little, test a little.

Hi,

I have created the query with the joins like you suggested.


Now what I need to do
----------------------------
For each group of records in query1 that are related to the record in query2:
Do a union query
Do a crosstab query
Union(?) the crosstab to a master crosstab query

I have already done these queries as individual queries. But i need to do
this process for each group of records in query1 that are related to the
unique record in query2.


I should explain why I am doing this in case there is a better way:
------------------------------------------------------------------------
I have a crosstab query which is based on a union query (union transposes
the records). This crosstab uses the "Total: First" option (because there are
non-numeric characters in the crosstab values). Because I am using the
"Total: First" option, I need to use a where clause to tell the queries to
use a specified group of records in query1. I want to make crosstabs of all
of the croups of records, not just the specified ones.
I need a master crosstab to include crosstab rows for all of the groups of
records in query1 that are related to a unique record in query2.

I am doing this so I can make a report that shows each crosstab - grouped by
"Study Site".

-Ben

Use this substituting your query and field names --
SELECT [Query1].*
FROM [Query2] INNER JOIN [Query1] ON [Query2].[Field1] = [Query1].[Field1]
AND [Query2].[Field2] = [Query1].[Field2] AND [Query2].[Field3] =
[Query1].[Field3] ... etc ...;

--
Build a little, test a little.

Hello,

I have a query called query1.
I also have query2 which is a list of unique records in query1 based on
certain fields.

For each unique record in query2, I would like to select the records in
query1 that match the values in query2. *Then make a union query, a crosstab
query, and then union each set of unique records into one big query (for a
report).*

The union stuff between the *s is not important.

What I want to know is:

Is it possible to loop through records using sql?
I think it is possible to do this through vba, by looping through the
records in query1 and then generating the queries I want through vba.

If it is not possible to do this through sql, then any pointers about the
vba method would be appreciated. It seems it would be simpler if I could just
do it through sql though.

So, again my question is: Is it possible to loop through records using sql
(and then generate queries)?

If necessary, I can provide examples of my data, although it seems difficult
to effectively show tables/queries in text format.

-Ben