Using a total count in a query

[Guest]

I have two tables that I want to merge. But I want a fixed number of entries
in the final result, let's say 100. I want to pull all of the entries from
the first table -- let's say there are 10. I want to pull the remaining items
randomly from the second table; in this case I will need 90, but that number
will vary. I know I can count the number of entries in a table, but I'm not
sure if I can use the result in a SELECT TOP from the second table. I tried
the following:

SELECT TOP (100-[Total Required].SumOfRequired) FROM...

where the query doing the totalling is called Total Required, and the header
displayed when you run the query is called SumOfRequired. This didn't work;
it complains about my syntax.

Is what I'm trying to do doable? It doesn't seem like a completely bizarre
thing. Or am I going about it the wrong way?

By the way, I can handle the randomization; don't worry about that. It's the
variable in the SELECT TOP that has me troubled.

TIA!

 

[John Vinson]

Is what I'm trying to do doable? It doesn't seem like a completely bizarre
thing. Or am I going about it the wrong way?

AFAIK the only way to do this is to construct the SQL string including
the (variable) TOP property in VBA code.

John W. Vinson[MVP]

 

[Allen Browne]

You can certainly take the approach you suggest, if you build a string and
execute it.

Dim strSql As String
strSql = "INSERT INTO Table3 SELECT TOP " & _
100 - DCount("*", "Table1") & " Table2.* FROM Table2 " & _
"ORDER BY Rnd(Table2.ID);"
dbEngine(0)(0).Execute strSql, dbFailOnError

 

[Guest]

OK, thanks... I was afraid of that... Adds a whole new level of
messiness...

 

[Guest]

Yeah, I was trying to do it without going into VBA. I managed it in VBA, but
I haven't messed with the object model in a long while, and there's a fair
bit of a reacquaintance time with the unintuitive (and horribly documented)
object model. (How the hell do you simply open a recordset from a query from
the open database? A half hour -- just for that -- and many hairs-pulled-out
later, oh yeah, set the DAO reference and use CurrentDb; NOT easy to find,
NOT intuitive, had to go to a book I bought years ago). But sorry, I digress
and rant...

By the way, you have a very compact way of doing it... In none of the
documentation did I see anything that looked like that... I may do that to be
more efficient...

Thanks much,
BnB

You can certainly take the approach you suggest, if you build a string and
execute it.

Dim strSql As String
strSql = "INSERT INTO Table3 SELECT TOP " & _
100 - DCount("*", "Table1") & " Table2.* FROM Table2 " & _
"ORDER BY Rnd(Table2.ID);"
dbEngine(0)(0).Execute strSql, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have two tables that I want to merge. But I want a fixed number of
entries
in the final result, let's say 100. I want to pull all of the entries from
the first table -- let's say there are 10. I want to pull the remaining
items
randomly from the second table; in this case I will need 90, but that
number
will vary. I know I can count the number of entries in a table, but I'm
not
sure if I can use the result in a SELECT TOP from the second table. I
tried
the following:

SELECT TOP (100-[Total Required].SumOfRequired) FROM...

where the query doing the totalling is called Total Required, and the
header
displayed when you run the query is called SumOfRequired. This didn't
work;
it complains about my syntax.

Is what I'm trying to do doable? It doesn't seem like a completely bizarre
thing. Or am I going about it the wrong way?

By the way, I can handle the randomization; don't worry about that. It's
the
variable in the SELECT TOP that has me troubled.

TIA!

 

[Allen Browne]

Here's a quick refresher on the DAO object model:
http://allenbrowne.com/ser-04.html

The little graphic at the end of that acticle was actually my screen
background back in the Access 2 days.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yeah, I was trying to do it without going into VBA. I managed it in VBA,
but
I haven't messed with the object model in a long while, and there's a fair
bit of a reacquaintance time with the unintuitive (and horribly
documented)
object model. (How the hell do you simply open a recordset from a query
from
the open database? A half hour -- just for that -- and many
hairs-pulled-out
later, oh yeah, set the DAO reference and use CurrentDb; NOT easy to find,
NOT intuitive, had to go to a book I bought years ago). But sorry, I
digress
and rant...

By the way, you have a very compact way of doing it... In none of the
documentation did I see anything that looked like that... I may do that to
be
more efficient...

Thanks much,
BnB

You can certainly take the approach you suggest, if you build a string
and
execute it.

Dim strSql As String
strSql = "INSERT INTO Table3 SELECT TOP " & _
100 - DCount("*", "Table1") & " Table2.* FROM Table2 " & _
"ORDER BY Rnd(Table2.ID);"
dbEngine(0)(0).Execute strSql, dbFailOnError

I have two tables that I want to merge. But I want a fixed number of
entries
in the final result, let's say 100. I want to pull all of the entries
from
the first table -- let's say there are 10. I want to pull the remaining
items
randomly from the second table; in this case I will need 90, but that
number
will vary. I know I can count the number of entries in a table, but I'm
not
sure if I can use the result in a SELECT TOP from the second table. I
tried
the following:

SELECT TOP (100-[Total Required].SumOfRequired) FROM...

where the query doing the totalling is called Total Required, and the
header
displayed when you run the query is called SumOfRequired. This didn't
work;
it complains about my syntax.

Is what I'm trying to do doable? It doesn't seem like a completely
bizarre
thing. Or am I going about it the wrong way?

By the way, I can handle the randomization; don't worry about that.
It's
the
variable in the SELECT TOP that has me troubled.

TIA!