"romantercero" <(E-Mail Removed)> wrote in
message news:FLednTjD4-(E-Mail Removed)
> hi!
>
> Long Time Reader Firs time poster....
>
> I hope some one can help me with this as I have not been able to find
> an an answer anywhere....
>
> Ok, so I want to manipulate data on a table using various SQL queries.
> So I can create some temporary table and then delete them once I have
> my way with them but I thought it would be faster using
> DAO.recordsets. Generating the first one based on a table is no
> problem
>
>
>
> Dim rs1 As DAO.Recordset
> Dim rs2 As DAO.Recordset
>
>
> Set rs1 = CurrentDb.OpenRecordset("SELECT palletid,
> clave,lote,sum(pal) as pallets FROM Disponibles " &
> _
> "GROUP BY palletid, clave,lote,facturacion " & _
> "HAVING (((Facturacion) = " & intCliente
> & ") AND
> ((Sum(disponibles.pal))>0));")
>
>
>
> Now, generating the seccond recordset based on the first one will not
> work. I get Run-Time error 3078 The MSJet DBE cannot find the input
> table or query 'rs2'. Here's the code for that:
>
> [code:1:0a480bf3f2]
> Set rs2 = CurrentDb.OpenRecordset("SELECT palletid,
> avg(pallets) AS PalletCount FROM rs2 " & _
> "GROUP BY palletid;")
> [/code:1:0a480bf3f2]
That would have had to be "FROM rs1" anyway, even if it would work. But
it won't -- see below.
> Also
> [code:1:0a480bf3f2]
> CantidadPallets = DSum("PalletCount",
> "[rs2]")
> [/code:1:0a480bf3f2]
> Will give me the same error. I gather that recordsets cannot be used
> interchangebly with tables or queries :-(
>
> Any thoughs will be greatly apreciated!
No, I'm afraid that won't work. Recordsets exist in memory, but they
are not objects known to the database, so you can't use SQL or a domain
aggregate function to select from one.
If it helps, you can get the same effect by combining the SQL
statements, treating the first as a virtual table. Like this:
Set rs2 = CurrentDb.OpenRecordset( _
"SELECT T.palletid, avg(T.pallets) AS PalletCount " & _
"FROM (" & _
"SELECT palletid, clave, lote, sum(pal) as pallets " & _
"FROM Disponibles " & _
"GROUP BY palletid, clave, lote, facturacion " & _
"HAVING ((Facturacion = " & intCliente & ") AND " & _
"(Sum(disponibles.pal)>0)) " & _
") As T " & _
"GROUP BY T.palletid"
or, building on that, this:
Set rs3 = CurrentDb.OpenRecordset( _
"SELECT Sum(S.PalletCount) As CantidadPallets FROM ("
"SELECT T.palletid, avg(T.pallets) AS PalletCount " & _
"FROM (" & _
"SELECT palletid, clave, lote, sum(pal) as pallets " & _
"FROM Disponibles " & _
"GROUP BY palletid, clave, lote, facturacion " & _
"HAVING ((Facturacion = " & intCliente & ") AND " & _
"(Sum(disponibles.pal)>0)) " & _
") As T " & _
"GROUP BY T.palletid" & _
") As S"
CantidadPallets = rs3!CantidadPallets
rs3.Close
Be aware, though, that these queries are all independent of one another.
You don't need to open recordset rs1 before opening rs2, nor rs2 before
opening rs3. But neither do you get any benefit from doing so -- except
maybe you may get the benefit of some data caching on the part of the
database engine; I don't know about that.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
Similar Threads
